Active Dataguard 11g

Introduction:-

 

Active Dataguard is 11g Feature .The Standby Database in 11g can be open read-only mode keeping the Managed recovery Enabled. This is an Enterprise Edition Feature with additional separate license required to use Active Dataguard Feature.

 

Benefits:-

1. Active Dataguard Standby Database delivers high return on investment when used for Queries, Reports, and Backups along with providing Disaster Protection.
2. Physical Standby Database would be in Managed Recovery Mode and along with it Standby Database can be used for Reporting Purposes. It thus unlocks additional processing power of Production Databases.
3. It is a Combination of Oracle High Availability and Disaster Recovery.

 

Comparison:-

 

DataGuard 11g	Active Dataguard 11g
Stop Redo Apply at 8:00 AM	Redo Apply is always on
Open Read only for Queries	Always open Read only
By 4:00 PM,data on Physical Standby is 8 Hours old	Queries and Reports always see latest Data
Any Failover will be delayed due to backlog of data that must be applied	Failover is immediate when needed, Standby Database is always in sync with primary

 

 

Functioning:-

Operations Permitted:-

1. Addition of Temporary Files to support Huge sort Operations in Reporting.
2. DML from Standby Database using Database Links.  Explanation: Create a DB link on Primary Database. Force a log switch on Primary so that DB Link gets created on Standby Database. Now try an insert from Standby Database using the DB Link, the insert would succeed and the same value will get reflected on Primary Database .Make sure there is a TNS Entry on Standby Database server for the Primary Database.

                               

 

 

Operations Non-Permitted:-

1. Any DMLs (excluding simple SELECT statements) or DDLs
2. Query accessing local sequences
3. DMLs to local temporary tables
   
   

Protection Modes:-

Maximum Availability: - Supports Sync, Affirm (Waits till netwok_time_out) – {Recommended}

Maximum Protection: - Supports Sync, Affirm

Maximum Performance: - Async {Default Mode}

Extensions/Parameters in 11gR2:-

1) Standby Database can use Block Change Tracking File.

2) STANDBY_MAX_DATA_DELAY:-

These parameter values are the amount of time in seconds. It signifies that a change being committed on Primary and the time it can be queried on an Active Standby Database.

NONE = Queries will be executed irrespective of the Lag.

Non-Zero (Seconds) =Queries will be executed only if apply lag is less than or equal to the value

Zero=Queries Guaranteed to return the exact same result as if the query was issued on Primary Database

 If the Limit exceeds ORA-3172 is given.

Note:- The above Parameter requires Maximum Availability and Real Time Apply. This is a session Parameter hence if you want to enable it you need to create a trigger to enable it permanently.

3) DB_LOST_WRITE_PROTECT:-

4) Block Media Recovery: - If corruption occurs on Primary Database which has Active Standby, block media is performed automatically transparent to the Application using a good copy from Standby Database. Converse is also true.

5) COMPATIBLE parameter should be at least set to 11.0.0

 

Miscellaneous:-

1. Flexible options to scale Read-Only Performance via adding more single node active standby databases or scale an active standby database using Oracle Real Application Clusters.
2. Handy Views : V$DATAGUARD_STATS,V$STANDBY_EVENT_HISTOGRAM(11gR2),
3. From 11gR2 ASH Reports also can be taken for Standby Database.
4. Configuration of Active Dataguard is explained in the attached excel.