Oracle Data Guard – Fixing Wrong Path for New Data Files

datafiledataguardoraclestandby

I've created a Standby Database with the following duplicate command:

run {
duplicate target database for standby from active database
spfile
parameter_value_convert 'ORCL','ORCLSTBY'
set db_name='ORCL'
set db_unique_name='ORCLSTBY'
set db_create_file_dest='/u01/app/oracle/oradata/ORCLSTBY'
set db_recovery_file_dest='/u01/app/oracle/oradata/ORCLSTBY'
set db_file_name_convert='/ORCL/','/ORCLSTBY/'
set log_file_name_convert='/ORCL/','/ORCLSTBY/'
set standby_file_management='AUTO'
;
}

During the duplicate command the existing datafile location are correctly overwritten from '/u01/app/oracle/oradata/ORCL' to '/u01/app/oracle/oradata/ORCLSTBY' on Stanby Instance.
After I've activated the Data Guard Broker:

DGMGRL> CREATE CONFIGURATION adgconfig AS PRIMARY DATABASE IS ORCL CONNECT IDENTIFIER IS ORCL;
DGMGRL> ADD DATABASE ORCLSTBY AS CONNECT IDENTIFIER IS ORCLSTBY MAINTAINED AS PHYSICAL;
DGMGRL> ENABLE CONFIGURATION;

When I try to create a new datafile on primary instance here comes the problems:

ALTER TABLESPACE TBS_TEST ADD DATAFILE '/u01/app/oracle/oradata/ORCL/TEST02.dbf' size 100m;

On the Standby side the new file is create under the wrong directory with worng name (example: /u01/app/oracle/oradata/ORCLSTBY/datafile/qwt9yr_test2.dbf)

Anyone know what's wrong on my configuration?
Thank you

Best Answer

By setting db_create_file_dest, you enabled Oracle Managed Files in the standby database, so the database generated a path using the value of this parameter, the value of db_unique_name and the name of tablespace.

If you don't want to use OMF, unset db_create_file_dest.

Related Question