How to Enable Real-Time Query in the Standby Database
Real-Time Query
Before 11g database, the standby database must be mount state, and data guard does not allow the standby database to open to read-only when apply service is on. In such case, nobody except SYSDBA can access the standby database. But this has been changed since 11g database.
Please note that real-time apply is different from real-time query, which can receive and apply online redo log from the primary database since 10g.
Real-Time Query is one feature of Active Data Guard (ADG), you can query the standby database under read-only with apply service to share the loading of primary database, especially when one instance of the primary RAC is down.
Please note that, additional license may be required to use active data guard including real-time query.
Two ways can enable Real-Time Query, one is to enable it with broker, the other is to enable it without broker.
With Data Guard Broker
Since the broker enables real-time apply by default, the left job is only to alter the standby database to read only.
1. Make sure the value of compatible is at least 11.
[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.0.0
2. Change the State of Standby Database.
DGMGRL> edit database standb set state=apply-off;
Succeeded.
DGMGRL> edit database standb set state=read-only;
Succeeded.
DGMGRL> edit database standb set state=apply-on;
Succeeded.
DGMGRL> show database standb
Database - standb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
standb1
standb2 (apply instance)
Database Status:
SUCCESS
In the above, setting APPLY-OFF may not be required. This is because setting READ-ONLY will disable the apply service.
Without Data Guard Broker
Without the broker, you must open the standby database to read only by yourself in order to enable real-time apply.
1. Cancel MRP.
SQL> alter database recover managed standby database cancel;
Database altered.
2. Alter the standby database to read only.
SQL> alter database open read only;
Database altered.
3. Start MRP.
SQL>alter database recover managed standby database disconnect from session;
Database altered.
4. Check the status of the standby database after enabling real-time apply.
SQL> select OPEN_MODE, DATABASE_ROLE, DATAGUARD_BROKER from v$database;
OPEN_MODE DATABASE_ROLE DATAGUAR
-------------------- ---------------- --------
READ ONLY WITH APPLY PHYSICAL STANDBY DISABLED
SQL> set linesize 100;
SQL> column name format a15;
SQL> column value format a15;
SQL> column time_computed format a20;
SQL> column datum_time format a20;
SQL> select name, value, time_computed, datum_time from v$dataguard_stats where name='apply lag';
NAME VALUE TIME_COMPUTED DATUM_TIME
--------------- --------------- -------------------- --------------------
apply lag +00 00:00:00 11/27/2012 20:37:02 11/27/2012 20:37:02
SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process in ('RFS','LNS','MRP0');
INST_ID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
2 RFS IDLE 1 102 1614 1
1 RFS IDLE 2 97 1195 1
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
1 MRP0 APPLYING_LOG 2 97 1192 102400
You can notice that OPEN_MODE has been changed into READ ONLY WITH APPLY.
