PROBLEM:

After adding a datafile in primary database, recovery process in standby stopped with below error.

— Primary database:

SQL> alter tablespace prim add datafile size 1g;

Tablespace altered.

— Error in alert log of standby database

File #5 added to control file as ‘UNNAMED00005’ because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Fri Feb 10 12:19:57 2017
Errors in file /oracle/app/oracle/diag/rdbms/noncdb1/STDBY/trace/STDBY_pr00_24003.trc:
ORA-01274: cannot add data file that was originally created as ‘/archive/NONPLUG/NONCDB/NONCDB/datafile/o1_mf_prim_d9v1fq7k_.dbf’
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 13111955
Fri Feb 10 12:19:57 2017
Errors in file /oracle/app/oracle/diag/rdbms/noncdb1/STDBY/trace/STDBY_pr00_24003.trc:
ORA-01274: cannot add data file that was originally created as ‘/archive/NONPLUG/NONCDB/NONCDB/datafile/o1_mf_prim_d9v1fq7k_.dbf’
Fri Feb 10 12:19:57 2017
Checker run found 1 new persistent data failures
Fri Feb 10 12:19:57 2017
MRP0: Background Media Recovery process shutdown (STDBY)

 

SOLUTION:

This issue happens, when the standby_file_management parameter is set to MANUAL in standby database. So when a datafile is added in primary database, standby database is unable to process it. To fix it follow the below process

1. Check the standby_file_management parameter in the standby database.

SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL

2. Cancel the recovery:

SQL> recover managed standby database disconnect from session;
Media recovery complete.

3. Check the file, which caused the issue:[ STANDBY]

SQL> select file#, error, name from v$datafile_header where ERROR='FILE MISSING';

     FILE# ERROR
---------- -----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
         5 FILE MISSING

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

NAME
--------------------------------------------------------------------------------
/oracle/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00005

3. Recreate the datafile as below [ STANDBY ]

SQL> alter database create datafile '/oracle/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00005' as new;

database altered.

If you dont have OMF files, then get the exact datafile name from primary and recreate.

-- Primary 
SQL> select name from v$datafile where file#=5;
NAME
--------------------------------------------------------------------------------
/archive/NONPLUG/NONCDB/NONCDB/datafile/o1_mf_prim_d9v1fq7k_.dbf


-- Recreate datafile in standby

SQL> alter database create datafile '/oracle/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00005' 
as '/archive/NONPLUG/NONCDB/NONCDB/datafile/o1_mf_prim_d9v1fq7k_.dbf';

4. Set standby_file_management to AUTO, to avoid similar issue in future

alter system set standby_file_management=AUTO scope=both;

5. Start recovery in standby database

alter database recover managed standby database disconnect from session;