11g Active Data Guard - enabling Real-Time Query

Oracle 11g Active Data Guard: Enabling Real-Time Query

In Oracle Database 11g, the Active Data Guard feature was introduced. This powerful capability allows a physical standby database to be open in read-only mode for queries while simultaneously applying redo information received from the primary database. This means the standby database can be actively used for reporting and other read-intensive workloads, yet remain continuously up-to-date with the primary.

It's important to note that Active Data Guard is a licensed feature.

Enabling Active Data Guard for Real-Time Query

To enable Active Data Guard and allow real-time querying on your 11g standby database, follow these SQL*Plus commands:

1. Shutdown the Standby Database: Ensure the standby database is completely shut down before changing its mode.

   SHUTDOWN IMMEDIATE;
   
   

2. Start the Standby Database in Mount Mode: The database must be in mount mode to change its open state.

   STARTUP MOUNT;
   
   

3. Open the Standby Database in Read-Only Mode: This command opens the database for read access.

   ALTER DATABASE OPEN READ ONLY;
   
   

4. Start Managed Recovery and Disconnect from Session: This critical step starts the managed recovery process, which continuously applies redo logs, while allowing your current session to disconnect. The DISCONNECT FROM SESSION clause ensures that the recovery process runs in the background.

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
   
   

   Once managed recovery starts with Active Data Guard, it continues to apply redo even while the database is open read-only. Therefore, there is no need to explicitly switch back from read-only mode to managed recovery mode in this configuration.

Managing Redo Apply via Data Guard Broker (DGMGRL)

If you are using Oracle Data Guard Broker, you can also control the redo apply process from the DGMGRL command-line interface. This provides a more integrated way to manage your Data Guard configuration.

• Stop Redo Apply: This command stops the redo apply process on the standby database.

  DGMGRL> EDIT DATABASE 'PRODDB' SET STATE='APPLY-OFF';
  
  

  (Replace 'PRODDB' with the unique name of your standby database as defined in the Data Guard Broker configuration.)

• Open Standby Read-Only (via SQL*Plus): If redo apply was stopped, you can then open the standby database for read-only access.

  SQL> ALTER DATABASE OPEN READ ONLY;
  
  

• Restart Redo Apply: After performing any necessary read-only operations, you can restart the redo apply process using DGMGRL.

  DGMGRL> EDIT DATABASE 'PRODDB' SET STATE='APPLY-ON';
  
  

  (Replace 'PRODDB' with the unique name of your standby database.)

By following these steps, you can leverage Oracle 11g Active Data Guard to enable real-time querying on your standby database, enhancing its utility for reporting and offloading primary database workload.