Recently, I wanted to restore my Oracle database on a different server in a different ASM diskgroup.\u00a0 Sounds simple but I was tripped up on the syntax.<\/p>\n
Before I started the restore, I did the following:<\/p>\n
1. Started RMAN & connected to the target.<\/p>\n
2. Started the database nomount using the pfile.<\/p>\n
3. Restored the control file.<\/p>\n
4. Mounted the database.<\/p>\n
Depending on your situation, you may or may not need to perform the steps above.\u00a0 I wanted the database restore to write my files to a another ASM diskgroup that was named ‘NEW’.\u00a0 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.<\/p>\n
SET NEWNAME FOR DATAFILE < file # > TO\u00a0 < new ASM diskgroup >;<\/p>\n
For the destination, you only need to select the name of the diskgroup.\u00a0 You do not have to tell the ASM the full path.\u00a0 The ASM will handle it from that point.\u00a0 I had a problem because I attempted to put the full path of the files including directories & sub directories like with flat files.<\/p>\n
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;<\/p>\n
You will have to add the statement “SWITCH DATAFILE ALL” after the restore statement & before the recovery statement.<\/p>\n
5. Now I am ready to run my restore & recovery statements.\u00a0 Please note if you are using a tape system for the restore, you may have to allocate channels too.\u00a0 Below I am using the example database that comes with the Oracle installation.<\/p>\n
RUN{\r\n SET NEWNAME FOR DATAFILE 1 TO '+NEW';\r\n SET NEWNAME FOR DATAFILE 2 TO '+NEW';\r\n SET NEWNAME FOR DATAFILE 3 TO '+NEW';\r\n SET NEWNAME FOR DATAFILE 4 TO '+NEW';\r\n SET NEWNAME FOR DATAFILE 5 TO '+NEW';\r\n RESTORE DATABASE;\r\n SWITCH DATAFILE ALL;\r\n RECOVER DATABASE;\r\n }<\/pre>\n![\"RestoreNewASM01\"](\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2014\/08\/RestoreNewASM01.jpg\")
<\/a><\/p>\nNote: I could not get all of the restore text in one screen shot, so I broke it up into two screen shots.<\/p>\n
![\"RestoreNewASM02\"](\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2014\/08\/RestoreNewASM02.jpg\")
<\/a><\/p>\nWhen the restore & recovery was finished, I ran the ‘alter database open resetlogs’ statement.<\/p>\n
<\/p>\n
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.<\/p>\n