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