Understanding RMAN TSPITR

In order to use TSPITR effectively, you need to understand what problems it can solve for you, what the major elements used in TSPITR are, what RMAN does during TSPITR, and limitations on when and how it can be applied.

RMAN TSPITR Concepts

Figure 8-1, "Tablespace Point-in-Time Recovery (TSPITR) Architecture" illustrates the context within which TSPITR takes place, and a general outline of the process.

Figure 8-1 Tablespace Point-in-Time Recovery (TSPITR) Architecture

Description of "Figure 8-1 Tablespace Point-in-Time Recovery (TSPITR) Architecture"

The figure contains the following entities:

The target instance, containing the tablespace to be recovered
The Recovery Manager client
The control file and (optional) recovery catalog, used for the RMAN repository records of backup activity
Archived redo logs and backup sets from the target database, which are the source of the reconstructed tablespace.
The auxiliary instance, an Oracle database instance used in the recovery process to perform the actual work of recovery.

There are four other important terms related to TSPITR, which will be used in the rest of this discussion:

The target time, the point in time or SCN that the tablespace will be left at after TSPITR
The recovery set, which consists of the datafiles containing the tablespaces to be recovered;
The auxiliary set, which includes datafiles required for TSPITR of the recovery set which are not themselves part of the recovery set. The auxiliary set typically includes:
- A copy of the SYSTEM tablespace
- Datafiles containing rollback or undo segments from the target instance
- In some cases, a temporary tablespace, used during the export of database objects from the auxiliary instance
The auxiliary instance has other files associated with it, such as a control file, parameter file, and online logs , but they are not part of the auxiliary set.
The auxiliary destination, an optional location on disk which can be used to store any of the auxiliary set datafiles, control files and online logs of the auxiliary instance during TSPITR. Files stored here can be deleted after TSPITR is complete.

All of these terms will be referenced throughout the remainder of this chapter.

How TSPITR Works With an RMAN-Managed Auxiliary Instance

To perform TSPITR of the recovery set using RMAN and an automated auxiliary instance, you carry out the preparations for TSPITR described in "Planning and Preparing for TSPITR", and then issue the RECOVER TABLESPACE command, specifying, at a minimum, the tablespaces of the recovery set and the target time for the point-in-time recovery, and, if desired, an auxiliary destination as well.

RMAN then carries out the following steps:

If there is no connection to an auxiliary instance, RMAN creates the auxiliary instance, starts it up and connects to it.
Takes the tablespaces to be recovered offline in the target database
Restores a backup control file from a point in time before the target time to the auxiliary instance
Restores the datafiles from the recovery set and the auxiliary set to the auxiliary instance. Files are restored either in locations you specify for each file, or the original location of the file (for recovery set files) or in the auxiliary destination (for auxiliary set files, if you used the AUXILIARY DESTINATION argument of RECOVER TABLESPACE)
Recovers the restored datafiles in the auxiliary instance to the specified time
Opens the auxiliary database with the RESETLOGS option
Exports the dictionary metadata about objects in the recovered tablespaces to the target database
Shuts down the auxiliary instance
Issues SWITCH commands on the target instance, so that the target database control file now points to the datafiles in the recovery set that were just recovered at the auxiliary instance.
Imports the dictionary metadata from the auxiliary instance to the target instance, allowing the recovered objects to be accessed.
Deletes all auxiliary set files.

At that point the TSPITR process is complete. The recovery set datafiles are returned to their contents at the specified point in time, and belong to the target database.

Deciding When to Use TSPITR

Like a table import, RMAN TSPITR enables you to recover a consistent data set; however, the data set recovered includes an entire tablespace rather than one object.

RMAN TSPITR is most useful for situations such as these:

Recovering data lost after an erroneous TRUNCATE TABLE statement;
Recovering from logical corruption of a table;
Undoing the effects of an incorrect batch job or other DML statement that has affected only a subset of the database;
Recovering a logical schema to a point different from the rest of the physical database, when multiple schemas exist in separate tablespaces of one physical database.

Note that, as with database point-in-time recovery (DBPITR), you cannot perform TSPITR if you do not have your archived redo logs. For databases running in NOARCHIVELOG mode, you cannot perform TSPITR. You can only restore your entire database from a consistent backup.

Limitations of TSPITR

There are a number of situations which you cannot resolve by using TSPITR.

You cannot recover dropped tablespaces.
You cannot recover a renamed tablespace to a point in time before it was renamed. If you try to perform a TSPITR to an SCN earlier than the rename operation, RMAN cannot find the new tablespace name in the repository as of that earlier SCN (because the tablespace did not have that name at that SCN).

In this situation, you must recover the entire database to a point in time before the tablespace was renamed. The tablespace will be found under the name it had at that earlier time.
You cannot recover tables without their associated constraints, or constraints without the associated tables.
You cannot use TSPITR to recover any of the following:
- Replicated master tables
- Partial tables (for example, if you perform RMAN TSPITR on partitioned tables and spread partitions across multiple tablespaces, then you must recover all tablespaces which include partitions of the table.)
- Tables with VARRAY columns, nested tables, or external files
- Snapshot logs and snapshot tables
- Tablespaces containing undo or rollback segments
- Tablespaces that contain objects owned by SYS, including rollback segments

TSPITR has some other limitations:

If a datafile was added after the point to which RMAN is recovering, an empty datafile by the same name will be included in the tablespace after RMAN TSPITR.
TSPITR will not recover query optimizer statistics for recovered objects. You must gather new statistics after the TSPITR.
Assume that you run TSPITR on a tablespace, and then bring the tablespace online at time t. Backups of the tablespace created before time t are no longer usable for recovery with a current control file. You cannot run TSPITR again on this tablespace to recover it to any time less than or equal to time t, nor can you use the current control file to recover the database to any time less than or equal to t. Therefore, you must back up the recovered tablespace as soon as TSPITR is complete.

Limitations of TSPITR Without a Recovery Catalog

If you do not use a recovery catalog when performing TSPITR, then note the following special restrictions:

The undo segments at the time of the TSPITR must be part of the auxiliary set. Because RMAN has no historical record of the undo in the control file, RMAN assumes that the current rollback or undo segments were the same segments present at the time to which recovery is performed. If the undo segments have changed since that time, then TSPITR will fail.
TSPITR to a time that is too old may not succeed if Oracle has reused the control file records for needed backups. (In planning your database, set the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter to a value large enough to ensure that control file records needed for TSPITR are kept.)
Assume that you run TSPITR on a tablespace, and then bring the tablespace online at time t. When not using a recovery catalog, the current control file has no record of the older incarnation of the recovered tablespace. Thus, recovery with a current control file that involves this tablespace can no longer use a backup taken prior to time t. You can, however, perform incomplete recovery of the whole database to any time less than or equal to t, if you can restore a backup control file from before time t.