on 10-29-2013 10:55 AM
Hi All,
I try to restore a backup of my PRD System on a sandbox. The backup I took has a time stamp of 20131016. The restore of this backup was successful.
I also generate a trace file in PRD with the command "alter database backup controlfile to trace;" that I renamed to "create_controlfile.sql".
As I tried to create the controlfiles by running the script "create_controlfile.sql", I received the following errors:
CREATE CONTROLFILE SET DATABASE "SID" RESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'/oracle/SID/sapdata1/el731d_6/el731d.data6'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Indeed, after this date (20131016) the database was changed, we extended a tablespace with 2 datafiles.
During the restore of this old backup, the two datafiles were not recreated.
Is there any solution to this issue?
Many thanks in advance for your help.
Regards
Hi,
When was the control file create after extending the tablespace or at the time of backup.
Can you also past the control file script and the sid of source and target.
Thanks
Rishi Abrol
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
do a cross check to see which files you have specified in the create script and which files excist
ls -l /oracle/CH7/sapdata1/system_1/system.data1
ls -l /oracle/CH7/sapdata4/btabi_4/btabi.data4
ls -l /oracle/CH7/sapdata4/btabd_4/btabd.data4
ls -l /oracle/CH7/sapdata1/system_1/sysaux01.dbf
ls -l /oracle/CH7/sapdata1/temp_1/temp.data1
ls -l /oracle/CH7/sapdata2/temp_2/temp.data2
ls -l /oracle/CH7/sapdata3/temp_3/temp.data3
ls -l /oracle/CH7/sapdata1/stabi_1/stabi.data1
ls -l /oracle/CH7/sapdata2/stabi_2/stabi.data2
ls -l /oracle/CH7/sapdata3/stabi_3/stabi.data3
ls -l /oracle/CH7/sapdata1/stabd_1/stabd.data1
ls -l /oracle/CH7/sapdata2/stabd_2/stabd.data2
ls -l /oracle/CH7/sapdata3/stabd_3/stabd.data3
ls -l /oracle/CH7/sapdata1/btabi_1/btabi.data1
ls -l /oracle/CH7/sapdata2/btabi_2/btabi.data2
ls -l /oracle/CH7/sapdata3/btabi_3/btabi.data3
ls -l /oracle/CH7/sapdata1/btabd_1/btabd.data1
ls -l /oracle/CH7/sapdata2/btabd_2/btabd.data2
ls -l /oracle/CH7/sapdata3/btabd_3/btabd.data3
ls -l /oracle/CH7/sapdata1/pooli_1/pooli.data1
ls -l /oracle/CH7/sapdata2/pooli_2/pooli.data2
ls -l /oracle/CH7/sapdata3/pooli_3/pooli.data3
ls -l /oracle/CH7/sapdata1/poold_1/poold.data1
ls -l /oracle/CH7/sapdata2/poold_2/poold.data2
ls -l /oracle/CH7/sapdata3/poold_3/poold.data3
ls -l /oracle/CH7/sapdata1/clui_1/clui.data1
ls -l /oracle/CH7/sapdata2/clui_2/clui.data2
ls -l /oracle/CH7/sapdata3/clui_3/clui.data3
ls -l /oracle/CH7/sapdata1/clud_1/clud.data1
ls -l /oracle/CH7/sapdata2/clud_2/clud.data2
ls -l /oracle/CH7/sapdata3/clud_3/clud.data3
ls -l /oracle/CH7/sapdata1/ddici_1/ddici.data1
ls -l /oracle/CH7/sapdata2/ddici_2/ddici.data2
ls -l /oracle/CH7/sapdata3/ddici_3/ddici.data3
ls -l /oracle/CH7/sapdata1/ddicd_1/ddicd.data1
ls -l /oracle/CH7/sapdata2/ddicd_2/ddicd.data2
ls -l /oracle/CH7/sapdata3/ddicd_3/ddicd.data3
ls -l /oracle/CH7/sapdata1/docui_1/docui.data1
ls -l /oracle/CH7/sapdata2/docui_2/docui.data2
ls -l /oracle/CH7/sapdata3/docui_3/docui.data3
ls -l /oracle/CH7/sapdata1/docud_1/docud.data1
ls -l /oracle/CH7/sapdata2/docud_2/docud.data2
ls -l /oracle/CH7/sapdata3/docud_3/docud.data3
ls -l /oracle/CH7/sapdata4/btabd_5/btabd.data5
ls -l /oracle/CH7/sapdata4/btabi_6/btabi.data6
ls -l /oracle/CH7/sapdata4/btabd_6/btabd.data6
ls -l /oracle/CH7/sapdata4/btabi_7/btabi.data7
ls -l /oracle/CH7/sapdata4/btabd_7/btabd.data7
ls -l /oracle/CH7/sapdata4/btabi_8/btabi.data8
ls -l /oracle/CH7/sapdata1/loadi_1/loadi.data1
ls -l /oracle/CH7/sapdata2/loadi_2/loadi.data2
ls -l /oracle/CH7/sapdata3/loadi_3/loadi.data3
ls -l /oracle/CH7/sapdata1/loadd_1/loadd.data1
ls -l /oracle/CH7/sapdata2/loadd_2/loadd.data2
ls -l /oracle/CH7/sapdata3/loadd_3/loadd.data3
ls -l /oracle/CH7/sapdata1/proti_1/proti.data1
ls -l /oracle/CH7/sapdata2/proti_2/proti.data2
ls -l /oracle/CH7/sapdata3/proti_3/proti.data3
ls -l /oracle/CH7/sapdata1/protd_1/protd.data1
ls -l /oracle/CH7/sapdata2/protd_2/protd.data2
ls -l /oracle/CH7/sapdata3/protd_3/protd.data3
ls -l /oracle/CH7/sapdata4/btabi_9/btabi.data9
ls -l /oracle/CH7/sapdata4/btabd_8/btabd.data8
ls -l /oracle/CH7/sapdata4/el701d_1/el701d.data1
ls -l /oracle/CH7/sapdata4/el701i_1/el701i.data1
ls -l /oracle/CH7/sapdata4/es701d_1/es701d.data1
ls -l /oracle/CH7/sapdata4/es701d_2/es701d.data2
ls -l /oracle/CH7/sapdata1/sourcei_1/sourcei.data1
ls -l /oracle/CH7/sapdata2/sourcei_2/sourcei.data2
ls -l /oracle/CH7/sapdata3/sourcei_3/sourcei.data3
ls -l /oracle/CH7/sapdata1/sourced_1/sourced.data1
ls -l /oracle/CH7/sapdata2/sourced_2/sourced.data2
ls -l /oracle/CH7/sapdata3/sourced_3/sourced.data3
ls -l /oracle/CH7/sapdata1/user1d_1/user1d.data1
ls -l /oracle/CH7/sapdata2/user1d_2/user1d.data2
ls -l /oracle/CH7/sapdata3/user1d_3/user1d.data3
ls -l /oracle/CH7/sapdata1/user1i_1/user1i.data1
ls -l /oracle/CH7/sapdata2/user1i_2/user1i.data2
ls -l /oracle/CH7/sapdata3/user1i_3/user1i.data3
ls -l /oracle/CH7/sapdata1/undo_1/undo.data1
ls -l /oracle/CH7/sapdata4/btabi_5/btabi.data5
ls -l /oracle/CH7/sapdata1/system_2/system.data2
ls -l /oracle/CH7/sapdata4/btabd_10/btabd.data10
ls -l /oracle/CH7/sapdata4/btabi_11/btabi.data11
ls -l /oracle/CH7/sapdata4/btabd_11/btabd.data11
ls -l /oracle/CH7/sapdata4/btabi_12/btabi.data12
ls -l /oracle/CH7/sapdata4/es701i_1/es701i.data1
ls -l /oracle/CH7/sapdata4/btabd_9/btabd.data9
ls -l /oracle/CH7/sapdata4/btabi_10/btabi.data10
ls -l /oracle/CH7/sapdata4/btabd_12/btabd.data12
ls -l /oracle/CH7/sapdata4/btabd_13/btabd.data13
ls -l /oracle/CH7/sapdata4/btabi_13/btabi.data13
ls -l /oracle/CH7/sapdata4/btabi_14/btabi.data14
ls -l /oracle/CH7/sapdata4/btabd_14/btabd.data14
ls -l /oracle/CH7/sapdata1/system_3/system.data3
ls -l /oracle/CH7/sapdata1/el731d_1/el731d.data1
ls -l /oracle/CH7/sapdata1/el731i_1/el731i.data1
ls -l /oracle/CH7/sapdata1/el731d_2/el731d.data2
ls -l /oracle/CH7/sapdata1/el731d_3/el731d.data3
ls -l /oracle/CH7/sapdata1/el731d_4/el731d.data4
ls -l /oracle/CH7/sapdata1/el731d_5/el731d.data5
ls -l /oracle/CH7/sapdata1/el731i_2/el731i.data2
ls -l /oracle/CH7/sapdata1/es731d_1/es731d.data1
ls -l /oracle/CH7/sapdata1/es731i_1/es731i.data1
ls -l /oracle/CH7/sapdata1/es731d_2/es731d.data2
ls -l /oracle/CH7/sapdata1/es731d_3/es731d.data3
ls -l /oracle/CH7/sapdata1/es731d_4/es731d.data4
ls -l /oracle/CH7/sapdata1/es731d_5/es731d.data5
ls -l /oracle/CH7/sapdata1/es731d_6/es731d.data6
ls -l /oracle/CH7/sapdata1/es731d_7/es731d.data7
ls -l /oracle/CH7/sapdata1/es731d_8/es731d.data8
ls -l /oracle/CH7/sapdata1/es731d_9/es731d.data9
ls -l /oracle/CH7/sapdata1/es731d_10/es731d.data10
ls -l /oracle/CH7/sapdata1/es731d_11/es731d.data11
ls -l /oracle/CH7/sapdata1/es731d_12/es731d.data12
ls -l /oracle/CH7/sapdata1/es731d_13/es731d.data13
ls -l /oracle/CH7/sapdata1/es731d_14/es731d.data14
ls -l /oracle/CH7/sapdata1/es731d_15/es731d.data15
ls -l /oracle/CH7/sapdata1/es731d_16/es731d.data16
ls -l /oracle/CH7/sapdata1/es731d_17/es731d.data17
ls -l /oracle/CH7/sapdata1/es731i_2/es731i.data2
ls -l /oracle/CH7/sapdata1/es731i_3/es731i.data3
ls -l /oracle/CH7/sapdata1/es731i_4/es731i.data4
ls -l /oracle/CH7/sapdata1/es731i_5/es731i.data5
ls -l /oracle/CH7/sapdata1/es731i_6/es731i.data6
ls -l /oracle/CH7/sapdata1/es731i_7/es731i.data7
ls -l /oracle/CH7/sapdata1/es731i_8/es731i.data8
ls -l /oracle/CH7/sapdata1/es731i_9/es731i.data9
ls -l /oracle/CH7/sapdata1/es731i_10/es731i.data10
ls -l /oracle/CH7/sapdata1/es731i_11/es731i.data11
ls -l /oracle/CH7/sapdata1/es731i_12/es731i.data12
ls -l /oracle/CH7/sapdata1/es731i_13/es731i.data13
ls -l /oracle/CH7/sapdata1/es731i_14/es731i.data14
ls -l /oracle/CH7/sapdata1/el731d_6/el731d.data6
ls -l /oracle/CH7/sapdata1/el731d_7/el731d.data7
ls -l /oracle/CH7/sapdata1/el731d_8/el731d.data8
you can run the above from a script and veryfy the out put with the contents of the create script.
or you can open the create script and go to line 159 and check which datafile it is complaining about, also make sure you ended the command correctly not with ', and only with a '
best regards
marius
Hi Abdellatif,
the bane of non-RMAN backup / restore procedures
Remove the corresponding two data files from the control file script (and create the control file), create the sub folders for them manually and start the recovery. Oracle database recovery will re-construct the DDL statements as well, if you want to go that far by recovery.
For more details please check section "Recovering Through an Added Data File with a Backup Control File" in the official documentation.
Regards
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.