Oracle 11G数据库DataGuard灾备切换方案

1、Oracle 11G数据库DataGuard灾备切换方案一、 检查1、 确定MRP进程在正常运行备库执行如下SQL确定MRP进程正常：SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%'主库执行如下SQL，确定备库是“REAL TIME APPLY”状态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、 确定有足够的归档进程在所有的主备库实例上查询参数LOG_ARCHIVE_MAX_PROCESSES，确定其值大于等于4，但不会太大3、 确定目标备库的REDO为clear状态虽然在发起SWITCHOVER TO PRIMARY

3、命令时，备库的REDO会自动转换为CLEAR状态，但依然建议在SWITCHOVER前REDO为CLEAR状态。确保正确设置了LOG_FILE_NAME_CONVERT参数。使用如下SQL在目标备库上查看REDO状态: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、果如上的查询有结果，则需要停止备库的REDOAPPLY，并通过如下的SQL来对其进行CLEARSQL> ALTER DATABASE CLEAR LOGFILE GROUP <ORL GROUP# from the query above>4、 确定没有大量的GAP主库执行如下SQL查看主库当前的REDO SEQUENCESQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;在备库上执行如下查询，确定查询出来的结果与上面的结果相比较只差1-2个数值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、 确定主库以及目标备库的所有文件都为ONLINE主备库分别执行如下SQL，查看tempfile是否正常，如果备库上缺失文件则需要进行处理：SELECT TMP.NAME FILENAME, BYTES, TS.NAME TABLESPACEFROM V$TEMPFILE TMP, V$TAB

6、LESPACE TS WHERE TMP.TS#=TS.TS#;在主备库分别执行如下SQL，查看数据文件状态，结果应该一致SELECT NAME FROM V$DATAFILE WHERE STATUS=OFFLINE;如果备库上有比主库多出的OFFLINE状态的数据文件，则将其ONLINE：ALTER DATABASE DATAFILE &FILE_ID ONLINE;二、 切换1、 检查主库是否可切换至STANDBY主库执行如下SQL执行检查SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS-TO S

7、TANDBY如上的SQL查询结果如果为”TO STANDBY” 或者”SESSIONS ACTIVE”表示主库可切换至STANDBY，如果不为这两个值，则说明REDO传输存在问题。2、 停止主库第一个节点以外的所有实例（RAC）最好使用shutdown normal或者shutdown immediate方式停止数据库。如果使用了shutdown abort将其他节点进行了关闭，则需等待RAC reconfig完成，且第一个节点将其余REDO正常前滚或回滚3、 切换主库至STANDBY角色将主库切换至STANDBYALTER DATABASE COMMIT TO SWITCHOVER TO S

8、TANDBY WITH SESSION SHUTDOWN;如果遇到ORA-16139报错，且V$DATABASE视图中DATABASE_ROLE字段的值已为”PHYSICAL STANDBY”，则可继续（这种问题的出现其中一个可能是数据库有大量的数据文件）。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目标备库上执行如下SQL进行检查SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS-TO PRIMARY如上的SQL查询结果如果为”TO PRIMARY” 或者”SESSIONS ACTIVE”表示目标备库可切换至PRIMARY，如果不为

14、这两个值，则说明REDO传输或者应用存在问题。6、 切换备库至PRIMARY在目标备库执行如下命令ALTER 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、 打开新的主库在新的主库上打开数据库ALTER DATABASE OPEN;8、 检查新主库的TEMPFILE如果存在问题则进行处理。9、 重启新的备库首先停止新的备库SHUTDOWN ABORT;注：如果使用immediate停止数据库，则其依然会使用abort方式停止数据库，会在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三、 无法正常切换的处理若主数据库异常中断无法连接做switchover处理，需要将灾备环境强制切换为主库(即failover)，需要注意的是，此种切换是将备库强制进行切换，可能会由于主备库之间并未完全同步导致有数据丢失，需慎重处理。1、 检查备库是否可正常切换至PRIMARY备库执行如下SQL执行检查SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS - -

20、 PHYSICAL STANDBY NOT ALLOWED如上的SQL查询结果如果为”TO PRIMARY” 或者”SESSIONS ACTIVE”表示目标备库可正常切换至PRIMARY，如果不为这两个值，则说明REDO传输或者应用存在问题，则需要执行强制切换。2、 关闭备库的MRP进程在目标备库执行如下命令ALTER 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、 切换数据库到Primary执行如下SQL检查备库的状态SQL> select database_role,switchover_status fro

27、m v$database; DATABASE_ROLE SWITCHOVER_STATUS - - PHYSICAL STANDBY TO PRIMARY如果结果为“TO PRIMARY”则说明已经可以切换到主库，继续如下操作。将备库切换为主库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: