Oracle 11.2.0.4.0 Dataguard部署和日常维护(5)-Datauard 主备切换和故障转移篇

1. dataguard主备切换

1.1. 查看当前主备库是否具备切换条件

on slave

select sequence#,first_time,next_time,archived,applied from v$archived_log where SEQUENCE#=(select max(SEQUENCE#) from v$archived_log);

 SEQUENCE# 　　FIRST_TIME           　 NEXT_TIME       　　　　 ARCHIVED  　　 APPLIED
---------- 　　------------------- 　　------------------- 　　--------- 　　 ---------------------------
    51 　　　　2017-07-03 22:00:41 　　 2017-07-04 17:04:55 　　YES           IN-MEMORY

select sequence#,first_time,next_time,archived,applied from v$archived_log where SEQUENCE#=(select max(SEQUENCE#) from v$archived_log);

 SEQUENCE# FIRST_TIME           NEXT_TIME       ARCHIVED  APPLIED
---------- ------------------- ------------------- --------- ---------------------------
    51 2017-07-03 22:00:41 2017-07-04 17:04:55 YES         IN-MEMORY

select PROCESS,SEQUENCE#,STATUS from v$managed_standby;

PROCESS              　　　　SEQUENCE# 　　STATUS
--------------------------- ---------- ------------------------------------
ARCH                    　　47 　　　　　　CLOSING
ARCH                    　　50 　　　　　　CLOSING
ARCH                     　　0 　　　　   CONNECTED
ARCH                    　　51 　　　　　　CLOSING
RFS                     　　 0           IDLE
RFS                    　　 52 　　　　　　IDLE
RFS                     　　 0 　　　　　　IDLE
MRP0                    　　52 　　　　　　APPLYING_LOG

on master

select SEQUENCE#,STATUS from v$log order by SEQUENCE#;

 SEQUENCE# 　　STATUS
---------- ------------------------------------------------
    50 　　　　INACTIVE
    51 　　　　INACTIVE
    52 　　　　CURRENT

column DATABASE_ROLE format a20
column OPEN_MODE format a15
column PROTECTION_MODE format a20
column SWITCHOVER_STATUS format a20
select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE         OPEN_MODE       PROTECTION_MODE      SWITCHOVER_STATUS
-------------------- --------------- -------------------- --------------------
PRIMARY          　　  READ WRITE      MAXIMUM PERFORMANCE  TO STANDBY

1.2. 将主库转换为备库

alter database commit to switchover to physical standby with session shutdown;
startup mount;
alter database recover managed standby database using current logfile disconnect from session;
select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE         OPEN_MODE        PROTECTION_MODE      SWITCHOVER_STATUS
-------------------- --------------- -------------------- --------------------
PHYSICAL STANDBY      MOUNTED          MAXIMUM PERFORMANCE  TO PRIMARY

1.3. 将备库转换为主库

column DATABASE_ROLE format a20
column OPEN_MODE format a15
column PROTECTION_MODE format a20
column SWITCHOVER_STATUS format a20
select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE        OPEN_MODE       PROTECTION_MODE      SWITCHOVER_STATUS
-------------------- --------------- -------------------- --------------------
PHYSICAL STANDBY     MOUNTED         MAXIMUM PERFORMANCE  TO PRIMARY

alter database commit to switchover to primary;
alter database open;
select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE        OPEN_MODE      PROTECTION_MODE       SWITCHOVER_STATUS
-------------------- --------------- -------------------- --------------------
PRIMARY              READ WRITE     MAXIMUM PERFORMANCE   TO STANDBY

1.4. 测试主备数据同步状态

on primary

select sequence#,first_time,next_time,archived,applied from v$archived_log where SEQUENCE#=(select max(SEQUENCE#) from v$archived_log);

 SEQUENCE# FIRST_TIME          NEXT_TIME       　　 ARCHIVED  APPLIED
---------- ------------------- ------------------- --------- ---------------------------
    54 　　2017-07-05 02:30:34 2017-07-05 02:30:40  YES       NO
    54 　　2017-07-05 02:30:34 2017-07-05 02:30:40  YES       YES

alter system archive log current;
alter system archive log current;

select sequence#,first_time,next_time,archived,applied from v$archived_log where SEQUENCE#=(select max(SEQUENCE#) from v$archived_log);

 SEQUENCE# FIRST_TIME           NEXT_TIME          ARCHIVED  APPLIED
---------- ------------------- ------------------- --------- ---------------------------
    56 　　2017-07-05 02:40:01  2017-07-05 02:40:03 YES       NO
    56 　　2017-07-05 02:40:01  2017-07-05 02:40:03 YES       NO

on standby

select sequence#,first_time,next_time,archived,applied from v$archived_log where SEQUENCE#=(select max(SEQUENCE#) from v$archived_log);

 SEQUENCE# FIRST_TIME          NEXT_TIME           ARCHIVED  APPLIED
---------- ------------------- ------------------- --------- ---------------------------
    56     2017-07-05 02:40:01 2017-07-05 02:40:03 YES       IN-MEMORY

2. dataguard故障转移

2.1. 前提:主库已经挂掉

2.2. 将备库强制转换为主库

select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE        OPEN_MODE       PROTECTION_MODE      SWITCHOVER_STATUS
-------------------- --------------- -------------------- --------------------
PHYSICAL STANDBY     MOUNTED         MAXIMUM PERFORMANCE  NOT ALLOWED

alter database recover managed standby database finish force;
alter database commit to switchover to primary;
shutdown immediate;
startup;
select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE        OPEN_MODE       PROTECTION_MODE      SWITCHOVER_STATUS
-------------------- --------------- -------------------- --------------------
PRIMARY              READ WRITE      MAXIMUM PERFORMANCE  FAILED DESTINATION