How to resolve ORA-01111, ORA-01110, ORA-01157

There are many reasons for a file being created as UNNAMED or MISSING in the standby database, including insufficient disk space on standby site (or) Improper parameter settings related to file management.

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.

For example if we add a data file on the Primary when parameter STANDBY_FILE_MANAGEMENT on standby set to MANUAL , While recovery process(MRP) is trying to apply archives, Due to that parameter setting it will create an Unnamed file in $ORACLE_HOME/dbs and it will cause to kill MRP process and Errors will be as below.

Errors From Alert Log file:-

Errors in file /oracle/app/oracle/admin/CC/bdump/cc_mrp0_22294.trc:
ORA-01111: name for data file 536 is unknown - rename to correct file
ORA-01110: data file 536: '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
ORA-01157: cannot identify/lock data file 536 - see DBWR trace file
ORA-01111: name for data file 536 is unknown - rename to correct file
ORA-01110: data file 536: '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'

Trace File:-

MRP0: Background Media Recovery terminated with error 1111
ORA-01111: name for data file 536 is unknown - rename to correct file
ORA-01110: data file 536: '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
ORA-01157: cannot identify/lock data file 536 - see DBWR trace file
ORA-01111: name for data file 536 is unknown - rename to correct file
ORA-01110: data file 536: '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
ORA-01111: name for data file 536 is unknown - rename to correct file
ORA-01110: data file 536: '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
ORA-01157: cannot identify/lock data file 536 - see DBWR trace file
ORA-01111: name for data file 536 is unknown - rename to correct file
ORA-01110: data file 536: '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'
ARCH: Connecting to console port...

Troubleshooting:-

Check for the files needs to be recovered.

SQL> select * from v$recover_file where error like '%FILE%';

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME
---------- ------- ------- -------------------- ---------- --------------------
       536 ONLINE  ONLINE  FILE MISSING                  0

SQL>

Identify on primary of data file 536(Primary Database)

SQL>  select file#,name from v$datafile where file#=536;

     FILE# NAME
---------- ----------------------------------------------------------------------
       536 +DATA/c/datafile/undotbs9.595.750444337

SQL>

Identify dummy file name created in (Standby)

SQL> select file#,name from v$datafile where file#=536;

     FILE# NAME
---------- ----------------------------------------------------------------------
       536 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536

SQL>

Crosscheck that no MRP is running and STANDBY_FILE_MANAGEMENT can be enabled once after creating file on standby

SQL> alter database create datafile '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536' as '+DATA3/cc/datafile/undotbs9_595_750444337';
Database altered.
SQL>

If You are managing File system ASM with OMF, you are not allowed to create data file, Because it will file system names and format maintained by ASM.  If you try to Create datafile as above with ASM File, You will pass with below error.

SQL> alter database create datafile '/u01/oracle/orahome/dbs/UNNAMED00613' as '+DATA3/cc/datafile/undotbs9_595_750444337'
 *
 ERROR at line 1:
 ORA-01276: Cannot add file
 +DATA3/cc/datafile/undotbs9_595_750444337. File has an Oracle Managed Files file name.

Then Run above command as shown below

SQL> alter database create datafile '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536' as new;
Database altered.
SQL>

Enable STANDBY_FILE_MANAGEMENT to AUTO & Start MRP.

SQL> show parameter standby_file_management
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL

SQL> alter system set standby_file_management=AUTO scope=both;
System altered.
SQL> 
SQL> show parameter standby_file_management
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>

After creating the file, MRP will start applying archives on standby database.

Note:-

Setting STANDBY_FILE_MANAGEMENT to AUTO causes Oracle to automatically create files on the standby database and, in some cases, overwrite existing files. Care must be taken when setting STANDBY_FILE_MANAGEMENT and DB_FILE_NAME_CONVERT so that existing standby files will not be accidentally overwritten.

 

Website Comments

  1. Joel
    Reply

    Wow. What a clear and helpful explanation. I was freaking out when I saw this error, totally new experience to me! Now everything is clicking again. Thanks so much for taking the time to post this info, you rock!

  2. jhaibk
    Reply

    Good Info. I did encounter this issue 2 years back. In addition I would like to add more info:
    Scenario 1: When STANDBY_FILE_MANAGEMENT is set to AUTO,.
    one must take care of the architecture of the primary and secondary OS. Either the file systems for datafiles must be same or if different then DB_FILE_NAME_CONVERT must be used. Else, on addition of a file at primary end, a corresponding file will be created in secondary at the $ORACLE_HOME/dbs with name MISSING_XXXX. In this scenario, this file need to be renamed to a new file as soon as possible.
    Scenario 2: When STANDBY_FILE_MANAGEMENT is set to MANUAL.
    This is generally the case when architecture of the 2 OS servers hosting DBs are not similar. In this case, on addition a file to primary database. DBA must create same file on secondary. This is already covered by Nassyam.

  3. Mujeeb
    Reply

    Thank you Nassyam for sharing , Today i Encounter same error and with the help of above info i resolved issue….;)

  4. Suresh
    Reply

    Thank you I thought I almost resolved the issue But

    Yes, Following condition is true for us

    “If You are managing File system ASM with OMF, you are not allowed to create data file, Because it will file system names and format maintained by ASM. If you try to Create datafile as above with ASM File, You will pass with below error.”

    How and what do we do from this step on wards?

  5. Luis Silva
    Reply

    When using ASM with OMF you must to create

    alter database create datafile ‘/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536’ as ‘+DGXX’;

    Because both example show errors…

  6. TJ
    Reply

    Bacon saved. Thank you for the post! Now to switch standby_file_management=auto again.

Post a comment

*