Oracle Database Error Solutions – Easy & Practical Guides

Welcome to a dedicated platform for solving common Oracle Database errors like ORA-01194, ORA-01555, ORA-01017, ORA-12154 and more.

Learn step-by-step solutions, real-world troubleshooting, and best practices to handle Oracle issues efficiently.

View All Oracle Error Solutions

Oracle Data Guard Monitoring Queries – Complete Oracle DBA Monitoring Guide

Oracle Data Guard Monitoring Queries – Complete Oracle DBA Monitoring Guide

Oracle Data Guard is one of the most powerful disaster recovery and high availability solutions available for Oracle Databases. It enables organizations to maintain synchronized standby databases that can be activated quickly in the event of a primary database failure, planned maintenance activity, or disaster recovery scenario.

While configuring Oracle Data Guard is relatively straightforward for experienced DBAs, maintaining a healthy Data Guard environment requires continuous monitoring. Archive log transport failures, apply lag, archive gaps, recovery process issues, and network problems can all compromise your disaster recovery strategy if they are not detected promptly.

In this comprehensive Oracle DBA guide, we will cover the most important Oracle Data Guard monitoring queries used in production environments to monitor:

  • Primary Database Health
  • Standby Database Health
  • Archive Log Shipping
  • Redo Apply Status
  • Transport Lag
  • Apply Lag
  • Managed Recovery Process (MRP)
  • Standby Redo Logs
  • Data Guard Broker Status
  • Switchover Readiness
  • Failover Readiness

Table of Contents

  1. Why Oracle Data Guard Monitoring Is Critical
  2. Oracle Data Guard Architecture Overview
  3. Daily Oracle Data Guard Health Check
  4. Check Database Role
  5. Check Open Mode
  6. Check Protection Mode
  7. Verify Archive Log Transport Configuration
  8. Check Archive Destination Status
  9. Monitor Current Archive Log Generation
  10. Check Current Archive Log Received on Standby
  11. Monitor Managed Recovery Process (MRP)
  12. Monitor RFS (Remote File Server) Processes
  13. Monitor Apply Lag
  14. Monitor Transport Lag
  15. Display Complete Data Guard Statistics
  16. Detect Archive Log Gaps
  17. Check Last Received Archive Log
  18. Check Last Applied Archive Log
  19. Real-Time Apply Verification
  20. Verify Standby Redo Logs
  21. Verify Flashback Database Status
  22. Monitor Fast Recovery Area (FRA)
  23. Monitor Oracle Data Guard Broker Configuration
  24. Verify Switchover Readiness
  25. Verify Failover Readiness
  26. Verify Data Guard Synchronization
  27. Enterprise Data Guard Health Check Script
  28. Monitor Data Guard Using Oracle Enterprise Manager (OEM)
  29. Advanced Oracle Data Guard Monitoring Queries
  30. Oracle Data Guard Performance Analysis
  31. Oracle Data Guard Best Practices
  32. Recommended Daily DBA Checklist
  33. Frequently Asked Questions (FAQ)
  34. Suggested Internal Linking Structure
  35. Conclusion

Why Oracle Data Guard Monitoring Is Critical

Many organizations assume that once Data Guard is configured, it will continue operating flawlessly forever. Unfortunately, this assumption can lead to serious business risks.

Without proper monitoring, organizations may encounter:

  • Archive log transport failures
  • Unnoticed archive gaps
  • Standby database synchronization issues
  • Increasing apply lag
  • Data loss exposure
  • Failed switchover operations
  • Failed failover operations
  • Disaster recovery readiness failures

A healthy Data Guard environment should always maintain minimal transport lag and apply lag while continuously shipping and applying redo data.

Oracle Data Guard Architecture Overview

Before reviewing monitoring queries, it is important to understand the primary components of Oracle Data Guard.

Component Description
Primary Database Production database generating redo logs.
Physical Standby Database Block-for-block copy of the primary database.
Logical Standby Database Receives SQL statements instead of physical redo blocks.
Redo Transport Services Transfers redo logs from primary to standby.
Managed Recovery Process (MRP) Applies redo logs on standby database.
Data Guard Broker Management framework for Data Guard administration.
Standby Redo Logs Support Real-Time Apply functionality.


Daily Oracle Data Guard Health Check

Every Oracle DBA should begin with a quick Data Guard health check.

Check Database Role

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

Expected values:

  • PRIMARY
  • PHYSICAL STANDBY
  • LOGICAL STANDBY
  • SNAPSHOT STANDBY

Check Open Mode

SQL> SELECT OPEN_MODE FROM V$DATABASE;

Typical values:

Database Type Expected Open Mode
Primary Database READ WRITE
Mounted Standby MOUNTED
Active Data Guard READ ONLY WITH APPLY

Check Protection Mode

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;

Possible Protection Modes:

  • Maximum Protection
  • Maximum Availability
  • Maximum Performance

Most organizations use Maximum Performance due to its balance between availability and performance.

Verify Archive Log Transport Configuration

Archive log shipping is the foundation of Oracle Data Guard. If archive logs are not transported correctly, standby databases cannot remain synchronized.

SQL> SELECT DEST_ID, DESTINATION, STATUS, ERROR
FROM V$ARCHIVE_DEST
WHERE STATUS <> 'INACTIVE';

Healthy systems should show:

STATUS = VALID
ERROR = (blank)

If the ERROR column contains values, immediate investigation is required.

Check Archive Destination Status

SQL> SELECT DEST_ID, STATUS, ERROR
FROM V$ARCHIVE_DEST_STATUS;

This query provides additional information regarding transport destination health.

Status Meaning
VALID Destination operating normally
ERROR Transport issue detected
INACTIVE Destination disabled

Monitor Current Archive Log Generation

Checking archive log generation on the primary database helps determine current activity levels.

SQL> SELECT THREAD#, MAX(SEQUENCE#)
FROM V$ARCHIVED_LOG
GROUP BY THREAD#;

Record these sequence numbers and compare them with the standby database.

Check Current Archive Log Received on Standby

SQL> SELECT THREAD#, MAX(SEQUENCE#)
FROM V$ARCHIVED_LOG
GROUP BY THREAD#;

The standby database should be close to the primary database sequence numbers.

Significant differences may indicate:

  • Network issues
  • Archive transport failures
  • Archive gaps
  • Recovery process problems

Quick Primary vs Standby Synchronization Check

Compare the latest archive sequence numbers between primary and standby databases.

Database Latest Sequence
Primary 12500
Standby 12498

A small difference is normal. Large differences require investigation.

Common Oracle Data Guard Monitoring Objectives

  • Verify redo transport is functioning.
  • Verify redo apply is functioning.
  • Detect archive gaps quickly.
  • Monitor transport lag.
  • Monitor apply lag.
  • Verify standby database readiness.
  • Verify switchover readiness.
  • Verify failover readiness.
  • Ensure disaster recovery compliance.

Featured Snippet: What Are Oracle Data Guard Monitoring Queries?

Oracle Data Guard Monitoring Queries are SQL queries used by Oracle DBAs to monitor the health, synchronization, archive log transport, redo apply status, lag metrics, and disaster recovery readiness of Oracle Data Guard environments. These queries help identify archive gaps, transport failures, apply delays, and standby database issues before they impact business continuity.

Next Section: Managed Recovery Process (MRP), Apply Lag Monitoring, Transport Lag Monitoring, Archive Gap Detection, Real-Time Apply Monitoring, and Standby Redo Log Verification.

Monitor Managed Recovery Process (MRP)

The Managed Recovery Process (MRP) is responsible for applying redo logs received from the primary database to the physical standby database.

If MRP is not running, redo logs may continue to arrive at the standby database, but they will not be applied, causing synchronization delays.

Check MRP Status

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#
FROM V$MANAGED_STANDBY;

Healthy output should show:

MRP0 APPLYING_LOG

This indicates that the standby database is actively applying redo data.

Understanding Common MRP Status Values

Status Meaning
APPLYING_LOG Recovery is actively applying redo.
WAIT_FOR_LOG MRP waiting for next archive log.
IDLE No activity detected.
ERROR Recovery encountered a problem.

Monitor RFS (Remote File Server) Processes

The Remote File Server (RFS) receives redo data from the primary database.

SQL> SELECT PROCESS, STATUS, CLIENT_PROCESS, THREAD#, SEQUENCE#
FROM V$MANAGED_STANDBY;

Expected process:

RFS

If RFS processes are missing, redo transport may not be functioning correctly.

Monitor Apply Lag

Apply lag represents the delay between receiving redo data and applying it on the standby database.

This is one of the most important Oracle Data Guard monitoring metrics.

SQL> SELECT NAME, VALUE
FROM V$DATAGUARD_STATS
WHERE NAME='apply lag';

Example Output

+00 00:00:10

This means the standby database is approximately 10 seconds behind the primary database.

Apply Lag Best Practices

Apply Lag Health Status
0-30 Seconds Excellent
30 Seconds - 5 Minutes Acceptable
5-30 Minutes Needs Investigation
30+ Minutes Critical

Monitor Transport Lag

Transport lag measures the delay in shipping redo data from the primary database to the standby database.

SQL> SELECT NAME, VALUE
FROM V$DATAGUARD_STATS
WHERE NAME='transport lag';

Example:

+00 00:00:05

This indicates a transport delay of five seconds.

Display Complete Data Guard Statistics

SQL> SELECT *
FROM V$DATAGUARD_STATS;

This query provides a comprehensive overview of:

  • Transport Lag
  • Apply Lag
  • Apply Finish Time
  • Estimated Synchronization Metrics

Detect Archive Log Gaps

An archive gap occurs when one or more archive logs are missing from the standby database.

Archive gaps are among the most common causes of Data Guard synchronization problems.

SQL> SELECT *
FROM V$ARCHIVE_GAP;

Expected Healthy Result

No Rows Selected

If rows are returned, the standby database is missing archive logs and recovery cannot continue until the gap is resolved.

Check Last Received Archive Log

SQL> SELECT MAX(SEQUENCE#) LAST_RECEIVED
FROM V$ARCHIVED_LOG;

This query identifies the most recent archive log received by the standby database.

Check Last Applied Archive Log

SQL> SELECT MAX(SEQUENCE#) LAST_APPLIED
FROM V$ARCHIVED_LOG
WHERE APPLIED='YES';

Comparing last received and last applied sequences helps identify apply delays.

Monitor Archive Apply Progress

SQL> SELECT THREAD#, SEQUENCE#, APPLIED
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE# DESC;

Values for APPLIED include:

  • YES
  • NO
  • IN-MEMORY

Real-Time Apply Verification

Real-Time Apply allows standby databases to apply redo directly from Standby Redo Logs without waiting for archive log creation.

Check if Real-Time Apply is active:

SQL> SELECT PROCESS, STATUS
FROM V$MANAGED_STANDBY
WHERE PROCESS='MRP0';

Look for:

APPLYING_LOG

This typically indicates Real-Time Apply functionality.

Verify Standby Redo Logs

Standby Redo Logs (SRLs) are mandatory for optimal Data Guard performance.

SQL> SELECT GROUP#, THREAD#, SEQUENCE#, STATUS
FROM V$STANDBY_LOG;

Check Standby Redo Log Sizes

SQL> SELECT GROUP#, THREAD#, BYTES/1024/1024 MB
FROM V$STANDBY_LOG;

Standby Redo Logs should match the size of online redo logs on the primary database.

Recommended Standby Redo Log Configuration

Oracle recommends:

Number of SRLs = Number of Online Redo Log Groups + 1

Example:

Primary Redo Groups Recommended SRLs
3 4
4 5
5 6

Verify Flashback Database Status

Flashback Database is strongly recommended in Data Guard environments because it simplifies reinstatement after failover operations.

SQL> SELECT FLASHBACK_ON
FROM V$DATABASE;

Expected result:

YES

Monitor Fast Recovery Area (FRA)

A full Fast Recovery Area can stop archive generation and disrupt Data Guard synchronization.

SQL> SELECT SPACE_LIMIT/1024/1024 MB_LIMIT, SPACE_USED/1024/1024 MB_USED,
SPACE_RECLAIMABLE/1024/1024 MB_RECLAIMABLE
FROM V$RECOVERY_FILE_DEST;

DBAs should proactively monitor FRA utilization to avoid outages.

Featured Snippet: How Do You Check Data Guard Lag?

To check Oracle Data Guard lag, query V$DATAGUARD_STATS and review the values for Apply Lag and Transport Lag. These metrics show how far behind the standby database is compared to the primary database and help identify synchronization issues before they become critical.

Next Section: Data Guard Broker Monitoring, Switchover Readiness Checks, Failover Readiness Validation, Advanced Health Check Scripts, Enterprise Monitoring Queries, and Production Troubleshooting Scenarios.

Monitor Oracle Data Guard Broker Configuration

Oracle Data Guard Broker simplifies the management, monitoring, switchover, and failover operations of Oracle Data Guard environments.

If your organization uses Data Guard Broker, monitoring Broker status should be part of your daily DBA health checks.

Verify Broker Configuration Status

DGMGRL> SHOW CONFIGURATION;

Healthy output:

Configuration Status:
SUCCESS

If the status shows:

WARNING
ERROR

Further investigation is required.

Display Detailed Database Information

DGMGRL> SHOW DATABASE VERBOSE standbydb;

This command displays:

  • Apply Lag
  • Transport Lag
  • Database Role
  • Real-Time Query Status
  • Log Transport Status
  • Apply Status
  • Flashback Status

Check Data Guard Broker Properties

DGMGRL> SHOW DATABASE standbydb;

This command verifies database-specific Broker settings and operational status.

Verify Switchover Readiness

Before performing a switchover operation, verify that both primary and standby databases are ready.

Check Primary Database Status

SQL> SELECT SWITCHOVER_STATUS
FROM V$DATABASE;

Expected output:

TO STANDBY

Check Standby Database Status

SQL> SELECT SWITCHOVER_STATUS
FROM V$DATABASE;

Expected output:

TO PRIMARY

Common Switchover Status Values

Status Meaning
TO STANDBY Primary ready for switchover
TO PRIMARY Standby ready for switchover
NOT ALLOWED Switchover not currently possible
SESSIONS ACTIVE Active sessions must be handled first

Verify Failover Readiness

In disaster recovery scenarios, failover readiness is critical.

Check:

  • Archive log synchronization
  • Apply lag
  • Transport lag
  • Flashback Database status
  • Standby database health

Verify Data Guard Synchronization

Primary Database

SQL> SELECT THREAD#, MAX(SEQUENCE#)
FROM V$ARCHIVED_LOG
GROUP BY THREAD#;

Standby Database

SQL> SELECT THREAD#, MAX(SEQUENCE#)
FROM V$ARCHIVED_LOG
GROUP BY THREAD#;

Compare both results.

Large differences indicate:

  • Network bottlenecks
  • Transport failures
  • Apply delays
  • Archive gaps

Check Data Guard Lag Summary

SQL> SELECT NAME, VALUE
FROM V$DATAGUARD_STATS
WHERE NAME IN
(
'transport lag',
'apply lag'
);

This query provides a quick synchronization overview.

Enterprise Data Guard Health Check Script

The following script provides a quick overview of Data Guard health.

SQL> SELECT DATABASE_ROLE,
       OPEN_MODE,
       PROTECTION_MODE
     FROM V$DATABASE;

SQL> SELECT NAME,
       VALUE
     FROM V$DATAGUARD_STATS;

SQL> SELECT PROCESS,
       STATUS
     FROM V$MANAGED_STANDBY;

SQL> SELECT *
     FROM V$ARCHIVE_GAP;

Many DBAs schedule this script every 15 minutes through Enterprise Manager or custom monitoring tools.

Monitor Data Guard Using Oracle Enterprise Manager (OEM)

Oracle Enterprise Manager provides graphical monitoring capabilities.

Key metrics include:

  • Transport Lag
  • Apply Lag
  • Redo Rate
  • Archive Log Generation
  • Recovery Status
  • Broker Configuration Health

Recommended Alert Thresholds

Metric Warning Critical
Apply Lag 5 Minutes 30 Minutes
Transport Lag 5 Minutes 15 Minutes
Archive Gap Any Gap Immediate Action
FRA Usage 80% 95%

Production Scenario: Archive Gap Detection

A financial services company reported a Data Guard synchronization issue after a network outage.

The DBA executed:

SQL> SELECT *
     FROM V$ARCHIVE_GAP;

Result:

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
1       25010         25015

The standby database was missing archive logs 25010 through 25015.

Resolution:

  • Copied missing archive logs from primary server.
  • Registered archive logs manually.
  • Restarted Managed Recovery Process.
SQL> ALTER DATABASE REGISTER LOGFILE '/backup/arch_25010.arc';

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Synchronization was successfully restored.

Production Scenario: Apply Lag Growth

A retail organization observed apply lag increasing from seconds to several hours.

Investigation revealed:

  • Standby server CPU saturation
  • Slow storage subsystem
  • Heavy reporting workload

Corrective actions:

  • Added CPU resources.
  • Optimized reporting workload.
  • Enabled Active Data Guard optimization.

Apply lag returned to normal levels.

Production Scenario: FRA Full Condition

An Oracle 19c standby database stopped applying redo logs.

Investigation showed:

SQL> SELECT SPACE_USED/SPACE_LIMIT*100
     FROM V$RECOVERY_FILE_DEST;

Result:

98%

The Fast Recovery Area was nearly full.

After deleting obsolete backups and increasing FRA size, Data Guard operations resumed normally.

Common Oracle Data Guard Monitoring Mistakes

  • Monitoring only archive transport.
  • Ignoring apply lag.
  • Not monitoring archive gaps.
  • Failing to review Broker status.
  • Ignoring FRA utilization.
  • Not testing switchovers regularly.
  • Not validating failover readiness.
  • Assuming synchronization without verification.

Featured Snippet: How Do You Monitor Oracle Data Guard?

Oracle Data Guard can be monitored using views such as V$DATABASE, V$DATAGUARD_STATS, V$MANAGED_STANDBY, V$ARCHIVE_GAP, and V$ARCHIVE_DEST_STATUS. These views help DBAs monitor redo transport, apply lag, archive gaps, recovery processes, standby synchronization, and overall disaster recovery readiness.

Next Section: Advanced Monitoring Queries, Data Guard Performance Analysis, Best Practices, FAQ Section, Internal Linking Strategy.

Advanced Oracle Data Guard Monitoring Queries

Experienced Oracle DBAs often require deeper visibility into Data Guard performance and synchronization behavior. The following queries provide advanced monitoring capabilities for enterprise environments.

Check Redo Apply Rate

SQL> SELECT NAME, VALUE, UNIT
     FROM V$DATAGUARD_STATS;

This query helps determine how quickly redo is being applied on the standby database.

Monitor Data Guard Processes

SQL> SELECT PROCESS, PID, STATUS, CLIENT_PROCESS, THREAD#, SEQUENCE#
FROM V$MANAGED_STANDBY;

Key processes include:

  • RFS (Remote File Server)
  • MRP0 (Managed Recovery Process)
  • ARCH
  • LNS
  • DGRD

Monitor Archive Generation Rate

SQL> SELECT THREAD#, COUNT(*) ARCHIVES_GENERATED
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME > SYSDATE - 1
GROUP BY THREAD#;

This query helps estimate daily redo generation.

Monitor Recovery Progress

SQL> SELECT ITEM, SOFAR, TOTAL, UNITS
FROM V$RECOVERY_PROGRESS;

Useful during large recovery operations and failover scenarios.

Oracle Data Guard Performance Analysis

Performance bottlenecks can significantly increase transport lag and apply lag.

Common causes include:

  • Network latency
  • Slow storage systems
  • CPU bottlenecks
  • Archive log spikes
  • Standby database workload
  • Insufficient Standby Redo Logs

Check Redo Generation Volume

SQL> SELECT TRUNC(FIRST_TIME), COUNT(*) LOGS_GENERATED
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(FIRST_TIME)
ORDER BY 1 DESC;

This helps identify periods of excessive redo generation.

Identify Recovery Bottlenecks

SQL> SELECT EVENT, TOTAL_WAITS, TIME_WAITED
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE '%recovery%';

This query highlights recovery-related wait events.

Oracle Data Guard Best Practices

  • Monitor apply lag continuously.
  • Monitor transport lag continuously.
  • Enable Flashback Database.
  • Configure Standby Redo Logs correctly.
  • Use Data Guard Broker.
  • Review alert logs daily.
  • Monitor FRA utilization.
  • Test switchover procedures regularly.
  • Perform periodic failover testing.
  • Automate Data Guard health checks.

Recommended Daily DBA Checklist

Task Frequency
Check Apply Lag Daily
Check Transport Lag Daily
Check Archive Gaps Daily
Review Alert Log Daily
Verify MRP Status Daily
Verify Broker Status Daily
Review FRA Usage Daily

Frequently Asked Questions (FAQ)

How do I check Oracle Data Guard apply lag?

SQL> SELECT NAME, VALUE
FROM V$DATAGUARD_STATS
WHERE NAME='apply lag';

This query displays how far the standby database is behind the primary database.

How do I check transport lag in Oracle Data Guard?

SQL> SELECT NAME, VALUE
FROM V$DATAGUARD_STATS
WHERE NAME='transport lag';

Transport lag indicates delays in shipping redo logs from primary to standby.

How do I verify MRP is running?

SQL> SELECT PROCESS, STATUS
FROM V$MANAGED_STANDBY;

Look for:

MRP0 APPLYING_LOG

How do I detect archive gaps?

SQL> SELECT *
FROM V$ARCHIVE_GAP;

If rows are returned, archive logs are missing on the standby database.

How do I verify Data Guard Broker health?

DGMGRL> SHOW CONFIGURATION;

The configuration should report:

SUCCESS

What is an acceptable apply lag?

For most production environments:

  • 0–30 seconds = Excellent
  • Less than 5 minutes = Acceptable
  • More than 30 minutes = Critical

Featured Snippet: Oracle Data Guard Monitoring Queries

Oracle Data Guard Monitoring Queries are SQL statements used to monitor redo transport, redo apply, archive gaps, apply lag, transport lag, Managed Recovery Process (MRP), standby redo logs, and Data Guard Broker health. These queries help Oracle DBAs maintain standby database synchronization and ensure disaster recovery readiness.

Conclusion

Oracle Data Guard remains one of the most important technologies for disaster recovery and business continuity. However, a Data Guard environment is only as reliable as its monitoring strategy.

By implementing the monitoring queries covered in this guide, Oracle DBAs can proactively detect archive transport failures, recovery process issues, archive gaps, synchronization delays, and infrastructure bottlenecks before they impact production systems.

Whether you manage Oracle 11g, 12c, 19c, or 23ai environments, regular Data Guard monitoring should be considered a critical operational responsibility.

Proactive monitoring reduces downtime, minimizes data loss risks, and ensures that standby databases are always prepared for switchover and failover operations.

About the Author

Rana Abdul Wahid is an Oracle Database Administrator with extensive experience in Oracle Database Administration, Oracle EBS, RMAN Backup & Recovery, Data Guard, Performance Tuning, and High Availability Solutions.

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Contact / Feedback Form

Name

Email *

Message *