RMAN - Automated TSPITR

There are now different ways to do TSPITR.

1. In traditional way, I may have to restore SYSTEM, SYSAUX, UNDO and the USER DATA tablespce first in a auxiliary database and then export this particular Schema and then import in the Target database.
2. Use a DUPLICATE DATABASE command to create this temporary database and export this particular Schema and then import in the Target database.
3. Now we can use fully automated RMAN TSPITR also.

Before I go further, let me make this clear that this Schema that I want to restore is the ONLY schema in the Tablespace that I am restoring. So, it's one-to-one relationship between schema and Tablespace and Schema.

Here is how it goes - Connect to RMAN, Connect to Catalog and run this single line command.

rman target /

connect catalog TEST_RMAN/****@RMANDB;

recover tablespace USERS until time "to_date('2009-10-15 15:40:00','YYYY-MM-DD HH24:MI:SS')" auxiliary destination '/u01/oradata/RESTORE';


That's it. Once the above statement is executed, RMAN does the following for us:

• Creates auxiliary instance (including the pfile etc..)
• Mounts the auxiliary instance
• Makes the candidate tablespace into OFFLINE
• Restores the basic tablespace UNDO, SYTEM,SYSAUX and then the required tablespace
• Applies archives (completes recovery)
• Opens the database
• Performs an export of the objects residing in the tablespace
• Shutdown aux instance
• Import the objects into the target database
• Remove aux instance and cleanup

Now since I was having only one-to-one relationship between Tablespace and Schema so only one schema got back to the given point-in-time but if there are multiple schemas in this tablespace then all of them will go to this given point-in-time.