How to Retrieve REDO File Name with Specific Sequence

Question

I have an Oracle Log Based Capture exception, which contains a sequence number. How can I see the relevant REDO file name containing the specific sequence?

Environment

HVR 5

Answer

For archive logs, run:

SQL> select thread#, sequence#, first_time, next_time, name from v$archived_log where sequence# = XXXX order by thread#, sequence#

For online logs, run:

SQL> select l.thread#, l.sequence#, l.first_time, l.next_time, f.member from v$log l, v$logfile f where l.group# = f.group# and sequence# = XXXX ;