At a time when a lot was depending on a restore (ain’t it always??) I looked at my trusty documentation and said with full confidence I was able to duplicate a database from a backup.
Production application was stopped, the database was mine. I won’t delve into the reason why the prod application had to be stopped, but let’s say it was necessary. This is not something to do lightly since a lot is deepening on this application/database.
It was bad enough the backup took as long as it did anyway.
But: we had a backup, on disk, from the FRA. Copy to the other server, create pfile, spfile, little bit of no-mount etc..
Then the last step: duplicate the database to a new db, and …. BLAM!
Errors in memory script RMAN-03015: error occurred in stored script Memory Script RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted ORA-06512: at "SYS.X$DBMS_RCVMAN", line 13658 ORA-06512: at line 1 RMAN-03015: error occurred in stored script Memory Script RMAN-06136: ORACLE error from auxiliary database: ORA-01180: can not create datafile 1 ORA-01110: data file 1: '+DATA/SID/datafile/system.259.804442145' ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 7074 ORA-06512: at line 1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 06/30/2015 09:06:34 RMAN-05501: aborting duplication of target database
Stress levels to the max, everybody screaming , panicking, running in the hallways, alarms blaring…
What happened? RMAN bailed out..I spend quite some time working on the wrong angle since the error was ( for me at the time) misleading.
The error translates to: hey, I want to write the datafile named ‘xxxx/yyyy/zzzz’ but I can’t.
The reason why eluded me, but datafile 1 is the system datafile. Which gave me a clue. The location it was complaining about, is the location of the file from the source database.
This was the most eluding part, since I was thinking the control file of the source was used and not the RMAN altered one.
But no. The reason why the file could not be written was: the SOURCE datafile could not be found! RMAN was telling me in a very obfuscated way he couldn’t find the source file!
How come? Easy. When creating the backup from the source database, the backupset was spread over two directories, each with the date: one with the date 27th of June, one with the 28th of June. Both directories where copied over, however the controlfile was located in the dir with data of the 28th and the big ass backup file containing the datafiles in the dir of the 27th.
How was RMAN supposed to know this? It ain’t no virus searching the whole server for files, it will only look where told to look. And that was in the directory of the 28th.
I still need to do some experimenting with “catalog start with …” But since people where screaming and panicking, a quick fix was: copy the files of the directory of the 27th into the directory of the 28th, and start the duplicate process again, after restarting the instance with the pfile, spfile etc..
Lo and behold: the duplicate restarted and the process didn’t bail out.
Better yet: it completed to the end, resulting in a duplicated database.
So whenever you encounter this error, remember to check if RMAN can find the backup files! And the quick fix is to copy/move all the files next to the backup piece containing the controlfile.
As a side note, this is the comment from Oracle support, regarding this issue:
“During any rman restore whether explicit or via duplicate, rman will first try to restore a file from backup.
If a backup is not found, rman creates the file because it is possible to recover a file by creating a new empty file and applying all redo since file creation – but we cannot do this for files from system tablespace.
So ORA-01180: can not create datafile 1, is symptomatic of not having found the file in backup.”