Oracle Database : Performing Incomplete Recovery from a missing archivelog file (Change-Based, Cancel-Based, Time-Based)

Performing Incomplete Recovery

Incomplete recovery may sometimes be necessary. For example, there may be a desire to send the database back in time due to error, incorrect DML changes, or sometimes incomplete recovery may be the only option (due to missing archivelog sequence).

Incomplete recovery recovers the database to a point in time in the past. There are three ways to perform incomplete recovery: change-based recovery, cancel-based recovery, and time-based recovery.

This article will cover all options for recovering from a missing archivelog sequence number. Consider the following error during database recovery:

ORA-308:  cannot open archived log 'd:\orant\arc\arch_387.arc
Ora-07360: sfifi: stat error, unable to obtain information...
HP-UX Error: 2: No such file or directory

Oracle wants archived redo log file with sequence number 387. Looking at the archivelog destination, you notice that file arch_387.arc is not available:

arch_382.arc	arch_386.arc
arch_383.arc	arch_388.arc
arch_384.arc	arch-389.arc
arch_385.arc	arch-390.arc

Thus the only option available is to perform incomplete recovery of the database. You need to restore the database from a backup completed prior to the point in time of your recovery. Perform recover using any of the below methods:

Change-Based Recovery

To do change-based recovery, you need to know the highest system change number (SCN) written to the archived redo log just before the missing log. You can then issue the recovery statement recover database until change scn_number; where the scn_number is that SCN written to archived redo log file sequence number 386 (i.e., one less than the missing log sequence number 387). You can get that SCN information from a view called v$archived_log owned by Oracle user sys.

Using the query ‘select first_change#-1, sequence# from v$archived_log where sequence#=387;’ you can find out the desired SCN. The output from this query is the following:

SQL> select first_change#-1, sequence#
     2> from v$archived_log
     3> where sequence#=387;

FIRST_CHANGE#-1  SEQUENCE# 
----------       ----------
      9999             387
1 row selected.

Now that you know the SCN, you can perform the recovery, using these steps:

1. Connect to the mounted instance as sysdba.

2. Issue:

sql> recover automatic database until change 9999;

3. Oracle will stop recovery before it looks for arch_387.arc and informs you of the following:

ORA-00289:  Suggestion: d:\orant\arc\arch_387.arc
Ora-00280:  Change 9999 for thread 1 is in sequence #387
Ora-00278:  Logfile 'd:\orant\arc\arch_386.arc' no...
Log applied.
Media recovery complete.
SQL>

The final step in this process is to open the database (discussed below).

Cancel-Based Recovery

Cancel-based recovery proceeds until you enter the word cancel. This can be done by either typing ‘cancel’ at a prompt similar to:

Specify log: {=suggested | filename | AUTO | CANCEL}

Or by entering ‘alter database recover cancel’ at the sql prompt. Using the cancel option at the prompt suggests, let’s do our recovery again. You are logged into Server Manager and have issued connect internal and then startup mount. When you enter the command:

SQL> recovery database until cancel;

OR

SQL> recovery database until cancel using backup controlfile (if controlfile is also restored)

Oracle suggests the name of the first archived redo log it requires. Press the ENTER key to accept the name and keep going until Oracle asks for log with sequence number 387. Enter the word cancel, and recovery stops, as Oracle tells you the following:

ORA-00289:  Suggestion : d:\orant\arc\arch_386.arc
ORA-00278:  Logfile 'd:\orant\arc]arch_386.arc' no...
Specify log: {=suggest | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

The final step in this process is to open the database (discussed below).

Time-Based Recovery

To use time-based recovery, you need to know the time recorded in v$archived_log for archived redo log sequence 387 (the missing redo log). By issuing the query:

SQL>  select first_time from v$archived_log where sequence# = 387;

you get the following time:

FIRST_TIME
------------------
12/21/98 14:42:04

With the database mounted, this recovery would use the command:

SQL> recover database until time '1998/12/21:14:42:04';

Oracle suggests the name of the first archived redo log it requires. Enter the reply auto and Oracle applies archived redo logs until the sequence number 387. The output returned by Oracle once finished would be something like:

ORA-00280: Change 9999 for thread 1 is in sequence #387
ORA-00278: Logfile 'd:\orant\arcarch_386.arc\ no...
Log applied.
Media recovery complete.

The final step in this process is to open the database (discussed below).

Opening the database after incomplete media recovery

After any incomplete recovery, the database must be opened with the RESETLOGS option:

SQL>  alter database open resetlogs;

Note, resetting the redo log does the following:

1. Creates the online redo log files if they do not currently exist.
2. Reinitializes the control file metadata about online redo logs and redo threads to start generating sequence #1.
3. Updates all current datafiles and online redo logs with a new RESETLOGS SCN and time stamp.

Because recovery through resetlogs is not possible a new backup must be taken of the database once it is opened with the RESETLOGS option.