Active Dataguard in Oracle 11g

What is Active Dataguard in Oracle 11g

  • 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 11g)

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

 

How to enable the Active Dataguard  Oracle 11g

I am assuming we have Production site and standby site  running and 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 single standby database

Active dataguard in oracle 11g

Also with multiple standby database creating reader Farm
oracle Active dataguard  11g