Cannot Add Oracle Managed File on Standby Database

Read our docs Datafiles are not added in the standby even when using OMF, this can happen when the parameters for OMF are not set correctly

Problem Details

Problem Statement

Applicable Error Code

201407160910 - ORA-01276: Cannot add file
F:\ORADATA\PROD\DATAFILE\O1_MF_WINSURE__9WD8SG6M_.DBF 
File has an Oracle Managed Files file name.
Dbvisit Standby terminated. 
Return code = 433

Affected Versions

Affects Standby Version 6.0 and 7.0

Affected Platforms

Should be platform independent.

Problem Description

Adding datafile on Standby database fails with the following error:

Dbvisit Standby: 6.0.54.11597 Process id: 1624)

201407160910 - ORA-01276: Cannot add file F:\ORADATA\PROD\DATAFILE\O1_MF_WINSURE__9WD8SG6M_.DBF File has an Oracle Managed Files file name.

Dbvisit Standby terminated. 
Return code = 433

In this particular case, parameter db_create_file_dest was not set correctly.

Solution

Manually add datafile on Standby Database:

SQL> alter database create datafile 'D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00017' as  `F:\oradata\PRODSTDBY\DATAFILE\O1_MF_WINSURE__9WD8SG6M_.DBF';

Once the file is added, run dbvisit as normal to send and apply the logs.

Also make sure that database parameter db_create_file_dest is set correctly, this can be done using:

SQL> alter system set db_create_file_dest=F:\oradata\PRODSTDBY\DATAFILE scope spfile;

If you do have the database parameter db_create_file_dest set in your standby database, you can run the following command:

SQL> alter database create datafile 19 as NEW;

One more thing you can look on is using Dbvisit Synchronise database functionality which utilises RMAN incremental refresh behind the scenes to bring your standby database back up to date with the primary. You can find out more information about this here:

https://dbvisit.atlassian.net/wiki/display/UGDS7/Synchronize+a+Standby+Database

Ankit Arora July 25, 2014 15:35

We're here to help!

Comments