Oracle 11g DG手工switchover切換標準化流程

知識 08-13

環境：RHEL 6.5 + Oracle GI 11.2.0.4 + Oracle DB 11.2.0.4 Primary RAC（2 nodes） + Standby RAC（2 nodes）

Oracle DG切換類型有兩種：switchover和failover。對於switchover而言，是計劃內的由DBA主動去執行的操作，所以它的操作步驟一定是可以形成標準化流程的。本文就在我的實驗環境下做一次基本的標準化switchover流程：

準備工作
1.主庫切換為備庫
2.備庫切換為主庫
3.新主庫open，新備庫啟動並開啟MRP
4.確定同步正常，啟動其他節點

準備工作

切換前準備：關閉主備庫RAC的其他節點

我這裡是分別關閉主備庫的第二個節點：

PRIMARY NODE2"Instance [grid@jyrac2 ~]$ srvctl stop instance -d jyzhao -i jyzhao2

STANDBY NODE2"Instance [grid@jystdrac2 ~]$ srvctl stop instance -d mynas -i jyzhao2

當然也可以SQLplus操作關閉其他節點的實例.

1.主庫切換為備庫

1.主庫切換為備庫:

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

觀察主庫alert日誌：

Sun Aug 13 09:54:53 2017 alter database commit to switchover to standby with session shutdown ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 13741] (jyzhao1) Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Waiting for all FAL entries to be archived... All FAL entries have been archived. Waiting for potential Physical Standby switchover target to become synchronized... Active, synchronized Physical Standby switchover target has been identified Sun Aug 13 09:54:56 2017 Errors in file /opt/app/oracle/diag/rdbms/jyzhao/jyzhao1/trace/jyzhao1_j000_29834.trc: ORA-12012: error on auto execute of job 3 ORA-16456: switchover to standby in progress or completed Switchover End-Of-Redo Log thread 1 sequence 182 has been fixed Switchover: Primary highest seen SCN set to 0x0.0x456df2 ARCH: Noswitch archival of thread 1, sequence 182 ARCH: End-Of-Redo Branch archival of thread 1 sequence 182 ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 ARCH: Standby redo logfile selected for thread 1 sequence 182 for destination LOG_ARCHIVE_DEST_2 Archived Log entry 411 added for thread 1 sequence 182 ID 0x958da9ee dest 1: ARCH: Archiving is disabled due to current logfile archival Primary will check for some target standby to have received alls redo Final check for a synchronized target standby. Check will be made once. Sun Aug 13 09:54:59 2017 Process (ospid 4297) is suspended due to switchover to physical standby operation. LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target Active, synchronized target has been identified Target has also received all redo Backup controlfile written to trace file /opt/app/oracle/diag/rdbms/jyzhao/jyzhao1/trace/jyzhao1_ora_13741.trc Clearing standby activation ID 2509089262 (0x958da9ee) The primary database controlfile was created using the "MAXLOGFILES 192" clause. There is space for up to 188 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE "srl1.f" SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE "srl2.f" SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE "srl3.f" SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE "srl4.f" SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE "srl5.f" SIZE 52428800; Archivelog for thread 1 sequence 182 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully. Switchover: Complete - Database shutdown required USER (ospid: 13741): terminating the instance Sun Aug 13 09:55:00 2017 ORA-1092 : opitsk aborting process Instance terminated by USER, pid = 13741 Completed: alter database commit to switchover to standby with session shutdown Shutting down instance (abort) License high water mark = 11 Sun Aug 13 09:55:01 2017 Instance shutdown complete

主要注意到正常應該有「End-Of-Redo Branch archival」字樣，並且最終成功切換到standby，最後資料庫是關閉的。

2.備庫切換為主庫

操作之前，可以看alert日誌，也可以使用SQL查詢是否可以切換：

select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;

2.備庫切換為主庫:

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

查看備庫alert日誌：

Sun Aug 13 09:58:30 2017 alter database commit to switchover to primary ALTER DATABASE SWITCHOVER TO PRIMARY (jyzhao1) Maximum wait for role transition is 15 minutes. Switchover: Media recovery is still active Role Change: Canceling MRP - no more redo to apply Sun Aug 13 09:58:31 2017 MRP0: Background Media Recovery cancelled with status 16037 Errors in file /opt/app/oracle/diag/rdbms/mynas/jyzhao1/trace/jyzhao1_mrp0_7745.trc: ORA-16037: user requested cancel of managed recovery operation Sun Aug 13 09:58:31 2017 Managed Standby Recovery not using Real Time Apply Recovery interrupted! MRP0: Background Media Recovery process shutdown (jyzhao1) Role Change: Canceled MRP All dispatchers and shared servers shutdown CLOSE: killing server sessions. CLOSE: all sessions shutdown successfully. Sun Aug 13 09:58:34 2017 SMON: disabling cache recovery Backup controlfile written to trace file /opt/app/oracle/diag/rdbms/mynas/jyzhao1/trace/jyzhao1_ora_7669.trc SwitchOver after complete recovery through change 4550130 Online log +DATA/mynas/onlinelog/group_1.266.951608731: Thread 1 Group 1 was previously cleared Online log +FRA/mynas/onlinelog/group_1.257.951608737: Thread 1 Group 1 was previously cleared Online log +DATA/mynas/onlinelog/group_2.267.951608745: Thread 1 Group 2 was previously cleared Online log +FRA/mynas/onlinelog/group_2.258.951608751: Thread 1 Group 2 was previously cleared Online log +DATA/mynas/onlinelog/group_3.268.951608757: Thread 2 Group 3 was previously cleared Online log +FRA/mynas/onlinelog/group_3.259.951608763: Thread 2 Group 3 was previously cleared Online log +DATA/mynas/onlinelog/group_4.269.951608769: Thread 2 Group 4 was previously cleared Online log +FRA/mynas/onlinelog/group_4.260.951608775: Thread 2 Group 4 was previously cleared Standby became primary SCN: 4550128 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 Sun Aug 13 09:59:07 2017 ARC1: Becoming the "no SRL" ARCH