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;

--開始切換，如果狀態為“TO PRIMARY”，則WITH SESSION SHUTDOWN從句可以去掉。
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

--開啟新主庫
ALTER DATABASE OPEN;

附一份DG主備庫切換狀態說明：

v$database
NOT ALLOWED - On a primary database, this status indicates that there are no valid and enabled standby databases. On a standby database, this status indicates that a switchover request has not been received from the primary database. 
 
SESSIONS ACTIVE - The database has active sessions. On a physical standby database, the WITH SESSION SHUTDOWN SQL clause must be specified to perform a role transition while in this state. On a logical standby database, a role transition can be performed while
in this state, but the role transition will not complete until all current transactions have committed. 
 
SWITCHOVER PENDING - On a physical standby database, this status indicates that a switchover request has been received from the primary database and is being processed. A physical standby database cannot switch to the primary role while in this transient state. 
 
SWITCHOVER LATENT - On a physical standby database, this status indicates that a switchover request was pending, but the original primary database has been switched back to the primary role. 
 
TO PRIMARY - The database is ready to switch to the primary role. 
 
TO STANDBY - The database is ready to switch to either the physical or logical standby role. 
 
TO LOGICAL STANDBY - The database has received a data dictionary from a logical standby database and is ready to switch to the logical standby role. 
 
RECOVERY NEEDED - On a physical standby database, this status indicates that additional redo must be applied before the database can switch to the primary role. 
 
PREPARING SWITCHOVER - On a primary database, this status indicates that a data dictionary is being received from a logical standby database in preparation for switching to the logical standby role. On a logical standby database, this status indicates that
the data dictionary has been sent to the primary database and other standby databases. 
 
PREPARING DICTIONARY - On a logical standby database, this status indicates that the data dictionary is being sent to the primary database and other standby databases in preparation for switching to the primary role. 
 
FAILED DESTINATION - On a primary database, this status indicates that one or more standby destinations are in an error state. 
 
RESOLVABLE GAP - On a primary database, this status indicates that one or more standby databases have a redo gap that can be automatically resolved by fetching the missing redo from the primary database or from another standby database. 
 
UNRESOLVABLE GAP - On a primary database, this status indicates that one or more standby databases have a redo gap that cannot be automatically resolved by fetching the missing redo from the primary database or from another standby database. 
 
LOG SWITCH GAP - On a primary database, this status indicates that one or more standby databases are missing redo due to a recent log switch. 

SWITCHOVER 樣本：

原主庫：
SQL> alter database commit to switchover to physical standby;

Database altered.

SQL> select open_mode,database_role,switchover_status from v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           PHYSICAL STANDBY RECOVERY NEEDED

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2228904 bytes
Variable Size             331353432 bytes
Database Buffers           75497472 bytes
Redo Buffers                4292608 bytes
Database mounted.

原備庫：
SQL> select open_mode,database_role,switchover_status from v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode,database_role,switchover_status from v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           PRIMARY          TO STANDBY

原主庫(新備庫)：
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.
SQL> select open_mode,database_role,switchover_status from v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
MOUNTED              PHYSICAL STANDBY RECOVERY NEEDED

此時剛轉換好的新備庫SWITCHOVER狀態是RECOVERY NEEDED，這是正常的，主庫切幾次日誌以後就會變成NOT ALLOWED狀態了，另外這個時候由於是REDO APPLY模式，雖然是11g，但也是無法直接OPEN的，如果要OPEN，必須先停止REDO APPLY：

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

由於11g的ACTIVE DG功能(需要LISENCE，否則屬於非法使用)，所以即便在OPEN狀態下，依然能夠REDO APPLY：

SQL> alter database recover managed standby database disconnect;

Database altered.

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

至此，整個SWITCHOVER就做完了，SWITCHOVER是不會遺失資料的(不同於FAILOVER)，如果FAILOVER能做到沒有GAP，也可以不丟資料，並且正常使用SWITCHOVER切換(見官方文檔B25608，第8章)。

-----------------------------------------------------------------------------------------------------
by aaron8219 chinaunix blog：http://blog.chinaunix.net/uid/24612962.html
原創文章，轉載請註明連結，謝謝！

http://blog.chinaunix.net/uid-24612962-id-3842449.html

http://blog.csdn.net/aaron8219/article/details/10035547