How to Find and List Oracle Archive Logs for Specific Dates and RMAN Backups

As an Oracle Database Administrator, managing archive logs is a daily reality. Whether you are dealing with a disk space crisis in your Fast Recovery Area (FRA), preparing for a Point-in-Time Recovery (PITR), or auditing database changes, you frequently need to answer two critical questions:

  1. Which archive logs were generated during a specific time window?
  2. Which RMAN backup contains a specific transaction log file?

In this post, we will walk through the exact SQL queries and Recovery Manager (RMAN) commands needed to safely locate and list your archive logs in Oracle 10g and newer environments.

Part 1: Listing Archive Logs for a Specific Date Range

When analyzing database activity or preparing for a data recovery window, your first stop is the v$archived_log dynamic performance view.

To list all archive logs generated between 5 July and 6 July, you need to filter the first_time column. Using explicit date-time formatting prevents issues with native database NLS_DATE_FORMAT settings.

The SQL Query

Run the following query in SQL*Plus to get a clean breakdown of the logs, including their physical file size in Megabytes (MB):

sql

SELECT 
    thread#, 
    sequence#, 
    name, 
    first_time, 
    next_time, 
    blocks * block_size / 1024 / 1024 AS size_mb 
FROM 
    v$archived_log 
WHERE 
    first_time >= TO_DATE('05-JUL-2026 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
    AND first_time <= TO_DATE('06-JUL-2026 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
ORDER BY 
    thread#, 
    sequence#;

Use code with caution.

Pro-Tip: Clean Up Your SQL*Plus Formatting

If your terminal output looks like scrambled text, run these session formatting commands right before executing your query to make it highly readable:

sql

SET LINESIZE 200
SET PAGESIZE 100
COL name FORMAT a60
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Use code with caution.

Part 2: Finding the RMAN Backup for a Specific Archive Log

Once you have identified your target sequence numbers from the step above, you might need to extract them from a backup. If you know the archive log sequence number (e.g., 1045), you can track down its physical backup piece using either RMAN or a direct SQL query.

Method A: The Quick RMAN Command

Log into Recovery Manager and run the LIST BACKUP command targeted to that specific sequence and thread:

bash

rman target /

Use code with caution.

rman

RMAN> LIST BACKUP OF ARCHIVELOG SEQUENCE 1045 THREAD 1;

Use code with caution.

In the output text, look for the Backup Piece header. The Piece Name line will tell you the exact file path on your local disk or the handle interacting with your tape library.

Method B: Querying via SQL*Plus

If you don’t have prompt access to RMAN but have DBA access to SQL*Plus, you can map the control file views (v$backup_piece, v$backup_redolog, and v$archived_log) together to find the file location:

sql

SELECT 
    bpd.handle AS backup_file_path,
    al.sequence#,
    al.thread#,
    bpd.completion_time
FROM 
    v$backup_piece bpd
JOIN 
    v$backup_redolog brl ON bpd.set_stamp = brl.set_stamp AND bpd.set_count = brl.set_count
JOIN 
    v$archived_log al ON brl.thread# = al.thread# AND brl.sequence# = al.sequence#
WHERE 
    al.sequence# = 1045;

Use code with caution.

Key Takeaways for DBAs

  • Check the Status: If the name column in v$archived_log shows DELETED, the file has been purged from the operating system but the metadata still exists in the control file.
  • Keep RMAN Sync’d: If you manually delete files from disk, always run CROSSCHECK ARCHIVELOG ALL; followed by DELETE EXPIRED ARCHIVELOG ALL; inside RMAN to keep your catalog clean.