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
You can view the output of the RMAN session by downloading this file.. which is quite self-explanatory and informative too!