MRP0: Background Media Recovery terminated with error 1274

Yesterday one of our standby database recovery stopped with below error :-

Errors in file /ora_backup/TEST/duplicate_test/AMIT/diag/diag/rdbms/standby/STANDBY/trace/STANDBY_pr00_12842.trc:
ORA-01119: error in creating database file '/ora_backup/TEST1/restore_test/AMIT/DATA/AMIT_03.dbf'
Additional information: 1
File #13 added to control file as 'UNNAMED00013'.
Originally created as:
'/ora_backup/TEST1/restore_test/AMIT/DATA/AMIT_03.dbf'
Recovery was unable to create the file as:
'/ora_backup/TEST1/restore_test/AMIT/DATA/AMIT_03.dbf'
MRP0: Background Media Recovery terminated with error 1274
Errors in file /ora_backup/TEST/duplicate_test/AMIT/diag/diag/rdbms/standby/STANDBY/trace/STANDBY_pr00_12842.trc:
ORA-01274: cannot add datafile '/ora_backup/TEST1/restore_test/AMIT/DATA/AMIT_03.dbf' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 5662710 but controlfile could be ahead of datafiles.

Problem :-

It seems in primary a datafile has been added and it's not able to replicate the same in standby due to above errors.

Cause :-

Above error can cause due to below reasons :-

1. STANDBY_FILE_MANAGEMNET parameter is set to maual
2. If having Different file structure, db_file_name_convert is not properly set according to standby directory structure or db_create_file_dest parameter is not set in standby

Solution :-

In these kind of scenario when due to above parameters are missing in standby,
when we add a datafile in primary it failed to replicate in standby. We can see below mentioned error in alert log and recovery will stop due to this.

ORA-01274: cannot add datafile '/ora_backup/TEST1/restore_test/AMIT/DATA/AMIT_03.dbf' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).

Datafile detail will be present in standby controlfile but with below name :-


/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00013

So to resume recovery in these kind of scenarios we have to maualy create this datafile and start the recovery process. PFB steps :-

1. Check standby_file_management is set to manual

to add a datafile maualy in STANDBY database above parameter has to be in manual state

SQL> alter system set standby_file_management=manual;

System altered.

from DB_BROKER also we can change this :-

DGMGRL> edit database 'STANDBY' set property standbyfilemanagement=manual;

Property "standbyfilemanagement" updated

2. Create the database file with correct name in Standby database

SQL> alter database create datafile '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00013' as '/ora_backup/TEST/duplicate_test/AMIT/DATA/STANDBY/datafile/amit03.dbf';

Database altered.

3. Check the datafile details in controlfile :-

/ora_backup/TEST/duplicate_test/AMIT/DATA/STANDBY/datafile/amit03.dbf

now its coming appropriate

4. Start the Mrp process to resume recovery.

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

Database altered.

Check the alert logfile to verify everything is fine. You can also check from Dataguard regarding sync details :-

DGMGRL> show database "STANDBY"

Database - STANDBY

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds (computed 2 seconds ago)

  Apply Lag:       0 seconds (computed 2 seconds ago)

  Apply Rate:      0 Byte/s

  Real Time Query: OFF

  Instance(s):

    STANDBY

Database Status:

SUCCESS

To get rid from these kind of issues in future , I did following changes in this database:-

1. change STANDBY_FILE_MANAGEMENt to AUTO

2. Set the db_file_name_convert parameter accoding to standby database.

I hope this article helped you.

Regards,
Amit Rath