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'


recover database


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

didn't work.


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.


I just noticed that you mentioned cold backups in your question. There's usually only one reason to make cold database backups – your database is in NOARCHIVELOG mode. If your database is in NOARCHIVELOG mode, you cannot use TSPITR, because it requires the database to be in ARCHIVELOG mode to perform incomplete database recovery using archive logs.

All you can do in NOARCHIVELOG mode to restore the tablespace(s) to some point in the past is use the previous backups of database, but in this case you can only restore the database (and therefore tablespaces) to the points in the past when these backups were made, because no archive logs are available and thus no recovery is possible.

This means that to restore your tablespace (database) as it existed two days before, you need to have a full backup of database made two days back, or you need a full database backup made more than two days back and all incremental backups of database up to two days before.

If you have all the backups required, you can duplicate your database to a different host, restoring it until two days before (or earlier), extract the tablespace(s) you need to restore (do you remember we can't recover in NOARCHIVELOG mode?), and then move this tablespace(s) to the destination database.

You can duplicate the database using DUPLICATE RMAN command (see the chapter "Creating and Updating Duplicate Databases with RMAN" in Database Backup and Recovery Advanced User's Guide).

You can use RMAN to move tablespaces to a different database using Transportable Tablespaces.

  • FIXED: You can use Data Pump to move tablespaces to a different database using Transportable Tablespaces. – Yasir Arsanukaev May 29 '13 at 11:32
  • yes my db is running in NOARCHIVELOG mode. As you said do I have to restore the total database , means all the tablespaces in it will have to be restored from the full backup ?? I have the full backup of the DB 2 days ago. – kumarprd May 30 '13 at 4:24
  • Yea, you will need to DUPLICATE the database to a different host using auxiliary instance, which you would manually configure, and the backups made as of date you want your tablespaces restore to. After the database is successfully duplicated from backups, it is automatically OPENed, and you can use transportable tablespaces to move the data as it existed on the date of backup to your primary database. – Yasir Arsanukaev May 30 '13 at 9:21

Usually this error comes up when you restore from a hot backup and any archivelogs needed for consistent recovery is not available.

In your case, since you are restoring from cold backup, this error should not be shown.

The open database statement is not available in your question.


alter database open resetlogs;
  • -1 you are missing the reason for the error message in this case. To reset the redo logs is a fine method to destroy a database if there is needed information in the redo logs – miracle173 Aug 20 '13 at 19:57
  • What I understand is that any data from redo log/ archive logs is not required if you are restoring from a cold backup. Pls correct me if I am wrong – BigB Aug 21 '13 at 5:21
  • the OP is replaces only de datafiles of one tablespace by the cold backup. The rest of the database including the redo logs are left unchanged. This is not a valid method to make a tablespace point in time recovery. The database now properly alerts that these files from the cold backup are older than the rest of the database and therefore need recovery. If the database was shut down with the 'abort' parameter and you throw away the redo logs you will loose the information that the database needs for crash recovery that is necessary after 'shutdoown abort' and you will loose your whole database – miracle173 Aug 21 '13 at 7:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.