Find the Archivelog names by using the SCN

During database recovery, you may have a SCN number and need to know the archivelog names. Here is the SQL for the answer:

column first_change# format 9,999,999,999
column next_change# format 9,999,999,999

alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';

select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#
from v$archived_log
where 35297312527 between first_change# and next_change#;

If you see 'D' in the STATUS column, the archive log has been deleted from the disk. You may need to restore it from the tape.

SEQUENCE# number usually shows up on the archivelog name. You can use RMAN command to restore them:

restore archivelog from logseq=45164 until logseq=45179;

Or, use commands to check the backup status:

list backup of archivelog all completed after 'SYSDATE - 21';
list backup of archivelog from logseq=45164 until logseq=45179;