Saturday, May 20, 2017

ORA-01111: name for data file xx is unknown - rename to correct file


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)



Auto Scroll Stop Scroll