Tablespace Point-in-time Recovery (TSPITR) using RMAN

Scenario: A logical corruption occurred due to an user error and have to quickly get back to the state just before the time of the error. Possible options: Database point-in-time recovery, but the downside is the entire database has to be unavailable and other transactions after the PITR would be lost. The other option would be Tablespace Point-in-time-Recovery (TSPITR) - the obvious choice here. I had known that RMAN could be used to perform TSPITR, but I realized just today how powerful, simple, automated, error-free and fast it could be to perform TSPITR using RMAN - it's really amazing. The intent of this post is just to high-light and appreciate the usefulness of RMAN to perform TSPITR.

Assuming we have the latest backup, just executing the following statement would do everything needed to get the entire tablespace to a point-in-time in the past. Believe me, you don't have to do anything other than just this statement!


recover tablespace APP_DATA until time
"to_date('2009-08-04 12:15:00’,’YYYY-MM-DD HH24:MI:SS')"
auxiliary destination ’/opt/oracle/temp’;

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

That's it....you now have all the objects back in the tablespace!.

You can view the output of the RMAN session by downloading this file.. which is quite self-explanatory and informative too!