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-MEMORYselect 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-MEMORYselect 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 CURRENTcolumn 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 PRIMARYalter 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 YESalter 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 ALLOWEDalter 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
2.3. 重建备库
