Monitoring Oracle Archive Logs: A Quick Guide

As an Oracle DBA, keeping a close eye on your archived redo logs is crucial for maintaining database health and ensuring smooth recovery operations. Today, I’ll share a simple yet powerful script that helps you monitor your archive logs effectively.

The Power of v$archived_log

The v$archived_log view is a goldmine of information about your archived redo logs. It contains details about all archived logs that have been generated by your database, including their sequence numbers, timestamps, and whether they’ve been applied to a standby database.

The Monitoring Script

Here’s a script that gives you a clear view of your archive log activity over the past 24 hours:

-- Set display preferences for better readability
set lines 120 pages 200

-- Format dates for better human readability
alter session set nls_date_format='DD-MON-RRRR:Hh24:MI:SS';

-- Query archive log information from the past day
select
    dest_id,
    FIRST_TIME,
    THREAD#,
    SEQUENCE#,
    ARCHIVED,
    APPLIED
from
    v$archived_log
where
    first_time > sysdate -1
order by
    1,2
/

Understanding the Output

This script returns several key pieces of information:

1. DEST_ID: The archive destination ID where the log was archived
2. FIRST_TIME: When the log was first created
3. THREAD#: The thread number (useful in RAC environments)
4. SEQUENCE#: The log sequence number
5. ARCHIVED: Whether the log has been archived (YES/NO)
6. APPLIED: Whether the log has been applied (relevant for standby databases)

Why This Matters

Regularly monitoring your archive logs helps you:

• Verify that logs are being archived properly
• Identify any gaps in your log sequence
• Monitor the lag between primary and standby databases
• Plan your backup and recovery strategies effectively

Pro Tip

For a more comprehensive view, you might want to combine this with information from v$archive_dest and v$archive_dest_status to get a complete picture of your archive log destinations and their status.

What archive log monitoring techniques do you find most valuable in your environment? Share your thoughts in the comments!