SWITCHOVER主库出现LOG SWITCH GAP和RESOLVABLE GAP解决一例
今天做switchover，环境是11.2.0.3+OEL5.7，开始时主备库状态都是正常的，符合直接切换条件：
主库：
SQL> select open_mode,database_role,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE PRIMARY TO STANDBY
备库：
SQL> select open_mode,database_role,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
主库直接进行swichover：
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
SQL> select open_mode,database_role,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE PRIMARY LOG SWITCH GAP
提示有日志切换GAP，于是直接重启主库：
SQL> startup force
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2228904 bytes
Variable Size 322964824 bytes
Database Buffers 83886080 bytes
Redo Buffers 4292608 bytes
Database mounted.
Database opened.
SQL> select open_mode,database_role,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE PRIMARY RESOLVABLE GAP
状态由LOG SWITCH GAP变成了RESOLVABLE GAP，从字面理解是主备库之间存在GAP，于是执行：
SQL> ALTER SYSTEM FLUSH REDO TO ora11dg2;
SQL> select open_mode,database_role,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE PRIMARY TO STANDBY
把主库REDO FLUSH到备库以后，以上状态消失，又重新回到之前的TO STANDBY状态了，可以重新进行SWITCHOVER了。
如果主库状态不是TO STANDBY，而是SESSION ACTIVE，就要加上WITH SESSION SHUTDOWN，否则不能切换成功，除此以外的其他状态，是不能直接进行转换的。备库通常的状态是NOT ALLOWED，当主库做了切换以后，会变成TO PRIMARY，所以通常是现在主库做ROLE TRANSITION，然后再在备库做。
以下是一些消除主备库之间GAP的命令和说明：
--主库，将所有未传送的redo传送给从库，target_db_name使用DB_UNIQUE_NAME 。
ALTER SYSTEM FLUSH REDO TO target_db_name;
--验证备库
SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
--如果必要，拷贝归档日志到从库，并进行注册
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
--重复上一步，知道确认所有归档完毕。
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
--查看目标日志传输路径状态和GAP状态
SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
--在目标备库上，停止日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
--在目标备库上
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
--如果日志确定丢失，可以采用激活方式，但这样会有数据丢失。
--ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
--验证目标备库
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
--开始切换，如果