RMAN TSPITR

Example on using RMAN TSPITR (Tablespace Point-in-Time Recovery):


Login into your RMAN Catalog by setting up your database environment.

rman catalog rman/password@catalogdb target /

RMAN> configure channel 1 device type sbt_tape parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=adm_p,OB2BARLIST=restore_job)';

RMAN> configure channel 2 device type sbt_tape parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=adm_p,OB2BARLIST=restore_job)';

I am configuring the above channels manually as I had issues RMAN allocating channels for AUXILIARY instance...

run {
SET NEWNAME FOR DATAFILE '/ora05/oradata/db1/abc_ts_01.dbf' TO '/ora03/oradata/db1/abc_ts_01.dbf'; --here I am changing the default location of a datafile to a new location
recover tablespace abc_ts until time "to_date('16-FEB-2013 06:41:00','DD-MON-YYYY HH24:MI:SS')" AUXILIARY DESTINATION '/ora05_1/auxdest'; --this is optional but I personally wanted to give a storage to RMAN for its dynamic Auxiliary instance.
}

The above will restore your tablespace until the time specified.

Once the above is successful, you will notice that tablespace is OFFLINE as Oracle wants you to take a backup of that tablespace and lets you bring that tablespace online.

So, take a backup:

RMAN> backup tablespace abc_ts; --or take a backup from your backup software...

and bring it online:

RMAN> sql 'alter tablespace abc_ts online';