Recover Database Until

If you need to recover your database to a point in time by scn, sequence or time, you can use the following query to see the relation between time-scn-sequence, after restoring your database from a proper backup.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YY HH24:MI:SS';

SQL> select NAME, SEQUENCE#, THREAD#, FIRST_TIME, FIRST_CHANGE#, NEXT_TIME, NEXT_CHANGE# from v$archived_log where SEQUENCE# > 166;

Sample Output:

NAME SEQUENCE# THREAD# FIRST_TIME FIRST_CHANGE# NEXT_TIME NEXT_CHANGE#
------------------------------ --------- ------- ---------- ----------------- --------- ----------------
/arch/1_166_593039.arc 166 1 10-11-08 06:31:15 34516912 10-11-08 06:31:36 34521645
/arch/1_167_593039.arc 167 1 10-11-08 06:31:36 34521645 10-11-08 06:31:56 34527024
/arch/1_168_593039.arc 168 110-11-08 06:31:56 34527024 10-11-08 06:32:10 34532094
/arch/1_169_593039.arc 169 1 10-11-08 06:32:10 34532094 10-11-08 06:32:35 34537223
...

You can modify the where clause depending on your needs. SEQUENCE# gives the sequence number of the archive log. FIRST_CHANGE# and NEXT_CHANGE# specify the first and last System Change Number (SCN); FIRST_TIME and NEXT_TIME specify the starting and ending time of that archivelog. regarding to these information you can decide any of the following recover operations:

RMAN> recover database until sequence 162280;
RMAN> recover database until SCN 34527024;
RMAN> recover database until time '10-11-08 06:31:15'

,or if you want to manually control recover process with specifying archive logs one by one, you can use "until cancel" clause in SQL. This recovery process continues until you cancel. If your archive logs are not on their default path you can specify the full path of the archive logs in this recovery process.

SQL > recover database until cancel;