The term incomplete database recovery means that you can’t recover all committed transactions. Incomplete means that 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 don’t have all the redo required to perform a complete recovery. You’re missing either the archived redo log 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 : restore and recovery. The restore step re-creates data files, and the recover step applies redo up to the specified point in time. The restore process can be initiated from RMAN in a couple of different ways:
- RESTORE DATABASE UNTIL
- FLASHBACK DATABASE
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 RESTORE DATABASE 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. I recommend that 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 can’t 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:
select file#, status, fuzzy,
error, checkpoint_change#,
to_char(checkpoint_time,'dd-mon-rrrr hh24:mi:ss') as checkpoint_time
from v$datafile_header;
Note 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. For more details on TSPITR, see RMAN Recipes for Oracle Database 12c.
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 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 archived redo log files on disk will be applied. If the archived redo log 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.
Tip Starting with Oracle 10g, you can perform parallel media recovery by using the RECOVER DATABASE PARALLEL command.
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).
Note After incomplete database recovery is performed, you are required to open your database with the ALTER DATABASE OPEN RESETLOGS command.
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.
Determining the Type of Incomplete Recovery
Time-based restore and recovery is commonly used when you know the approximate date and time to which you want to recover your database. For instance, you may know approximately the time you want to stop the recovery process, but not a particular SCN.
Log sequence–based and cancel-based recovery work well in situations in which you have missing or damaged log files. In such scenarios, you can recover only up to your last good archived redo log file.
SCN-based recovery works well if you can pinpoint the SCN at which you want to stop the recovery process. You can retrieve SCN information from views such as V$LOG and V$LOG_HISTORY. You can also use tools such as LogMiner to retrieve the SCN of a particular SQL statement.
Restore point recoveries work only if you have established restore points. In these situations, you restore and recover up to the SCN associated with the specified restore point.
TSPITR is used in situations in which you need to restore and recover just a few tablespaces. You can use RMAN to automate many of the tasks associated with this type of incomplete recovery.
Performing Time-Based Recovery
To restore and recover your database back to a point in time in the past, you can use either the UNTIL TIME clause of the RESTORE and RECOVER commands or the SET UNTIL TIME clause within a run{} block. RMAN will restore and recover the database up to, but not including, the specified time. In other words, RMAN will restore any transactions committed prior to the time specified. RMAN automatically stops the recovery process when it reaches the time you specified.
The default date format that RMAN expects is YYYY-MM-DD:HH24:MI:SS. However, I recommend using the TO_DATE function and specifying a format mask. This eliminates ambiguities with different national date formats and having to set the OS NLS_DATE_FORMAT variable. The following example specifies a time when issuing the restore and recover commands:
$ rman target /
RMAN> startup mount;
RMAN> restore database until time
"to_date('15-jan-2015 12:20:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database until time
"to_date('15-jan-2015 12:20:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database open resetlogs;
If everything goes well, you should see output such as this:
Statement processedPerforming Log Sequence-Based Recovery
Usually this type of incomplete database recovery is initiated because you have a missing or damaged archived redo log file. If that’s the case, you can recover only up to your last good archived redo log file, because you can’t skip a missing archived redo log.
How you determine which archived redo log file to restore up to (but not including) will vary. For example, if you are physically missing an archived redo log file, and RMAN can’t find it in a backup set, you’ll receive a message such as this when trying to apply the missing file:
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 19...
Based on the previous error message, you would restore up to (but not including) log sequence 19.
$ rman target /
RMAN> startup mount;
RMAN> restore database until sequence 19;
RMAN> recover database until sequence 19;
RMAN> alter database open resetlogs;
If successful, you should see output such as this:
Statement processed Note Log sequence–based recovery is similar to user-managed cancel-based recovery. See Chapter 3 for details on a user-managed cancel-based recovery.
Performing SCN-Based Recovery
SCN-based incomplete database recovery works in situations in which you know the SCN value at which you want to end the restore-and-recovery session. RMAN will recover up to, but not including, the specified SCN. RMAN automatically terminates the restore process when it reaches the specified SCN.
You can view your database SCN information in several ways:
- Using LogMiner to determine an SCN associated with a DDL or DML statement
- Looking in the alert.log file
- Looking in your trace files
- Querying the FIRST_CHANGE# column of V$LOG, V$LOG_HISTORY and V$ARCHIVED_LOG
After establishing the SCN to which you want to restore, use the UNTIL SCN clause to restore up to, but not including, the SCN specified. The following example restores all transactions that have an SCN that is less than 95019865425:
$ rman target /
RMAN> startup mount;
RMAN> restore database until scn 95019865425;
RMAN> recover database until scn 95019865425;
RMAN> alter database open resetlogs;
If everything goes well, you should see output such as this:
Statement processedRestoring to a Restore Point
There are two types of restore points: normal and guaranteed. The main difference between a guaranteed restore point and a normal restore point is that a guaranteed restore point is not eventually aged out of the control file; a guaranteed restore point will persist until you drop it. Guaranteed restore points do require an FRA. However, for incomplete recovery using a guaranteed restore point, you do not have to have flashback database enabled.
You can create a normal restore point using SQL*Plus, as follows:
SQL> create restore point MY_RP;This command creates a restore point, named MY_RP, that is associated with the SCN of the database at the time the command was issued. You can view the current SCN of your database, as shown:
SQL> select current_scn from v$database;You can view restore point information in the V$RESTORE_POINT view, like so:
SQL> select name, scn from v$restore_point;The restore point acts like a synonym for the particular SCN. The restore point allows you to restore and recover to an SCN without having to specify a number. RMAN will restore and recover up to, but not including, the SCN associated with the restore point.
This example restores and recovers to the MY_RP restore point:
$ rman target /
RMAN> startup mount;
RMAN> restore database until restore point MY_RP;
RMAN> recover database until restore point MY_RP;
RMAN> alter database open resetlogs;
Restoring Tables to a Previous Point
Starting with Oracle 12c, you can restore individual tables from RMAN backups via the RECOVER TABLE command. This gives you with the ability to restore and recover a table back to a point in time in the past.
The table-level restore feature uses a temporary auxiliary instance and the Data Pump utility. Both the auxiliary instance and Data Pump create temporary files when restoring the table. Before initiating a table-level restore, first create two directories: one to hold files used by the auxiliary instance and one to store a Data Pump dump file:
$ mkdir /tmp/oracle
$ mkdir /tmp/recover
The prior two directories are referenced within the RECOVER TABLE command via the AUXILIARY DESTINATION and DATAPUMP DESTINATION clauses. In the following bit of code, the INV table, owned by MV_MAINT, is restored as it was at a prior SCN:
recover table mv_maint.inv
until scn 4689805
auxiliary destination '/tmp/oracle'
datapump destination '/tmp/recover';
Providing that RMAN backups are available that contain the state of the table at the specified SCN, a table-level restore and recovery is performed.
Note You can also restore a table to an SCN, a point in time, or a log sequence number.
When RMAN performs a table-level recovery, it automatically creates a temporary auxiliary database, uses Data Pump to export the table, and then imports the table back into the target database as it was at the specified restore point. After the restore is finished, the auxiliary database is dropped, and Data Pump dump file is removed.
Tip Although the RECOVER TABLE command is a nice enhancement, I would recommend that, if you have an accidentally dropped table, you first explore using the Flashback Table to Before Drop feature to restore the table. Or, if the table was erroneously deleted from, then use the Flashback Table feature to restore the table back to a point in time in the past. If neither of the prior options are viable, then consider using the RMAN Recover Table feature.
