How To Find Archive Log For Specific SCN

There are times, like when we perform a restore, where we need to know which archive log file has a particular sequence or SCN. With the following query you can obtain the archive log file. Logically we need know the SCN we seek. The sentence would be like this.

 
SELECT 
  SEQUENCE# 
FROM 
  V$ARCHIVED_LOG 
WHERE 
   THREAD#=1 AND       -- IF RAC IS ENABLE -----
   <My specific SCN> BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#  -- My SCN ---
/

Example;

 
SELECT 
  SEQUENCE# 
FROM 
  V$ARCHIVED_LOG 
WHERE 
   THREAD#=2 AND 
   216149709709 BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#
/

We no need to perform the query on the GV$ARCHIVED_LOG, perform it on V$ARCHIVED_LOG is sufficient.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s