Active Dataguard in Oracle

 

Table of Contents

• What is Active Dataguard in Oracle
• Operations  which are Allowed On a Read-Only Database(Active Dataguard in Oracle)
• Operations which are not allowed On a Read-Only Database
• Oracle data guard vs active data guard
• How to enable the Active Dataguard  in Oracle
• If using Data Guard broker for configuration
• How to Check if Active Data Guard is Already Enabled

What is Active Dataguard in Oracle

• The Active Data Guard Option available with Oracle Database 11g Enterprise Edition enables you to open a physical standby database for read-only access for reporting, for simple or complex queries, sorting while Redo Apply continues to apply changes from the production database to the standby database.
• All queries reading from the physical standby database execute in real time, and return current results as redo continue to apply to standby
• With Active Data Guard, you can offload any operation that requires up-to-date, read-only access to the standby database, enhancing and protecting the performance of the production database without any compromise in Recovery Point or Recovery Time objective
• it require a separate license

Operations  which are Allowed On a Read-Only Database(Active Dataguard in Oracle)

(1) Issue SELECT statements, including queries that require multiple sorts that leverage TEMP segments
(2) Use ALTER SESSION and ALTER SYSTEM statements, Use SET ROLE
(3)Call stored procedures
(4) Use database links (dblinks) to write to remote databases
(5) Use stored procedures to call remote procedures via dblinks
(6) Use SET TRANSACTION READ ONLY for transaction-level read consistency
(7) Issue complex queries (such as grouping SET queries and WITH CLAUSE queries)

Operations which are not allowed On a Read-Only Database

An Active Data Guard standby database is subject to the same restrictions as any Oracle database that is open read-only. These include:
(1)Any DMLs (excluding simple SELECT statements) or DDLs
(2)Query accessing local sequences
(3)DMLs to local temporary tables

Oracle data guard vs Active data guard

How to enable the Active Dataguard  in Oracle

I am assuming we have a Production site and standby site  running and the redo apply is in progress

stop redo apply

SQL> recover managed standby database cancel;

Open the database read-only

SQL> alter database open read only;

 Once the database is open, start redo apply:

SQL> recover managed standby database disconnect using current logfile;

If using Data Guard broker for configuration

Stop redo apply using DGMGRL

DGMGRL> EDIT DATABASE ‘TEST' STATE=‘APPLY-OFF’

In SQL*Plus open the database read-only

SQL> alter database open read only;

Restart redo apply by issuing the following command:

DGMGRL> EDIT DATABASE ‘TEST' STATE=‘APPLY-ON’

How to Check if Active Data Guard is Already Enabled

Use the following query to confirm that Data Guard is in active mode:

SQL> SELECT 'Using Active Data Guard' AG FROM V$MANAGED_STANDBY M, V$DATABASE D WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE='READ ONLY';
AG
-----------------------
Using Active Data Guard

Active Dataguard can be used for a single standby database

Also with multiple standby databases creating reader Farm