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

Comments