Introduction :
From 11gR2, a physical standby database can be opened in read only mode for the benefit of query and reporting which is the feature known as an Active Data guard (ADG). We can also perform database backups on standby when opened in ADG to offload running a backup on Production environment.
In this blog, let’s see how to enable physical standby database in read-only and how it works in real time apply mode.
How active dataguard works:
On primary, LNSn process captures redo from redo log buffer (or from redo logfiles if written quickly by LGWR process) and then sends it to RFS process on Standby database through Oracle net.
Once RFS process writes redo to standby redo logfiles it will be applied on standby database through MRP process.
RFS process can directly communicate to ARCn process for gap resolution in case of network outage and gets the data using the archive redo log from primary database.
Since physical standby is up to date with the primary , we can offload the read-only queries in ADG and can take fast incremental backups on a physical standby with up to 20x faster in 11gR2.
The following diagrams shows the architecture of an Oracle Dataguard and ADG in sync and async modes.
Here is an example of how to enable a physical standby database in active database guard in 11gR2.
Steps to enable real time apply:
Step 1: Check the database role and mode.
SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;
Step 2: Enable real time apply on standby.
SQL> alter database recover managed standby database disconnect from session using current logfile;
Step 3: Check recovery mode status on standby.
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;
Step 4: Check recovery mode status from Primary database.
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2;
Step 5 : Observe the changes from primary to standby in real time mode.
Here is an example of transaction replicated to standby in real time apply mode.
Bottom line:
Oracle Active Data Guard is the most comprehensive solution available to eliminate single points of failure for mission critical Oracle Databases.
