Incomplete Database Recovery

Incomplete recovery can mean restoring a database to a specific point in time (date and time), a specific SCN (system change number) or sequence number (archive log). You normally restore all the datafiles and controlfile (only required if physical structure has changed), any archived redo logs but you do not restore the redo log files (not normally backed up).

If you lose all copies of the current logfile group perform a incomplete recovery upto the last log switch, also if you recover tablespaces that are in a different time to other tablespaces, they must be all the same time.

Incomplete recovery is always done in mount mode and will use the controlfile, a incomplete recovery can only be performed if you have SYSDBA privilege. Use v$log_history to obtain the archive redo logs thread and sequence numbers if performing a sequence number recovery.

The sequence for incomplete recovery recover is:

  1. shutdown abort (who cares you are going to recover anyway)
  2. startup mount
  3. set the nls_date_format
  4. set until time (this is the point on where you specify the time you want to recover to)
  5. restore (data files that have been backed up will hopefully be restored)
  6. recover (use the redo logs to rollward the database upto the point in time you specified)
  7. open resetlogs (see below note)
  8. backup
RMAN Recovery

rman> run {
   set until time ’2007-12-10 11:29:00’
   restore database;
   recover database;
   alter database open resetlogs; (always used during incomplete recovery - see below)

see below for recovery options

RMAN Recover Options set until time ’2007-12-10 11:29:00’ (note: this is based on the NLS_DATE_FORMAT)
set until sequence 10305 thread 1; (note: it is one less than specified)
set until scn 67543;
SQL Recovery

c:\> restore the files in the normal way
sql> startup mount;
sql> recover database until time ’2007-12-10 11:29:00’;
sql> alter database open resetlogs; (always used during incomplete recovery - see below)

see below for recovery options

Note: When doing an incomplete recovery with SQL*Plus you have no option with the date option it must be yyyy-mm-dd hh24:mis:ss

SQL Recover Options

recover database until cancel (At random type cancel to stop the recovery)
recover database until change 309121
recover database until time ’2007-12-10 11:29:00’ (see below for options)


The restlogs clause is required in most incomplete recovery to open the database. It resets the redo log sequence for the oracle database. For recovery through a resetlogs to work, it is vital that the names generated for the archive logs let oracle distinguish between logs produced by different incarnations. This is why you use the %r in the parameter log_archive_format, %r is the incarnation other wise archive logs could be written over.

After a resetlogs there will be a new database incarnation number and the log switch number will be reset. In previous version all old backups and archive logs would have been useless but not any more in Oracle 10g.

Recovering Database Incarnations

Anytime you use the open resetlogs command, the incarnation of the database changes and a new incarnation begins. The previous incarnation is called ancestor incarnation and the latest is called current incarnation. to recover using previous incarnations you

    1. Obtain the incarnation key that was current at the time you want to recover the database to

                 rman> list incarnation;            (for this example we will use 2)

    2. start the database

                 rman> startup fource nomount;

    3. reset the current incarnation to the incarnation that was current at the point in time that you want to recover to

                rman> reset database to incarnation 2;

    4. restore the old controlfile and mount the database

         rman> restore controlfile from autobackup;
         rman> alter database mount;

    5. restore and recover the database until the point in time or SCN

         rman> restore database;
         rman> recover database until scn 1000;

    6. open the database after resetting the online log files;

                rman> alter database open resetlogs;