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