ORA-01111/ORA-01274 on a standby database

if you see errors like this in the alert-log of your standby database:

MRP0: Background Media Recovery terminated with error 1274
Errors in file /oradata/dbs100/admin/diag/rdbms/dbs100dg2/dbs100dg2/trace/dbs100dg2_mrp0_4371.trc:
ORA-01274: cannot add datafile '+DBS100_DATA_DG/dbs100dg1/datafile/tbs1.276.793899763' - 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 10967593 but controlfile could be ahead of datafiles.
MRP0: Background Media Recovery process shutdown (dbs100dg2)

or this:

Errors in file /oradata/dbs100/admin/diag/rdbms/dbs100dg2/dbs100dg2/trace/dbs100dg2_mrp0_4981.trc:
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/opt/oracle/product/base/11.2.0.3.1/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/opt/oracle/product/base/11.2.0.3.1/dbs/UNNAMED00006'

… don’t panic. it is easy to fix.

first of all this happens if the STANDBY_FILE_MANAGEMENT parameter is set to “MANUAL” on the standby database. but how do you fix it ? the first option would be to re-create the standby database, but this would be a lot of work and depending on the size of the database could require a huge amount of time.

the easy way is to do it like this ( on the standby database, of course ):

SQL> ALTER DATABASE CREATE DATAFILE '/opt/oracle/product/base/11.2.0.3.1/dbs/UNNAMED00006' AS '+DBS100_DATA_DG';
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter system set standby_file_management='AUTO' scope=both;
System altered.

don’t forget to set the STANDBY_FILE_MANAGEMENT to “AUTO” after you created the file so that you don’t have to worry about such things in the future…