1. 检查主备库的状态
- on primary
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 select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 68
- on standby
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 NOT ALLOWED select SEQUENCE#,APPLIED from v$archived_log where SEQUENCE#=(select max(sequence#) from v$archived_log); SEQUENCE# APPLIED ---------- --------------------------- 68 IN-MEMORY select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# ------------------- ----------------------- ---------- ARCH CLOSING 68 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CLOSING 65 RFS IDLE 0 RFS IDLE 69 RFS IDLE 0 MRP0 APPLYING_LOG 69
2. 取消备库的MPR恢复线程
alter database recover managed standby database cancel;
3. 备库以readonly方式打开
alter database open read only; select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS from v$database; DATABASE_ROLE OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS -------------------- --------------- -------------------- -------------------- PHYSICAL STANDBY READ ONLY MAXIMUM PERFORMANCE NOT ALLOWED select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# ------------------- ----------------------- ---------- ARCH CLOSING 68 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CLOSING 65 RFS IDLE 0 RFS IDLE 69 RFS IDLE 0
4. 开启备库的MPR线程
alter database recover managed standby database using current logfile disconnect from session;
select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# ------------------- ----------------------- ---------- ARCH CLOSING 68 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CLOSING 65 RFS IDLE 0 RFS IDLE 69 RFS IDLE 0 MRP0 APPLYING_LOG 69
5. 测试active dataguard
- on primary
create table test01 as select FILE_NAME from dba_data_files; select * from test01; FILE_NAME ------------------------------------------------------------ /u01/app/oracle/oradata/userdata/users01.dbf /u01/app/oracle/oradata/userdata/undotbs01.dbf /u01/app/oracle/oradata/userdata/sysaux01.dbf /u01/app/oracle/oradata/userdata/system01.dbf
- on standby
select * from test01; FILE_NAME ------------------------------------------------------------ /u01/app/oracle/oradata/userdata/users01.dbf /u01/app/oracle/oradata/userdata/undotbs01.dbf /u01/app/oracle/oradata/userdata/sysaux01.dbf /u01/app/oracle/oradata/userdata/system01.dbf