11g ADG主备切换步骤

一、概述

主备切换：11g切换时，如果主库为RAC，需要关闭其它实例，仅保留一个即可，12c以后可以不用关闭实例

二、切换前检查

2.1 检查传输通道和GAP

备库执行
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

2.2 检查延迟时间

备库执行，延迟尽可能等于0
SQL> COLUMN NAME FORMAT A24
SQL> COLUMN VALUE FORMAT A16     
SQL> COLUMN DATUM_TIME FORMAT A24
SQL> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;

2.3 检查主库V$DATABASE.SWITCHOVER_STATUS

查询结果为to standby/session active，可以准备切换成备库
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

三、开始切换

3.1 主库切换为备库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

3.2 重启原主库到mount

执行完3.1步骤后，主库默认已经关闭了
SQL> shutdown immediate
SQL> startup mount

3.3 主库查询V$DATABASE.SWITCHOVER_STATUS

检查结果应该为TO_PRIMARY或SESSIONS ACTIVE
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

3.4 备库切换为主库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

3.5 打开新主库

SQL> ALTER DATABASE OPEN;

3.6 在新备库打开mrp

SQL> alter database open;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

四、切换后检查

4.1 主备检查进程情况

SQL> col group_# for a5
SQL> col client_pid for a8
SQL> set line 250 pagesize 9999
SQL> SELECT A.INST_ID,A.PROCESS,A.STATUS,A.CLIENT_PROCESS,A.CLIENT_PID,A.GROUP# GROUP_#,A.THREAD#,A.SEQUENCE#,A.DELAY_MINS,
A.RESETLOG_ID,C.SID,C.SERIAL#,A.PID SPID,B.PNAME FROM GV$MANAGED_STANDBY A, GV$PROCESS B, GV$SESSION C
WHERE A.PID = B.SPID AND B.ADDR = C.PADDR AND A.INST_ID = B.INST_ID AND B.INST_ID = C.INST_ID ORDER BY A.INST_ID,B.PNAME;

4.2 新主库查询通道是否有error

SQL> set line 300
SQL> col DEST_NAME for a60
SQL> select dest_name,status,error from gv$archive_dest;

4.3 备库观察传输和应用延迟

SQL> COLUMN NAME FORMAT A24
SQL> COLUMN VALUE FORMAT A16     
SQL> COLUMN DATUM_TIME FORMAT A24
SQL> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;