Fully Automated TSPITR

Couple of days ago, I have tested Automatic TSPITR feature which is introduced with version 10g. Maybe, many experts might have already discussed about this and might have written articles about this feature। However, I just thought of sharing my experience which could help to the DBA who is not yet aware of this feature or never tested this feature.

Starting with version 10g, Tablespace Point-in-Time Recovery(TSPITR) can also be done automatically, which is referred as fully Automated TSPITR.

This process requires the following two manual steps:

1. Specify the auxiliary location for RMAN to restore the auxiliary datafile/datasets
2. Configure the required channels on Target Instance.

Manual TSPITR is required the following procedure:

The basic procedure for performing user-managed TSPITR is as follows:

Take the tablespaces requiring TSPITR offline.

• Plan the setup of the auxiliary database.
• Create the auxiliary database and recover it to the desired point in time.
• Drop the tablespaces requiring TSPITR from the primary database.
• Use the transportable tablespace*** feature to transport the set of tablespaces from the auxiliary database to the primary database.

*** Probably in versions 8i and 9i, because transportable tablespace were first introduced with 8i (v. 8.1.5). Version which doesn’t have TTS, can do export and import of desired table/s.

To perform full automated TSPITR, you just need to add AUXILIARY DESTINATION in the RMAN along with recover tablespace.

Assuming that a table has been dropped from tablespace DATATS:

Example:

run
{ RECOVER TABLESPACE datats
UNTIL logseq thread 1 -- thread 1 indicates single instance
AUXILIARY DESTINATION '/u10/temp'; --Auxiliary Location
}

When the above script runs, 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.

What I like about this feature is that, given the enough space to the auxiliary location, RMAN will do everything which required for TSPITR and once the process is done it will automatically obliterate the auxiliary instance, including files which restored during this process.

Its really worth to have a test about this feature.

Please note that I have not discussed about the prerequisites of performing TSPITR.

References:

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit003.htm#sthref778
Metalink Note : Getting Started with Transportable Tablespaces - Note:100693.1

Happy reading,
Jaffar