DG 读写分离

读写分离（实时查询standby）

 

备用数据库一边进行日志恢复，一边对外提供服务，如果在加上最大保护模式，用户在备用数据库上就能查看到准确的实时数据了。

 

先确认备库的状态

SQL> select NAME ,OPEN_MODE, PROTECTION_MODE ,PROTECTION_LEVEL, REMOTE_ARCHIVE, DATABASE_ROLE, SWITCHOVER_STATUS from v$database;   NAME         OPEN_MODE              PROTECTION_MODE             PROTECTION_LEVEL --------- -------------------- -------------------- -------------------- REMOTE_A DATABASE_ROLE   SWITCHOVER_STATUS -------- ---------------- -------------------- ORCL          MOUNTED          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE ENABLED  PHYSICAL STANDBY NOT ALLOWED

 

进入只读状态

SQL> alter database recover managed standby database cancel;   Database altered.   SQL> alter database open;   Database altered.   SQL> select NAME ,OPEN_MODE, PROTECTION_MODE ,PROTECTION_LEVEL, REMOTE_ARCHIVE, DATABASE_ROLE, SWITCHOVER_STATUS from v$database;   NAME         OPEN_MODE              PROTECTION_MODE             PROTECTION_LEVEL --------- -------------------- -------------------- -------------------- REMOTE_A DATABASE_ROLE   SWITCHOVER_STATUS -------- ---------------- -------------------- ORCL         READ ONLY         MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE ENABLED  PHYSICAL STANDBY NOT ALLOWED

 

再次启动数据库的恢复

SQL> alter database recover managed standby database disconnect from session;   Database altered.   SQL> select NAME ,OPEN_MODE, PROTECTION_MODE ,PROTECTION_LEVEL, REMOTE_ARCHIVE, DATABASE_ROLE, SWITCHOVER_STATUS from v$database;   NAME         OPEN_MODE              PROTECTION_MODE             PROTECTION_LEVEL --------- -------------------- -------------------- -------------------- REMOTE_A DATABASE_ROLE   SWITCHOVER_STATUS -------- ---------------- -------------------- ORCL         READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE ENABLED  PHYSICAL STANDBY NOT ALLOWED

 

现在备库的状态现在是read only with apply.

 

现在在主库上做一下操作

SQL> create table test (id number);   Table created.   SQL> alter system switch logfile;   System altered.

现在去查备库是看不到test这个表的，做几次日志切换就能在备库看到test表了。

 

SQL> select * from test; select * from test               * ERROR at line 1: ORA-00942: table or view does not exist     SQL> /   no rows selected

 

可见主库上的变化已经映射到备库了，因为目前还不是实时恢复的，使用实时恢复，这个时候，必须有standby log

 

SQL> select * from v$logfile;       GROUP# STATUS  TYPE    MEMBER                                                                      IS_ ---------- ------- ------- ---------------------------------------------------------------------- ---           3        ONLINE  /u01/app/oracle/oradata/std/redo03.log                                       NO           2        ONLINE  /u01/app/oracle/oradata/std/redo02.log                                       NO           1        ONLINE  /u01/app/oracle/oradata/std/redo01.log                                       NO           4        STANDBY /u01/app/oracle/fast_recovery_area/STD/onlinelog/o1_mf_4_9kof4jt9_.log YES           5        STANDBY /u01/app/oracle/fast_recovery_area/STD/onlinelog/o1_mf_5_9kof4m2w_.log YES           6        STANDBY /u01/app/oracle/fast_recovery_area/STD/onlinelog/o1_mf_6_9kof4n53_.log YES           7        STANDBY /u01/app/oracle/fast_recovery_area/STD/onlinelog/o1_mf_7_9kof4nyc_.log YES

 

再次取消recover，重新使用实时恢复。

SQL> alter database recover managed standby database using current logfile disconnect from session;   Database altered.   SQL> select NAME ,OPEN_MODE, PROTECTION_MODE ,PROTECTION_LEVEL, REMOTE_ARCHIVE, DATABASE_ROLE, SWITCHOVER_STATUS from v$database;   NAME         OPEN_MODE              PROTECTION_MODE             PROTECTION_LEVEL --------- -------------------- -------------------- -------------------- REMOTE_A DATABASE_ROLE   SWITCHOVER_STATUS -------- ---------------- -------------------- ORCL          READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE ENABLED  PHYSICAL STANDBY NOT ALLOWED

 

主库上插入

SQL> insert into test values (1);   1 row created.   SQL> insert into test values (2);   1 row created.   SQL> commit   2  ;   Commit complete.

 

在备库立马就可以看到

SQL> select * from test;            ID ----------           1           2