DBA Arena

Symptoms:

Media Recovery Stopped on standby database after adding datafile on primary.
 

Error:

MRP0: Background Media Recovery terminated with error 1111
 
Alert Logfile:


Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/oracle/diag/rdbms/erpdr/ERPPROD/trace/ERPPROD_pr00_44120.trc:
ORA-01111: name for data file 58 is unknown - rename to correct file
ORA-01110: data file 58: '/u01/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00058'
ORA-01157: cannot identify/lock data file 58 - see DBWR trace file
ORA-01111: name for data file 58 is unknown - rename to correct file
ORA-01110: data file 58: '/u01/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00058'
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (ERPPROD)

 

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:

1. Standby_file_management is set to MANUAL.
2. 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.
3. 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.
 

On Standby Database:


SQL> select * from v$recover_file where error like '%FILE%';


SQL> select name from v$datafile where name like '%UNNAME%';

NAME
--------------------------------------------------------------------------------
/u01/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00058

 
On Primary Database:

SQL> select file#,name from v$datafile where file#=58;

FILE# NAME
---------- ---------------------------------------------------------
58 +DATA/erpprod/datafile/apex_1460658040692991.550.1012735763

 
Make sure the standby_file_management parameter is set to manual.


SQL> show parameter standby_file_management

NAME TYPE VALUE
------------------------------------ --------------------------------- ----------
standby_file_management string MANUAL

 
If you change the parameter to AUTO, it will not allow create the datafile.

SQL> alter database create datafile '/u01/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00058' as '/oradata/ERPPROD/datafile/apex_1460658040692991.550.1012735763';
alter database create datafile '/u01/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00058' as '/oradata/ERPPROD/datafile/apex_1460658040692991.550.1012735763'
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management
is automatic.



SQL> alter database create datafile '/u01/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00058' as '/oradata/ERPPROD/datafile/apex_1460658040692991.550.1012735763';

System altered.

 
If the physical standby database is RAC, then please make the change to all standby instances.
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*';

 
Start the MRP process

SQL> alter database recover managed standby database disconnect from session;

12 rows selected.