Oracle 11G數(shù)據(jù)庫DataGuard災(zāi)備切換方案

1、Oracle 11G數(shù)據(jù)庫DataGuard災(zāi)備切換方案一、 檢查1、 確定MRP進(jìn)程在正常運行備庫執(zhí)行如下SQL確定MRP進(jìn)程正常：SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%'主庫執(zhí)行如下SQL，確定備庫是“REAL TIME APPLY”狀態(tài)SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;RECOVERY_MODE-MANAGED REAL TIME APPLY如果備庫沒有啟用real-time a

2、pply，則需要重新將備庫啟動至real-time apply：SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;2、 確定有足夠的歸檔進(jìn)程在所有的主備庫實例上查詢參數(shù)LOG_ARCHIVE_MAX_PROCESSES，確定其值大于等于4，但不會太大3、 確定目標(biāo)備庫的REDO為clear狀態(tài)雖然在發(fā)起SWITCHOVER TO PRIMARY

3、命令時，備庫的REDO會自動轉(zhuǎn)換為CLEAR狀態(tài)，但依然建議在SWITCHOVER前REDO為CLEAR狀態(tài)。確保正確設(shè)置了LOG_FILE_NAME_CONVERT參數(shù)。使用如下SQL在目標(biāo)備庫上查看REDO狀態(tài):SQL> SELECT DISTINCT L.GROUP# FROM V$LOG L, V$LOGFILE LF     WHERE L.GROUP# = LF.GROUP#     AND L.STATUS NOT IN (UNUSED, CLEARING,CLEARING_CURRENT);如

4、果如上的查詢有結(jié)果，則需要停止備庫的REDOAPPLY，并通過如下的SQL來對其進(jìn)行CLEARSQL> ALTER DATABASE CLEAR LOGFILE GROUP <ORL GROUP# from the query above>4、 確定沒有大量的GAP主庫執(zhí)行如下SQL查看主庫當(dāng)前的REDO SEQUENCESQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;在備庫上執(zhí)行如下查詢，確定查詢出來的結(jié)果與上面的結(jié)果相比較只差1-2個數(shù)值SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIV

5、ED_LOGWHERE APPLIED = 'YES'AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#FROM V$DATABASE_INCARNATION WHERE STATUS = CURRENT)GROUP BY THREAD#;5、 確定主庫以及目標(biāo)備庫的所有文件都為ONLINE主備庫分別執(zhí)行如下SQL，查看tempfile是否正常，如果備庫上缺失文件則需要進(jìn)行處理：SELECT TMP.NAME FILENAME, BYTES, TS.NAME TABLESPACEFROM V$TEMPFILE TMP, V$TAB

6、LESPACE TS WHERE TMP.TS#=TS.TS#;在主備庫分別執(zhí)行如下SQL，查看數(shù)據(jù)文件狀態(tài)，結(jié)果應(yīng)該一致SELECT NAME FROM V$DATAFILE WHERE STATUS=OFFLINE;如果備庫上有比主庫多出的OFFLINE狀態(tài)的數(shù)據(jù)文件，則將其ONLINE：ALTER DATABASE DATAFILE &FILE_ID ONLINE;二、 切換1、 檢查主庫是否可切換至STANDBY主庫執(zhí)行如下SQL執(zhí)行檢查SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS-TO S

7、TANDBY如上的SQL查詢結(jié)果如果為”TO STANDBY” 或者”SESSIONS ACTIVE”表示主庫可切換至STANDBY，如果不為這兩個值，則說明REDO傳輸存在問題。2、 停止主庫第一個節(jié)點以外的所有實例（RAC）最好使用shutdown normal或者shutdown immediate方式停止數(shù)據(jù)庫。如果使用了shutdown abort將其他節(jié)點進(jìn)行了關(guān)閉，則需等待RAC reconfig完成，且第一個節(jié)點將其余REDO正常前滾或回滾3、 切換主庫至STANDBY角色將主庫切換至STANDBYALTER DATABASE COMMIT TO SWITCHOVER TO S

8、TANDBY WITH SESSION SHUTDOWN;如果遇到ORA-16139報錯，且V$DATABASE視圖中DATABASE_ROLE字段的值已為”PHYSICAL STANDBY”，則可繼續(xù)（這種問題的出現(xiàn)其中一個可能是數(shù)據(jù)庫有大量的數(shù)據(jù)文件）。4、 確定STANDBY收到EOR在主庫的ALERT日志中可以看到類似如下的信息：Switchover: Primary controlfile converted to standby controlfile succesfully.Tue Mar 15 16:12:15 2011MRP0 started with pid=17, OS

9、id=2717 MRP0: Background Managed Standby Recovery process started (SFO)Serial Media Recovery startedManaged Standby Recovery not using Real Time ApplyOnline logfile pre-clearing operation disabled by switchoverMedia Recovery Log /u01/app/flash_recovery_area/SFO/archivelog/2011_03_15/o1_mf_1_133

10、_6qzl0yvd_.arcIdentified End-Of-Redo for thread 1 sequence 133Resetting standby activation ID 0 (0x0)Media Recovery End-Of-Redo indicator encounteredMedia Recovery Applied until change 4314801MRP0: Media Recovery Complete: End-Of-REDO (SFO)MRP0: Background Media Recovery process shutdown (SFO)Tue Ma

11、r 15 16:12:21 2011Switchover: Complete - Database shutdown required (SFO)Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN同時在所有備庫的ALERT日志中可以看到類似如下的信息：Tue Mar 15 16:12:15 2011RFS8: Assigned to RFS process 2715RFS8: Identified database type as 'physical stand

12、by': Client is Foreground pid 2568Media Recovery Log /u01/app/flash_recovery_area/NYC/archivelog/2011_03_15/o1_mf_1_133_6qzl0yjp_.arcIdentified End-Of-Redo for thread 1 sequence 133Resetting standby activation ID 2680651518 (0x9fc77efe)Media Recovery End-Of-Redo indicator encounteredMedia Recove

13、ry ContinuingResetting standby activation ID 2680651518 (0x9fc77efe)Media Recovery Waiting for thread 1 sequence 1345、 檢查STANDBY能夠切換至PRIMARY目標(biāo)備庫上執(zhí)行如下SQL進(jìn)行檢查SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS-TO PRIMARY如上的SQL查詢結(jié)果如果為”TO PRIMARY” 或者”SESSIONS ACTIVE”表示目標(biāo)備庫可切換至PRIMARY，如果不為

14、這兩個值，則說明REDO傳輸或者應(yīng)用存在問題。6、 切換備庫至PRIMARY在目標(biāo)備庫執(zhí)行如下命令A(yù)LTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;同時在alert日志中有類似如下信息Tue Mar 15 16:16:44 2011ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWNALTER DATABASE SWITCHOVER TO PRIMARY (NYC)Maximum wait for role transiti

15、on is 15 minutes.Switchover: Media recovery is still activeRole Change: Canceling MRP - no more redo to applyTue Mar 15 16:16:45 2011MRP0: Background Media Recovery cancelled with status 16037Errors in file /u01/app/diag/rdbms/nyc/NYC/trace/NYC_pr00_2467.trc:ORA-16037: user requested cancel of manag

16、ed recovery operationManaged Standby Recovery not using Real Time ApplyRecovery interrupted!Waiting for MRP0 pid 2460 to terminateErrors in file /u01/app/diag/rdbms/nyc/NYC/trace/NYC_pr00_2467.trc:ORA-16037: user requested cancel of managed recovery operationTue Mar 15 16:16:45 2011MRP0: Background

17、Media Recovery process shutdown (NYC)Role Change: Canceled MRP7、 打開新的主庫在新的主庫上打開數(shù)據(jù)庫ALTER DATABASE OPEN;8、 檢查新主庫的TEMPFILE如果存在問題則進(jìn)行處理。9、 重啟新的備庫首先停止新的備庫SHUTDOWN ABORT;注：如果使用immediate停止數(shù)據(jù)庫，則其依然會使用abort方式停止數(shù)據(jù)庫，會在alert日志中看到類似如下信息：Performing implicit shutdown abort due to switchover to physical standbyShutt

18、ing down instance (abort)License high water mark = 15USER (ospid: 14665): terminating the instanceInstance terminated by USER, pid = 14665啟動新的備庫：SQL> STARTUP MOUNT;SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;10、 意外或回退參考Appendix A.4.5 Roll

19、Back After Unsuccessful Switchover and Start Over三、 無法正常切換的處理若主數(shù)據(jù)庫異常中斷無法連接做switchover處理，需要將災(zāi)備環(huán)境強制切換為主庫(即failover)，需要注意的是，此種切換是將備庫強制進(jìn)行切換，可能會由于主備庫之間并未完全同步導(dǎo)致有數(shù)據(jù)丟失，需慎重處理。1、 檢查備庫是否可正常切換至PRIMARY備庫執(zhí)行如下SQL執(zhí)行檢查SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS - -

20、 PHYSICAL STANDBY NOT ALLOWED如上的SQL查詢結(jié)果如果為”TO PRIMARY” 或者”SESSIONS ACTIVE”表示目標(biāo)備庫可正常切換至PRIMARY，如果不為這兩個值，則說明REDO傳輸或者應(yīng)用存在問題，則需要執(zhí)行強制切換。2、 關(guān)閉備庫的MRP進(jìn)程在目標(biāo)備庫執(zhí)行如下命令A(yù)LTER DATABASE RECOVER MANAGED STADNBY DATABASE FINISH;同時在alert日志中有類似如下信息ALTER DATABASE RECOVER managed standby database finish Terminal Recovery

21、: request posted (DMPDB) Wed Mar 04 21:34:34 2015 Begin: Standby Redo Logfile archival End: Standby Redo Logfile archival Terminal Recovery timestamp is '03/04/2015 21:34:34' Terminal Recovery: applying standby redo logs. Terminal Recovery: thread 1 seq# 34 redo required Media Recovery Waiti

22、ng for thread 1 sequence 34 Terminal Recovery: End-Of-Redo log allocation Terminal Recovery: standby redo logfile 4 created '/archivelog/dmpdb/arch_1_0_820054583.log' This standby redo logfile is being created as part of the failover operation. This standby redo logfile should be deleted aft

23、er the switchover to primary operation completes. Media Recovery Log /archivelog/dmpdb/arch_1_0_820054583.log Terminal Recovery: log 4 reserved for thread 1 sequence 34 Recovery of Online Redo Log: Thread 1 Group 4 Seq 34 Reading mem 0 Mem# 0: /archivelog/dmpdb/arch_1_0_820054583.log Identified End-

24、Of-Redo (failover) for thread 1 sequence 34 at SCN 0xffff.ffffffff Incomplete Recovery applied until change 1234252 time 03/04/2015 21:23:43 MRP0: Media Recovery Complete (DMPDB) Terminal Recovery: successful completion Wed Mar 04 21:34:35 2015 ARCH: Archival stopped, error occurred. Will continue r

25、etrying ORACLE Instance DMPDB - Archival Error ORA-16014: log 4 sequence# 34 not archived, no available destinations ORA-00312: online log 4 thread 1: '/archivelog/dmpdb/arch_1_0_820054583.log' Forcing ARSCN to IRSCN for TR 0:1234252 Attempt to set limbo arscn 0:1234252 irscn 0:1234252 Reset

26、ting standby activation ID 2865247982 (0xaac836ee) MRP0: Background Media Recovery process shutdown (DMPDB) Terminal Recovery: completion detected (DMPDB) Completed: ALTER DATABASE RECOVER MANAGED STADNBY DATABASE FINISH3、 切換數(shù)據(jù)庫到Primary執(zhí)行如下SQL檢查備庫的狀態(tài)SQL> select database_role,switchover_status fro

27、m v$database; DATABASE_ROLE SWITCHOVER_STATUS - - PHYSICAL STANDBY TO PRIMARY如果結(jié)果為“TO PRIMARY”則說明已經(jīng)可以切換到主庫，繼續(xù)如下操作。將備庫切換為主庫SQL> alter database commit to switchover to primary; Database altered.打開新的主庫SQL> alter database open; Database altered.alert日志中看到類似如下信息：alter database commit to switchover to primary ALTER DATABASE SWITCHOVER TO PRIMARY (DMPDB) Maximum wait for role transition is 15 minutes. All dispatchers and shared servers shutdown CLOSE: killing server sessions. CLOSE: all sessions shutdown successfully. Wed Mar 04 21: