MRP0: Background Media Recovery terminated with error 1274 | ORA-01274: cannot add data file

I got ” MRP0: Background Media Recovery terminated with error 1274 after adding a Datafile ” error in Oracle dataguard ( Standby Site ).

 

MRP0: Background Media Recovery terminated with error 1274

 

Details of error are as follows.

 

File #692 added to control file as 'UNNAMED00692' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Errors with log +ARCH_MSDB/MSDBDR/ARCHIVELOG/2020_12_02/thread_4_seq_336447.1795.1058125471
PR00 (PID:94177): MRP0: Background Media Recovery terminated with error 1274
2020-12-06T09:57:00.113322+03:00
Errors in file /u01/app/oracle/diag/rdbms/MSDBdr/MSDBDR1/trace/MSDBDR1_pr00_94177.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/MSDB/DATAFILE/bigdatatbs.184399.1058113529'
2020-12-06T09:57:01.755485+03:00
Recovery interrupted!
2020-12-06T09:58:53.698469+03:00
rfs (PID:322307): Archived Log entry 4725 added for B-977575671.T-8.S-359459 ID 0x7a7af737 LAD:2

 

 

ORA-01274: cannot add data file

The ORA-01274 error is related with the new added datafile to the Production and  standby_file_management parameter is set to MANUAL in standby database.

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:

• Standby_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
• If standby_file_management is set to Auto ,but directory path of Primary and standby are different , db_file_name_convert is not set ,but db_create_file_dest has been set to wrong value on standby.

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.

 

SQL> show parameter standby_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL>

 

To solve this error, Find the unnamed file in Standby. You can find out This unnamed filename both in alertlog and with the following script.

SQL> select file#,name from v$datafile where name like '%UNNAMED%';

/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00692

 

Now create it again as follows, it will solve this error.

 

SQL> alter database create datafile '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00692' as '+DATAC1';

Database altered.

SQL>

 

And set the standby_file_management  to the auto as follows.

SQL>alter database set standby_file_management = 'auto' scope=both sid='*';

Now Start the MRP Process and start dataguard again as follows.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL>

 

 

You can read the following post to see dataguard related errors.

ORA-01111: name for data file | ORA-01110: data file | MRP0: Background Media Recovery terminated with error 1111

 

 

 

 

If you want to learn how to install Dataguard ( Standby ), read the following post.

Oracle DataGuard Physical Standby Installation Step by Step Using RMAN -1

 

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )