We were trying to restore a production database on to a test environment which we do as a usual refresh when application needs. We followed the same procedure which was followed for other databases for long time but this time it failed for this specific database.
The init file of the target database consists the following parameters.
*.db_create_file_dest='+ABD_DATA_DG01'
*.db_create_online_log_dest_1='+ABD_LOG_DG01'
Once the control file is placed in the respective location and the target database is in mount state, we did a restore which resulted in following error.
Restore command used:
run { set until time = "to_date ('22-Aug-2021 18:00', 'dd-Mon-yyyy hh24:mi')"; restore database; recover database; }
Error received:
... ... ... using channel ORA_DISK_7 using channel ORA_DISK_8 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00031 to +ABP_DATA_DG01/ABP/sr3_28/sr3.data28 channel ORA_SBT_TAPE_1: restoring datafile 00103 to +ABP_DATA_DG01/ABP/sr3_89/sr3.data89 channel ORA_SBT_TAPE_1: restoring datafile 00129 to +ABP_DATA_DG01/ABP/DATAFILE/psapsr3usr.390.1049882411 channel ORA_SBT_TAPE_1: restoring datafile 00143 to +ABP_DATA_DG01/ABP/DATAFILE/psapsr3.402.1074438749 ... ... ... channel ORA_SBT_TAPE_8: restoring datafile 00091 to +ABP_DATA_DG01/ABP/sr3_79/sr3.data79 channel ORA_SBT_TAPE_8: restoring datafile 00104 to +ABP_DATA_DG01/ABP/sr3usr_3/sr3usr.data3 channel ORA_SBT_TAPE_8: reading from backup piece ./server1234/ABP/20210820/set628874_piece1_4a070fpn_628874_1_1 channel ORA_SBT_TAPE_1: ORA-19870: error while restoring backup piece ./server1234/ABP/20210820/set628870_piece1_46070fpm_628870_1_1 ORA-19504: failed to create file "+ABP_DATA_DG01/ABP/sr3_28/sr3.data28" ORA-17502: ksfdcre:3 Failed to create file +ABP_DATA_DG01/ABP/sr3_28/sr3.data28 ORA-15001: diskgroup "ABP_DATA_DG01" does not exist or is not mounted ORA-15018: diskgroup cannot be created channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00009 to +ABP_DATA_DG01/ABP/sr3_6/sr3.data6 channel ORA_SBT_TAPE_1: restoring datafile 00037 to +ABP_DATA_DG01/ABP/sr3_34/sr3.data34 ... ... ... ORA-15018: diskgroup cannot be created failover to previous backup creating datafile file number=1 name=+ABP_DATA_DG01/ABP/system_1/system.data1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/23/2021 21:30:39 ORA-01180: can not create datafile 1 ORA-01110: data file 1: '+ABP_DATA_DG01/ABP/system_1/system.data1' RMAN>
As per Oracle support note "RMAN is Not Restoring OMF Datafiles in Their Original Location (Doc ID 882555.1)", the order in which RMAN restores OMF files is as below
- If "SET NEWNAME" is specified, RMAN will use that name for restore.
- If the original file exists, RMAN will use the original filename for restore.
- If the DB_CREATE_FILE_DEST is set, RMAN will use the disk group name specified and create another name for that file in this disk group.
- If no DB_CREATE_FILE_DEST is set and the original file does not exist, then RMAN will create another name for that file in the original disk group.
Let's analyze where the restore went wrong.
- We have not set "SET NEWNAME", so point 1 is invalid here.
- We dont have a database and overwriting the same, so point 2 is invalid
- We have set DB_CREATE_FILE_DEST, so point 4 is invalid.
Point 3 says RMAN should use the disk group name specified but if you see the error details closely, RMAN is checking for production database's disk group ABP_DATA_DG01 instead of the one provided to the parameter DB_CREATE_FILE_DEST = ABD_DATA_DG01.
To top it up, there are 2 files already restored to the proper disk group before the error is thrown out.
So what's happening here?
After further analysis on why 2 files got created properly as expected and not others, we can see that the files which are being thrown error are not OMF and custom names provided by user. In this case, the files are created with these names by the application's space management software.
From one of the backup set:
+ABP_DATA_DG01/ABP/sr3_89/sr3.data89 -- custom name
+ABP_DATA_DG01/ABP/DATAFILE/psapsr3usr.390.1049882411 -- Oracle Managed File (OMF)
+ABP_DATA_DG01/ABP/DATAFILE/psapsr3.402.1074438749 -- Oracle Managed File (OMF)
Since we have a mix of OMF and custom files, RMAN restored the OMF to proper disk group but it is looking for the original disk group for the non OMF files.
How do we resolve this?
Since we have a mix of OMF and non OMF files, we can make use of "SET NEWNAME" (point 1) for the restore to work properly.
Revised restore command:
run { set until time = "to_date ('22-Aug-2021 18:00', 'dd-Mon-yyyy hh24:mi')"; SET NEWNAME FOR DATABASE TO '+ABD_DATA_DG01'; restore database; switch datafile all; switch tempfile all; recover database; }
After using SET NEWNAME command, the restore completed without any issues.
References:
Happy restoring...!!!
Useful information brother... Wish you more success.
ReplyDelete