Page: 1 2

Most of us are aware of the Oracle Tablespace Point-in-Time Recovery feature. Starting with version 10g, Tablespace Point-in-Time Recovery (TSPITR) can also be done automatically. Oracle 10g blessed database administrators with the powerful feature of automatic Tablespace Point-in-Time Recovery (Automatic TSPITR). This is referred as fully Automated TSPITR.

Automated TSPITR:

There are only two requirements beyond the preparations when performing fully automated TSPITR and letting RMAN manage the entire process.

• First you have to specify the auxiliary location for RMAN to restore the auxiliary datafile/datasets.
• Next you have to configure the required channels on Target Instance.

User-managed TSPITR :

Below is the basic procedure for performing user-managed TSPITR.

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

AUXILIARY DESTINATION:

You must add AUXILIARY DESTINATION in the RMAN along with recover tablespace in order to perform full automated TSPITR. In the below script we assume that a table has been dropped from tablespace DATATS

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

RMAN performs below steps when the above script is run.

Connection:

RMAN creates the auxiliary instance, starts it up and connects to it if there is no connection to an auxiliary instance.

Take Tablespace:

It then takes the tablespaces to be recovered offline in the target database.

Restore backup control file:

Then it restores a backup control file from a point in time before the target time to the auxiliary instance. Continued...

Page: 1 2

 More Database Articles
   Database Security: Step by step guideline
   Oracle on Linux, Some useful commands & tips
   Efficiently handle problematic Oracle Sequences!!
   Oracle Guide: Recovering accidentally dropped tables!!
   Database Benchmarking – Some Facts!