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.
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';

SQL> select file#,name from v$datafile where file_id=18;
-----  ---------------------------------------------------------
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;

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 🙂






Categories: ORA errors

Tagged as: , , ,

53 replies

  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!

  7. I really can’t believe how great this site is. Keep up the good work. I’m going to tell all my friends about this place.

  8. I think the admin of this website is in fact working hard in support of his web page, for the reason that here every information is quality based stuff.

  9. Hello! Someone in my Myspace group shared this website with us so I came to check it
    out. I’m definitely loving the information. I’m book-marking
    and will be tweeting this to my followers!
    Fantastic blog and great style and design.

  10. Thank you for some other wonderful post. The place else could anyone get that
    kind of information in such an ideal method of writing?
    I have a presentation subsequent week, and I’m on the search for such info.

  11. Thanks for the marvelous posting! I definitely enjoyed
    reading it, you might be a great author. I will make certain to bookmark your blog and
    definitely will come back in the foreseeable future.
    I want to encourage yourself to continue your great writing, have a nice holiday weekend!

  12. Hi there! I know this is kinda off topic however , I’d figured I’d ask.
    Would you be interested in exchanging links or maybe guest
    writing a blog post or vice-versa? My blog addresses
    a lot of the same topics as yours and I believe we could greatly benefit from each
    other. If you might be interested feel free to send me an email.
    I look forward to hearing from you! Excellent blog by the way!

  13. Valuable information. Lucky me I found your web site by
    accident, and I am shocked why this accident
    did not happened earlier! I bookmarked it.

  14. hi!,I really like your writing very much! share we
    communicate extra approximately your post on AOL? I need an expert on this space to unravel my problem.
    May be that’s you! Looking forward to look you.

  15. Hello my loved one! I wish to say that this post is awesome, great written and include almost all significant infos.

    I would like to see more posts like this .

  16. You need to take part in a contest for one of the highest quality
    websites on the web. I’m going to highly recommend this web site!

  17. After looking into a few of the blog articles on your blog, I honestly like your technique of blogging.
    I saved it to my bookmark site list and will be checking back
    soon. Take a look at my website as well and let me know your opinion.

  18. Thanks for sharing your thoughts. I really appreciate your efforts and
    I will be waiting for your further post thank you once again.

  19. Simply want to say your article is as amazing. The
    clearness in your put up is simply great and that i
    could think you are a professional in this subject.
    Fine with your permission let me to grab your RSS feed to stay updated with coming
    near near post. Thanks a million and please continue the
    gratifying work.

  20. I’m not that much of a internet reader to be honest but your
    sites really nice, keep it up! I’ll go ahead and bookmark your site
    to come back in the future. Cheers

  21. I blog often and I seriously appreciate your content. Your article has truly peaked my interest.
    I’m going to book mark your blog and keep checking for
    new details about once a week. I opted in for your RSS feed too.

  22. Hi! Someone in my Facebook group shared this site with us so I came to look it over. I’m definitely enjoying the information. I’m bookmarking and will be tweeting this to my followers! Exceptional blog and great design and style.

  23. Hello there, I found your web site by way of Google at the same time as looking for a comparable topic,
    your site came up, it appears to be like
    good. I’ve bookmarked it in my google bookmarks.
    Hello there, just become alert to your blog thru Google, and
    found that it is truly informative. I am gonna be careful
    for brussels. I’ll appreciate should you continue this
    in future. Lots of people shall be benefited from your writing.

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 )

Connecting to %s