备库的切换状态为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
也并非备库断档或者没有应用主库传输过来的日志导致的。切换后，主备库的各种状态显示正常。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2135702/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2135702/