Oracle11DATA GUARD實時應用加實時查詢功能

Oracle10g增加了DATA GUARD的日誌實時應用功能，11g新增了實時查詢功能。這兩個功能可以同時應用。

DATA GUARD的建立：http://yangtingkun.itpub.net/post/468/388465

Oracle11新特性——DATA GUARD開啟模式下應用歸檔：http://yangtingkun.itpub.net/post/468/388599

對於PRIMARY資料，只需要將對應的LOG_ARCHIVE_DEST改為同步方式。為了更好的說明問題，首先將歸檔日誌切換一下：

SQL> alter system set log_archive_dest_2 =
2 'SERVICE=ora11g_s SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11g_s';

系統已更改。

SQL> alter system switch logfile;

系統已更改。

下面登陸STANDBY資料庫，新增STANDBY LOGFILE，並修改初始化引數，使得STANDBY資料庫可以處理STANDBY LOGFILE：

$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Sep 27 18:52:10 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount ORACLE instance started.

Total System Global Area 267825152 bytes
Fixed Size 1299316 bytes
Variable Size 150998156 bytes
Database Buffers 113246208 bytes
Redo Buffers 2281472 bytes
Database mounted.
SQL> set pages 100 lines 120
SQL> col member format a80
SQL> select group#, member from v$logfile;

GROUP# MEMBER
---------- --------------------------------------------------------------------
1 /data/oracle/oradata/ora11g/ORA11G_S/onlinelog/o1_mf_1_3f5dsz0v_.log
1 /data/oracle/flash_recovery_area/ORA11G_S/onlinelog/o1_mf_1_3f5dszg1_.log
2 /data/oracle/oradata/ora11g/ORA11G_S/onlinelog/o1_mf_2_3f5dt1vl_.log
2 /data/oracle/flash_recovery_area/ORA11G_S/onlinelog/o1_mf_2_3f5dt26o_.log
3 /data/oracle/oradata/ora11g/ORA11G_S/onlinelog/o1_mf_3_3f5dt3gn_.log
3 /data/oracle/flash_recovery_area/ORA11G_S/onlinelog/o1_mf_3_3f5dt3yj_.log

6 rows selected.

SQL> select group#, bytes from v$log;

GROUP# BYTES
---------- ----------
1 52428800
3 52428800
2 52428800

SQL> alter database add standby logfile
2 ('/data/oracle/oradata/ora11g/ORA11G_S/onlinelog/sredo1.log') size 50m;

Database altered.

SQL> alter database add standby logfile
2 ('/data/oracle/oradata/ora11g/ORA11G_S/onlinelog/sredo2.log') size 50m;

Database altered.

SQL> alter database add standby logfile
2 ('/data/oracle/oradata/ora11g/ORA11G_S/onlinelog/sredo3.log') size 50m;

Database altered.

SQL> alter database add standby logfile
2 ('/data/oracle/oradata/ora11g/ORA11G_S/onlinelog/sredo4.log') size 50m;

Database altered.

SQL> alter system set log_archive_dest_3 =
2 'LOCATION=/data/oracle/oradata/ora11g/archivelogstand
3 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLES) DB_UNIQUE_NAME=ora11g_s';

System altered.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database
2 using current logfile disconnect from session;

Database altered.

至此，DATA GUARD的REAL TIME APPLY已經配置完成。下面檢查一下DATA GUARD是否正常工作。登陸PRIMARY KEY進行資料修改，並提交：

SQL> select * from t_standby;

no rows selected

SQL> insert into t_standby select * from tab;

4 rows created.

SQL> commit;

Commit complete.

登陸STANDBY資料庫，檢查修改是否已經同步過來：

SQL> conn yangtk/yangtk
SQL> select * from t_standby;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T TABLE
T_SESSION TABLE
T_SESSION_STAT TABLE
T_STANDBY TABLE

現在資料庫同時處於REAL TIME APPLY和REAL TIME QUERY狀態。