Today lt’s have a tour towards the steps which we are going to solve the issue regards “Background Media Recovery terminated with ORA-1274 after adding a Datafile

Symptoms in alert.log file:

Wed Nov 02 15:35:48 2017

ALTER DATABASE RECOVER  managed standby database disconnect using current logfile

Attempt to start background Managed Standby Recovery process (STDB5_DG)

Wed Nov 02 15:35:48 2017

MRP0 started with pid=43, OS id=27960

MRP0: Background Managed Standby Recovery process started (STDB5_DG)

tarted logmerger process

Wed Nov 02 15:35:53 2017

Managed Standby Recovery starting Real Time Apply

MRP0: Background Media Recovery terminated with error 1111

Errors in file /opt/oracle/diag/rdbms/STDB5_DG/STDB5_DG/trace/STDB5_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/oraclehelp01/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/oraclehelp01/UNNAMED00029'

Managed Standby Recovery not using Real Time Apply

Slave exiting with ORA-1111 exception

Errors in file /opt/oracle/diag/rdbms/STDB5_DG/STDB5_DG/trace/STDB5_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/oraclehelp01/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/oraclehelp01/UNNAMED00029'

Recovery Slave PR00 previously exited with exception 1111

MRP0: Background Media Recovery process shutdown (STDB5_DG)

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

Trace file:

root@stagedb4 rdbms]# cat /opt/oracle/diag/rdbms/STDB5_DG/STDB5_DG/trace/STDB5_DG_pr00_28005.trc

Trace file /opt/oracle/diag/rdbms/STDB5_DG/STDB5_DG/trace/STDB5_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: STDB5_DG

Redo thread mounted by this instance: 1

Oracle process number: 44

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



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

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

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

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

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

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




Started Parallel Media Recovery

*** 2017-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/oraclehelp01/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/oraclehelp01/UNNAMED00029'

*** 2017-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/oraclehelp01/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/oraclehelp01/UNNAMED00029'

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

Managed Standby Recovery not using Real Time Apply




*** 2017-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/oraclehelp01/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/oraclehelp01/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 

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/oraclehelp01/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_201701.288.921498875

— In Standby

SQL> show parameter file_name_convert

NAME                   TYPE                              VALUE
------------------------------------ --------------------------------- -----------
db_file_name_convert   string     /db3_dg/, /db4_dg/, /stdb3_dg/, /STDB5_DG/
log_file_name_convert  string     /stdb3_dg/, /STDB5_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/oraclehelp01/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

Thank you for your valuable time.
Thought of the day !
“Today is a wonderful day to have a wonderful day!”

Thank you for giving your valuable time to read the above information.

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Tagged:

About The Author

Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.