UNNAMED datafile issue in standby database: ORA-01111, ORA-01110, ORA-01157

We have following two reasons for getting UNNAMED files created on standby database:

1. STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database. Setting this parameter to MANUAL causes MRP process to termainate creating UNNAMED datafile under $ORACLE_HOME/dbs location.
OR
2. We have insufficient space available at mount point /diskgroup of standby database server.


Alert log will show following errors:

MRP0: Background Media Recovery terminated with error 1111
Mon Jun 14 07:52:10 2014
Errors in file /u04/app/oracle/admin/testdr/TEST_DR_dr_srv1/bdump/testdr1_mrp0_28595.trc:
ORA-01111: name for data file 18 is unknown - rename to correct file
ORA-01110: data file 18: '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018'
ORA-01157: cannot identify/lock data file 18 - see DBWR trace file
ORA-01111: name for data file 18 is unknown - rename to correct file
ORA-01110: data file 18: '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018'
Mon Jun 14 07:52:10 2014
Errors in file /u04/app/oracle/admin/fodsdr/FODS_DR_rdr-db1/bdump/testdr1_mrp0_28595.trc:
ORA-01111: name for data file 18 is unknown - rename to correct file
ORA-01110: data file 18: '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018'
ORA-01157: cannot identify/lock data file 18 - see DBWR trace file
ORA-01111: name for data file 18 is unknown - rename to correct file
ORA-01110: data file 18: '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018'
Mon Jun 14 07:52:10 2014
MRP0: Background Media Recovery process shutdown (TESTDR1)

How to resolve:


On standby database run following command to find missing files:

SQL> select FILE#||','||ERROR||','||STATUS||','||RECOVER||','||FUZZY||','||NAME from v$datafile_header where ERROR='FILE MISSING';
FILE#||','||ERROR||','||STATUS||','||RECOVER||','||FUZZY||','||NAME
--------------------------------------------------------------------------------
18,FILE MISSING,ONLINE,,,

SQL> select file#,name from v$datafile where file_id=18;
FILE#  NAME
-----  ---------------------------------------------------------
18     /u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018

On primary database check for datafile details:

SQL> select FILE_NAME||','||TABLESPACE_NAME||','||FILE_ID||','||STATUS||','||bytes from dba_data_files where file_id = 18;
FILE_NAME||','||TABLESPACE_NAME||','||FILE_ID||','||STATUS||','||BYTES
--------------------------------------------------------------------------------
+DATA1/test/datafile/test.639.756552633,TEST,18,AVAILABLE,32212254720

Change STANDBY_FILE_MANAGEMENT to MANUAL on standby database:

SQL> alter system set standby_file_management=MANUAL sid='*';
System altered.

Now manually create missing datafile on standby database:

SQL> alter database create datafile '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018' as '+DATA1' size 30G;

Now once again change STANDBY_FILE_MANAGEMENT to AUTO on standby database:

SQL> alter system set standby_file_management=AUTO sid=’*’;
System altered.


Now start managed recovery on standby database:

SQL> recover managed standby database disconnect from session;

After this u will see MRP process starts applying archivelog on standby database.


Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

 

 

Advertisements

24 thoughts on “UNNAMED datafile issue in standby database: ORA-01111, ORA-01110, ORA-01157

  1. I do believe all the ideas you’ve introduced in your post.
    They’re very convincin and will deinitely work. Still, the posts
    are vedy short for novices. Could you please lengthen them a bit fromm subsequent time?
    Thank you for the post.

  2. We absolutely love your blog and find the majority of your post’s to be just what I’m looking for.
    Does one offer guest writers to write content
    in your case? I wouldn’t mind writing a post or elaborating on a number of the subjects you write regarding here.
    Again, awesome weblog!

  3. Thank you for the good writeup. It in fact was a
    amusement account it. Look advanced to more added agreeable from you!
    By the way, how could we communicate?

  4. “Greetings! Very helpful advice within this post! It is the little changes which will make the greatest changes. Thanks for sharing!”

  5. I have been absent for a while, but now I remember why I used to love this website. Thank you, I’ll try and check back more often. How frequently you update your web site?

  6. excellent post, very informative. I’m wondering why the other experts
    of this sector don’t realize this. You must proceed your writing.
    I’m confident, you’ve a great readers’ base already!

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