Oracle DB: 12.2
OS Version: RHEL6
When starting MRP process on my standby database it Is
failing with datafile error
SQL> alter database recover managed standby
database using current logfile disconnect;
Database altered.
Something NOT right, when I start MRP this is what I
see in alert log
Errors in file /opt/oracle/diag/rdbms/mydb_cb1/mydb/trace/MYDB_mrp0_38254.trc:
ORA-01111:
name for data file 23 is unknown - rename to correct file
ORA-01110:
data file 23: '/opt/oracle/product/12.1.0.2.64/dbs/UNNAMED00023'
ORA-01157:
cannot identify/lock data file 23 - see DBWR trace file
ORA-01111:
name for data file 23 is unknown - rename to correct file
ORA-01110:
data file 23: '/opt/oracle/product/12.1.0.2.64/dbs/UNNAMED00023'
Fri Apr 14 11:19:35 2017
MRP0: Background Media Recovery process shutdown (mydb)
SQL> select file#,name from v$datafile where
file#=23;
FILE# NAME
----------
-------------------------------------------------------
23
/opt/oracle/product/12.1.0.2.64/dbs/UNNAMED00023
On source primary host:
FILE# NAME
---------- -------------------------------------------------------
23 /opt/oracle/u02/oradata/mydb/devdata02.dbf
Reason:
SQL> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
standby_file_management string MANUAL
from the alert log below I can see that on APR 13 when
datafile added to prod and it failed on standby since “STANDBY_FILE_MANAGEMENT”
is MANUAL and MRP shut down IMMEDIATELY
Alert log:
Thu Apr 13 16:47:14 2017
Media Recovery Log /opt/oracle/archive/mydb/TS0001_0000933735_MYDB0833139554.ARC
File #23 added
to control file as 'UNNAMED00023' because
the parameter
STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Errors with log /opt/oracle/archive/MYDB/TS0001_0000933735_MYDB0833139554.ARC
MRP0: Background Media Recovery terminated with error
1274
Thu Apr 13 16:47:14 2017
Errors in file /opt/oracle/diag/rdbms/MYDB_cb1/mydb/trace/mydb_pr00_45355.trc:
ORA-01274: cannot add data file that was originally
created as '/opt/oracle/u02/oradata/MYDB/devdata02.dbf'
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change
327857714852
Thu Apr 13 16:47:14 2017
Block change tracking service stopping.
Stopping background process CTWR
Thu Apr 13 16:47:14 2017
Checker run found 1 new persistent data failures
Thu Apr 13 16:47:15 2017
Errors in file /opt/oracle/diag/rdbms/MYDB_cb1/MYDB/trace/mydb_pr00_45355.trc:
ORA-01274:
cannot add data file that was originally created as '/opt/oracle/u02/oradata/MYDB/devdata02.dbf'
Thu Apr 13 16:47:16 2017
MRP0: Background Media Recovery process shutdown (MYDB)
SOLUTION:
SQL> alter database create datafile
'/opt/oracle/product/12.1.0.2.64/dbs/UNNAMED00023' as '/opt/oracle/u02/oradata/MYDB/devdata02.dbf';
Database altered.
SQL> select file#, name from v$datafile where
file#=23;
FILE# NAME
----------
------------------------------------------------------------------
23
/opt/oracle/u02/oradata/MYDB/devdata02.dbf
Start MRP now and it started applying logs………….
SQL> alter database recover managed standby
database using current logfile disconnect;
Database altered.
Refer:
Background Media Recovery terminated with ORA-1274
after adding a Datafile (Doc ID 739618.1)
