Issue:
We found standby log was not applying in standby database and the difference count was keep increasing. Media recover process also not starting up.
Cause:
Upon receiving such alert that log apply is not happening in standby, we were verifying the standby alert log and noticed below error.
File #723 added to control file as ‘UNNAMED00723’ because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Errors with log /mnt/PROD/prod/proddata/archive/1_210462_931968203.dbf
MRP0: Background Media Recovery terminated with error 1274
Then we understood that in primary node, a datafile was added around that time and it caused MRP process in standby server halted reason being STANDBY_FILE_MANAGEMENT is set to MANUAL.
Fix:
Execute below in Standby:
SQL> alter database create datafile ‘/mnt/PROD/prod/proddb/12102/dbs/UNNAMED00723’ as ‘/oracle/prod/proddata/nologging02.dbf’;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
The above command ensures standby server datafiles are intact. To avoid these issues in future, we need to ensure STANDBY_FILE_MANAGEMENT is set to AUTO. This parameter will take care of creating datafile as per primary node.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
