Background Media Recovery terminated with ORA-1274 after adding a Datafile

Symptoms:

alart.log file:

Wed Nov 02 15:35:48 2016

ALTER DATABASE RECOVER managed standby database disconnect using current logfile

Attempt to start background Managed Standby Recovery process (STDB4_DG)

Wed Nov 02 15:35:48 2016

MRP0 started with pid=43, OS id=27960

MRP0: Background Managed Standby Recovery process started (STDB4_DG)

started logmerger process

Wed Nov 02 15:35:53 2016

Managed Standby Recovery starting Real Time Apply

MRP0: Background Media Recovery terminated with error 1111

Errors in file /opt/oracle/diag/rdbms/stdb4_dg/STDB4_DG/trace/STDB4_DG_pr00_28005.trc:

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

ORA-01110: data file 29: '/opt/oracle/product/11.2.0.3/dbs/UNNAMED00029'

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

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

ORA-01110: data file 29: '/opt/oracle/product/11.2.0.3/dbs/UNNAMED00029'

Managed Standby Recovery not using Real Time Apply

Slave exiting with ORA-1111 exception

Errors in file /opt/oracle/diag/rdbms/stdb4_dg/STDB4_DG/trace/STDB4_DG_pr00_28005.trc:

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

ORA-01110: data file 29: '/opt/oracle/product/11.2.0.3/dbs/UNNAMED00029'

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

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

ORA-01110: data file 29: '/opt/oracle/product/11.2.0.3/dbs/UNNAMED00029'

Recovery Slave PR00 previously exited with exception 1111

MRP0: Background Media Recovery process shutdown (STDB4_DG)

Completed: ALTER DATABASE RECOVER managed standby database disconnect using current logfile

Trace file:

root@stagedb4 rdbms]# cat /opt/oracle/diag/rdbms/stdb4_dg/STDB4_DG/trace/STDB4_DG_pr00_28005.trc

Trace file /opt/oracle/diag/rdbms/stdb4_dg/STDB4_DG/trace/STDB4_DG_pr00_28005.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /opt/oracle/product/11.2.0.3

System name: Linux

Node name: stagedb4.ipx.com

Release: 2.6.18-406.el5

Version: #1 SMP Fri May 1 10:37:57 EDT 2015

Machine: x86_64

Instance name: STDB4_DG

Redo thread mounted by this instance: 1

Oracle process number: 44

Unix process pid: 28005, image: oracle@stagedb4.ipx.com (PR00)

*** 2016-11-02 15:35:53.727

*** SESSION ID:(3022.7173) 2016-11-02 15:35:53.727

*** CLIENT ID:() 2016-11-02 15:35:53.727

*** SERVICE NAME:() 2016-11-02 15:35:53.727

*** MODULE NAME:() 2016-11-02 15:35:53.727

*** ACTION NAME:() 2016-11-02 15:35:53.727

Started Parallel Media Recovery

*** 2016-11-02 15:35:53.746 4320 krsh.c

Managed Standby Recovery starting Real Time Apply

DDE rules only execution for: ORA 1110

----- START Event Driven Actions Dump ----

---- END Event Driven Actions Dump ----

----- START DDE Actions Dump -----

Executing SYNC actions

----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----

Successfully dispatched

----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----

Executing ASYNC actions

----- END DDE Actions Dump (total 0 csec) -----

DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)

ORA-01110: data file 29: '/opt/oracle/product/11.2.0.3/dbs/UNNAMED00029'

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

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

ORA-01110: data file 29: '/opt/oracle/product/11.2.0.3/dbs/UNNAMED00029'

*** 2016-11-02 15:35:53.856 4320 krsh.c

MRP0: Background Media Recovery terminated with error 1111

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

ORA-01110: data file 29: '/opt/oracle/product/11.2.0.3/dbs/UNNAMED00029'

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

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

ORA-01110: data file 29: '/opt/oracle/product/11.2.0.3/dbs/UNNAMED00029'

*** 2016-11-02 15:35:53.856 4320 krsh.c

Managed Standby Recovery not using Real Time Apply

*** 2016-11-02 15:35:53.858

Completed Media Recovery

Managed Recovery: Not Active posted.

Slave exiting with ORA-1111 exception

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

ORA-01110: data file 29: '/opt/oracle/product/11.2.0.3/dbs/UNNAMED00029'

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

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

ORA-01110: data file 29: '/opt/oracle/product/11.2.0.3/dbs/UNNAMED00029'

Cause:

This Error occurs if we add a Datafile OR Tablespace in PRIMARY Database and that could not be translated to the Standby Database due to these Reasons:

  • Standby_file_management is set to MANUAL

  • Primary & Physical Standby are having different file structures and DB_FILE_NAME_CONVERT is not set according to the Directory Structures in Primary and Standby

  • Insufficient Space or wrong Permissions on the Standby Database to create the Datafile

  • If standby_file_management is set to Auto ,but directory path of Primary and standby are different , db_file_name_convert is not set ,but db_create_file_dest has been set to wrong value on standby.

The Redo Log generated from Primary will have Information about the Tablespace / Datafile added however it could not be created successfully in Physical Standby Database due to the standby_file_management = MANUAL

or is not able to find the specified Folder due to a missing / incorrect Filename Conversion.

The File Entry is added to Standby Controlfile as "UNNAMED0000n" in /dbs or /database

folder depends on the Operating System and eventually the MRP terminates.

Alert Log in Standby Shows MRP is terminated with below error

=================================================================

File #5 added to control file as 'UNNAMED00005' because

the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL

The file should be manually created to continue.

MRP0: Background Media Recovery terminated with error 1274

Thu Sep 25 19:02:35 2008

Errors in file c:\oracle\product\10.2.0\admin\mystd\bdump\mystd_mrp0_3436.trc:

ORA-01274: cannot add datafile 'D:\ORADATA\PRIM\SALES01.DBF' - file could not be created

By default it is AUTO by broker.

Solution:

For version < 12c

-- In Standby

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

FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME

---------- --------------------- --------------------- ------------------------------------------------

29 ONLINE ONLINE FILE MISSING 0

SQL> select name from v$datafile;

NAME

----------------------------------------------------------------------------------

.

.

+DATA/db4_dg/datafile/ipx_large_data_2.295.882184319

+DATA/db4_dg/datafile/webapp_data.286.882189151

+INDX/db4_dg/datafile/webapp_index.267.882189995

/opt/oracle/product/11.2.0.3/dbs/UNNAMED00029

+INDX/db4_dg/datafile/ipx_large_index_tmp.268.882190631

+DATA/db4_dg/datafile/ipx_large_data_tmp.274.882192831

+INDX/db4_dg/datafile/ipx_large_index_3.264.882188755

+DATA/db4_dg/datafile/ipx_large_data_201503.301.882186179

.

.

54 rows selected.

-- In Primary

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

FILE# NAME

----------------------------------------------------------------------------------

29 +DATA/stdb3_dg/datafile/ipx_large_data_201601.288.921498875

-- In Standby

SQL> show parameter file_name_convert

NAME TYPE VALUE

------------------------------------ --------------------------------- -----------

db_file_name_convert string /db3_dg/, /db4_dg/, /stdb3_dg/, /stdb4_dg/

log_file_name_convert string /stdb3_dg/, /stdb4_dg/

SQL> show parameter standby_file_management

NAME TYPE VALUE

------------------------------------ --------------------------------- ----------

standby_file_management string MANUAL

SQL> alter database create datafile '/opt/oracle/product/11.2.0.3/dbs/UNNAMED00029' as '+DATA' SIZE 2048M;

Database altered.

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

no rows selected

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

Database altered.

SQL> select process, status , sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#

--------------------------- ------------------------------------ ----------

ARCH OPENING 326

ARCH CONNECTED 0

ARCH CLOSING 326

ARCH CLOSING 330

ARCH CLOSING 6031

ARCH CLOSING 329

ARCH CLOSING 331

ARCH CLOSING 6030

ARCH OPENING 326

ARCH OPENING 326

RFS IDLE 0

RFS IDLE 6033

RFS IDLE 0

13 rows selected.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select process,sequence#,status from v$managed_standby;

PROCESS SEQUENCE# STATUS

--------------------------- ---------- ------------------------------------

ARCH 326 OPENING

ARCH 0 CONNECTED

ARCH 326 CLOSING

ARCH 330 CLOSING

ARCH 6031 CLOSING

ARCH 329 CLOSING

ARCH 331 CLOSING

ARCH 6030 CLOSING

ARCH 326 OPENING

ARCH 326 OPENING

RFS 0 IDLE

RFS 6033 IDLE

RFS 0 IDLE

MRP0 356 APPLYING_LOG

References:

Oracle Doc ID 739618.1