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
Hope you like the information on Active data guard in Oracle. Please do provide the feedback
Related Articles
ORA-01111 : ORA-01111: name for data file happens if the new data file created in primary does not get created on Standby location. Check out how to resolve the error.
physical Standby database: Check out this post for a step-by-step process on creating a Physical Standby database in oracle. it is quite useful for disaster recovery purpose
Disaster Recovery: What is disaster recovery, how to achieve it
Oracle Database Recovery: Check out 14 Oracle Database Recovery scenarios with solutions. All the scenarios like loss of control file, datafile, redo, archive log are explained
How to recover database using RMAN: How to recover database using RMAN step by step