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
Thank you somuch for uploading , greatly helps me to resolve my issue.