Description: –
Active Data Guard allows a physical standby database to be opened in READ ONLY mode while redo apply continues in the background. This enables the standby database to be used for reporting and query workloads without interrupting synchronization with the primary database.
Although the standby database is open for reporting, DML and DDL operations are not permitted while it is in READ ONLY mode.
Steps to Enable Active Data Guard
Step 1: Verify Primary Database Status
Check that the primary database is open and generating redo.
SQL> select status,instance_name,database_role from v$instance,v$database;
STATUS INSTANCE_NAME DATABASE_ROLE
———— ————- —————-
OPEN RACDB PRIMARY
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
145
Step 2: Verify Physical Standby Status
Check the standby database state and role:
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
——- ————- ———————
MOUNTED RAC_DBST PHYSICAL STANDBY
SQL> select max(sequence#) from v$archived_log where applied=’YES’;
MAX(SEQUENCE#)
————–
145
Step 3: Confirm Managed Recovery Process (MRP) is Running
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
——— ———— ———-
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 149
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 149
9 rows selected.
Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 149.
This indicates:
- MRP is active
- All available redo has been applied
- Standby is waiting for the next archive log
Step 4: Open the Standby Database
Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ ONLY Mode.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
—— ————– —————- —————
OPEN RAC_DBST PHYSICAL STANDBY READ ONLY
Step 5: Restart Redo Apply (Enable Active Data Guard)
Start managed recovery again:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
——— ———— ———
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 149
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 149
9 rows selected.
The MRP is active and is waiting for the log sequence 149 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.
Conclusion
Active Data Guard is successfully enabled. The physical standby database:
- Remains synchronized with the primary
- Continuously applies redo
- Is open for reporting workloads
- Reduces load on the primary
- Provides disaster recovery readiness
This configuration is ideal for environments requiring high availability along with reporting workload offloading.
