ORA-1274 Encountered on Physical Standby After Adding Datafile to Primary

ORA-1274 Encountered on Physical Standby After Adding Datafile to Primary [ID 388659.1]
		Modified 01-FEB-2009 Type HOWTO Status PUBLISHED

In this Document
Goal
Solution
References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.5.0 to 10.2.0.1
Information in this document applies to any platform.

Goal

To explain how to recover from ORA-1274 encountered on physical standby database after adding a datafile to the primary database.

Solution

Oracle recommends running your physical standby database with the following parameters set:

STANDBY_FILE_MANAGEMENT=AUTO

and

DB_FILE_NAME_CONVERT ... set to an appropriate string value that will help to name datafiles automatically added on the standby database when the create datafile statement is run there.

If these parameters are not set, or accidentally become unset, and a datafile is added to the primary database, it is possible that you will encounter ORA-1274 : cannot add datafile '%s' - file could not be created

The datafile will not be generated on the standby file system but an entry will be added to your standby controlfile that refers to this datafile as an 'UNNAMEDxxx' file.

For example:

File #31 added to control file as 'UNNAMED00031'.

In our example case, standby_file_management was set to AUTO but db_file_name_convert was not set. Therefore while performing managed recovery on the standby database we encounter a create datafile statement and we attempt to create this datafile using the same path as was specified on the primary. Since we are unable to create this file in the designated path, we place an entry into the controlfile indicating that this datafile *should* be here, but is not.

*****
To correct this problem we need to create a blank datafile on the file system that we can recover using the archives generated since this datafile was added on the primary database. We also need to update the controlfile to rename this file from 'UNNAMEDxxx' to its appropriate path/filename.

The Step By Step Procedure for Correcting this problem is as follows:
===============================================

1. alter system set standby_file_management=manual scope=memory;
2. alter database create datafile '' as '/oradata/standby/data/data1' ;
3. alter system set standby_file_management=auto scope=both;
4. alter database recover managed standby database disconnect from session;
5. be sure to set your db_file_name_convert to the appropriate string pair as documented in Oracle Reference guide to avoid encountering this problem on your physical standby database in the future.

Please note, this will only work if ALL of the archives generated since this datafile was created are still available.

References

NOTE:29430.1 - How to Recover a Database Having Added a Datafile Since Last Backup

---

Related Products Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition Keywords CONTROL FILE; CONTROLFILE; STANDBY DATABASE; DB_FILE_NAME_CONVERT; STANDBY_FILE_MANAGEMENT; PHYSICAL STANDBY Errors ORA-1274

Back to top

Rate this document

Article Rating Rate this document Excellent Good Poor Did this document help you? Yes No Just browsing How easy was it to find this document? Very easy Somewhat easy Not easy		Comments Provide some feedback
Cancel