Data Guard

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, 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.

There are three key steps to enable a standby database to Real-Time Query:

  • Set “compatible” to 11.0 or above.
  • It will imply to use Active Data Guard.

  • Restart the standby database to open.
  • Actually, it will open the standby database to read-only state.

  • Enable Real-Time Apply.
  • This will take the standby database into a zero apply lag mode. Without the broker intervention, you have to enable applying manually.

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 restart the standby database to open.

  1. Make sure the value of compatible is at least 11.
  2. [oracle@standby01 ~]$ sqlplus / as sysdba
    ...
    SQL> show parameter compatible

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    compatible                           string      11.2.0.0.0

  3. Check the status of standby database before restart.
  4. SQL> select OPEN_MODE, DATABASE_ROLE, DATAGUARD_BROKER, GUARD_STATUS from v$database;

    OPEN_MODE            DATABASE_ROLE    DATAGUAR
    -------------------- ---------------- --------
    MOUNTED              PHYSICAL STANDBY ENABLED
    ...

  5. Restart the standby database to open.
  6. [oracle@standby01 ~]$ srvctl stop database -d compdb
    [oracle@standby01 ~]$ srvctl start database -d compdb -o open

  7. Check the status of standby database after restart.
  8. [oracle@standby01 ~]$ sqlplus / as sysdba
    ...
    SQL> select OPEN_MODE, DATABASE_ROLE, DATAGUARD_BROKER from v$database;

    OPEN_MODE            DATABASE_ROLE    DATAGUAR
    -------------------- ---------------- --------
    READ ONLY WITH APPLY PHYSICAL STANDBY ENABLED

    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 19:09:25       11/27/2012 19:09:24
    ...
    ...
    [oracle@primary01 ~]$ dgmgrl /
    ...
    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

Without Data Guard Broker

Without the broker, you must enable real-time apply by yourself in order to enable.

  1. Disable data guard broker on both databases (primary + standby).
  2. SQL> alter system set dg_broker_start=FALSE scope=both;

    System altered.
    SQL> alter system set dg_broker_start=FALSE scope=both;

    System altered.

  3. Check the status of data guard broker.
  4. [oracle@primary01 ~]$ dgmgrl /
    ...
    DGMGRL> show configuration
    Error:
    ORA-16525: the Data Guard broker is not yet available

    Configuration details cannot be determined by DGMGRL
    ...

  5. Restart the standby database.
  6. [oracle@standby01 ~]$ srvctl stop database -d compdb
    [oracle@standby01 ~]$ srvctl start database -d compdb -o open

  7. Check the status of the standby database before enabling real-time apply.
  8. [oracle@standby01 ~]$ sqlplus / as sysdba
    ...
    SQL> select OPEN_MODE, DATABASE_ROLE, DATAGUARD_BROKER from v$database;

    OPEN_MODE            DATABASE_ROLE    DATAGUAR
    -------------------- ---------------- --------
    READ ONLY            PHYSICAL STANDBY DISABLED

  9. Enable real-time apply service.
  10. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

    Database altered.

  11. Check the status of the standby database after enabling real-time apply.
  12. 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.

One thought on “How to Enable Real-Time Query in the Standby Database

  1. executed on both:
    SQL> alter system set dg_broker_start=FALSE scope=both;

    System altered.

    SQL> select OPEN_MODE, DATABASE_ROLE, DATAGUARD_BROKER from v$database;

    OPEN_MODE DATABASE_ROLE DATAGUAR
    ——————– —————- ——–
    READ ONLY PHYSICAL STANDBY DISABLED

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

    Database altered.

    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 08/15/2018 07:34:28

    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
    ———- ——— ———— ———- ———- ———- ———-
    1 RFS IDLE 0 0 0 0
    1 RFS IDLE 1 10499 441 2
    1 MRP0 WAIT_FOR_LOG 1 10499 0 0

Leave a Reply

Your email address will not be published. Required fields are marked *