【DATAGUARD】物理dg配置客户端无缝切换 (八.2)--Fast-Start Failover 的配置
1.1 BLOG文档结构图
1.2 前言部分
1.2.1 导读
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:
① Data Guard Broker 的配置
② Fast-Start Failover 的配置
③ Oracle DataGuard 之客户端TAF 配置
④ 使用DGMGRL 来管理数据库
⑤ 物理dg管理和维护的一些sql
⑥ DataGuard 客户端特级配置
注意:本篇BLOG中代码部分需要特别关注的地方我都用黄色背景和红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33,thread 2的最大归档日志号为43是需要特别关注的地方。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。
1.2.2 实验环境介绍
项目 |
主库 |
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 |
1.2.3 相关参考文章链接
dg的系列文章参考:
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库(一): http://blog.itpub.net/26736162/viewspace-1448197/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库(二 ): http://blog.itpub.net/26736162/viewspace-1448207/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库(三 ): http://blog.itpub.net/26736162/viewspace-1481972/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库 (四)--添加一个物理dg节点 :http://blog.itpub.net/26736162/viewspace-1484878/
【DATAGUARD】物理dg的switchover切换(五) :http://blog.itpub.net/26736162/viewspace-1753111/
【DATAGUARD】物理dg的failover切换(六): http://blog.itpub.net/26736162/viewspace-1753130/
【DATAGUARD】物理dg在主库丢失归档文件的情况下的恢复(七) : http://blog.itpub.net/26736162/viewspace-1780863/
【DATAGUARD】物理dg配置客户端无缝切换 (八.1)--Data Guard Broker 的配置:http://blog.itpub.net/26736162/viewspace-1811839/
1.2.4 本文简介
本篇blog是基于cuug的公开课内容,我自己进行实践的操作,视频可以参考:http://blog.itpub.net/26736162/viewspace-1624453/ ,简介我就不多写了,把cuug的内容直接copy过来吧,觉得还是比较有用的。
这个技术如果你不知道,不能算是ORACLE高手
这个技术如果你不知道,就不能说你会DataGuard
这个技术如果你不知道,......
本次网络课程,研究当主备库发生切换时,如何在主库启动一个service,保证客户端的连接能够继续,而且还能够继续select查询操作,而不管主备库是在哪台服务器上;同时保证新的客户连接没有任何的问题。本课程网络上的例子不多,陈老师花了将近一年的时间人肉搜索,最近才找到,急不可待的要分享给大家。
1、DataGuard的配置(快速)
2、创建service
3、创建触发器
4、主备库切换测试
由于内容较多,我打算分为4个章节来共享给大家,贴个图,不要奇怪,还有一个章节是实验过程中配到的问题解决。
本篇为第二节,Fast-Start Failover 的配置。
1.3 相关知识点扫盲
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的维护和管理工作。尤其是减少了在出现突然问题时的心慌意乱和手忙脚乱。
1.4 实验部分
1.4.1 实验目标
Fast-Start Failover 配置并完成实验。
1.4.2 前提准备条件
1.4.2.1 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 DB_UNIQUE_ 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 DB_UNIQUE_ 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>
1.4.2.2 确保broker配置为运行在MAX Availability模式
确保broker已经配置,同时运行模式为最大可用模式或者最大性能模式,如果数据库运行模式为最大可用模式,确保参数LogXptMode 配置为SYNC,如果是最大性能模式,则参数LogXptMode 应该为ASYNC
[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>
主库告警日志:
Tue Sep 29 10:31:27 2015
ALTER SYSTEM SET log_archive_dest_2='service="tns_oradgphy_dgmgrl"','LGWR SYNC AFFIRM 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 SWITCH ALL LOGFILE start (oradg11g)
Tue Sep 29 10:31:27 2015
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
Tue Sep 29 10:31:27 2015
NSS2 started with pid=37, OS id=46913
LGWR: Standby redo logfile selected for thread 1 sequence 160 for destination LOG_ARCHIVE_DEST_2
ALTER SYSTEM SWITCH ALL LOGFILE complete (oradg11g)
Thread 1 advanced to log sequence 160 (LGWR switch)
Current log# 2 seq# 160 mem# 0: /u01/app/oracle/oradata/oradg11g/redo02.log
Tue Sep 29 10:31:30 2015
Archived Log entry 522 added for thread 1 sequence 159 ID 0x5495fd70 dest 1:
Tue Sep 29 10:31:31 2015
ARC3: Archive log rejected (thread 1 sequence 159) at host 'tns_oradgphy_dgmgrl'
FAL[server, ARC3]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance oradg11g - Archival Error. Archiver continuing.
Tue Sep 29 10:31:44 2015
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
ALTER SYSTEM ARCHIVE LOG
Tue Sep 29 10:31:45 2015
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Standby redo logfile selected to archive thread 1 sequence 161
LGWR: Standby redo logfile selected for thread 1 sequence 161 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 161 (LGWR switch)
Current log# 3 seq# 161 mem# 0: /u01/app/oracle/oradata/oradg11g/redo03.log
Archived Log entry 525 added for thread 1 sequence 160 ID 0x5495fd70 dest 1:
备库告警日志:
Tue Sep 29 10:31:30 2015
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Assigned to RFS process 46919
RFS[4]: Selected log 5 for thread 1 sequence 160 dbid 1403587593 branch 886695024
Tue Sep 29 10:31:30 2015
Archived Log entry 148 added for thread 1 sequence 159 ID 0x5495fd70 dest 1:
Tue Sep 29 10:31:31 2015
Media Recovery Waiting for thread 1 sequence 160 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 160 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/oradgphy/standby_redo05.log
Tue Sep 29 10:31:44 2015
Archived Log entry 149 added for thread 1 sequence 160 ID 0x5495fd70 dest 1:
Tue Sep 29 10:31:44 2015
Media Recovery Waiting for thread 1 sequence 161
Tue Sep 29 10:31:45 2015
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[5]: Assigned to RFS process 46931
RFS[5]: Selected log 4 for thread 1 sequence 161 dbid 1403587593 branch 886695024
Recovery of Online Redo Log: Thread 1 Group 4 Seq 161 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/oradgphy/standby_redo04.log
---从数据库层次查看配置情况是否修改:
主库:
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 10:43:18 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:43:18 SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service="tns_oradgphy_dgmgrl",
LGWR SYNC AFFIRM delay=0 opti
onal compression=disable max_f
ailure=0 max_connections=1 reo
pen=300 db_unique_name="oradgp
hy" net_timeout=30, valid_for=
(all_logfiles,primary_role)
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
10:43:20 SQL> set line 9999
10:43:30 SQL> col name format a10
10:43:30 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
10:43:30 SQL> col DB_UNIQUE_NAME format a15
10:43:30 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 oradg11g 2547638 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY YES READ WRITE TO STANDBY
已用时间: 00: 00: 00.00
10:43:30 SQL> SELECT d.DBID,
10:43:30 2 d.DB_UNIQUE_NAME,
10:43:30 3 d.FORCE_LOGGING,
10:43:30 4 d.FLASHBACK_ON,
10:43:30 5 d.FS_FAILOVER_STATUS,
10:43:30 6 d.FS_FAILOVER_CURRENT_TARGET,
10:43:30 7 d.FS_FAILOVER_THRESHOLD,
10:43:30 8 d.FS_FAILOVER_OBSERVER_PRESENT,
10:43:30 9 d.FS_FAILOVER_OBSERVER_HOST
10:43:30 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.01
10:43:30 SQL>
备库:
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 10:43:53 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:43:53 SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service="tns_oradg11g_dgmgrl",
LGWR ASYNC NOAFFIRM delay=0 o
ptional compression=disable ma
x_failure=0 max_connections=1
reopen=300 db_unique_name="ora
dg11g" net_timeout=30, valid_f
or=(all_logfiles,primary_role)
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
10:43:54 SQL> set line 9999
10:44:01 SQL> col name format a10
10:44:01 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
10:44:01 SQL> col DB_UNIQUE_NAME format a15
10:44:01 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 2547673 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
已用时间: 00: 00: 00.00
10:44:01 SQL> SELECT d.DBID,
10:44:01 2 d.DB_UNIQUE_NAME,
10:44:01 3 d.FORCE_LOGGING,
10:44:01 4 d.FLASHBACK_ON,
10:44:01 5 d.FS_FAILOVER_STATUS,
10:44:01 6 d.FS_FAILOVER_CURRENT_TARGET,
10:44:01 7 d.FS_FAILOVER_THRESHOLD,
10:44:01 8 d.FS_FAILOVER_OBSERVER_PRESENT,
10:44:01 9 d.FS_FAILOVER_OBSERVER_HOST
10:44:01 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:44:01 SQL>
1.4.3 启动observer观察进程
选定第三台机器,安装DGMGRL,用于启动observer,这里命令为observer server,配置observer server的配置tnsnames.ora文件,保证observer能正常连接到 primary 和 standby 数据库,我们测试就使用同一台机器测试。
新开一个单独的窗口:
[oracle@rhel6_lhr lhr]$ dgmgrl sys/lhr@tns_oradg11g_dgmgrl "start observer"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。
已连接。
观察程序已启动
该窗口一直挂起。。。。
注意启动observer后,DGMGRL就会阻塞在这个命令上。observer的操作信息以后会在这个窗口显示,有启动就有关闭,如下:
[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradgphy_dgmgrl "stop observer"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。
已连接。
完成。
[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradgphy_dgmgrl "start observer"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。
已连接。
观察程序已启动
1.4.4 配置FastStartFailover
1.4.4.1 配置每个数据库Failover的目标。这一步是决定当数据库出问题后会自动failover目标
DGMGRL> edit database 'oradg11g' set property 'FastStartFailoverTarget'='oradgphy';
已更新属性 "FastStartFailoverTarget"
DGMGRL>
DGMGRL> edit database 'oradgphy' set property 'FastStartFailoverTarget'='oradg11g';
已更新属性 "FastStartFailoverTarget"
1.4.4.2 设定FastStartFailoverThreshold值
这个设置是决定了primary坏了多长时间之后会执行自动的failover操作。这里设置的是30s
DGMGRL> edit configuration set property FastStartFailoverThreshold=30;
已更新属性 "faststartfailoverthreshold"
1.4.4.3 启用Fast-Start Failover
DGMGRL> ENABLE FAST_START FAILOVER;
已启用。
DGMGRL> SHOW FAST_START FAILOVER;
快速启动故障转移: ENABLED
阈值: 30 秒
目标: oradgphy
观察程序: rhel6_lhr
滞后限制: 30 秒 (未使用)
关闭主数据库: TRUE
自动恢复: TRUE
可配置的故障转移条件
健康状况:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle 错误条件:
(无)
DGMGRL>
主库告警日志:
Tue Sep 29 11:09:03 2015
Fast-Start Failover (FSFO) has been enabled between:
Primary = "oradg11g"
Standby = "oradgphy"
Tue Sep 29 11:09:03 2015
FSFP started with pid=42, OS id=49349
从告警日志可以看出,主库上启动了一个进程fsfp的进程:
[oracle@rhel6_lhr ~]$ ps -ef|grep fsfp
oracle 49349 1 0 11:09 ? 00:00:00 ora_fsfp_oradg11g
oracle 49383 43618 0 11:09 pts/1 00:00:00 grep fsfp
[oracle@rhel6_lhr ~]$
[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 verbose
配置 - fsf_oradg11g_lhr
保护模式: MaxAvailability
数据库:
oradg11g - 主数据库
oradgphy - (*) 物理备用数据库
(*) 快速启动故障转移目标
属性:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
快速启动故障转移: ENABLED
阈值: 30 秒
目标: oradgphy
观察程序: rhel6_lhr
滞后限制: 30 秒 (未使用)
关闭主数据库: TRUE
自动恢复: TRUE
配置状态:
SUCCESS
DGMGRL>
数据库级别查看,主库:
11:11:56 SQL> set line 9999
11:12:06 SQL> col name format a10
11:12:06 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
11:12:06 SQL> col DB_UNIQUE_NAME format a15
11:12:06 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 oradg11g 2549890 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY YES READ WRITE TO STANDBY
已用时间: 00: 00: 00.00
11:12:06 SQL> SELECT d.DBID,
11:12:06 2 d.DB_UNIQUE_NAME,
11:12:06 3 d.FORCE_LOGGING,
11:12:06 4 d.FLASHBACK_ON,
11:12:06 5 d.FS_FAILOVER_STATUS,
11:12:06 6 d.FS_FAILOVER_CURRENT_TARGET,
11:12:06 7 d.FS_FAILOVER_THRESHOLD,
11:12:06 8 d.FS_FAILOVER_OBSERVER_PRESENT,
11:12:06 9 d.FS_FAILOVER_OBSERVER_HOST
11:12:06 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 SYNCHRONIZED oradgphy 30 YES rhel6_lhr
已用时间: 00: 00: 00.00
11:12:06 SQL>
备库:
11:11:29 SQL> set line 9999
11:12:13 SQL> col name format a10
11:12:13 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
11:12:13 SQL> col DB_UNIQUE_NAME format a15
11:12:13 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 2549900 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
已用时间: 00: 00: 00.00
11:12:13 SQL> SELECT d.DBID,
11:12:13 2 d.DB_UNIQUE_NAME,
11:12:13 3 d.FORCE_LOGGING,
11:12:13 4 d.FLASHBACK_ON,
11:12:13 5 d.FS_FAILOVER_STATUS,
11:12:13 6 d.FS_FAILOVER_CURRENT_TARGET,
11:12:13 7 d.FS_FAILOVER_THRESHOLD,
11:12:14 8 d.FS_FAILOVER_OBSERVER_PRESENT,
11:12:14 9 d.FS_FAILOVER_OBSERVER_HOST
11:12:14 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 SYNCHRONIZED oradgphy 30 YES rhel6_lhr
已用时间: 00: 00: 00.01
11:12:14 SQL>
1.4.5 测试Fast-Start Failover 的功能
1.4.5.1 Shutdown abort 主库
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 11:18:18 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
11:18:18 SQL> set line 9999
11:18:19 SQL> col name format a10
11:18:19 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
11:18:19 SQL> col DB_UNIQUE_NAME format a15
11:18:19 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 oradg11g 2550295 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY YES READ WRITE TO STANDBY
已用时间: 00: 00: 00.00
11:18:19 SQL> SELECT d.DBID,
11:18:20 2 d.DB_UNIQUE_NAME,
11:18:20 3 d.FORCE_LOGGING,
11:18:20 4 d.FLASHBACK_ON,
11:18:20 5 d.FS_FAILOVER_STATUS,
11:18:20 6 d.FS_FAILOVER_CURRENT_TARGET,
11:18:20 7 d.FS_FAILOVER_THRESHOLD,
11:18:20 8 d.FS_FAILOVER_OBSERVER_PRESENT,
11:18:20 9 d.FS_FAILOVER_OBSERVER_HOST
11:18:20 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 SYNCHRONIZED oradgphy 30 YES rhel6_lhr
已用时间: 00: 00: 00.00
11:18:20 SQL> shutdown abort;
ORACLE 例程已经关闭。
11:18:27 SQL>
1.4.5.2 查看告警日志及server窗口
11:18:58.99 2015年9月29日 星期二
正在为数据库 "oradgphy" 启动快速启动故障转移...
立即执行故障转移, 请稍候...
故障转移成功, 新的主数据库为 "oradgphy"
11:19:04.72 2015年9月29日 星期二
[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradgphy_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 verbose
配置 - fsf_oradg11g_lhr
保护模式: MaxAvailability
数据库:
oradgphy - 主数据库
警告: ORA-16817: 快速启动故障转移配置不同步
oradg11g - (*) 物理备用数据库 (禁用)
ORA-16661: 需要恢复备用数据库
(*) 快速启动故障转移目标
属性:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
快速启动故障转移: ENABLED
阈值: 30 秒
目标: oradg11g
观察程序: rhel6_lhr
滞后限制: 30 秒 (未使用)
关闭主数据库: TRUE
自动恢复: TRUE
配置状态:
WARNING
DGMGRL>
备库告警日志:
Tue Sep 29 11:18:26 2015
RFS[5]: Possible network disconnect with primary database
Tue Sep 29 11:18:26 2015
RFS[6]: Assigned to RFS process 46955
RFS[6]: Possible network disconnect with primary database
Tue Sep 29 11:18:26 2015
RFS[7]: Assigned to RFS process 46921
RFS[7]: Possible network disconnect with primary database
Tue Sep 29 11:18:58 2015
Attempting Fast-Start Failover because the threshold of 30 seconds has elapsed.
Tue Sep 29 11:18:59 2015
Data Guard Broker: Beginning failover
Tue Sep 29 11:18:59 2015
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Tue Sep 29 11:18:59 2015
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_pr00_46860.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2550301
Tue Sep 29 11:18:59 2015
MRP0: Background Media Recovery process shutdown (oradgphy)
Managed Standby Recovery Canceled (oradgphy)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
Attempt to do a Terminal Recovery (oradgphy)
Media Recovery Start: Managed Standby Recovery (oradgphy)
started logmerger process
Tue Sep 29 11:19:00 2015
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Terminal Recovery timestamp is '09/29/2015 11:19:00'
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 163 redo required
Terminal Recovery:
Recovery of Online Redo Log: Thread 1 Group 4 Seq 163 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/oradgphy/standby_redo04.log
Identified End-Of-Redo (failover) for thread 1 sequence 163 at SCN 0xffff.ffffffff
Incomplete Recovery applied until change 2550302 time 09/29/2015 11:18:25
Media Recovery Complete (oradgphy)
Terminal Recovery: successful completion
Tue Sep 29 11:19:00 2015
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance oradgphy - Archival Error
ORA-16014: log 4 sequence# 163 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo04.log'
Forcing ARSCN to IRSCN for TR 0:2550302
Attempt to set limbo arscn 0:2550302 irscn 0:2550302
Resetting standby activation ID 1419115888 (0x5495fd70)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
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.
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'
CLOSE: all sessions shutdown successfully.
Tue Sep 29 11:19:03 2015
SMON: disabling cache recovery
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_rsm0_45481.trc
Standby terminal recovery start SCN: 2550301
RESETLOGS after incomplete recovery UNTIL CHANGE 2550302
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: 2550300
Tue Sep 29 11:19:03 2015
Setting recovery target incarnation to 5
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
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
ALTER DATABASE OPEN
Data Guard Broker initializing...
Tue Sep 29 11:19:03 2015
Assigning activation ID 1419206889 (0x549760e9)
LGWR: Primary database is in MAXIMUM AVAILABILITY mode
LGWR: Destination LOG_ARCHIVE_DEST_2 is using asynchronous network I/O
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
Thread 1 advanced to log sequence 2 (thread open)
Tue Sep 29 11:19:03 2015
ARC3: Becoming the 'no SRL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC3: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 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
Tue Sep 29 11:19:03 2015
NSA2 started with pid=17, OS id=49982
Error 1034 received logging on to the standby
ARC3: Error 1034 Creating archive log file to 'tns_oradg11g_dgmgrl'
Archived Log entry 152 added for thread 1 sequence 1 ID 0x549760e9 dest 1:
Archiver process freed from errors. No longer stopped
Tue Sep 29 11:19:03 2015
Error 1034 received logging on to the standby
PING[ARC2]: Heartbeat failed to connect to standby 'tns_oradg11g_dgmgrl'. Error is 1034.
[45481] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:44509384 end:44509514 diff:130 (1 seconds)
Dictionary check beginning
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
Tue Sep 29 11:19:04 2015
SMCO started with pid=18, OS id=49996
No Resource Manager plan active
Starting background process QMNC
Tue Sep 29 11:19:04 2015
QMNC started with pid=20, OS id=49998
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_archive_format='%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 log_archive_dest_state_2='RESET' SCOPE=BOTH;
Failover succeeded. Primary database is now oradgphy.
Tue Sep 29 11:19:04 2015
idle dispatcher 'D000' terminated, pid = (17, 1)
Starting background process CJQ0
Tue Sep 29 11:19:04 2015
CJQ0 started with pid=34, OS id=50027
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/oradgphy/redo03.log
ARC3: STARTING ARCH PROCESSES
Tue Sep 29 11:19:06 2015
ARC4 started with pid=26, OS id=50033
Tue Sep 29 11:19:07 2015
FSFP started with pid=35, OS id=50037
ARC4: Archival started
ARC3: STARTING ARCH PROCESSES COMPLETE
ARC3: Becoming the 'no SRL' ARCH
krsk_srl_archive_int: Enabling archival of deferred physical standby SRLs
Archived Log entry 153 added for thread 1 sequence 2 ID 0x549760e9 dest 1:
Archived Log entry 154 added for thread 1 sequence 163 ID 0x5495fd70 dest 1:
Shutting down archive processes
ARCH shutting down
ARC4: Archival stopped
Tue Sep 29 11:21:44 2015
ARC0: Becoming the 'no SRL' ARCH
Tue Sep 29 11:21:45 2015
ARC1: Becoming the 'no SRL' ARCH
1.4.5.3 在sqlplus手动启动原主库到mount状态,并观察dgmgrl的server状态
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 11:22:55 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
已连接到空闲例程。
11:22:55 SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 385879344 bytes
Database Buffers 20971520 bytes
Redo Buffers 8466432 bytes
数据库装载完毕。
Server 窗口:
11:24:55.93 2015年9月29日 星期二
正在为数据库 "oradg11g" 启动恢复过程...
正在恢复数据库 "oradg11g", 请稍候...
操作要求关闭实例 "oradg11g" (在数据库 "oradg11g" 上)
正在关闭实例 "oradg11g"...
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
操作要求启动实例 "oradg11g" (在数据库 "oradg11g" 上)
正在启动实例 "oradg11g"...
ORACLE 例程已经启动。
数据库装载完毕。
继续恢复数据库 "oradg11g"...
已成功恢复数据库 "oradg11g"
11:26:03.30 2015年9月29日 星期二
DGMGRL> show configuration verbose
配置 - fsf_oradg11g_lhr
保护模式: MaxAvailability
数据库:
oradgphy - 主数据库
oradg11g - (*) 物理备用数据库
(*) 快速启动故障转移目标
属性:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
快速启动故障转移: ENABLED
阈值: 30 秒
目标: oradg11g
观察程序: rhel6_lhr
滞后限制: 30 秒 (未使用)
关闭主数据库: TRUE
自动恢复: TRUE
配置状态:
ORA-16610: 命令 "REINSTATE DATABASE oradg11g" 正在进行中
DGM-17017: 无法确定配置状态
DGMGRL> show configuration verbose
配置 - fsf_oradg11g_lhr
保护模式: MaxAvailability
数据库:
oradgphy - 主数据库
oradg11g - (*) 物理备用数据库
(*) 快速启动故障转移目标
属性:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
快速启动故障转移: ENABLED
阈值: 30 秒
目标: oradg11g
观察程序: rhel6_lhr
滞后限制: 30 秒 (未使用)
关闭主数据库: TRUE
自动恢复: TRUE
配置状态:
SUCCESS
DGMGRL>
可以看到状态正常, FSF生效。
1.4.5.4 测试新的环境是否同步
11:26:56 SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 7
下一个存档日志序列 9
当前日志序列 9
11:31:46 SQL> set line 9999
11:31:50 SQL> col name format a10
col FS_FAILOVER_OBSERVER_HOST format a20
11:31:50 SQL> col DB_UNIQUE_NAME format a15
11:31:50 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 2551424 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY YES READ WRITE TO STANDBY
已用时间: 00: 00: 00.00
11:31:50 SQL> SELECT d.DBID,
11:31:50 2 d.DB_UNIQUE_NAME,
11:31:50 3 d.FORCE_LOGGING,
11:31:50 4 d.FLASHBACK_ON,
11:31:50 5 d.FS_FAILOVER_STATUS,
11:31:50 6 d.FS_FAILOVER_CURRENT_TARGET,
11:31:50 7 d.FS_FAILOVER_THRESHOLD,
11:31:50 8 d.FS_FAILOVER_OBSERVER_PRESENT,
11:31:50 9 d.FS_FAILOVER_OBSERVER_HOST
11:31:50 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 SYNCHRONIZED oradg11g 30 YES rhel6_lhr
已用时间: 00: 00: 00.00
已用时间: 00: 00: 00.07
11:32:56 SQL> create table lhr.testfsfdg as select * from dual;
表已创建。
已用时间: 00: 00: 00.36
11:33:05 SQL> select * from lhr.testfsfdg ;
D
-
X
已用时间: 00: 00: 00.01
11:33:15 SQL>
备库:
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 11:31: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
11:31:39 SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 8
下一个存档日志序列 0
当前日志序列 9
11:31:41 SQL> set line 9999
11:31:55 SQL> col name format a10
11:31:55 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
11:31:55 SQL> col DB_UNIQUE_NAME format a15
11:31:55 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
SELECT d.DBID,
DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G oradg11g 2551429 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
已用时间: 00: 00: 00.01
11:31:55 SQL> 11:31:55 2 d.DB_UNIQUE_NAME,
11:31:55 3 d.FORCE_LOGGING,
11:31:55 4 d.FLASHBACK_ON,
11:31:55 5 d.FS_FAILOVER_STATUS,
11:31:55 6 d.FS_FAILOVER_CURRENT_TARGET,
11:31:55 7 d.FS_FAILOVER_THRESHOLD,
11:31:55 8 d.FS_FAILOVER_OBSERVER_PRESENT,
11:31:55 9 d.FS_FAILOVER_OBSERVER_HOST
11:31:55 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 SYNCHRONIZED oradg11g 30 YES rhel6_lhr
已用时间: 00: 00: 00.01
11:31:55 SQL> select * from lhr.testfsfdg ;
D
-
X
已用时间: 00: 00: 00.00
11:33:21 SQL>
11:33:21 SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 8
下一个存档日志序列 0
当前日志序列 9
11:35:39 SQL>
可以看到日志序列号已经重新开始了。
1.4.5.5 重新shutdown abort主库回到最初的oradg11g为主库,oradgphy为备库的状态
我们重新shutdown abort主库回到最初的oradg11g为主库,oradgphy为备库的状态,
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 13:43:03 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
13:43:03 SQL> set line 9999
13:43:17 SQL> col name format a10
13:43:17 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
13:43:17 SQL> col DB_UNIQUE_NAME format a15
13:43:17 SQL> select dbid,name, DB_UNIQUE_NAME,RESETLOGS_CHANGE#,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME DB_UNIQUE_NAME RESETLOGS_CHANGE# CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- --------------- ----------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G oradgphy 2550303 2575340 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY YES READ WRITE TO STANDBY
已用时间: 00: 00: 00.01
13:43:17 SQL> SELECT d.DBID,
13:43:17 2 d.DB_UNIQUE_NAME,
13:43:17 3 d.FORCE_LOGGING,
13:43:17 4 d.FLASHBACK_ON,
13:43:17 5 DATAGUARD_BROKER,
13:43:17 6 d.FS_FAILOVER_STATUS,
13:43:17 7 d.FS_FAILOVER_CURRENT_TARGET,
13:43:17 8 d.FS_FAILOVER_THRESHOLD,
13:43:17 9 d.FS_FAILOVER_OBSERVER_PRESENT,
13:43:17 10 d.FS_FAILOVER_OBSERVER_HOST
13:43:17 11 FROM v$database d;
DBID DB_UNIQUE_NAME FOR FLASHBACK_ON DATAGUAR FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER
---------- --------------- --- ------------------ -------- ---------------------- ------------------------------ --------------------- ------- --------------------
1403587593 oradgphy YES YES ENABLED SYNCHRONIZED oradg11g 30 YES rhel6_lhr
已用时间: 00: 00: 00.00
13:43:17 SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 12
下一个存档日志序列 14
当前日志序列 14
13:43:21 SQL> shutdown abort;
ORACLE 例程已经关闭。
13:43:26 SQL>
手动启动备库到mount状态后继续查看:
13:45:15 SQL> set line 9999
13:48:27 SQL> col name format a10
13:48:27 SQL> col FS_FAILOVER_OBSERVER_HOST format a20
13:48:27 SQL> col DB_UNIQUE_NAME format a15
13:48:27 SQL> select dbid,name, DB_UNIQUE_NAME,RESETLOGS_CHANGE#,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME DB_UNIQUE_NAME RESETLOGS_CHANGE# CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- --------------- ----------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G oradg11g 2575356 2575896 MAXIMUM AVAILABILITY RESYNCHRONIZATION PRIMARY YES READ WRITE NOT ALLOWED
已用时间: 00: 00: 00.00
13:48:27 SQL> SELECT d.DBID,
13:48:27 2 d.DB_UNIQUE_NAME,
13:48:27 3 d.FORCE_LOGGING,
13:48:27 4 d.FLASHBACK_ON,
13:48:27 5 DATAGUARD_BROKER,
13:48:27 6 d.FS_FAILOVER_STATUS,
13:48:27 7 d.FS_FAILOVER_CURRENT_TARGET,
13:48:27 8 d.FS_FAILOVER_THRESHOLD,
13:48:27 9 d.FS_FAILOVER_OBSERVER_PRESENT,
13:48:27 10 d.FS_FAILOVER_OBSERVER_HOST
13:48:27 11 FROM v$database d;
DBID DB_UNIQUE_NAME FOR FLASHBACK_ON DATAGUAR FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER
---------- --------------- --- ------------------ -------- ---------------------- ------------------------------ --------------------- ------- --------------------
1403587593 oradg11g YES YES ENABLED REINSTATE REQUIRED oradgphy 30 YES rhel6_lhr
已用时间: 00: 00: 00.00
13:48:27 SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 1
下一个存档日志序列 3
当前日志序列 3
13:49:46 SQL>
至此,Fast-Start Failover 的配置及其测试完成。
1.5 总结
本篇为第二节,请查看第三节: Oracle DataGuard 之客户端TAF 配置
1.6 About Me
About Me
.............................................................................................................................................● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用
● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新
● 本文itpub地址:http://blog.itpub.net/26736162/viewspace-1811936/
● 本文博客园地址:http://www.cnblogs.com/lhrbest
● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/
● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群号:230161599(满)、618766405
● 微信群:可加我微信,我拉大家进群,非诚勿扰
● 联系我请加QQ好友(646634621),注明添加缘由
● 于 2015-09-28 09:00~ 2015-09-29 23:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解
● 版权所有,欢迎分享本文,转载请保留出处
.............................................................................................................................................
● 小麦苗的微店:https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麦苗出版的数据库类丛书:http://blog.itpub.net/26736162/viewspace-2142121/
.............................................................................................................................................
使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(xiaomaimiaolhr)及QQ群(DBA宝典),学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群1 小麦苗的DBA宝典QQ群2 小麦苗的微店
.............................................................................................................................................