Recovery Manager (RMAN) automatic tablespace point-in-time recovery

 Recovery Manager (RMAN) automatic tablespace point-in-time recovery (commonly abbreviated TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.

There are four other important terms related to TSPITR,

• 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.

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

Fully Automated RMAN TSPITR

When performing fully automated TSPITR, letting RMAN manage the entire process, there are only two requirements beyond the preparations in "Planning and Preparing for TSPITR":

• You must specify the auxiliary destination for RMAN to use for the auxiliary set datafiles and other files for the auxiliary instance. 

• You must configure any channels required for the TSPITR on the target instance. (The auxiliary instance will use the same channel configuration as the target instance when performing the TSPITR.)

RMAN bases as much of the configuration for TSPITR as possible on your target database. During TSPITR, the recovery set datafiles are written in their current locations on the target database. The same channel configurations in effect on the target database are used on the auxiliary instance when restoring files from backup. Auxiliary set datafiles and other auxiliary instance files, however, are stored in the auxiliary destination.

Performing Fully Automated RMAN TSPITR

To actually peform automated RMAN TSPITR, start the RMAN client, connecting to the target database and, if applicable, a recovery catalog. This example shows connecting in NOCATALOG mode, using operating system authentication:

% rman TARGET /

If you have configured channels that RMAN can use to restore from backup on the primary instance, then you are ready to perform TSPITR now, by running the RECOVER TABLESPACE... UNTIL... command.

This example returns the users and tools tablespaces to the end of log sequence number 1300, and stores the auxiliary instance files (including auxiliary set datafiles) in the destination /disk1/auxdest:

RMAN> RECOVER TABLESPACE users, tools 

     UNTIL LOGSEQ 1300 THREAD 1

      AUXILIARY DESTINATION '/disk1/auxdest';

Assuming the TSPITR process completes without error, the tablespaces are taken offline by RMAN, restored from backup and recovered to the desired point in time on the auxiliary instance, and then re-imported to the target database. The tablespaces are left offline at the end of the process. All auxiliary set datafiles and other auxiliary instance files are cleaned up from the auxiliary destination.

Tasks to Perform After Successful TSPITR

If TSPITR completes successfully, you must back up the recovered tablespaces, and then you can bring them online.

Backing Up Recovered Tablespaces After TSPITR

It is very important that you backup recovered tablespaces immediately after TSPITR is completed.

After you perform TSPITR on a tablespace, you cannot use backups of that tablespace from before the TSPITR was completed and the tablespace put back on line. If you start using the recovered tablespaces without taking a backup, you are running your database without a usable backup of those tablespaces. For this example, the users and tools tablespaces must be backed up, as follows:

RMAN> BACKUP TABLESPACE users, tools;

You can then safely bring the tablespaces online, as follows:

RMAN> SQL "ALTER TABLESPACE users, tools ONLINE";

Your recovered tablespaces are now ready for use.