Check Status of Received and Applied Archives in Oracle Dataguard

by

Oracle Data Guard is a high-availability, disaster recovery, and data protection solution for Oracle databases. This article provides a comprehensive guide on checking the status of received and applied archives in Oracle DataGuard to maintain a seamless replication process.

Archive Log Management in Oracle Data Guard

Oracle Data Guard works by transmitting redo logs from the primary database to the standby database. The standby database receives these logs and applies them to maintain synchronization with the primary database. (received and applied archives in Oracle DataGuard)

Check the Received Archive Logs

To check the received archive logs on the standby database, execute the following SQL query:

SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
WHERE dest_id=2
ORDER BY sequence# DESC;
Received and Applied Archives in Oracle DataGuard

Explanation:

  • sequence# – The sequence number of the archived log.
  • first_time – The timestamp when the archived log was created.
  • next_time – The timestamp of the next archived log.
  • applied – Indicates whether the log has been applied (YES/NO).
  • dest_id=2 – Specifies the destination ID for the standby database.

Received and Applied Archives in Oracle DataGuard

By executing this query, administrators can verify which logs have been received and identify any missing sequence numbers that could indicate a gap in the logs.

Check Applied Archive Logs

To check the status of applied logs on the standby database, run the following SQL command:

SELECT sequence#, applied
FROM v$archived_log
WHERE applied = 'YES'
ORDER BY sequence# DESC;
Check Applied Archive Logs

Explanation:

  • This query retrieves a list of applied logs in descending order.
  • If a log is missing from this list but appears in the received logs, it may indicate a delay in the application process.

Monitoring Real-time Log Shipping and Apply Progress

To get real-time information about log shipping and apply status, use the “v$managed_standby” view:
(received and applied archives in Oracle DataGuard)

SELECT process, status, sequence#
FROM v$managed_standby;

Explanation:

  • process – Identifies the process name (RFS, MRP).
  • status – Displays the current status (e.g. “WAITING FOR REDO”, “APPLYING”).
  • sequence# – Shows the sequence number currently being processed.

Identifying Log Gaps between Primary and Standby

To identify gaps between primary and standby databases execute the following SQL:

SELECT * FROM v$archive_gap;

The above query will return the missing log sequences that need to be manually restored or re-synchronized.

Manually Registering Missing Archive Logs

If there are missing logs, manually register them using the following command:

ALTER DATABASE REGISTER LOGFILE 'path_to_missing_log_file';

After registering the missing logs, restart the managed recovery process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Checking Standby Database Lag

To monitor the lag between the primary and standby databases execute the following SQL:

SELECT name, value FROM v$dataguard_stats WHERE name = 'apply lag';

This query returns the time difference between the latest applied log and the current redo log generated on the primary database. If the lag is significant, further investigation may be needed.

Ensuring Synchronization Between Primary and Standby

To confirm that the standby database is synchronized with the primary database, execute the SQL:

SELECT sequence#, applied FROM v$archived_log ORDER BY sequence# DESC;

If the last applied sequence matches the latest received sequence, the databases are in sync.

Dataguard Common Issues and Troubleshooting

Logs Not Being Received

  • Verify network connectivity between primary and standby databases.
  • Check if log shipping is enabled using:
SELECT dest_id, status FROM v$archive_dest_status;
  • Ensure the archive destination is correctly configured in the tnsnames.ora file.

Archives Logs Not Being Applied

  • Check if the recovery process is running using below SQL:
SELECT process, status FROM v$managed_standby;
  • Restart the managed recovery process if required:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Resolving Archive Gaps

  • Identify missing logs using:
SELECT * FROM v$archive_gap;
  • Restore missing logs from backups and register them manually.

High Apply Lag

  • Monitor system performance using “v$dataguard_stats“.
  • Tune network and disk I/O to improve redo apply performance.

Best Practices for Managing Oracle Data Guard Logs

  • Regularly monitor log reception and application using SQL queries.
  • Set up alerts for missing or delayed logs.
  • Maintain a backup of archived logs to prevent data loss.
  • Optimize network configuration to reduce lag.
  • Perform periodic gap analysis to ensure consistency.

By following these best practices and monitoring logs effectively.

received and applied archives in Oracle DataGuard, you can read more about Oracle Dataguard

Leave a Comment