11g DataGuard實現故障轉移（Failover）

hooca發表於2016-06-28
原文網址 : http://blog.itpub.net/22621861/viewspace-2121089/
AI
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;

  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;

  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

  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

  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;

  3. SQL> select name,open_mode from v$database;

  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;

  4. FAILOVER_SCN
  5. ----------------------------------------
  6. 2299055
原始主庫上執行

點選(此處)摺疊或開啟

  1. SQL> startup mount

  3. FLASHBACK DATABASE TO SCN 2299055;

  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章