一.切换前检查
1.检查备库已经全部接收到主库的redo如果是最大可用性、最大保护性模式，可以在primary端查看v$archive_dest_status，确认是否所有的redo已经传送到备库#在主库执行SQL> select db_unique_name,protection_mode,synchronization_status,synchronized from v$archive_dest_status;DB_UNIQUE_NAME PROTECTION_MODE SYNCHRONIZATION_STATUS SYN------------------------------ -------------------- ---------------------- ---NONE MAXIMUM PERFORMANCE CHECK CONFIGURATION NOdgs MAXIMUM PERFORMANCE DESTINATION HAS A GAP NOSQL>
可以看出主备未同步且是最大性能保护模式，还要执行更多检查操作(如果是最大可用性、最大保护性模式但未同步，同样需要执行更多检查操作)：
#在主库执行SQL> select thread#,sequence#,status from v$log; THREAD# SEQUENCE# STATUS---------- ---------- ---------------- 1 46 INACTIVE 1 47 CURRENT 1 45 INACTIVESQL>
#在备库执行
SQL> select client_process,process,sequence#,status from v$managed_standby;CLIENT_P PROCESS SEQUENCE# STATUS-------- --------- ---------- ------------ARCH ARCH 46 CLOSINGARCH ARCH 0 CONNECTEDARCH ARCH 0 CONNECTEDARCH ARCH 0 CONNECTEDARCH ARCH 44 CLOSINGARCH RFS 0 IDLEUNKNOWN RFS 0 IDLEUNKNOWN RFS 0 IDLELGWR RFS 47 IDLEN/A MRP0 47 WAIT_FOR_LOG10 rows selected.SQL>
从结果可以看出，当前主库发送来的sequence是47
2.检查备库已经将所有redo应用到备库
2.1 physical standby环境SQL> select client_process,process,sequence#,status from v$managed_standby;CLIENT_P PROCESS SEQUENCE# STATUS-------- --------- ---------- ------------...N/A MRP0 48 WAIT_FOR_LOG...10 rows selected.SQL>
如果MRP0的状态是"wait_for_gap",则不能进行switchover。"wait_for_log"表示要么没有开启实时应用、要么开启了delay设置。如果是设置了delay，要停止redo apply，使用nodelay重启redo apply，否则无法switchover。
2.2 logical standby环境
SQL> select applied_scn, latest_scn, mining_scn from v$logstdby_progress;SQL> select status from v$logstdby_process where type='READER';
3.停掉主库上的job、rman操作等
SQL> select process, operation, r.status, mbytes_processed pct, s.status from v$rman_status r, v$session s where r.sid=s.sid;
二、正式切换
1.主库查看
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS--------------------TO STANDBYSQL> SELECT PROGRAM, TYPE FROM V$SESSION WHERE TYPE='USER';PROGRAM TYPE------------------------------------------------ ----------sqlplus@dgp.example.com (TNS V1-V3) USERSQL>
2.备库查看
SQL> SELECT PROGRAM, TYPE FROM V$SESSION WHERE TYPE='USER';PROGRAM TYPE------------------------------------------------ ----------sqlplus@dgs.example.com (TNS V1-V3) USERoracle@dgp.example.com (TNS V1-V3) USERoracle@dgp.example.com (TNS V1-V3) USERoracle@dgp.example.com (TNS V1-V3) USERoracle@dgp.example.com (TNS V1-V3) USERSQL>
3.主库执行switchover操作
SQL> alter database commit to switchover to physical standby with session shutdown;
对应的主库alert日志内容：
Thu Apr 21 05:24:25 2016alter database commit to switchover to physical standby with session shutdownALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 2796] (dgp)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 identifiedSwitchover End-Of-Redo Log thread 1 sequence 48 has been fixedSwitchover: Primary highest seen SCN set to 0x0.0x110542ARCH: Noswitch archival of thread 1, sequence 48ARCH: End-Of-Redo Branch archival of thread 1 sequence 48ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2ARCH: Standby redo logfile selected for thread 1 sequence 48 for destination LOG_ARCHIVE_DEST_2Archived Log entry 83 added for thread 1 sequence 48 ID 0x664ffa3a dest 1:ARCH: Archiving is disabled due to current logfile archivalPrimary will check for some target standby to have received alls redoFinal check for a synchronized target standby. Check will be made once.LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover targetActive, synchronized target has been identifiedTarget has also received all redoBackup controlfile written to trace file /p01/app/oracle/diag/rdbms/dgp/dgp/trace/dgp_ora_2796.trcClearing standby activation ID 1716517434 (0x664ffa3a)The primary database controlfile was created using the'MAXLOGFILES 16' clause.There is space for up to 13 standby redo logfilesUse the following SQL commands on the standby database to createstandby 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;Archivelog for thread 1 sequence 48 required for standby recoverySwitchover: Primary controlfile converted to standby controlfile succesfully.Switchover: Complete - Database shutdown requiredUSER (ospid: 2796): terminating the instanceInstance terminated by USER, pid = 2796Completed: alter database commit to switchover to physical standby with session shutdownShutting down instance (abort)License high water mark = 5Thu Apr 21 05:24:28 2016Instance shutdown complete
对应备库alert日志：
Thu Apr 21 05:24:24 2016RFS[3]: Possible network disconnect with primary databaseThu Apr 21 05:24:27 2016RFS[4]: Assigned to RFS process 2841RFS[4]: Selected log 5 for thread 1 sequence 48 dbid 1716485690 branch 909194174Thu Apr 21 05:24:27 2016Archived Log entry 41 added for thread 1 sequence 48 ID 0x664ffa3a dest 1:Thu Apr 21 05:24:27 2016RFS[1]: Possible network disconnect with primary databaseThu Apr 21 05:24:27 2016RFS[5]: Assigned to RFS process 2843RFS[5]: Possible network disconnect with primary databaseThu Apr 21 05:24:27 2016RFS[2]: Possible network disconnect with primary databaseThu Apr 21 05:24:28 2016Media Recovery Log /s01/app/oracle/arch/1_48_909194174.dbfIdentified End-Of-Redo (switchover) for thread 1 sequence 48 at SCN 0x0.110542Resetting standby activation ID 1716517434 (0x664ffa3a)Media Recovery End-Of-Redo indicator encounteredMedia Recovery ContinuingMedia Recovery Waiting for thread 1 sequence 49
4.备库切换成主库
SQL> alter database commit to switchover to primary with session shutdown;
切换过程对应的alert日志：
Thu Apr 21 05:26:59 2016ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARYWITH SESSION SHUTDOWNALTER DATABASE SWITCHOVER TO PRIMARY (dgs)Maximum wait for role transition is 15 minutes.Switchover: Media recovery is still activeRole Change: Canceling MRP - no more redo to applyThu Apr 21 05:27:01 2016MRP0: Background Media Recovery cancelled with status 16037Errors in file /s01/app/oracle/diag/rdbms/dgs/dgs/trace/dgs_mrp0_2377.trc:ORA-16037: user requested cancel of managed recovery operationRecovery interrupted!MRP0: Background Media Recovery process shutdown (dgs)Role Change: Canceled MRPAll dispatchers and shared servers shutdownCLOSE: killing server sessions.CLOSE: all sessions shutdown successfully.Thu Apr 21 05:27:02 2016SMON: disabling cache recoveryBackup controlfile written to trace file /s01/app/oracle/diag/rdbms/dgs/dgs/trace/dgs_ora_2755.trcSwitchOver after complete recovery through change 1115458Online log /s01/app/oracle/oradata/dgs/redo01.log: Thread 1 Group 1 was previously clearedOnline log /s01/app/oracle/oradata/dgs/redo02.log: Thread 1 Group 2 was previously clearedOnline log /s01/app/oracle/oradata/dgs/redo03.log: Thread 1 Group 3 was previously clearedStandby became primary SCN: 1115456AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.Switchover: Complete - Database mounted as primaryCompleted: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARYWITH SESSION SHUTDOWN
5.打开新主库
SQL> alter database open;
对应alert日志：
Thu Apr 21 05:27:52 2016idle dispatcher 'D000' terminated, pid = (17, 1)Thu Apr 21 05:28:01 2016ARC1: Becoming the 'no SRL' ARCHThu Apr 21 05:28:39 2016alter database openThu Apr 21 05:28:39 2016Assigning activation ID 1717040723 (0x6657f653)Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZEDThread 1 advanced to log sequence 50 (thread open)Thread 1 opened at log sequence 50 Current log# 2 seq# 50 mem# 0: /s01/app/oracle/oradata/dgs/redo02.logSuccessful open of redo thread 1MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setThu Apr 21 05:28:39 2016SMON: enabling cache recoveryThu Apr 21 05:28:39 2016***********************************************************************Fatal NI connect error 12514, connecting to: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dgp.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dgp)(CID=(PROGRAM=oracle)(HOST=dgs.example.com)(USER=oracle)))) VERSION INFORMATION: TNS for Linux: Version 11.2.0.4.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production Time: 21-APR-2016 05:28:39 Tracing not turned on. Tns error struct: ns main err code: 12564 TNS-12564: TNS:connection refused ns secondary err code: 0 nt main err code: 0 nt secondary err code: 0 nt OS err code: 0***********************************************************************
6.打开新的备库
SQL> shutdown immediateSQL> startup mountSQL> alter database recover managed standby database using current logfile disconnect;
7.新的主库查看
SQL> select open_mode,PROTECTION_MODE,PROTECTION_LEVEL, DATABASE_ROLE from v$database;OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE-------------------- -------------------- -------------------- ----------------READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARYSQL>
8.新的备库查看
SQL> select open_mode,PROTECTION_MODE,PROTECTION_LEVEL, DATABASE_ROLE from v$database;OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE-------------------- -------------------- -------------------- ----------------MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBYSQL>