Oracle – Rename Datafile When File Does Not Exist

oracleoracle-10g-r2

I am trying to clone our production database in test virtual machine. Our production database works with ASM. I would like to use plain file system in test database.

I created database, services, pfile etc. I gave production database control files to test database. Right now I am trying to rename datafiles in test database. I get following error.

 SQL> startup mount;
  .....
 SQL> alter database rename file '+NEWDATA/XX/XX01.dbf' to      'M:\oracle\datafiles\XX\XX01.dbf'; alter database rename file
 '+NEWDATA/XX/XX01.dbf' to 'M:\oracle\datafiles\XX\XX01.dbf';
* ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01141: error renaming data file 162 - new file
'M:\oracle\datafiles\XX\XX01.dbf' not found ORA-01110: data file 162:
'+NEWDATA/XX/XX01.dbf' ORA-27046: file size is not a multiple of
logical block size OSD-04012: file size mismatch O/S-Error: (OS 4) The
system cannot open the file.

My backup is normal rman backup pieces not backup as copy. I am trying to rename nonexistent files. After this rename process , I will try to restore database from rman backup. But this backup is not piece by piece copy.

Best Answer

You wrote:

After this rename process , I will try to restore database from rman backup. But this backup is not piece by piece copy.

Then this is not the method for doing this. In order to use the ALTER DATABASE RENAME FILE, the file have to already exist at the new location. Instead of this, you can (and should) handle this in RMAN:

sqlplus / as sysdba
startup mount
exit

Which you have already done, then:

rman target /

And run the following: 

run
{
  set newname for datafile '+NEWDATA/XX/XX01.dbf' to 'M:\oracle\datafiles\XX\XX01.dbf';
  set newname for datafile '+NEWDATA/XX/XX02.dbf' to 'M:\oracle\datafiles\XX\XX02.dbf';
  set newname for datafile '+NEWDATA/XX/XX03.dbf' to 'M:\oracle\datafiles\XX\XX03.dbf';
  # ..., list all datafiles this way
  # alternatively you can use the datafile number: set newname for datafile 1 to '.../...dbf';

  restore database;
  switch datafile all;
  recover database;
}
Related Question