ORA-01503: CREATE CONTROLFILE failed, unable to obtain file status

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Hi Abrol,

The create_controfile was generated after extending the tablespace.

Source: CH3, Target CH7.

Please find the attached script.

Thanks and regards

create_controlfile.txt.zip

[former_member206552 · Accepted solution]

Hi

if you are trying to restore a backup before the 2 datafiles where added then i would remove those 2 files from the create script and run again,

is this a online (up and till where do you want to roll forward) or offline backup you are restoring,

Best Regards

Marius

[Former Member · Accepted solution]

Hi Marius,

This is an online backup.

Removing the 2 lines in the script didn't help, Error:

ERROR at line 159:

ORA-02236: invalid file name

thanks and regards

[former_member206552 · Accepted solution]

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

[Former Member · Accepted solution]

Hi,

Thanks for the file,

As suggested remove the table space file that were added after.

Current error is due to this data file.

'/oracle/CH7/sapdata1/es731i_3/es731i.data3',

Please check if the data file is there.

Thanks

Rishi Abrol

[stefan_koehler]

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