ORA-01274: cannot add datafile 'XYZ' - file could not be created

When receiving this error, this is more than likely due to db_file_name_convert not being configured correctly in the standby database.  DB_FILE_NAME_CONVERT is used to convert a filename of a new datafile on the primary database to a filename on the standby database.

Here is how I fixed the issue on standby:

1. Set standby_file_management to MANUAL on the standby database.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

2. Create the datafile manually in the standby database.

ALTER DATABASE CREATE DATAFILE '/opt/oracle/app/db/11.2.0.1/dbs/UNNAMED00057' as '/opt/data/poc2adg/datafile/deal_data.257.767990655'; 

3. Set standby_file_management to AUTO on the standby database.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

4. Configure DB_FILE_NAME_CONVERT with the correct settings for datafile conversion.

ALTER SYSTEM SET DB_FILE_NAME_CONVERT='primary_db_datafile_location','standby_db_datafile_location' SCOPE=SPFILE;


5. Bounce standby and re-enable auto recovery on the standby database.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Comments

Popular posts from this blog

RMAN-10038: database session for channel prm3 terminated unexpectedly

ORA-17630: Mismatch in the remote file protocol version client 2 server 3

ORA-00338: log {n} of thread {n} is more recent than control file