備庫的切換狀態為SWITCHOVER PENDING時進行dataguard主備庫角色切換

      以下是根據之前failover切換後恢復原來主庫為新備庫之後的，因為新備庫的切換狀態為SWITCHOVER PENDING，
所以就嘗試簡單地做個正常的switchover切換，想驗證備庫在這種的狀態是否能完成正常的主備切換。

----備庫的切換狀態為SWITCHOVER PENDING時進行dataguard主備庫角色切換：

----檢視主庫的切換狀態以及最大日誌號：

--切換狀態：

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

OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE

-------------------- -------------------- -------------------- ----------------

READ WRITE           MAXIMUM PERFORMANCE  TO STANDBY           PRIMARY

--最大日誌號：

SQL> select max(sequence#), to_char(max(first_time),'yyyy-mm-dd hh24:mi:ss') from v$log_history where thread#=1

  2   and RESETLOGS_TIME = (select max(a.RESETLOGS_TIME) from v$log_history a);

MAX(SEQUENCE#) TO_CHAR(MAX(FIRST_T

-------------- -------------------

           186 2017-03-19 06:01:41

----檢視備庫的切換狀態以及最大日誌號：

--切換狀態：

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

OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE

-------------------- -------------------- -------------------- ----------------

READ ONLY WITH APPLY MAXIMUM PERFORMANCE  SWITCHOVER PENDING   PHYSICAL STANDBY

--最大日誌號：

SQL> select max(sequence#), to_char(max(first_time),'yyyy-mm-dd hh24:mi:ss') from v$log_history where thread#=1

  2   and RESETLOGS_TIME = (select max(a.RESETLOGS_TIME) from v$log_history a);

MAX(SEQUENCE#) TO_CHAR(MAX(FIRST_T

-------------- -------------------

           186 2017-03-19 06:01:41

----進行主備庫角色切換：

--主庫切換兩個日誌並作一個監測點：

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

#主庫切換了日誌之後，依然要進行主備庫最大日誌號的核對，確保切換正常，此處省略。

 

----切換前再做狀態確認：

--主庫：

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

OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE

-------------------- -------------------- -------------------- ----------------

READ WRITE           MAXIMUM PERFORMANCE  TO STANDBY           PRIMARY

--備庫：

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

OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE

-------------------- -------------------- -------------------- ----------------

READ ONLY WITH APPLY MAXIMUM PERFORMANCE  SWITCHOVER PENDING   PHYSICAL STANDBY

 

----主庫開始切換成新備庫並重啟例項：

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

Database altered.

 

[oracle@oracle ~]$ sqlplus "/as sysdba"

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 19 15:13:48 2017

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> quit

... ...

SQL> startup

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                  2257800 bytes

Variable Size             503319672 bytes

Database Buffers          322961408 bytes

Redo Buffers                2392064 bytes

Database mounted.

Database opened.

SQL>

----檢視切換後的狀態：

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

OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE

-------------------- -------------------- -------------------- ----------------

READ ONLY            MAXIMUM PERFORMANCE  TO PRIMARY           PHYSICAL STANDBY

 

----檢視原備庫此時的切換狀態：

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

OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE

-------------------- -------------------- -------------------- ----------------

READ ONLY WITH APPLY MAXIMUM PERFORMANCE  TO PRIMARY           PHYSICAL STANDBY

 

----備庫切換成新主庫並開啟：

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

Database altered.

 

SQL> alter database open;

Database altered.

 

----檢視切換後的狀態：

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

OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE

-------------------- -------------------- -------------------- ----------------

MOUNTED              MAXIMUM PERFORMANCE  NOT ALLOWED          PRIMARY

 

----新主庫切換兩個日誌，並檢視最大日誌號：

--切換日誌：
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

--檢視日誌號：

SQL> select max(sequence#), to_char(max(first_time),'yyyy-mm-dd hh24:mi:ss') from v$log_history where thread#=1

  2   and RESETLOGS_TIME = (select max(a.RESETLOGS_TIME) from v$log_history a);

MAX(SEQUENCE#) TO_CHAR(MAX(FIRST_T

-------------- -------------------

           193 2017-03-19 15:21:18

 

----新備庫引用主庫傳輸的日誌並檢視最大日誌號：
--應用日誌：

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

Database altered.

--檢視日誌號： 

SQL> select max(sequence#), to_char(max(first_time),'yyyy-mm-dd hh24:mi:ss') from v$log_history where thread#=1

  2   and RESETLOGS_TIME = (select max(a.RESETLOGS_TIME) from v$log_history a);

MAX(SEQUENCE#) TO_CHAR(MAX(FIRST_T

-------------- -------------------

           193 2017-03-19 15:21:18

 

----新備庫引用日誌之後，檢視新主備庫的切換狀態：

---主庫：

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

OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE

-------------------- -------------------- -------------------- ----------------

READ WRITE           MAXIMUM PERFORMANCE  TO STANDBY           PRIMARY

 

---備庫：

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

OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE

-------------------- -------------------- -------------------- ----------------

READ ONLY WITH APPLY MAXIMUM PERFORMANCE  NOT ALLOWED          PHYSICAL STANDBY

#由這次順利的切換，可以看到，備庫的切換狀態SWITCHOVER PENDING並不影響主備庫之間的切換。備庫的切換狀態SWITCHOVER PENDING
也並非備庫斷檔或者沒有應用主庫傳輸過來的日誌導致的。切換後，主備庫的各種狀態顯示正常。