ORA-01110,ORA-27037 when creating Physical Standby from Cold backup

Recently,while creating a physical standby database after restoring the cold backup(9.2.0.8 version) and starting the MRP process i faced the below error-

Errors in file /oracle/app/admin/TEST/bdump/TESTdr_dbw0_1681.trc:
ORA-01157: cannot identify/lock data file 85 - see DBWR trace file
ORA-01110: data file 85: '/s02/oradata/TEST/TESTDATA36.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
MRP0: Background Media Recovery terminated with error 1110
Wed Oct 25 20:54:09 2010
Errors in file /oracle/app/admin/TEST/bdump/TESTdr_mrp0_1728.trc:
ORA-01110: data file 85: '/s02/oradata/TEST/TESTDATA36.dbf'
ORA-01157: cannot identify/lock data file 85 - see DBWR trace file
ORA-01110: data file 85: '/s02/oradata/TEST/TESTDATA36.dbf'
MRP0: Background Media Recovery process shutdown
Wed Oct 25 20:54:10 2010
Completed: alter database recover managed standby database disconnect from session

With the little experience that i have in oracle , i had faced some similar (error provided below) error where the standby_file_management was not set to AUTO(by default its MANUAL)

ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: '/oracle/app/oracle/product/9.2.0/db/dbs/UNNAMED00011'

This time it was different.The parameter standby_file_management was already set to AUTO and moreover i didn’t get something like “UNNAMED00011”.

Doing a little investigation, things got clear.A datafile was added to the tablespace in the database after the completion of the cold backup.Hence, the latest standby controlfile backup that i took from the primary had the new datafile name.The view v$datafile showed the full proper name “/s02/oradata/TEST/TESTDATA36.dbf” as it reads from the controlfile and the status showed “MISSING”.

To fix the problem :

SQL> alter system set standby_file_management=manual;

System altered.

SQL> alter database create datafile 85 as '/s02/oradata/TEST/TESTDATA36.dbf' size 2046M ; --> size same as primary db.

Database altered.

SQL> alter system set standby_file_management=auto;

System altered.

Then re-start the MRP

sql>alter database recover managed standby database disconnect from session

Things went well 🙂

5 thoughts on “ORA-01110,ORA-27037 when creating Physical Standby from Cold backup

  1. Thanks for sharing this, however,can you please tell as to whether the directory structure of your production and your standby database was same or different??

    1. Hi Ajinkya,

      The directory structure was same.My standby controlfile had the information of the newly created datafile but the database’s data dictionary had no idea about it.As the datafile didn’t exists physically on the OS level hence “ORA-27037: unable to obtain file status,SVR4 Error: 2: No such file or directory”.

      Regards,
      Anand

  2. We had the same error couldn’t resolve the issue we had a consultant from
    Zibase look at the issue and he finally determined that there was filesystem
    corruption once our sysadm resolved the issue it worked OK!

    1. Hi Jeff,

      Thanks for visiting the blog and also for your valuable input.You have provided one more area to look into in case of error. Thanks for it.

      In my case the datafile was not existing physically as it was created after the morning cold backup.

      Regards,
      Anand

  3. In fact I had to rename the datafiles in my data guard environment for the same error in physical standby database after it was re-synced with Primary PROD database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s