Archived Redo

Inspect the most recently archived redo logs, including thread, sequence, size and completion time.

SELECT
    thread#,
    sequence#,
    first_time,
    next_time,
    completion_time,
    blocks,
    ROUND(blocks * block_size / 1024 / 1024, 2) AS size_mb,
    archived,
    applied
FROM v$archived_log
WHERE archived = 'YES'
ORDER BY completion_time DESC
FETCH FIRST 30 ROWS ONLY;

Check the current status of configured archived redo destinations and the latest sequence sent to each one.

SELECT
    dest_id,
    destination,
    status,
    target,
    archived_thread#,
    archived_seq#,
    error
FROM v$archive_dest_status
WHERE status NOT IN ('DEFERRED', 'INACTIVE')
ORDER BY dest_id;

Summarize archived redo volume by day to identify changes in workload and archive generation.

SELECT
    TRUNC(completion_time) AS archive_day,
    COUNT(*) AS log_count,
    ROUND(
        SUM(blocks * block_size) / 1024 / 1024 / 1024,
        2
    ) AS size_gb
FROM v$archived_log
WHERE archived = 'YES'
GROUP BY TRUNC(completion_time)
ORDER BY archive_day DESC
FETCH FIRST 14 ROWS ONLY;

In RAC environments, review the most recent archived sequence generated by each redo thread.

SELECT
    thread#,
    MAX(sequence#) AS latest_sequence,
    MAX(completion_time) AS latest_archive_time
FROM v$archived_log
WHERE archived = 'YES'
GROUP BY thread#
ORDER BY thread#;

Review archived redo records and identify logs that have not yet been backed up by RMAN.

SELECT
    thread#,
    sequence#,
    completion_time,
    backup_count,
    name
FROM v$archived_log
WHERE archived = 'YES'
  AND backup_count = 0
ORDER BY completion_time DESC
FETCH FIRST 30 ROWS ONLY;

On a physical standby, identify the current archived redo gap that is blocking recovery.

SELECT
    thread#,
    low_sequence#,
    high_sequence#
FROM v$archive_gap
ORDER BY thread#;