Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

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;
}
share|improve this answer
1  
This is the only sane advice given. I'm shocked at the quality of the other answers. This is not Stack Overflow. –  Phil Jan 1 at 3:37
1  
Calm down @Phil. Without the added information that backup indeed existed the only sane answer was that for rename file destination file has to exist. And e.g. without backup but with cold copy on ASM I would not use rman but copy files with asmcmd and do rename. –  Mindaugas Riauba Jan 1 at 9:14
    
@Mindaugas we should probably make some allowance as it was posted in the middle of the night on New Year's Eve ;) –  Jack Douglas Jan 1 at 13:35
    
This is not answer for what I ask. You can not decide or force how I will behave. This answer decides how I should restore backup. With this answer, I will not be in situation where I need answer to my question. I did not ASKED how to restore renamed files with RMAN –  Atilla Ozgur Jan 1 at 13:48

When renaming datafile you have to move it to the new place first. ALTER DATABASE RENAME FILE command does not move file. It just changes file record in control file. But destination file has to exist.

Renaming and Relocating Datafiles

share|improve this answer
up vote -2 down vote accepted

When you rename your data files, you have to satisfy following conditions normally.

  • Database in mount stage , OR Tablespace is offline
  • data file should exist

My condition satisfy first one. Database is in mount stage. But when I try to rename data files, it asks for file. I do not have correct file since I will try to restore it from backup. But oracle only asks for correct size in file (ORA-27046: file size is not a multiple of logical block size). This file do not have to be correct file. Therefore create dummy file with correct size, multiple of block size. How to achieve this.

in windows use powershell

$datafile = "M:\oracle\datafiles\XX\XX01.dbf"
$f = new-object System.IO.FileStream $datafile, Create, ReadWrite
$f.SetLength(20MB)
$f.Close()

in windows fsutil

fsutil file createnew <filename> <length>
fsutil file createnew M:\oracle\datafiles\XX\XX01.dbf  20M

in Linux

fallocate -l 20M app/oracle/datafiles/XX/XX01.dbf

After creating file, you can use normal alter database rename command.

 alter database rename file '+NEWDATA/XX/XXX.dbf' to 'M:\oracle\datafiles\XX\XXX.dbf';
share|improve this answer
1  
This makes no sense, why would you create dummy files for a tablespace that exists? ifso you can just recreate the tablespace? ... Because apparently you don't need the data in them –  Munchi Dec 31 '13 at 11:29
2  
Unnecessary and bad practice. –  Balazs Papp Dec 31 '13 at 13:33
    
tablespace does not exists. I am trying to manually clone database. –  Atilla Ozgur Jan 1 at 13:46

Your Answer

 
discard

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.