UNNAMED FILE ERROR IN STANDBY AFTER ADDING NEW FILE TO PRIMARY DATABASE – ORA-01111, ORA-01110, ORA-

Case Study

Today I got a new problem in one of our banking client. After verifying the error, alert.log & Trace file I got the actual problem that on production database one datafile is created which is not created on desired location. Now we have to solve the error because bank need to synchronize database on urgent basis.

Note: If your STANDBY_FILE_MANAGEMENT parameter is not configured Auto in Data Guard environment and file is created then file will created on standby server with unnamed file name in dbs directory.

This is what happened in this case. Now I am going to show you how to solve the problem.

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 Received at the time of Recovery:-

SQL> RECOVER STANDBY DATABASE;

ORA-00283: recovery session canceled due to errors

ORA-01111: name for data file 33 is unknown – rename to correct file

ORA-01110: data file 33: ‘E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00033’

ORA-01157: cannot identify/lock data file 33 – see DBWR trace file

ORA-01111: name for data file 33 is unknown – rename to correct file

ORA-01110: data file 33: ‘E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00033’

Trace File:-

MRP0: Background Media Recovery terminated with error 1111

ORA-01111: name for data file 33 is unknown – rename to correct file

ORA-01110: data file 33: ‘E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00033’

ORA-01157: cannot identify/lock data file 33 – see DBWR trace file

ORA-01111: name for data file 33 is unknown – rename to correct file

ORA-01110: data file 33: ‘E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00033’

ORA-01111: name for data file 33 is unknown – rename to correct file

ORA-01110: data file 33: ‘E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00033’

ORA-01157: cannot identify/lock data file 33 – see DBWR trace file

ORA-01111: name for data file 33 is unknown – rename to correct file

ORA-01110: data file 33: ‘E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00033’

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

—— ——- ——- ——————– ———- ——————–

33 ONLINE  ONLINE  FILE MISSING                  0

SQL>

Identify on primary of data file 33(Primary Database)

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

FILE# NAME

—— ———————————————————————-

33 M:\UNDO\UNDOTBS02.DBF

SQL>

Identify dummy file name created in (Standby)

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

FILE# NAME

—— ———————————————————————-

33 E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00033

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 ‘E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00033’ as ‘M:\UNDO\UNDOTBS02.DBF;

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 ‘ E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE \UNNAMED00033’ as ‘+DATA3\UNDO\UNDOTBS02.DBF

*

ERROR at line 1:

ORA-01276: Cannot add file

+DATA3\UNDO\UNDOTBS02. File has an Oracle Managed Files file name.

Then Run above command as shown below

SQL> alter database create datafile ‘E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00033’ 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> show parameter standby_file_management

NAME TYPE VALUE

———————————— ———– ————————–

standby_file_management string AUTO

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.