Saturday, 3 December 2022

New datafiles created in primary database were not created in the Standby

 Problem description: New datafiles created in primary database were not created in the Standby.

 Symptoms: In the standby alert log:

Media Recovery Log /apps/******/****/orclepmp1_1_3211_4234582.arc
 File #17 added to control file as 'UNNAMED00017'
 because the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL 
The file should be manually created to continue. 
Errors with log /apps/apps/******/****/orclepmp1_1_3211_4234582.arc 
MRP0: Background Media Recovery terminated with error 1274
 Errors in file /apps/*****/trace/orcl_pr00_101232.trc:
 ORA-01274: cannot add datafile '/apps/****/****/oradata/testing01.dbf' - file could not be created Recovery interrupted!


Workaround: On standby database:

sqlplus "/as sysdba"
select file#,name from v$datafile where name like '%UNNAMED%';
ALTER DATABASE CREATE DATAFILE 'unnamed datafile' AS 'proper filename';

An example would be: 

alter database create datafile '/apps/***/***/**/**/11.2.0/dbs/UNNAMED00020' as '/apps/***/***/***/testing01.dbf';
alter system set standby_file_management = AUTO;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
exit;

Note:

To avoid this problem, add following  parameter into standby configuration:

                sqlplus "/as sysdba"
alter system set standby_file_management = AUTO scope=both;
exit;