The term incomplete database recovery means you cannot recover all committed transactions. Incomplete means you do not apply all redo to restore up to the point of the last committed transaction that occurred in your database. In other words, you are restoring and recovering to a point in time in the past.

For this reason, incomplete database recovery is also called database point-in-time recovery (DBPITR). Typically, you perform incomplete database recovery for one of the following reasons:

•     You do not have all the redo required to perform a complete recovery. You are missing either the archivelog files or the online redo log files that are required for complete recovery. This situation could arise because the required redo files are damaged or missing.

•     You purposely want to roll back the database to a point in time in the past. For example, you would do this if somebody accidentally truncated a table and you intentionally wanted to roll back the database to just before the truncate table command was issued.

Incomplete database recovery consists of two steps: restore and recovery. The restore step re-creates data files, and the recover step applies the redo up to the specified point in time. The restore process can be initiated from RMAN in a couple of ways.

•     RESTORE DATABASE UNTIL

•     FLASHBACK DATABASE (may not need the restore depending on the UNDO information)

For the majority of incomplete database recovery circumstances, you use the RESTORE DATABASE UNTIL command to instruct RMAN to retrieve data files from the RMAN backup files. This type of incomplete database recovery is the main focus of this section of the chapter. The Flashback Database feature is covered in the section “Flashing Back a Database” later in this chapter.

The UNTIL portion of the RESTORE DATABASE command instructs RMAN to retrieve data files from a point in time in the past, based on one of the following methods:

•     Time

•     SCN

•     Log sequence number

•      Restore point

The RMAN RESTORE DATABASE UNTIL command will retrieve all data files from the most recent backup set or image copy. RMAN will automatically determine from the UNTIL clause which backup set contains the required data files.

If you omit the UNTIL clause of the R command, RMAN will retrieve data files from the latest available backup set or image copy. In some situations, this may be the behavior you desire. It is recommended you use the UNTIL clause to ensure that RMAN restores from the correct backup set.

When you issue the RESTORE DATABASE UNTIL command, RMAN will establish how to extract the data files from any of the following types of backups:

•     Full database backup

•     Incremental level 0 backup

•     Image copy backup generated by the BACKUP AS COPY command

You cannot perform an incomplete database recovery on a subset of your database’s online data files. When performing incomplete database recovery, all the checkpoint SCNs for all online data files must be synchronized before you can open your database with the ALTER DATABASE OPEN RESETLOGS command.

You can view the data file header SCNs and the status of each data file via this SQL query:

SQL> select file#, status, fuzzy, error, checkpoint_change#,

to_char(checkpoint_time,’dd-mon-rrrr hh24:mi:ss’) as checkpoint_time from v$datafile_header;

The FUZZY column V$DATAFILE_HEADER contains data files that have one or more blocks with an SCN value greater than or equal to the checkpoint SCN in the data file header. If a data file is restored and has a FUZZY value of YES, then media recovery is required.

The only exception to this rule of not performing an incomplete recovery on a subset of online database files is a tablespace point-in-time recovery (TSPITR), which uses the RECOVER TABLESPACE UNTIL command. TSPITR is used in rare situations; it restores and recovers only the tablespace(s) you specify.

The recovery portion of an incomplete database recovery is always initiated with the RECOVER DATABASE UNTIL command. RMAN will automatically recover your database up to the point specified with the UNTIL clause.

Just like the RESTORE command, you can recover up to the time, change/SCN, log sequence number, or restore point. When RMAN reaches the specified point, it will automatically terminate the recovery process.

Note regardless of what you specify in the UNTIL clause, rMaN will convert that into a corresponding UNTIL SCN clause and assign the appropriate sCN. this is to avoid any timing issues, particularly those caused by daylight saving time.

During a recovery, RMAN will automatically determine how to apply redo. First, RMAN will apply any incremental backups available. Next, any archivelog files on disk will be applied. If the archivelog files do not exist on disk, then RMAN will attempt to retrieve them from a backup set.

If you want to apply redo as part of an incomplete database recovery, the following conditions must be true:

•     Your database is in archivelog mode.

•     You have a good backup of all data files.

•     You have all redo required to restore up to the specified point.

When performing an incomplete database recovery with RMAN, you must have your database in mount mode. RMAN needs the database in mount mode to be able to read and write to the control file.

Also, with an incomplete database recovery, any SYSTEM tablespace data files are always recovered. Oracle will not allow your database to be open while restoring the SYSTEM tablespace data file(s).

After incomplete database recovery is performed, you are required to open your database with the ALTER DATABASE OPEN RESETLOGS command. Any time after issuing an ALTER DATABASE OPEN RESETLOGS, make sure a new backup is taken after this point as other backups may become invalid if trying to restore to after the resetlogs.

Depending on the scenario, you can use RMAN to perform a variety of incomplete recovery methods. The next section discusses how to determine what type of incomplete recovery to perform.