Recently, I wanted to restore my Oracle database on a different server in a different ASM diskgroup. Sounds simple but I was tripped up on the syntax.
Before I started the restore, I did the following:
1. Started RMAN & connected to the target.
2. Started the database nomount using the pfile.
3. Restored the control file.
4. Mounted the database.
Depending on your situation, you may or may not need to perform the steps above. I wanted the database restore to write my files to a another ASM diskgroup that was named ‘NEW’. For each file number in the database, a line has to let the restore where that file number is to moving to in the new ASM diskgroup.
SET NEWNAME FOR DATAFILE < file # > TO < new ASM diskgroup >;
For the destination, you only need to select the name of the diskgroup. You do not have to tell the ASM the full path. The ASM will handle it from that point. I had a problem because I attempted to put the full path of the files including directories & sub directories like with flat files.
If you do not know all the datafile numbers, once the controlfile is restored, you can open a sqlplus session to find out the datafile information with a query like: SELECT FILE#, NAME FROM V$DATAFILE;
You will have to add the statement “SWITCH DATAFILE ALL” after the restore statement & before the recovery statement.
5. Now I am ready to run my restore & recovery statements. Please note if you are using a tape system for the restore, you may have to allocate channels too. Below I am using the example database that comes with the Oracle installation.
RUN{
SET NEWNAME FOR DATAFILE 1 TO '+NEW';
SET NEWNAME FOR DATAFILE 2 TO '+NEW';
SET NEWNAME FOR DATAFILE 3 TO '+NEW';
SET NEWNAME FOR DATAFILE 4 TO '+NEW';
SET NEWNAME FOR DATAFILE 5 TO '+NEW';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
Note: I could not get all of the restore text in one screen shot, so I broke it up into two screen shots.
When the restore & recovery was finished, I ran the ‘alter database open resetlogs’ statement.
Added Note: Below is a screen shot of the of a recent restore moving not only the data files, but including the temp file & redo logs to the ASM.
With the temp file, it was moved like a data file except I had to use the word TEMPFILE. Then after the restore statement, I had to include the “SWITCH TEMPFILE ALL;”. For the moving of the redo logs, it was a SQL statement executed with RMAN.
Hello,
I did the same, ie. restoring the database to a different server and a diff. disk group.
After doing “set newname for database” instead of individually setting “set new name for datafile” for each datafile, I could restore the database. But when it came to recovery, I faced the following error :
******
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/08/2016 02:38:34
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until time ‘AUG 05 2016 17:30:00’ using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: ‘+<old_dg_path/datafile/system.275.903631491'
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: '+old_dg_path/datafile/system.275.903631491'
******
I see that the v$datafile view still shows the old diskgroup path, while the datafiles got restored to the new disk group location.
Do I need to do a "alter database rename file" and do a recovery ? If this is the case, I'll have tough time mapping the files from the source and target locations. Because, we've tablespaces which contain more than one datafiles. For eg. the system tablespace has 12 datafiles with the names totally different in source and target. How do I map them to rename, if I need to rename ?
Thanks
I attempted what you tried by just setting the new name, and had the same problem when I mounted the database. I found that had to set each individual file, which was a pain when I had over one hundred files in my database. If you find a different way, please let me know & would be happy to post your results. Thanks for reading!
I have been reading a number of oracle support document in prepration of a restore on identical server.
If you use oracle managed files, you want to configure you spfile prior to restore and recovery with db_file_name_convert and log_file_name_convert like so:
DB_FILE_NAME_CONVERT = ‘+DATA01’, ‘+UATDATA01’
LOG_FILE_NAME_CONVERT = ‘+DATA01’, ‘+UATDATA01’, ‘+FRA’, ‘+UATFRA’
If you restore control files from backups, you want to set it the same way:
If below control files are in use at Source Database :
CONTROL_FILES = ‘+DATA/prod/controlfile/current.116.5329108’, ‘+FRA/prod/controlfile/current.116.5329108’
Then, Auxiliary should have below parameter value before restoring control file / DUPLICATE:
CONTROL_FILES = ‘+DATA’, ‘+FRA’
If Diskgroup names are different at auxiliary, still the same pattern applies, e.g. :
CONTROL_FILES = ‘+UATDATA’, ‘+UATFRA’
Excellent Information, Thanks for the Update!!!