Datafile added in Primary Database but not applied in Standby Database or MRP0: Background Media Recovery terminated with error 1111

Errors

Managed Standby Recovery starting Real Time Apply
Tue Oct 21 09:48:09 2014
Errors in file /u01/app/oracle/diag/rdbms/stlbast/STLBAST/trace/STLBAST_dbw0_27220.trc:
ORA-01186: file 59 failed verification tests
ORA-01157: cannot identify/lock data file 59 - see DBWR trace file
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
File 59 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/stlbast/STLBAST/trace/STLBAST_pr00_1637.trc:
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
ORA-01157: cannot identify/lock data file 59 - see DBWR trace file
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
Managed Standby Recovery not using Real Time Apply
Slave exiting with ORA-1111 exception
Errors in file /u01/app/oracle/diag/rdbms/stlbast/STLBAST/trace/STLBAST_pr00_1637.trc:
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
ORA-01157: cannot identify/lock data file 59 - see DBWR trace file
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Recovery Slave PR00 previously exited with exception 1111

MRP0: Background Media Recovery process shutdown (STLBAST)

Symptoms

Adding a Tablespace / Datafile in Primary Database causes the MRP in Physical Standby Database to terminate with the Error below. STANDBY_FILE_MANAGEMENT = MANUAL is set on the Standby Database.

MRP0: Background Media Recovery terminated with error 1274
ORA-01274: cannot add datafile '...dbf' - file could not be created

In Unix Environment you will get the below Message when try to restart the MRP

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

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:

• Standy_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

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

Perform all mentioned Steps on the Standby Database:

Step 1: Ensure the standby_file_management = 'MANUAL'

NOTE : For the parameter db_file_name_convert change if the Data Guard Broker is enabled then edit the Parameters using the Broker,

DGMGRL>edit database '' set property DbFileNameConvert='<>','<>';
DGMGRL>edit database '' set property StandbyFileManagement=manual;

By default StandbyFileManagement is AUTO by broker.

Step 2: Identify the File which is "unnamedn"

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\ORADATA\MYSTD\SYSTEM.DBF
D:\ORADATA\MYSTD\UNDO.DBF
D:\ORADATA\MYSTD\SYSAUX.DBF
D:\ORADATA\MYSTD\SERVICE01.DBF
C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005



Step 3: Rename/create the Datafile to the correct Filename

SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' as 'D:\oradata\mystd\sales01.dbf';

If the standby is in ASM + OMF then use the below command,

SQL> alter database create datafile '/oracle/product/GSIPRDGB/dbs/UNNAMED00210' as <'+ASMDISKGROUPNAME'> size ;

or

SQL>alter database create datafile '/oracle/product/GSIPRDGB/dbs/UNNAMED00210' as new;

Step 4: Verify the Filename is correct

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\ORADATA\MYSTD\SYSTEM.DBF
D:\ORADATA\MYSTD\UNDO.DBF
D:\ORADATA\MYSTD\SYSAUX.DBF
D:\ORADATA\MYSTD\SERVICE01.DBF
D:\ORADATA\MYSTD\SALES01.DBF


Step 5: Change the STANDBY_FILE_MANAGMENT to AUTO

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

Step 6: Start the MRP (this is using Real Time Apply)

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.


Step 7: Verify the MRP is running as expected

SQL> select process, status , sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 99
ARCH CLOSING 103
MRP0 APPLYING_LOG 104
RFS IDLE 0
RFS IDLE 0
RFS IDLE 104