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.