11g DataGuard实现故障转移（Failover）

OS：Oracle Linux 6.6 x64
DB：Oracle 11.2.0.4


假设是物理备库。如果有多个备用库，选择SYNC备用数据库（即最高可用或最大保护模式），如果是ASYNC，比较哪个备库最新：

点击(此处)折叠或打开

1. SQL> SELECT THREAD#,SEQUENCE#,LAST_CHANGE#,LAST_TIME FROM V$STANDBY_LOG;
   
2. 
   
3.    THREAD# SEQUENCE# LAST_CHANGE# LAST_TIME
   
4. ---------- ---------- ------------ -------------------
   
5.          1 90 2298683 2016-06-27 15:36:39
   
6.          1 0
   
7.          1 0
   
8.          0 0

在决定了哪个备库将成为新主库后，在其上执行：

点击(此处)折叠或打开

1. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   
2. 
   
3. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

alert日志输出：

点击(此处)折叠或打开

1. Attempt to do a Terminal Recovery (jiangsu)
   
2. Media Recovery Start: Managed Standby Recovery (jiangsu)
   
3.  started logmerger process
   
4. Mon Jun 27 15:43:50 2016
   
5. Managed Standby Recovery not using Real Time Apply
   
6. Parallel Media Recovery started with 4 slaves
   
7. Media Recovery Waiting for thread 1 sequence 90 (in transit)
   
8. Killing 3 processes with pids 32087,10071,2798 (all RFS, wait for I/O) in order to disallow current and future RFS connections. Requested by OS process 2800
   
9. Begin: Standby Redo Logfile archival
   
10. End: Standby Redo Logfile archival
    
11. Terminal Recovery timestamp is '06/27/2016 15:43:54'
    
12. Terminal Recovery: applying standby redo logs.
    
13. Terminal Recovery: thread 1 seq# 90 redo required
    
14. Terminal Recovery:
    
15. Recovery of Online Redo Log: Thread 1 Group 4 Seq 90 Reading mem 0
    
16.   Mem# 0: /oradata/JIANGSU/onlinelog/o1_mf_4_cps7ymfr_.log
    
17.   Mem# 1: /fra/JIANGSU/onlinelog/o1_mf_4_cps7yq8o_.log
    
18. Identified End-Of-Redo (failover) for thread 1 sequence 90 at SCN 0xffff.ffffffff
    
19. Incomplete Recovery applied until change 2299105 time 06/27/2016 15:43:38
    
20. Media Recovery Complete (jiangsu)
    
21. Terminal Recovery: successful completion
    
22. Forcing ARSCN to IRSCN for TR 0:2299105Mon Jun 27 15:43:56 2016
    
23. 
    
24. ARCH: Archival stopped, error occurred. Will continue retrying
    
25. Attempt to set limbo arscn 0:2299105 irscn 0:2299105 ORACLE Instance jiangsu - Archival Error
    
26. 
    
27. ORA-16014: log 4 sequence# 90 not archived, no available destinations
    
28. ORA-00312: online log 4 thread 1: '/oradata/JIANGSU/onlinelog/o1_mf_4_cps7ymfr_.log'
    
29. ORA-00312: online log 4 thread 1: '/fra/JIANGSU/onlinelog/o1_mf_4_cps7yq8o_.log'
    
30. Resetting standby activation ID 3995005644 (0xee1ef2cc)
    
31. Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH
    
32. Mon Jun 27 15:44:44 2016
    
33. RFS[16]: Assigned to RFS process 2829
    
34. RFS[16]: No connections allowed during/after terminal recovery.

转换成主库：

点击(此处)折叠或打开

1. SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

alert日志输出：

点击(此处)折叠或打开

1. ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
   
2. ALTER DATABASE SWITCHOVER TO PRIMARY (jiangsu)
   
3. Maximum wait for role transition is 15 minutes.
   
4. Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/jiangsu/jiangsu/trace/jiangsu_ora_2578.trc
   
5. Standby terminal recovery start SCN: 2299056
   
6. RESETLOGS after incomplete recovery UNTIL CHANGE 2299105
   
7. Online log /oradata/JIANGSU/onlinelog/o1_mf_1_cps7x0ox_.log: Thread 1 Group 1 was previously cleared
   
8. Online log /fra/JIANGSU/onlinelog/o1_mf_1_cps7x0wf_.log: Thread 1 Group 1 was previously cleared
   
9. Online log /oradata/JIANGSU/onlinelog/o1_mf_2_cps7xk0t_.log: Thread 1 Group 2 was previously cleared
   
10. Online log /fra/JIANGSU/onlinelog/o1_mf_2_cps7xk70_.log: Thread 1 Group 2 was previously cleared
    
11. Online log /oradata/JIANGSU/onlinelog/o1_mf_3_cps7y2w2_.log: Thread 1 Group 3 was previously cleared
    
12. Online log /fra/JIANGSU/onlinelog/o1_mf_3_cps7y33x_.log: Thread 1 Group 3 was previously cleared
    
13. Standby became primary SCN: 2299055
    
14. Mon Jun 27 15:50:25 2016
    
15. Setting recovery target incarnation to 4
    
16. Switchover: Complete - Database mounted as primary
    
17. Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

接下来就可以打开了：

点击(此处)折叠或打开

1. SQL> ALTER DATABASE OPEN;
   
2. 
   
3. SQL> select name,open_mode from v$database;
   
4. 
   
5. NAME OPEN_MODE
   
6. --------- --------------------
   
7. SHANGHAI READ WRITE

---

恢复原始数据库


原始数据库在失败前启用了FLASHBACK，就可以用以下的方式恢复，否则只能重建DataGuard。


原始备库（当前主库）上执行，确认故障转移时的SCN：

点击(此处)折叠或打开

1. SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FAILOVER_SCN
   
2. FROM V$DATABASE;
   
3. 
   
4. FAILOVER_SCN
   
5. ----------------------------------------
   
6. 2299055

原始主库上执行

点击(此处)折叠或打开

1. SQL> startup mount
   
2. 
   
3. FLASHBACK DATABASE TO SCN 2299055;
   
4. 
   
5. ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

然后重启到mount模式，启动redo apply

点击(此处)折叠或打开

1. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
   
2. USING CURRENT LOGFILE DISCONNECT;

等完成了同步，就可以执行正常切换了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22621861/viewspace-2121089/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-2121089/