Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to restore an Oracle database ( to a test system with a different file layout. The production database is being backed up using RMAN, no catalog. We do a full backup of all the datafiles on Sunday (last one being Mar 9th) and then only backup the archivelogs every night during the week.

On 3/9 we had 11 datafiles. On 3/12 I created a new tablespace/datafile. When doing the restore today my general process has been:

  1. Install Oracle software only, no initial database
  2. Restore the SPFILE to a PFILE from the 3/9 backup and edit it. This is where I change the DB_DOMAIN and the new locations of the control files on the test server.
  3. Startup nomount with the new PFILE.
  4. Restore the control file from the 3/9 backup.
  5. Mount the database. Using sqlplus I rename the redo logs to their new location.
  6. Since the restored control files aren't aware of the backup pieces for 3/10 - 3/14, I run this for each missing day: CATALOG BACKUPPIECE '/path/to/backuppiece.bkp'
  7. Finally I run this RMAN command to restore/recover the database (getting the SCN from LIST BACKUPSET):

    SET UNTIL SCN 426265698;
    SET NEWNAME FOR DATAFILE 1 TO '/var/oracle/data/orcl/system01.dbf';
    SET NEWNAME FOR DATAFILE 2 TO '/var/oracle/data/orcl/sysaux01.dbf';
    SET NEWNAME FOR DATAFILE 3 TO '/var/oracle/data/orcl/undotbs01.dbf';
    SET NEWNAME FOR DATAFILE 4 TO '/var/oracle/data/orcl/users01.dbf';
    SET NEWNAME FOR DATAFILE 5 TO '/var/oracle/data/orcl/foo01.dbf';
    SET NEWNAME FOR DATAFILE 6 TO '/var/oracle/data/orcl/foo02.dbf';
    SET NEWNAME FOR DATAFILE 7 TO '/var/oracle/data/orcl/foo03.dbf';
    SET NEWNAME FOR DATAFILE 8 TO '/var/oracle/data/orcl/foo04.dbf';
    SET NEWNAME FOR DATAFILE 9 TO '/var/oracle/data/orcl/foo05.dbf';
    SET NEWNAME FOR DATAFILE 10 TO '/var/oracle/data/orcl/foo06.dbf';
    SET NEWNAME FOR DATAFILE 11 TO '/var/oracle/data/orcl/foo07.dbf';
    SET NEWNAME FOR DATAFILE 12 TO '/var/oracle/data/orcl/foo08.dbf';

So this process generally has been working fine; datafiles present in the 3/9 backup restore to their new location. The problem is the tablespace I created on 3/12. When the recovery process gets to that point, RMAN outputs:

creating datafile file number=12 name=/opt/oracle/oradata/orcl/foo08.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/14/2014 13:24:41
RMAN-20505: create datafile during recovery
ORA-01119: error in creating database file '/opt/oracle/oradata/orcl/foo08.dbf'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory

Now, I know that I could work around this by just creating the directory at the old location, letting RMAN restore into it, and moving the datafile later. But I'd like to understand why my last rename command isn't working, or what I need to change to get it to rename when restoring the archivelog.

share|improve this question
up vote 0 down vote accepted

SET NEWNAME works for RESTORE and SWITCH, but not for RECOVER. Datafiles had been renamed and switched before the recovery started.

There is a flaw in your process though, and the above issue is its side effect:

Restore the control file from the 3/9 backup.

Why? You do a nearly complete recovery to get as close as possible to the current state, not the state on 3/9. Restore the controlfile from the latest backup taken on 3/14.

Since the restored control files aren't aware of the backup pieces for 3/10 - 3/14, I run this for each

This step is unnecessary if you use the latest controlfile.

share|improve this answer
Do you think that if I had used the latest control file, which is aware of datafile 12, that the SET NEWNAME/SWITCH commands would have succeeded for datafile 12 when it got to that part of the archivelog recover? – sbutler Mar 14 '14 at 23:20
To answer my own comment, if the datafile is present in the control file then it will be renamed even before it's actually created in by the RECOVER DATABASE (you can see RMAN creating an empty datafile when it does a restore from the full backup). – sbutler Mar 18 '14 at 2:55

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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