Fix : ORA-01274: cannot add datafile

-- Managed Recovery Process (MRP) terminates on Standby database upon adding datafile on the Primary database
-- When Standby database not in synch due to adding of new tablespace or datafile to primary side.
-- ORA-01274: cannot add datafile
-- Tested in Oracle 10gR2 & 11gR2 for both Standard and Enrerprise edition

Sample collection from alert log from standby database:

Media Recovery Waiting for thread 1 sequence 181
Wed Jan 01 22:00:05 2014
Media Recovery Log /u03/FRA/PROD/ARCHIVELOGS/1_181_834437886.dbf
Media Recovery Log /u03/FRA/PROD/ARCHIVELOGS/1_182_834437886.dbf
File #19 added to control file as 'UNNAMED00019' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Errors with log /u03/FRA/PROD/ARCHIVELOGS/1_182_834437886.dbf
MRP0: Background Media Recovery terminated with error 1274
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/prod_pr00_28730.trc:
ORA-01274: cannot add datafile '/u02/ORADATA/PROD/reports01.dbf' - file could not be created
Wed Jan 01 22:00:28 2014
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 40386024462 but controlfile could be ahead of datafiles.
..................
..................


Checks:

-- In standby

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
standby_file_management              string      MANUAL


Note: Setting STANDBY_FILE_MANAGEMENT to AUTO causes Oracle to automatically create files on the standby database and, in some cases, overwrite existing files. Care must be taken when setting STANDBY_FILE_MANAGEMENT and DB_FILE_NAME_CONVERT, so that existing standby files will not be accidentally overwritten.

Here is a scenario where you try to create a tablespace on the Primary database and the initialization parameter STANDBY_FILE_MANAGEMENT is set to MANUAL on the Physiacl Standby Database.


-- standby
1) datafile location : /u02/ORADATA/PROD
2) Database name : PROD
-- primary
1) datafile location :  /u02/ORADATA/PROD
2) Database name : PROD


-- SOLUTION

Step-1:

Step 1: Get the file# and name from the Primary database and check what is the file name that is created on the Standby Database. It would be of the name as UNNAMED at the location “$ORACLE_HOME/dbs” location on LINUX and on windows it would be created at the location “%ORACLE_HOME%\database”

sql>  select file#,name from v$datafile;

FILE# NAME
---------    --------------------------------------------------------------------------------
19           /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00019

step-2:

sql> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00019'  AS
'/u02/ORADATA/PROD/reports01.dbf';

Note:
If its an OMF or on ASM, then the syntax would be as :

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006' as NEW;

Step 3:

Set the parameter STANDBY_FILE_MANAGEMENT to AUTO in the standby database and start the MRP.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter database recover managed standby database disconnectfrom session;

Now, the archives from the Primary would be applied on the standby database.

Here you go !!