How to Open Oracle Physical Standby Database in READ ONLY WITH APPLY Mode (Active Data Guard)
📋 Overview: Oracle Active Data Guard
Oracle Active Data Guard allows your physical standby database to be open in READ ONLY mode while simultaneously applying redo data from the primary database. This configuration enables real-time reporting, offloading read-only queries, and maintaining high availability without impacting production performance.
- Real-time query capability on standby database
- Offload reporting workload from primary database
- Zero data loss protection with continuous redo apply
- Enhanced disaster recovery readiness
⚠️ Licensing Note: Oracle Active Data Guard requires a separate license on top of Enterprise Edition. Ensure proper licensing before implementing in production environments.
⚙️ Prerequisites and Pre-Validation
1. Verify Primary-Standby Synchronization
Check for archive gaps on standby:
SQL> SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap; -- No rows returned = No gaps (good) -- If gaps exist, resolve them before proceeding
2. Verify Current Standby Status
Check database status:
SQL> SELECT name, open_mode, database_role FROM v$database; NAME OPEN_MODE DATABASE_ROLE --------- ------------ ---------------- ORCL MOUNTED PHYSICAL STANDBY
✓ Expected State: Database should be MOUNTED with PHYSICAL STANDBY role before proceeding.
🚀 START Process: Enable Active Data Guard (READ ONLY WITH APPLY)
1 Cancel Managed Recovery Process (MRP)
Stop the Managed Recovery Process before opening the database. In RAC environments, execute this command on the node where MRP is currently running.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered.
✓ Validation: Verify MRP has stopped:
SQL> SELECT process, status FROM v$managed_standby WHERE process = 'MRP0'; -- No rows returned indicates MRP stopped successfully
2 Open Database in READ ONLY Mode
Open the physical standby database in READ ONLY mode to allow query access while preparing for real-time apply.
SQL> ALTER DATABASE OPEN READ ONLY; Database altered.
✓ Validation: Confirm READ ONLY status:
SQL> SELECT name, open_mode FROM v$database; NAME OPEN_MODE --------- ----------- ORCL READ ONLY
3 Start Real-Time Apply (Active Data Guard)
Enable real-time redo apply using the USING CURRENT LOGFILE clause. This is the key command that activates Active Data Guard functionality.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE
DISCONNECT FROM SESSION;
Database altered.
💡 Command Explanation: USING CURRENT LOGFILE enables real-time apply from standby redo logs, while DISCONNECT FROM SESSION runs recovery as a background process.
4 Verify Active Data Guard Status
Confirm that Active Data Guard is successfully enabled and functioning correctly.
SQL> SELECT name, open_mode FROM v$database; NAME OPEN_MODE --------- -------------------- ORCL READ ONLY WITH APPLY
✅ Success Confirmation: READ ONLY WITH APPLY indicates Active Data Guard is operational - the database is open for queries while simultaneously applying redo changes.
Additional Validation Checks:
Check apply lag:
SQL> SELECT name, value, unit FROM v$dataguard_stats
WHERE name IN ('transport lag', 'apply lag');
NAME VALUE UNIT
-------------- ---------- --------
transport lag +00:00:02 hh:mi:ss
apply lag +00:00:03 hh:mi:ss
⛔ STOP Process: Disable Active Data Guard (Return to MOUNTED)
To stop Active Data Guard and return the standby database to MOUNTED state (for maintenance, patching, or license compliance):
Step 1: Cancel Managed Recovery
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered.
Step 2: Shutdown and Restart in MOUNT Mode
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT;
Step 3: Restart Standard Recovery (Optional)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Step 4: Verify MOUNTED Status
SQL> SELECT name, open_mode FROM v$database; NAME OPEN_MODE --------- ----------- ORCL MOUNTED
🔧 Common Issues and Troubleshooting
❌ Issue 1: Database Won't Open READ ONLY
Error: ORA-10458: standby database requires recovery
Cause: Archive gap exists or standby is significantly behind primary.
Solution: Resolve archive gaps and ensure standby is caught up before opening.
❌ Issue 2: High Apply Lag
Cause: Network latency, I/O bottlenecks, or insufficient resources.
Solutions:
- Enable parallel recovery: Add
PARALLEL 4to recovery command - Check network bandwidth and optimize if needed
- Ensure standby redo logs are on fast storage
- Monitor and limit heavy queries on standby
❌ Issue 3: MRP Process Not Starting
Check: Standby redo logs configuration and log transport services on primary.
-- Verify standby redo logs exist SQL> SELECT group#, thread#, bytes FROM v$standby_log; -- Check log archive destination on primary SQL> SELECT dest_id, status, error FROM v$archive_dest WHERE dest_id = 2;
💡 Best Practices and Recommendations
- Monitor apply lag regularly: Set up alerts for lag exceeding 30 seconds
- Use ASYNC redo transport: Better primary database performance
- Configure sufficient standby redo logs: (Number of online redo groups + 1) per thread
- Implement Resource Manager: Prevent query overload on standby
- Test switchover procedures: Quarterly testing ensures readiness
- Document procedures: Maintain current runbooks for operations team
❓ Frequently Asked Questions
Q: Can I write data to Active Data Guard standby?
A: No, Active Data Guard opens in READ ONLY mode. All DML operations are prohibited to ensure data consistency.
Q: What's the typical apply lag with Active Data Guard?
A: With real-time apply, expect less than 5 seconds under normal conditions. Optimal configurations can achieve 1-2 seconds lag.
Q: Do I need standby redo logs for Active Data Guard?
A: Yes, standby redo logs are required for real-time apply. Configure them to match online redo log sizes with one additional group per thread.
Post a Comment
Post a Comment