合 单实例物理DG配置客户端无缝切换的详细过程
- 前言部分
- 导读
- 实验环境介绍
- 相关知识点扫盲
- 实验部分
- 实验目标
- 前提条件
- 配置db_domain 和 listener 及 tnsnames
- dgmgrl中配置broker并启用
- 测试Data Guard Broker 功能
- 实验环境介绍
- 相关知识点扫盲
- 实验部分
- 实验目标
- 前提准备条件
- primary 与 standby 启用flashback database
- 确保broker配置为运行在MAX Availability模式
- 启动observer观察进程
- 配置FastStartFailover
- 配置每个数据库Failover的目标。这一步是决定当数据库出问题后会自动failover目标
- 设定FastStartFailoverThreshold值
- 启用Fast-Start Failover
- 测试Fast-Start Failover 的功能
- Shutdown abort 主库
- 查看告警日志及server窗口
- 在sqlplus手动启动原主库到mount状态,并观察dgmgrl的server状态
- 测试新的环境是否同步
- 重新shutdown abort主库回到最初的oradg11g为主库,oradgphy为备库的状态
- 实验环境介绍
- 实验部分
- 实验目标
- 客户端TAF 配置
- 首先在主库上配置一个TAF的service
- 建立一个存储过程,用于调用service,确保只在主库运行
- 创建1个触发器来确保服务可以运行
- 启动新创建的service
- 在备库查询,确认触发器和存过已经应用到备库
- 客户端tnsnames 配置
- 验证客户端的TAF
- 题外话:Java中的配置
- 使用tnsnames 配置
- 不使用tnsname
- 实验环境介绍
- 实验部分
- 实验目标
- 错误: ORA-16603: Data Guard 中介在配置 ID 中检测到不匹配
- ORA-16652: 已禁用快速启动故障转移目标备用数据库
物理dg配置客户端无缝切换 (1)--Data Guard Broker 的配置
前言部分
导读
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:
① Data Guard Broker 的配置
② Fast-Start Failover 的配置
③ Oracle DataGuard 之客户端TAF 配置
④ 使用DGMGRL 来管理数据库
⑤ 物理dg管理和维护的一些sql
⑥ DataGuard 客户端特级配置
实验环境介绍
项目 | 主库 | dg库 |
---|---|---|
db 类型 | 单实例 | 单实例 |
db version | 11.2.0.3 | 11.2.0.3 |
db 存储 | FS type | FS type |
ORACLE_SID | oradg11g | oradgphy |
db_name | oradg11g | oradg11g |
主机IP地址: | 192.168.59.130 | 192.168.59.130 |
OS版本及kernel版本 | RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64 | RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64 |
OS hostname | rhel6_lhr | rhel6_lhr |
本次课程,研究当主备库发生切换时,如何在主库启动一个service,保证客户端的连接能够继续,而且还能够继续select查询操作,而不管主备库是在哪台服务器上;同时保证新的客户连接没有任何的问题。本课程网络上的例子不多,陈老师花了将近一年的时间人肉搜索,最近才找到,急不可待的要分享给大家。
1、DataGuard的配置(快速)
2、创建service
3、创建触发器
4、主备库切换测试
相关知识点扫盲
oracle Data Guard,以最低成本实现最高的数据保护。在硬件上没有特殊要求,普通PC机即可实现。
简单的来说,Data Guard,就是自动创建和维护生产数据库(或主数据库)的一个或多个事务一致的副本(备用数据库)。如果主数据库不可用(因为故障、维护或者灾难),那么可以激活一个备用数据库并使之承担主数据库的角色。然而,在配置完成Data Guard后,若需要实现主备数据库间的切换,需要在主数据库及备用数据库上分别输入多个命令,切换步骤稍显麻烦。所以,一般情况下,DBA会将整个切换过程编辑成脚本,以便自动运行,进行状态切换。当然,oracle 也提供了工具Data Guard Broker,仅在控制端输入一个命令就能方便实现主备数据库间的切换。
在Data Guard Broker的基础上,配置并启用Fast-Start Failover,就能自动检测发现主机故障,实现主备切换,故障转移。
Data Guard Broker是建立在Data Guard基础上的一个对Data Guard配置,集中管理操作的一个平台。Broker的推出是为了简化DG复杂的管理过程,它最大的作用就是集中化的统一管理。
配置Data Guard Broker使用到的客户端工具是DGMGRL。它是一个命令行管理工具。
实验部分
实验目标
Data Guard Broker 的配置并完成实验。
前提条件
注意前提条件,Data Guard已经成功配置完成。
- 设置primary和standby启动时参数文件为spfile
- 配置DG_BROKER_CONFIG_FILEn
DG_BROKER_CONFIG_FILEn代表2个参数,分别为DG_BROKER_CONFIG_FILE1,DG_BROKER_CONFIG_FILE2,它们是Data Guard Broker的配置文件名。它们都有默认值(windows下是在ORACLE_HOME/database目录下,linux下是在ORACLE_HOME/dbs目录下),所以一般我们可以不进行特别设置。 - 设置listener
在Data Guard Broker环境中,需要在listener.ora静态添加一个service_name为db_unique_name_DGMGRL.db_domain的注册。这个service_name会在DGMGRL重启数据库的时候用到。通过DGMGRL重启数据库时DMON进程会先将数据库关闭,然后DGMGRL用此service_name通过listener连接到数据库,发出启动命令。 - 设置DG_BROKER_START为TRUE
这一步是启动Data Guard Broker monitor(DMON)进程,需要在2个数据库上面都运行下面的命令
alter system set dg_broker_start=true scope=both;
主库操作:
[oracle@rhel6_lhr lhr]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期一 9月 28 10:17:04 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:17:04 SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string oradglg, oradg11g
db_name string oradg11g
db_unique_name string oradg11g
global_names boolean FALSE
instance_name string oradg11g
lock_name_space string
log_file_name_convert string oradglg, oradg11g
processor_group_name string
service_names string oradg11g
10:17:08 SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileoradg11g.o
ra
10:17:14 SQL> show parameter DG_BROKER_CONFIG_FILE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/dr1oradg11g.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/dr2oradg11g.dat
10:20:43 SQL> set line 9999
10:20:44 SQL> col name format a10
10:20:44 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
10:20:44 SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G 2336836 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
已用时间: 00: 00: 00.01
10:20:44 SQL> SELECT d.DBID,
10:20:44 2 d.DB_UNIQUE_NAME,
10:20:44 3 d.FORCE_LOGGING,
10:20:44 4 d.FLASHBACK_ON,
10:20:44 5 d.FS_FAILOVER_STATUS,
10:20:44 6 d.FS_FAILOVER_CURRENT_TARGET,
10:20:44 7 d.FS_FAILOVER_THRESHOLD,
10:20:44 8 d.FS_FAILOVER_OBSERVER_PRESENT,
10:20:44 9 d.FS_FAILOVER_OBSERVER_HOST
10:20:44 10 FROM v$database d;
DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER
---------- ------------------------------ --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------
1403587593 oradg11g YES YES DISABLED 0
已用时间: 00: 00: 00.00
10:20:45 SQL>
10:20:45 SQL> ! ps -ef|grep dmon
oracle 7936 7895 0 10:21 pts/4 00:00:00 /bin/bash -c ps -ef|grep dmon
oracle 7938 7936 0 10:21 pts/4 00:00:00 grep dmon
10:21:17 SQL> show parameter dg_broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/dr1oradg11g.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/dr2oradg11g.dat
dg_broker_start boolean FALSE
10:21:39 SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE
10:21:53 SQL> alter system set dg_broker_start=true scope=both;
系统已更改。
已用时间: 00: 00: 00.02
10:21:56 SQL> ! ps -ef|grep dmon
oracle 7975 1 0 10:21 ? 00:00:00 ora_dmon_oradg11g
oracle 7976 7895 0 10:22 pts/4 00:00:00 /bin/bash -c ps -ef|grep dmon
oracle 7978 7976 0 10:22 pts/4 00:00:00 grep dmon
10:22:02 SQL>
10:22:02 SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
10:23:48 SQL> set line 9999
10:23:58 SQL> col name format a10
10:23:58 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
10:23:58 SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G 2337043 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
已用时间: 00: 00: 00.01
10:23:58 SQL> SELECT d.DBID,
10:23:58 2 d.DB_UNIQUE_NAME,
10:23:58 3 d.FORCE_LOGGING,
10:23:58 4 d.FLASHBACK_ON,
10:23:58 5 d.FS_FAILOVER_STATUS,
10:23:58 6 d.FS_FAILOVER_CURRENT_TARGET,
10:23:58 7 d.FS_FAILOVER_THRESHOLD,
10:23:58 8 d.FS_FAILOVER_OBSERVER_PRESENT,
10:23:58 9 d.FS_FAILOVER_OBSERVER_HOST
10:23:58 10 FROM v$database d;
DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER
---------- ------------------------------ --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------
1403587593 oradg11g YES YES DISABLED 0
已用时间: 00: 00: 00.00
10:24:00 SQL>
告警日志:
Mon Sep 28 10:21:56 2015
ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;
Mon Sep 28 10:21:56 2015
DMON started with pid=37, OS id=7975
Starting Data Guard Broker (DMON)
Mon Sep 28 10:22:04 2015
INSV started with pid=38, OS id=7984
备库操作:
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期一 9月 28 10:25:25 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:25:25 SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string oradg11g, oradgphy
db_name string oradg11g
db_unique_name string oradgphy
global_names boolean FALSE
instance_name string oradgphy
lock_name_space string
log_file_name_convert string oradg11g, oradgphy
processor_group_name string
service_names string oradgphy
10:25:27 SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileoradgphy.o
ra
10:25:35 SQL> show parameter DG_BROKER_CONFIG_FILE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/dr1oradgphy.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/dr2oradgphy.dat
10:25:43 SQL> set line 9999
10:25:51 SQL> col name format a10
10:25:51 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
10:25:51 SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
SELECT d.DBID,
d.DB_UNIQUE_NAME,
d.FORCE_LOGGING,
d.FLASHBACK_ON,
d.FS_FAILOVER_STATUS,
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G 2337499 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
已用时间: 00: 00: 00.11
10:25:52 SQL> 10:25:52 2 10:25:52 3 10:25:52 4 10:25:52 5 10:25:52 6 d.FS_FAILOVER_CURRENT_TARGET,
10:25:52 7 d.FS_FAILOVER_THRESHOLD,
10:25:52 8 d.FS_FAILOVER_OBSERVER_PRESENT,
10:25:52 9 d.FS_FAILOVER_OBSERVER_HOST
10:25:52 10 FROM v$database d;
DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER
---------- ------------------------------ --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------
1403587593 oradgphy YES NO DISABLED 0
已用时间: 00: 00: 00.00
10:25:56 SQL> ! ps -ef|grep dmon
oracle 7975 1 0 10:21 ? 00:00:00 ora_dmon_oradg11g
oracle 8461 8410 0 10:26 pts/5 00:00:00 /bin/bash -c ps -ef|grep dmon
oracle 8463 8461 0 10:26 pts/5 00:00:00 grep dmon
10:26:07 SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE
10:26:39 SQL> alter system set dg_broker_start=true scope=both;
系统已更改。
已用时间: 00: 00: 00.05
10:26:46 SQL> ! ps -ef|grep dmon
oracle 7975 1 0 10:21 ? 00:00:00 ora_dmon_oradg11g
oracle 8481 1 0 10:26 ? 00:00:00 ora_dmon_oradgphy
oracle 8501 8410 0 10:26 pts/5 00:00:00 /bin/bash -c ps -ef|grep dmon
oracle 8503 8501 0 10:26 pts/5 00:00:00 grep dmon
10:26:50 SQL>
配置db_domain 和 listener 及 tnsnames
目标: 配置db_domain 和 静态的listener ,listener中的GLOBAL_DBNAME为db_unique_name_DGMGRL.db_domain
主备库:
14:05:05 SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
14:13:46 SQL> alter system set db_domain='lhr.com' scope=spfile;
主备库listener的配置,注意红色的为新添加的内容:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 | [oracle@rhel6_lhr admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 28-9月 -2015 15:05:27 Copyright (c) 1991, 2011, Oracle. All rights reserved. 正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))) LISTENER 的 STATUS ------------------------ 别名 LISTENER 版本 TNSLSNR for Linux: Version 11.2.0.3.0 - Production 启动日期 28-9月 -2015 14:32:08 正常运行时间 0 天 0 小时 33 分 18 秒 跟踪级别 off 安全性 ON: Local OS Authentication SNMP OFF 监听程序参数文件 /u01/app/grid/11.2.0/network/admin/listener.ora 监听端点概要... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521))) 服务摘要.. 服务 "+ASM" 包含 1 个实例。 实例 "+ASM", 状态 READY, 包含此服务的 1 个处理程序... 服务 "PLSExtProc" 包含 1 个实例。 实例 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 服务 "ora11g" 包含 1 个实例。 实例 "ora11g", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 服务 "oradg11g" 包含 1 个实例。 实例 "oradg11g", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 服务 "oradg11g.lhr.com" 包含 2 个实例。 实例 "oradg11g", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 实例 "oradg11g", 状态 READY, 包含此服务的 1 个处理程序... 服务 "oradg11gXDB.lhr.com" 包含 2 个实例。 实例 "oradg11g", 状态 READY, 包含此服务的 1 个处理程序... 实例 "oradgphy", 状态 READY, 包含此服务的 1 个处理程序... 服务 "oradg11g_DGB.lhr.com" 包含 1 个实例。 实例 "oradg11g", 状态 READY, 包含此服务的 1 个处理程序... 服务 "oradg11g_DGMGRL.lhr.com" 包含 1 个实例。 实例 "oradg11g", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 服务 "oradglg" 包含 1 个实例。 实例 "oradglg", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 服务 "oradgphy" 包含 1 个实例。 实例 "oradgphy", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 服务 "oradgphy.lhr.com" 包含 2 个实例。 实例 "oradgphy", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 实例 "oradgphy", 状态 READY, 包含此服务的 1 个处理程序... 服务 "oradgphy_DGB.lhr.com" 包含 1 个实例。 实例 "oradgphy", 状态 READY, 包含此服务的 1 个处理程序... 服务 "oradgphy_DGMGRL.lhr.com" 包含 1 个实例。 实例 "oradgphy", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 服务 "oradgss" 包含 1 个实例。 实例 "oradgss", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 服务 "orclasm.lhr.com" 包含 1 个实例。 实例 "orclasm", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 命令执行成功 [oracle@rhel6_lhr admin]$ more /u01/app/grid/11.2.0/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/grid/11.2.0/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orclasm.lhr.com) (SID_NAME = orclasm) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) ) (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = oradg11g) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME= oradg11g) ) (SID_DESC = (GLOBAL_DBNAME = oradgphy) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME= oradgphy) ) (SID_DESC = (GLOBAL_DBNAME = oradglg) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME= oradglg) ) (SID_DESC = (GLOBAL_DBNAME = oradgss) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME= oradgss) ) (SID_DESC = (GLOBAL_DBNAME = ora11g) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME= ora11g) ) (SID_DESC = (GLOBAL_DBNAME = oradg11g.lhr.com) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME= oradg11g) ) (SID_DESC = (GLOBAL_DBNAME = oradgphy.lhr.com) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME= oradgphy) ) (SID_DESC = (GLOBAL_DBNAME = oradg11g_DGMGRL.lhr.com) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME= oradg11g) ) (SID_DESC = (GLOBAL_DBNAME = oradgphy_DGMGRL.lhr.com) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME= oradgphy) ) ) LOGGING_LISTENER = OFF LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/grid ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON #TRACE_LEVEL_LISTENER = OFF [oracle@rhel6_lhr admin]$ -- 主备库tnsnames的配置,注意红色的为新添加的内容: [oracle@rhel6_lhr admin]$ more tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. tns_oradg11g_DGMGRL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradg11g_DGMGRL.lhr.com) ) ) tns_oradgphy_DGMGRL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradgphy_DGMGRL.lhr.com) ) ) tns_oradg11g = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradg11g) ) ) tns_oradgphy = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradgphy) ) ) tns_oradglg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradglg) ) ) |
dgmgrl中配置broker并启用
[oracle@rhel6_lhr ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。
DGMGRL> help
可使用以下命令:
add 在中介配置中添加备用数据库
connect 连接到 Oracle 数据库实例
convert 将数据库从一种类型转换为另一种
create 创建中介配置
disable 禁用配置, 数据库或快速启动故障转移
edit 编辑配置, 数据库或实例
enable 启用配置, 数据库或快速启动故障转移
exit 退出程序
failover 将备用数据库更改为主数据库
help 显示命令的说明和语法
quit 退出程序
reinstate 将标记为恢复的数据库更改为可行的备用数据库
rem DGMGRL 会忽略注释
remove 删除配置, 数据库或实例
show 显示有关配置, 数据库或实例的信息
shutdown 关闭当前正在运行的 Oracle 数据库实例
sql 执行 SQL 语句
start 启动快速启动故障转移观察程序
startup 启动 Oracle 数据库实例
stop 停止快速启动故障转移观察程序
switchover 在主数据库和备用数据库之间切换角色
使用 "help \
DGMGRL> connect sys/lhr@tns_oradg11g_dgmgrl
已连接。
DGMGRL>
DGMGRL> show configuration
ORA-16532: Data Guard 中介配置不存在
配置详细资料不能由 DGMGRL 确定
DGMGRL> help create
创建中介配置
语法:
CREATE CONFIGURATION \
PRIMARY DATABASE IS \
CONNECT IDENTIFIER IS \
DGMGRL> create configuration 'fsf_oradg11g_lhr' as
primary database is 'oradg11g'
connect identifier is tns_oradg11g_dgmgrl;
已创建配置 "fsf_oradg11g_lhr", 其中主数据库为 "oradg11g"
DGMGRL> show configuration
配置 - fsf_oradg11g_lhr
保护模式: MaxPerformance
数据库:
oradg11g - 主数据库
快速启动故障转移: DISABLED
配置状态:
DISABLED
DGMGRL> add database 'oradgphy' as
connect identifier is tns_oradgphy_dgmgrl
maintained as physical;
已添加数据库 "oradgphy"
DGMGRL> show configuration
配置 - fsf_oradg11g_lhr
保护模式: MaxPerformance
数据库:
oradg11g - 主数据库
oradgphy - 物理备用数据库
快速启动故障转移: DISABLED
配置状态:
DISABLED
DGMGRL>
DGMGRL> enable configuration
已启用。
DGMGRL> show configuration
配置 - fsf_oradg11g_lhr
保护模式: MaxPerformance
数据库:
oradg11g - 主数据库
oradgphy - 物理备用数据库
快速启动故障转移: DISABLED
配置状态:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL> show database verbose oradg11g
数据库 - oradg11g
角色: PRIMARY
预期状态: TRANSPORT-ON
实例:
oradg11g
属性:
DGConnectIdentifier = 'tns_oradg11g_dgmgrl'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'oradgphy, oradg11g'
LogFileNameConvert = 'oradgphy, oradg11g'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'oradg11g'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel6_lhr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(INSTANCE_NAME=oradg11g)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t%s%r.dbf'
TopWaitEvents = '(monitor)'
数据库状态:
SUCCESS
DGMGRL> show database verbose oradgphy
数据库 - oradgphy
角色: PHYSICAL STANDBY
预期状态: APPLY-ON
传输滞后: 0 秒
应用滞后: 0 秒
实时查询: ON
实例:
oradgphy
属性:
DGConnectIdentifier = 'tns_oradgphy_dgmgrl'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'oradg11g, oradgphy'
LogFileNameConvert = 'oradg11g, oradgphy'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'oradgphy'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel6_lhr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradgphy_DGMGRL.lhr.com)(INSTANCE_NAME=oradgphy)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t%s%r.dbf'
TopWaitEvents = '(monitor)'
数据库状态:
SUCCESS
DGMGRL>
DGMGRL>
主库告警日志:
Mon Sep 28 15:20:28 2015
NSV1 started with pid=30, OS id=25660
Mon Sep 28 15:20:54 2015
ALTER SYSTEM SET log_archive_config='dg_config=(oradg11g,oradgphy)' SCOPE=BOTH;
Mon Sep 28 15:20:57 2015
ALTER SYSTEM SET log_archive_dest_2='service="tns_oradgphy_dgmgrl"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="oradgphy" net_timeout=30','valid_for=(all_logfiles,primary_role)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='oradg11g';
ALTER SYSTEM SET log_archiveformat='%t%s_%r.dbf' SCOPE=SPFILE SID='oradg11g';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='oradgphy','oradg11g' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='oradgphy','oradg11g' SCOPE=SPFILE;
ALTER SYSTEM ARCHIVE LOG
Mon Sep 28 15:20:57 2015
NSA2 started with pid=34, OS id=25706
备库告警日志:
NSV0 started with pid=21, OS id=25696
Mon Sep 28 15:20:57 2015
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='oradgphy';
ALTER SYSTEM SET log_archiveformat='%t%s_%r.dbf' SCOPE=SPFILE SID='oradgphy';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='oradg11g','oradgphy' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='oradg11g','oradgphy' SCOPE=SPFILE;
ALTER SYSTEM SET fal_server='tns_oradg11g_dgmgrl' SCOPE=BOTH;
Mon Sep 28 15:21:01 2015
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Assigned to RFS process 25708
RFS[4]: Selected log 4 for thread 1 sequence 145 dbid 1403587593 branch 886695024
测试Data Guard Broker 功能
在配置broker完成后,我们就可以利用broker完成switchover了。在DGMGRL中只需要简单的一个命令。
switchover to standby-database
(standby-database: 此参数是db_unique_name,大小写敏感。如果名称是大写的,需要用引号。表示切换standby-database为主机,原主机转换为备机状态)
DGMGRL> show configuration
配置 - fsf_oradg11g_lhr
保护模式: MaxPerformance
数据库:
oradg11g - 主数据库
oradgphy - 物理备用数据库
快速启动故障转移: DISABLED
配置状态:
SUCCESS
DGMGRL> switchover to oradgphy;
立即执行切换, 请稍候...
新的主数据库 "oradgphy" 正在打开...
操作要求关闭实例 "oradg11g" (在数据库 "oradg11g" 上)
正在关闭实例 "oradg11g"...
ORACLE 例程已经关闭。
操作要求启动实例 "oradg11g" (在数据库 "oradg11g" 上)
正在启动实例 "oradg11g"...
ORACLE 例程已经启动。
数据库装载完毕。
数据库已经打开。
切换成功, 新的主数据库为 "oradgphy"
DGMGRL> show configuration
配置 - fsf_oradg11g_lhr
保护模式: MaxPerformance
数据库:
oradgphy - 主数据库
oradg11g - 物理备用数据库
快速启动故障转移: DISABLED
配置状态:
SUCCESS
DGMGRL>
再切换一次,回到最初状态:
DGMGRL> switchover to oradg11g;
立即执行切换, 请稍候...
新的主数据库 "oradg11g" 正在打开...
操作要求关闭实例 "oradgphy" (在数据库 "oradgphy" 上)
正在关闭实例 "oradgphy"...
ORACLE 例程已经关闭。
操作要求启动实例 "oradgphy" (在数据库 "oradgphy" 上)
正在启动实例 "oradgphy"...
ORACLE 例程已经启动。
数据库装载完毕。
数据库已经打开。
切换成功, 新的主数据库为 "oradg11g"
DGMGRL> show configuration
配置 - fsf_oradg11g_lhr
保护模式: MaxPerformance
数据库:
oradg11g - 主数据库
oradgphy - 物理备用数据库
快速启动故障转移: DISABLED
配置状态:
SUCCESS
DGMGRL>
第一次切换的时候告警日志情况:
主库告警日志:
Mon Sep 28 15:26:24 2015
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 23943] (oradg11g)
Mon Sep 28 15:26:24 2015
Thread 1 advanced to log sequence 146 (LGWR switch)
Current log# 2 seq# 146 mem# 0: /u01/app/oracle/oradata/oradg11g/redo02.log
Mon Sep 28 15:26:24 2015
Stopping background process CJQ0
Stopping background process QMNC
Stopping Job queue slave processes, flags = 27
Mon Sep 28 15:26:27 2015
Archived Log entry 501 added for thread 1 sequence 145 ID 0x5495956b dest 1:
Waiting for Job queue slaves to complete
Job queue slave processes stopped
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'
CLOSE: all sessions shutdown successfully.
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for dest_id 2 to become synchronized...
Active, synchronized Physical Standby switchover target has been identified
Switchover End-Of-Redo Log thread 1 sequence 146 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x262c94
ARCH: Noswitch archival of thread 1, sequence 146
ARCH: End-Of-Redo Branch archival of thread 1 sequence 146
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 1 sequence 146 for destination LOG_ARCHIVE_DEST_2
Archived Log entry 502 added for thread 1 sequence 146 ID 0x5495956b dest 1:
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received alls redo
Final check for a synchronized target standby. Check will be made once.
Archive destination LOG_ARCHIVE_DEST_3 invalidated
DB_UNIQUE_NAME oradglg is not in the Data Guard configuration
Archive destination LOG_ARCHIVE_DEST_4 invalidated
DB_UNIQUE_NAME oradgss is not in the Data Guard configuration
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
Active, synchronized target has been identified
Target has also received all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradg11g/oradg11g/trace/oradg11g_rsm0_23943.trc
Clearing standby activation ID 1419089259 (0x5495956b)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Archivelog for thread 1 sequence 146 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
Mon Sep 28 15:26:37 2015
Performing implicit shutdown abort due to switchover to physical standby
Shutting down instance (abort)
License high water mark = 8
USER (ospid: 26018): terminating the instance
Instance terminated by USER, pid = 26018
Mon Sep 28 15:26:38 2015
Instance shutdown complete
ORA-1092 : opitsk aborting process
Mon Sep 28 15:26:38 2015
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Archive destination LOG_ARCHIVE_DEST_3 invalidated
DB_UNIQUE_NAME oradglg is not in the Data Guard configuration
Archive destination LOG_ARCHIVE_DEST_4 invalidated
DB_UNIQUE_NAME oradgss is not in the Data Guard configuration
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: rhel6_lhr
Release: 2.6.32-504.16.2.el6.x86_64
Version: #1 SMP Tue Apr 21 08:37:59 PDT 2015
Machine: x86_64
VM name: VMWare Version: 6
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileoradg11g.ora
System parameters with non-default values:
processes = 150
memory_target = 300M
memory_max_target = 400M
control_files = "/u01/app/oracle/oradata/oradg11g/control01.ctl"
control_files = "/u01/app/oracle/flash_recovery_area/oradg11g/control02.ctl"
db_file_name_convert = "oradgphy"
db_file_name_convert = "oradg11g"
log_file_name_convert = "oradgphy"
log_file_name_convert = "oradg11g"
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_dest_1 = "LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=oradg11g valid_for=(ALL_LOGFILES,ALL_ROLES)"
log_archive_dest_2 = "service="tns_oradgphy_dgmgrl""
log_archive_dest_2 = "LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="oradgphy" net_timeout=30"
log_archive_dest_2 = "valid_for=(all_logfiles,primary_role)"
log_archive_dest_3 = "SERVICE=tns_oradglg LGWR ASYNC db_unique_name=oradglg valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)"
log_archive_dest_4 = "SERVICE=tns_oradgss LGWR ASYNC db_unique_name=oradgss valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)"
log_archive_dest_state_1 = "ENABLE"
log_archive_dest_state_2 = "ENABLE"
log_archive_dest_state_3 = "defer"
log_archive_dest_state_4 = "defer"
log_archive_min_succeed_dest= 1
fal_client = "oradg11g"
fal_server = "tns_oradgphy_dgmgrl"
log_archive_trace = 0
log_archive_config = "dg_config=(oradg11g,oradgphy)"
log_archiveformat = "%t%s_%r.dbf"
log_archive_max_processes= 4
archive_lag_target = 0
db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 4122M
standby_file_management = "AUTO"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = "lhr.com"
global_names = TRUE
dispatchers = "(PROTOCOL=TCP) (SERVICE=oradg11gXDB)"
audit_file_dest = "/u01/app/oracle/admin/oradg11g/adump"
audit_trail = "DB"
db_name = "oradg11g"
db_unique_name = "oradg11g"
open_cursors = 300
dg_broker_start = TRUE
diagnostic_dest = "/u01/app/oracle"
Mon Sep 28 15:26:38 2015
PMON started with pid=2, OS id=26102
Mon Sep 28 15:26:38 2015
PSP0 started with pid=3, OS id=26104
Mon Sep 28 15:26:39 2015
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
destination database instance is 'started' not 'mounted'
Mon Sep 28 15:26:39 2015
VKTM started with pid=4, OS id=26112 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Mon Sep 28 15:26:39 2015
GEN0 started with pid=5, OS id=26116
Mon Sep 28 15:26:39 2015
DIAG started with pid=6, OS id=26118
Mon Sep 28 15:26:40 2015
DBRM started with pid=7, OS id=26120
Mon Sep 28 15:26:40 2015
DIA0 started with pid=8, OS id=26122
Mon Sep 28 15:26:40 2015
MMAN started with pid=9, OS id=26124
Mon Sep 28 15:26:40 2015
DBW0 started with pid=10, OS id=26126
Mon Sep 28 15:26:40 2015
LGWR started with pid=11, OS id=26128
Mon Sep 28 15:26:40 2015
CKPT started with pid=12, OS id=26130
Mon Sep 28 15:26:40 2015
SMON started with pid=13, OS id=26132
Mon Sep 28 15:26:40 2015
RECO started with pid=14, OS id=26134
Mon Sep 28 15:26:40 2015
MMON started with pid=15, OS id=26136
Mon Sep 28 15:26:40 2015
MMNL started with pid=16, OS id=26138
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/grid
Mon Sep 28 15:26:40 2015
DMON started with pid=19, OS id=26145
Mon Sep 28 15:26:40 2015
alter database mount
ARCH: STARTING ARCH PROCESSES
Mon Sep 28 15:26:44 2015
ARC0 started with pid=22, OS id=26157
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Successful mount of redo thread 1, with mount id 1419115888
Allocated 3981120 bytes in shared pool for flashback generation buffer
Starting background process RVWR
Mon Sep 28 15:26:45 2015
ARC1 started with pid=23, OS id=26159
Mon Sep 28 15:26:45 2015
RVWR started with pid=24, OS id=26161
Mon Sep 28 15:26:45 2015
ARC2 started with pid=25, OS id=26163
Mon Sep 28 15:26:45 2015
ARC3 started with pid=26, OS id=26165
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Physical Standby Database mounted.
Lost write protection disabled
ARC2: Becoming the active heartbeat ARCH
Completed: alter database mount
alter database open
Data Guard Broker initializing...
Data Guard Broker initialization complete
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
Beginning standby crash recovery.
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_145c0ktx3cj.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_146c0ktx6mn.arc
Identified End-Of-Redo (switchover) for thread 1 sequence 146 at SCN 0x0.262c94
Resetting standby activation ID 0 (0x0)
Incomplete Recovery applied until change 2501780 time 09/28/2015 15:26:30
Completed standby crash recovery.
SMON: enabling cache recovery
Mon Sep 28 15:26:46 2015
RFS[1]: Assigned to RFS process 26177
RFS[1]: Opened log for thread 1 sequence 148 dbid 1403587593 branch 886695024
Archived Log entry 504 added for thread 1 sequence 148 rlc 886695024 ID 0x54960fb7 dest 2:
Dictionary check beginning
Mon Sep 28 15:26:46 2015
RFS[2]: Assigned to RFS process 26179
RFS[2]: Opened log for thread 1 sequence 147 dbid 1403587593 branch 886695024
Archived Log entry 505 added for thread 1 sequence 147 rlc 886695024 ID 0x54960fb7 dest 2:
Dictionary check complete
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
RFS[2]: Selected log 4 for thread 1 sequence 149 dbid 1403587593 branch 886695024
Completed: alter database open
Archived Log entry 506 added for thread 1 sequence 149 ID 0x54960fb7 dest 1:
Mon Sep 28 15:26:46 2015
db_recovery_file_dest_size of 4122 MB is 6.96% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Sep 28 15:26:46 2015
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Assigned to RFS process 26185
RFS[3]: Selected log 4 for thread 1 sequence 150 dbid 1403587593 branch 886695024
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Starting Data Guard Broker (DMON)
Mon Sep 28 15:26:48 2015
INSV started with pid=30, OS id=26191
Mon Sep 28 15:26:51 2015
NSV1 started with pid=31, OS id=26199
Mon Sep 28 15:26:55 2015
RSM0 started with pid=32, OS id=26208
ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='oradg11g';
ALTER SYSTEM SET log_archiveformat='%t%s_%r.dbf' SCOPE=SPFILE SID='oradg11g';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='oradgphy','oradg11g' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='oradgphy','oradg11g' SCOPE=SPFILE;
ALTER SYSTEM SET fal_server='tns_oradgphy_dgmgrl' SCOPE=BOTH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (oradg11g)
Mon Sep 28 15:26:59 2015
MRP0 started with pid=33, OS id=26214
MRP0: Background Managed Standby Recovery process started (oradg11g)
started logmerger process
Mon Sep 28 15:27:04 2015
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u01/app/oracle/oradata/oradg11g/redo01.log
Clearing online log 1 of thread 1 sequence number 150
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/oradg11g/redo02.log
Clearing online log 2 of thread 1 sequence number 146
Mon Sep 28 15:27:05 2015
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/app/oracle/oradata/oradg11g/redo03.log
Clearing online log 3 of thread 1 sequence number 149
Clearing online redo logfile 3 complete
Mon Sep 28 15:27:07 2015
RFS[3]: Selected log 5 for thread 1 sequence 151 dbid 1403587593 branch 886695024
Mon Sep 28 15:27:07 2015
Archived Log entry 507 added for thread 1 sequence 150 ID 0x54960fb7 dest 1:
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_147c0ktxp86.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_148c0ktxp70.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_149c0ktxpgt.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_150c0ktycgh.arc
Media Recovery Waiting for thread 1 sequence 151 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 151 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/oradg11g/standby_redo05.log
备库告警日志:
Mon Sep 28 15:26:24 2015
Archived Log entry 128 added for thread 1 sequence 145 ID 0x5495956b dest 1:
Mon Sep 28 15:26:24 2015
Media Recovery Waiting for thread 1 sequence 146
Mon Sep 28 15:26:30 2015
RFS[6]: Assigned to RFS process 25998
RFS[6]: Selected log 4 for thread 1 sequence 146 dbid 1403587593 branch 886695024
Mon Sep 28 15:26:30 2015
Archived Log entry 129 added for thread 1 sequence 146 ID 0x5495956b dest 1:
Mon Sep 28 15:26:30 2015
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_pr00_24252.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Mon Sep 28 15:26:31 2015
MRP0: Background Media Recovery process shutdown (oradgphy)
Managed Standby Recovery Canceled (oradgphy)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Database not available for switchover
End-Of-REDO archived log file has not been recovered
Incomplete recovery SCN:0:2481770 archive SCN:0:2501780
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH LAST SWITCHOVER NODELAY
Media Recovery Start: Managed Standby Recovery (oradgphy)
started logmerger process
Mon Sep 28 15:26:32 2015
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_09_28/o1_mf_1_146c0ktx6n6.arc
Identified End-Of-Redo (switchover) for thread 1 sequence 146 at SCN 0x0.262c94
Resetting standby activation ID 1419089259 (0x5495956b)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Applied through change 2501780
Media Recovery Complete: End-Of-REDO (oradgphy)
Attempt to set limbo arscn 0:2501780 irscn 0:2501780
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH LAST SWITCHOVER NODELAY
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (oradgphy)
Maximum wait for role transition is 15 minutes.
krsv_proc_kill: Killing 2 processes (all RFS)
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Mon Sep 28 15:26:34 2015
SMON: disabling cache recovery
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_rsm0_24244.trc
SwitchOver after complete recovery through change 2501780
Online log /u01/app/oracle/oradata/oradgphy/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/oradgphy/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/oradgphy/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 2501778
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
Mon Sep 28 15:26:36 2015
idle dispatcher 'D000' terminated, pid = (17, 1)
ALTER DATABASE OPEN
Data Guard Broker initializing...
Mon Sep 28 15:26:37 2015
Assigning activation ID 1419120567 (0x54960fb7)
Mon Sep 28 15:26:37 2015
ARC3: Becoming the 'no SRL' ARCH
Thread 1 advanced to log sequence 148 (thread open)
ARC0: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 148
Current log# 2 seq# 148 mem# 0: /u01/app/oracle/oradata/oradgphy/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Archived Log entry 130 added for thread 1 sequence 147 ID 0x54960fb7 dest 1:
ARC3: Becoming the 'no SRL' ARCH
Mon Sep 28 15:26:37 2015
NSA2 started with pid=17, OS id=26022
Mon Sep 28 15:26:37 2015
***********************************************************************
Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 28-SEP-2015 15:26:37
Tracing not turned on.
Tns error struct:
ns main err code: 12537
***********************************************************************
Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 28-SEP-2015 15:26:37
Tracing not turned on.
Tns error struct:
ns main err code: 12537
[24244] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:21508174 end:21508224 diff:50 (0 seconds)
Dictionary check beginning
TNS-12537: TNS:connection closed
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 507
ns secondary err code: 12560
nt main err code: 507
TNS-00507: Connection closed
nt secondary err code: 0
nt OS err code: 0
TNS-00507: Connection closed
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 28-SEP-2015 15:26:37
Tracing not turned on.
Tns error struct:
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 507
TNS-00507: Connection closed
nt secondary err code: 0
nt OS err code: 0
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Starting background process SMCO
***********************************************************************
Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 28-SEP-2015 15:26:37
Tracing not turned on.
Tns error struct:
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 507
TNS-00507: Connection closed
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 28-SEP-2015 15:26:37
Tracing not turned on.
Tns error struct:
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 507
TNS-00507: Connection closed
nt secondary err code: 0
nt OS err code: 0
Error 12537 received logging on to the standby
FAL[server, ARC3]: Error 12537 creating remote archivelog file 'tns_oradg11g_dgmgrl'
FAL[server, ARC3]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance oradgphy - Archival Error. Archiver continuing.
Mon Sep 28 15:26:37 2015
SMCO started with pid=26, OS id=26033
***********************************************************************
Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 28-SEP-2015 15:26:37
Tracing not turned on.
Tns error struct:
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 507
TNS-00507: Connection closed
nt secondary err code: 0
nt OS err code: 0
Error 12537 received logging on to the standby
PING[ARC2]: Heartbeat failed to connect to standby 'tns_oradg11g_dgmgrl'. Error is 12537.
No Resource Manager plan active
Starting background process QMNC
Mon Sep 28 15:26:37 2015
QMNC started with pid=27, OS id=26036
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='oradgphy';
ALTER SYSTEM SET log_archiveformat='%t%s_%r.dbf' SCOPE=SPFILE SID='oradgphy';
Starting background process CJQ0
Mon Sep 28 15:26:37 2015
CJQ0 started with pid=32, OS id=26050
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='oradg11g','oradgphy' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='oradg11g','oradgphy' SCOPE=SPFILE;
ARC2: STARTING ARCH PROCESSES
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG
Mon Sep 28 15:26:38 2015
ARC4 started with pid=28, OS id=26052
ARC4: Archival started
ARC2: STARTING ARCH PROCESSES COMPLETE
PING[ARC2]: Heartbeat failed to connect to standby 'tns_oradg11g_dgmgrl'. Error is 16058.
Thread 1 advanced to log sequence 149 (LGWR switch)
Current log# 3 seq# 149 mem# 0: /u01/app/oracle/oradata/oradgphy/redo03.log
Archived Log entry 131 added for thread 1 sequence 148 ID 0x54960fb7 dest 1:
Mon Sep 28 15:26:41 2015
***********************************************************************
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(INSTANCE_NAME=oradg11g)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 28-SEP-2015 15:26:41
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(INSTANCE_NAME=oradg11g)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 28-SEP-2015 15:26:41
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(INSTANCE_NAME=oradg11g)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 28-SEP-2015 15:26:41
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 28-SEP-2015 15:26:42
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 28-SEP-2015 15:26:42
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 28-SEP-2015 15:26:42
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
ARC3: Becoming the 'no SRL' ARCH
Mon Sep 28 15:26:44 2015
ARC0: Becoming the 'no SRL' ARCH
Shutting down archive processes
ARCH shutting down
ARC4: Archival stopped
Mon Sep 28 15:26:46 2015
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
ARC0: Becoming the 'no SRL' ARCH
ARC3: Becoming the 'no SRL' ARCH
Thread 1 advanced to log sequence 150 (LGWR switch)
Current log# 1 seq# 150 mem# 0: /u01/app/oracle/oradata/oradgphy/redo01.log
ARC3: Becoming the 'no SRL' ARCH
Archived Log entry 134 added for thread 1 sequence 149 ID 0x54960fb7 dest 1:
ARC0: Becoming the 'no SRL' ARCH
ARC0: Standby redo logfile selected for thread 1 sequence 149 for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 150 for destination LOG_ARCHIVE_DEST_2
Mon Sep 28 15:27:05 2015
ALTER SYSTEM ARCHIVE LOG
Mon Sep 28 15:27:05 2015
Thread 1 cannot allocate new log, sequence 151
Checkpoint not complete
Current log# 1 seq# 150 mem# 0: /u01/app/oracle/oradata/oradgphy/redo01.log
Mon Sep 28 15:27:07 2015
ARC3: Becoming the 'no SRL' ARCH
Thread 1 advanced to log sequence 151 (LGWR switch)
Current log# 2 seq# 151 mem# 0: /u01/app/oracle/oradata/oradgphy/redo02.log
Archived Log entry 137 added for thread 1 sequence 150 ID 0x54960fb7 dest 1:
Mon Sep 28 15:27:07 2015
LNS: Standby redo logfile selected for thread 1 sequence 151 for destination LOG_ARCHIVE_DEST_2
至此,Data Guard Broker配置结束。
物理dg配置客户端无缝切换 (2)--Fast-Start Failover 的配置
实验环境介绍
项目 | 主库 | dg库 |
---|---|---|
db 类型 | 单实例 | 单实例 |
db version | 11.2.0.3 | 11.2.0.3 |
db 存储 | FS type | FS type |
ORACLE_SID | oradg11g | oradgphy |
db_name | oradg11g | oradg11g |
主机IP地址: | 192.168.59.130 | 192.168.59.130 |
OS版本及kernel版本 | RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64 | RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64 |
OS hostname | rhel6_lhr | rhel6_lhr |
相关知识点扫盲
Fast-Start Failover是建立在broker基础上的一个快速故障转换的机制,通过fast-start failover可以自动检测primary的故障,然后自动的failover到预先指定的standby上面,这样可以最大化的减少故障时间,提高数据库的可用性。
Fast-Start Failover是在broker的基础上再增加了一个单独的observer,用来监控primary和standby数据库的状态,一旦primary不可用,observer就会自动的切换到指定的standby上面。
FAST-START FAILOVER是ORACLE10G的一项新功能。这个功能可以实现当主库宕机时,预定的从库自动快速可靠地进行失败切换(FAILOVER)。切换完成之后,原来的主库恢复正常之后,将会自动地配置为从库。这的确是一项令DBA心动的功能,大大减少了DBA的维护和管理工作。尤其是减少了在出现突然问题时的心慌意乱和手忙脚乱。
实验部分
实验目标
Fast-Start Failover 配置并完成实验。
前提准备条件
primary 与 standby 启用flashback database
在主备库上开启闪回功能,否则后续报错16651 :
[oracle@rhel6_lhr lhr]$ oerr ora 16651
16651, 0000, "requirements not met for enabling fast-start failover"
// *Cause: The attempt to enable fast-start failover could not be completed
// because one or more requirements were not met:
// - The Data Guard configuration must be in either MaxAvailability
// or MaxPerformance protection mode.
// - The LogXptMode property for both the primary database and
// the fast-start failover target standby database must be
// set to SYNC if the configuration protection mode is set to
// MaxAvailability mode.
// - The LogXptMode property for both the primary database and
// the fast-start failover target standby database must be
// set to ASYNC if the configuration protection mode is set to
// MaxPerformance mode.
// - The primary database and the fast-start failover target standby
// database must both have flashback enabled.
// - No valid target standby database was specified in the primary
// database FastStartFailoverTarget property prior to the attempt
// to enable fast-start failover, and more than one standby
// database exists in the Data Guard configuration.
// *Action: Retry the command after correcting the issue:
// - Set the Data Guard configuration to either MaxAvailability
// or MaxPerformance protection mode.
// - Ensure that the LogXptMode property for both the primary
// database and the fast-start failover target standby database
// are set to SYNC if the configuration protection mode is set to
// MaxAvailability.
// - Ensure that the LogXptMode property for both the primary
// database and the fast-start failover target standby database
// are set to ASYNC if the configuration protection mode is set to
// MaxPerformance.
// - Ensure that both the primary database and the fast-start failover
// target standby database have flashback enabled.
// - Set the primary database FastStartFailoverTarget property to
// the DB_UNIQUE_NAME value of the desired target standby database
// and the desired target standby database FastStartFailoverTarget
// property to the DB_UNIQUE_NAME value of the primary database.
主库:
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 09:50:17 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
09:50:17 SQL> set line 9999
09:50:18 SQL> col name format a10
col FS_FAILOVER_OBSERVER_HOST format a20
09:50:18 SQL> col DB_UNIQUE_NAME format a10
09:50:18 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME DBUNIQUE\ CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G oradg11g 2544025 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
已用时间: 00: 00: 00.01
09:50:18 SQL> SELECT d.DBID,
09:50:18 2 d.DB_UNIQUE_NAME,
09:50:18 3 d.FORCE_LOGGING,
09:50:18 4 d.FLASHBACK_ON,
09:50:18 5 d.FS_FAILOVER_STATUS,
09:50:18 6 d.FS_FAILOVER_CURRENT_TARGET,
09:50:18 7 d.FS_FAILOVER_THRESHOLD,
09:50:18 8 d.FS_FAILOVER_OBSERVER_PRESENT,
09:50:18 9 d.FS_FAILOVER_OBSERVER_HOST
09:50:18 10 FROM v$database d;
DBID DBUNIQUE\ FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER
---------- ---------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------
1403587593 oradg11g YES YES DISABLED 0
已用时间: 00: 00: 00.01
09:50:24 SQL>
备库:
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 10:18:39 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:18:39 SQL> set line 9999
10:19:02 SQL> col name format a10
10:19:02 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
10:19:02 SQL> col DB_UNIQUE_NAME format a15
10:19:02 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
SELECT d.DBID,
---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G oradgphy 2545958 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
已用时间: 00: 00: 00.00
10:19:02 SQL> 10:19:02 2 d.DB_UNIQUE_NAME,
10:19:02 3 d.FORCE_LOGGING,
10:19:02 4 d.FLASHBACK_ON,
10:19:02 5 d.FS_FAILOVER_STATUS,
10:19:02 6 d.FS_FAILOVER_CURRENT_TARGET,
10:19:02 7 d.FS_FAILOVER_THRESHOLD,
10:19:02 8 d.FS_FAILOVER_OBSERVER_PRESENT,
10:19:02 9 d.FS_FAILOVER_OBSERVER_HOST
10:19:02 10 FROM v$database d;
DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER
---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------
1403587593 oradgphy YES NO DISABLED 0
已用时间: 00: 00: 00.00
10:19:02 SQL> alter database flashback on;
alter database flashback on
*
第 1 行出现错误:
ORA-01153: 激活了不兼容的介质恢复
已用时间: 00: 00: 00.00
10:19:18 SQL> alter database recover managed standby database cancel;
数据库已更改。
已用时间: 00: 00: 01.01
10:19:34 SQL> alter database flashback on;
数据库已更改。
已用时间: 00: 00: 01.40
10:19:38 SQL> set line 9999
10:19:53 SQL> col name format a10
10:19:53 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
10:19:53 SQL> col DB_UNIQUE_NAME format a15
10:19:53 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G oradgphy 2545994 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED
已用时间: 00: 00: 00.00
10:19:53 SQL> SELECT d.DBID,
10:19:53 2 d.DB_UNIQUE_NAME,
10:19:53 3 d.FORCE_LOGGING,
10:19:53 4 d.FLASHBACK_ON,
10:19:53 5 d.FS_FAILOVER_STATUS,
10:19:53 6 d.FS_FAILOVER_CURRENT_TARGET,
10:19:53 7 d.FS_FAILOVER_THRESHOLD,
10:19:53 8 d.FS_FAILOVER_OBSERVER_PRESENT,
10:19:53 9 d.FS_FAILOVER_OBSERVER_HOST
10:19:53 10 FROM v$database d;
DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER
---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------
1403587593 oradgphy YES YES DISABLED 0
已用时间: 00: 00: 00.00
10:19:53 SQL>
确保broker配置为运行在MAX Availability模式
确保broker已经配置,同时运行模式为最大可用模式或者最大性能模式,如果数据库运行模式为最大可用模式,确保参数LogXptMode 配置为SYNC,如果是最大性能模式,则参数LogXptMode 应该为ASYNC
http://www.linuxidc.com/upload/2012_09/120909121264761.gif
[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradg11g_dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。
已连接。
DGMGRL> show configuration
配置 - fsf_oradg11g_lhr
保护模式: MaxPerformance
数据库:
oradg11g - 主数据库
oradgphy - 物理备用数据库
快速启动故障转移: DISABLED
配置状态:
SUCCESS
DGMGRL> show resource verbose 'oradg11g' logxptmode on site 'oradg11g';
LogXptMode = 'ASYNC'
DGMGRL> show resource verbose 'oradgphy' logxptmode on site 'oradgphy';
LogXptMode = 'ASYNC'
DGMGRL> alter resource 'oradg11g' set property logxptmode='SYNC';
已更新属性 "logxptmode"
DGMGRL> alter resource 'oradgphy' set property logxptmode='SYNC';
已更新属性 "logxptmode"
DGMGRL> edit configuration set protection mode as maxavailability;
成功。
DGMGRL> show configuration
配置 - fsf_oradg11g_lhr
保护模式: MaxAvailability
数据库:
oradg11g - 主数据库
oradgphy - 物理备用数据库
快速启动故障转移: DISABLED
配置状态:
SUCCESS
DGMGRL> show resource verbose 'oradg11g' logxptmode on site 'oradg11g';
LogXptMode = 'SYNC'
DGMGRL> show resource verbose 'oradgphy' logxptmode on site 'oradgphy';
LogXptMode = 'SYNC'
DGMGRL>
主库告警日志: