Restore Oracle tablespace to its state from 2 days ago

backuporacleoracle-10grecovery

I have multiple test tablespaces in an Oracle SID. One of the tablespaces is updated with wrong data. It worked fine 2 days ago. I want to restore the tablespace to its state from 2 days ago.

I tried to restore the tablespace from the Coldbackups, but during startup it gave the error : 

ORA-01113: file 10 needs media recovery
ORA-01110: data file 10: '/user/oracle/metadata/lmptest/gkpdbspace.dbf'

And

recover database

or

recover datafile '/user/oracle/metadata/lmptest/gkpdbspace.dbf'

didn't work.

Best Answer

You probably tried to restore and then recover the database. When you recover the database all the files should be in sync before you can open the database for work. The error shown tells you that some of your data files are not in sync with the rest of the database.

What your really need to use to recover a tablespace or a set of tablespaces is Tablespace Point-in-Time Recovery (TSPITR). You can do it manually or with RMAN. In the latter case the command might look like:

recover tablespace app_data
until SCN 1234567
auxiliary destination '/tmp/auxdb';

And you wouldn't need to configure the auxiliary database manually. With user-managed TSPITR, on the other hand, you have to configure the auxiliary database first, and then use Transportable Tablespaces feature (see the section "Introduction to Transportable Tablespaces" in Database Administrator's Guide) to move the data files along with metadata to the target database.

For more details read "RMAN Tablespace Point-in-Time Recovery (TSPITR)" and "Performing User-Managed TSPITR" sections in Database Backup and Recovery Advanced User's Guide, and also "Tablespace Point-in-Time Recovery" section in Concepts.

Related Question