How to Switchover&Failover in DataGuard-11G

基础配置

为了方便每一步的操作是在哪个机器操作的，特意设置了glogin.sql
路径：$ORACLE_HOME/sqlplus/admin/glogin.sql中添加如下代码：
define _editor=vi
set line 150
set pagesize 9999
set long 5000
set serveroutput on size 1000000
col object_name for a30
col name for a30
col segment_name for a30
col file_name for a50
col event for a40
col value for a40
define gname=idle
column global_name new_value gname
set termout off
select lower(user)||'@'||(select db_unique_name from v$database) global_name from v$database;
set sqlprompt '&gname>'
set termout on

Switchover

1、检查主、备库归档日志序列一致

sys@prixcky>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
  202
sys@stdxcky>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
  202

2、检查主库有无活动session

sys@prixcky>select username,sid,serial# from v$session where username is not null;
USERNAME      SID    SERIAL#
----------------------------
XCKY           52        3
SYS            59     1083
SYS           142      789
SYS           225      151

3、检查主库是否可切换为physical standby

sys@prixcky>select name,database_role,protection_mode,switchover_status from v$database;
NAME       DATABASE_ROLE PROTECTION_MODE      SWITCHOVER_STATUS
-----------------------------------------------------------------
XCKY       PRIMARY       MAXIMUM PERFORMANCE  TO STANDBY

4、主库切换为目标备库

sys@prixcky> alter database commit to switchover to standby with session shutdown ;
Database altered.
sys@prixcky>select name,database_role,protection_mode,switchover_status from v$database;
NAME       DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
-------------------------------------------------------------------------
XCKY       PHYSICAL STANDBY MAXIMUM PERFORMANCE  RECOVERY NEEDED

5、目标备库启动到mount

此时，目标备库已经不再连接数据库，直接退出，
sys@prixcky>quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ora11g ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 13 05:08:45 2017
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
idle>startup mount
ORACLE instance started.
Total System Global Area 6413680640 bytes
Fixed Size    2240344 bytes
Variable Size 3187671208 bytes
Database Buffers3204448256 bytes
Redo Buffers   19320832 bytes
Database mounted. 

6、查看原备库是否可切换为目标主库

sys@stdxcky>select name,database_role,protection_mode,switchover_status from v$database;
NAME      DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
------------------------------------------------------------------
XCKY      PHYSICAL STANDBY MAXIMUM PERFORMANCE  SESSIONS ACTIVE

7、原备库切换为目标主库

sys@stdxcky>alter database commit to switchover to primary with session shutdown ;
Database altered.
sys@stdxcky>select name,database_role,protection_mode,switchover_status from v$database;
NAME       DATABASE_ROLE PROTECTION_MODE    SWITCHOVER_STATUS
------------------------------ ------------------------------------
XCKY       PRIMARY       MAXIMUM PERFORMANCE      NOT ALLOWED

8、目标主库开库

sys@stdxcky>alter database open;
Database altered.
sys@stdxcky>select name,database_role,protection_mode,switchover_status from v$database;
NAME       DATABASE_ROLE  PROTECTION_MODE      SWITCHOVER_STATUS
---------------------------------------------------------------
XCKY       PRIMARY        MAXIMUM PERFORMANCE  RESOLVABLE GAP

9、目标备库开库

idle>alter database open; 
Database altered.
idle>select name,database_role,protection_mode,switchover_status from v$database;
NAME       DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
-----------------------------------------------------------------
XCKY       PHYSICAL STANDBY MAXIMUM PERFORMANCE  RECOVERY NEEDED

10、检查主备库状态，恢复DG可用

目标备库恢复RTA
idle> recover managed standby database disconnect from session; ----启用日志应用，应用主库传输过来的redo
Media recovery complete.
idle>select name,database_role,protection_mode,switchover_status from v$database;
NAME       DATABASE_ROLE PROTECTION_MODE      SWITCHOVER_STATUS
------------------------------ -----------------------------------   --------------------

XCKY       PHYSICAL STANDBY MAXIMUM PERFORMANCE  NOT ALLOWED

查看recovery_mode为MANAGED,启用了日志应用
sys@prixcky>select dest_id,recovery_mode from v$archive_dest_status where dest_id=1;
   DEST_ID RECOVERY_MODE
    ----------   --------------
     1             MANAGED

sys@prixcky>alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

sys@prixcky>alter database recover managed standby database cancel;----首先要取消managed的recovery_mode
Database altered.

----启用RTA的recovery_mode
sys@prixcky>alter database recover managed standby database using current logfile disconnect from session;
Database altered.

sys@prixcky>select dest_id,recovery_mode from v$archive_dest_status where dest_id=1;
   DEST_ID RECOVERY_MODE
   ----------    -----------------------
         1         MANAGED REAL TIME APPLY

目标主库状态
sys@stdxcky>select name,database_role,protection_mode,switchover_status from v$database;
NAME       DATABASE_ROLE  PROTECTION_MODE               SWITCHOVER_STATUS
-----------------------------------------------------------------------------------------------------------------
XCKY       PRIMARY                   MAXIMUM PERFORMANCE     TO STANDBY