Incomplete recovery means that you don’t restore all transactions that were committed before the failure. With this type of recovery, you’re recovering to a point in time in the past, and transactions are lost. This is why incomplete recovery is also known as database point-in-time recovery (DBPITR).
Incomplete recovery doesn’t mean that you’re restoring and recovering only a subset of data files. In fact, with most incomplete scenarios, you have to restore all data files from the backup as part of the procedure. If you don’t want to recover all data files, you first need to take offline any data files you don’t intend to participate in the incomplete recovery process. When you initiate the recovery, Oracle will only recover data files that have an ONLINE value in the STATUS column of V$DATAFILE_HEADER.
You may want to perform an incomplete recovery for many different reasons:
- You attempt to perform a complete recovery but are missing the required archive redo logs or unarchived online redo log information.
- You want to restore the database back to a point in time in the past just prior to an erroneous user error (deleted data, dropped table, and so on).
- You have a testing environment in which you have a baseline copy of the database. After the testing is finished, you want to reset the database back to baseline for another round of testing.
You can perform user-managed incomplete recovery three ways:
- Cancel based
- SCN based
- Time based
Cancel based allows you to apply archive redo and halt the process at the boundary, based on an archive redo log file. For instance, say you’re attempting to restore and recover your database, and you realize that you’re missing an archive redo log. You have to stop the recover process at the point of your last good archive redo log. You initiate cancel-based incomplete recovery with the CANCEL clause of the RECOVER DATABASE statement:
SQL> recover database until cancel;If you want to recover up to and including a certain SCN number, use SCN-based incomplete recovery. You may know from the alert log or from the output of LogMiner the point to which you want to restore to a certain SCN. Use the UNTIL CHANGE clause to perform this type of incomplete recovery:
SQL> recover database until change 12345;If you know the time at which you want to stop the recovery process, use time-based incomplete recovery. For example, you may know that a table was dropped at a certain time and want to restore and recover the database up to the specified time. The format for a time-based recovery is always as follows: YYYY-MM-DD:HH24:MI:SS. Here is an example:
SQL> recover database until time '2012-10-21:02:00:00';When you perform an incomplete recovery, you have to restore all data files that you plan to have online when the incomplete restoration is finished. Here are the steps for an incomplete recovery:
- Shut down the database.
- Restore all the data files from the backup.
- Start the database in mount mode.
- Apply redo (roll forward) to the desired point, and halt the recovery process (use cancel-, SCN-, or time-based recovery).
- Open the database with the OPEN RESETLOGS clause.
The following example performs a cancel-based incomplete recovery. If the database is open, shut it down:
$ sqlplus / as sysdba
SQL> shutdown abort;Next, copy all data files from the backup (either a cold or hot backup). This example restores all data files from a hot backup. For this example the current control file is intact and doesn’t need to be restored. Here is a snippet of the OS copy commands for the database being restored:
cp /u01/hbackup/O12C/system01.dbf /u01/dbfile/O12C/system01.dbf
cp /u01/hbackup/O12C/sysaux01.dbf /u01/dbfile/O12C/sysaux01.dbf
cp /u01/hbackup/O12C/undotbs01.dbf /u01/dbfile/O12C/undotbs01.dbf
cp /u01/hbackup/O12C/users01.dbf /u01/dbfile/O12C/users01.dbf
cp /u01/hbackup/O12C/tools01.dbf /u01/dbfile/O12C/tools01.dbfAfter the data files have been copied back, you can initiate the recovery process. This example performs a cancel-based incomplete recovery:
$ sqlplus / as sysdba
SQL> startup mount;
SQL> recover database until cancel;At this point, the Oracle recovery process suggests an archive redo log to apply:
ORA-00279: change 3584872 generated at 11/02/2012 12:02:32 needed for thread 1
ORA-00289: suggestion : /u01/oraarch/O12C/1_1_798292887.dbf
ORA-00280: change 3584872 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}Apply the logs up to the point you where want to stop, and then type CANCEL:
CANCELThis stops the recovery process. Now, you can open the database with the RESETLOGS clause:
SQL> alter database open resetlogs;The database has been opened to a point in time in the past. The recovery is deemed incomplete because not all redo was applied.
Tip Now would be a good time to get a good backup of your database. This will give you a clean point from which to initiate a restore and recovery should a failure happen soon after you’ve opened your database.
PURPOSE OF OPEN RESETLOGS
Sometimes, you’re required to open your database with the OPEN RESETLOGS clause. You may do this when recreating a control file, performing a restore and recovery with a backup control file, or performing an incomplete recovery. When you open your database with the OPEN RESETLOGS clause, it either wipes out any existing online redo log files or, if the files don’t exist, recreates them. You can query the MEMBER column of V$LOGFILE to see which files are involved in an OPEN RESETLOGS operation.
Why would you want to wipe out what’s in the online redo logs? Take the example of an incomplete recovery, in which the database is deliberately opened to a point in time in the past. In this situation the SCN information in the online redo logs contains transaction data that will never be recovered. Oracle forces you to open the database with OPEN RESETLOGS to purposely wipe out that information.
When you open your database with OPEN RESETLOGS, you create a new incarnation of your database and reset the log sequence number back to 1. Oracle requires a new incarnation so as to avoid accidentally using any old archive redo logs (associated with a separate incarnation of the database), in the event that another restore and recovery is required.
