ORA-01111, ORA-01110, ORA-01157


Errors From Alert Log file:- in Standby location

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:- in Standby location

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:- in Standby location

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

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.

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

%d bloggers like this: