Before creating standby database, you need to consider where your standby database files will be located and make sure the filesystem has necessary paths pre-created.
Dbvisit will create standby database files regardless whether on ASM, Filesystem or ACFS according to the Oracle rules and concept.
The main consideration here is whether you will do file path conversion or not.
1 OMF files
Location of OMF files on standby server is completely driven by OMF parameters db_create_file_dest , db_create_online_log_dest_[x] and db_recovery_file_dest. You may change it to place your files on different ASM diskgroup, different filesystem mountpoint etc … OMF parameters on primary and standby are needed even if you don’t make any type of name/path conversion.
Don’t forget that Oracle automatically adds db_unique_name to the OMF path. Never set OMF parameters to contain any actual name. For example if you would set db_create_online_log_dest_1 = /u02/app/oracle/oradata/DEV, you would end up with nonsense path: /u02/app/oracle/oradata/DEV/DEV/onlinelog/o1_mf_3_lw9or7g1_.log
If you have identical filesystem structure on standby and on primary (no path conversion done):
You will need to set OMF parameters when creating standby database to the same values as on your primary
OMF parameters db_create_file_dest, db_recovery_file_dest or db_create_online_log_dest_[x] are needed even if your standby file locations are the same as on primary. They will be automatically copied from your current primary database init parameters during create standby database process.
If you would like to have different file paths on primary and standby or if you’re converting between ASM and filesystem (path conversion is done):
You will need to set OMF parameters when creating standby database to intended locations
OMF init parameters are:
db_create_file_dest db_create_online_log_dest_1 db_create_online_log_dest_2 db_create_online_log_dest_3 db_create_online_log_dest_4 db_create_online_log_dest_5
For each type of file OMF rules are applied differently:
OMF datafiles and tempfiles
will be created on standby in location <db_create_file_dest>/<db_unique_name>/datafile. For example if you have OMF datafile on primary (primary db_unique_name=SOLO):
+DATA/SOLO/DATAFILE/system.292.1148040717
and your standby will be on filesystem with init parameters:
db_create_file_dest = /u01/app/oracle/oradata
db_unique_name=dev
then standby OMF file will be created like so:
/u01/app/oracle/oradata/DEV/datafile/o1_mf_system_kqh8ln12_.dbf
db_unique_name is converted by oracle to capital letters for filesystem location.
OMF redo logs
Redo member paths on standby will be changed only during standby database activation or graceful switchover
Will be created on standby in location(s) <db_create_online_log_dest_[x]>/<db_unique_name>/onlinelog.
If parameters db_create_online_log_dest_[x] are not set, oracle will automatically create redo in <db_create_file_dest>/<db_unique_name>/onlinelog and its mirror in <db_recovery_file_dest>/<db_unique_name>/onlinelog.
For example if your primary has redo logs (only group 3 is displayed):
/u02/app/oracle/oradata/SLASH/onlinelog/o1_mf_3_lw9or7g1_.log /u03/app/oracle/oradata/SLASH/onlinelog/o1_mf_3_lw9or7mw_.log
and your standby will be on filesystem with init parameters
db_create_file_dest = /u02/app/oracle/oradata
db_recovery_file_dest = /u02/app/oracle/fast_recovery_area
db_unique_name=dev
db_create_online_log_dest_[x] are not set
then standby OMF redo logs will be created like so (only group 3 is displayed):
/u02/app/oracle/oradata/DEV/onlinelog/o1_mf_3_lw9or7g1_.log /u02/app/oracle/fast_recovery_area/DEV/onlinelog/o1_mf_3_lw9or7mw_.log
If your primary database uses exclusively OMF files, make sure that db_file_name_convert and log_file_name_convert parameters are not set at all when you create standby database
Primary ASM to Standby filesystem conversion example with OMF files
Let’s have a look how primary ASM to Standby Filesystem conversion works for exclusively OMF primary database.
Primary database has following configuration:
-- controlfiles: +DATA/SOLO/CONTROLFILE/current.279.1148040283 +RECO/SOLO/CONTROLFILE/current.3073.1148040285 -- redo: +DATA/SOLO/ONLINELOG/group_1.304.1149332117 +DATA/SOLO/ONLINELOG/group_2.303.1149332119 +DATA/SOLO/ONLINELOG/group_3.302.1149332125 +RECO/SOLO/ONLINELOG/group_1.2269.1149332119 +RECO/SOLO/ONLINELOG/group_2.2268.1149332119 +RECO/SOLO/ONLINELOG/group_3.1492.1149332125 -- datafiles: +DATA/SOLO/DATAFILE/system.283.1148040461 +DATA/SOLO/DATAFILE/sysaux.282.1148040633 +DATA/SOLO/DATAFILE/undotbs1.285.1148040671 +DATA/SOLO/DATAFILE/users.291.1148040705 +DATA/SOLO/DATAFILE/new_tbs.300.1148040849 -- tempfiles: +DATA/SOLO/TEMPFILE/temp.299.1149332231 -- db_unique_name = SOLO -- db_create_file_dest = +DATA -- db_recovery_file_dest = +RECO -- db_create_online_log_dest_[x] are not set -- db_file_name_convert and log_file_name_convert are not set
During create standby database we would OMF parameters like so:
Unique db name for standby database is set during DDC configuration creation, cannot be set during create standby database process
db_file_name_convert and log_file_name_convert must not be set
control_files parameter is not set as controlfiles are OMF
After standby database is created it will have following structure:
-- controlfiles: /data/SOLOX/controlfile/o1_mf_lxs3tvsd_.ctl /reco/SOLOX/controlfile/o1_mf_lxs3tvx8_.ctl -- redo: +DATA/SOLO/ONLINELOG/group_1.304.1149332117 +DATA/SOLO/ONLINELOG/group_2.303.1149332119 +DATA/SOLO/ONLINELOG/group_3.302.1149332125 +RECO/SOLO/ONLINELOG/group_1.2269.1149332119 +RECO/SOLO/ONLINELOG/group_2.2268.1149332119 +RECO/SOLO/ONLINELOG/group_3.1492.1149332125 -- datafiles: /data/SOLOX/datafile/o1_mf_system_lxs408bv_.dbf /data/SOLOX/datafile/o1_mf_sysaux_lxs45ttl_.dbf /data/SOLOX/datafile/o1_mf_undotbs1_lxs47236_.dbf /data/SOLOX/datafile/o1_mf_users_lxs47w16_.dbf /data/SOLOX/datafile/o1_mf_new_tbs_lxs4cqqm_.dbf -- tempfiles: /data/SOLOX/datafile/o1_mf_temp_%u_.tmp -- db_unique_name = SOLOX -- db_create_file_dest = /data -- db_recovery_file_dest = /reco -- db_create_online_log_dest_[x] are not set -- db_file_name_convert and log_file_name_convert are not set
Redo member locations will still point to ASM with primary db_unique_name on standby database. This is expected, the paths will be changed during activation or graceful switchover
2 Non-OMF files
If you have identical filesystem structure on standby and on primary (no path conversion done):
There’s no need to set any specific parameters for standby database if your primary database uses non-OMF files and you have identical filesystem structure / paths on standby and on primary.
If you have identical file structure, you must not set db_file_name_convert nor log_file_name_convert on your databases.
If you would like to have different file paths on primary and standby or if you’re converting between ASM and filesystem (path conversion is done):
Such conversion is possible, but you need to set the convert parameters for your standby database during standby database creation. There are two convert parameters to set:
db_file_name_convert = 'PATHA','PATHB','PATHC','PATHD' log_file_name_convert = 'PATHA','PATHB','PATHC','PATHD'
The convert parameters have following properties:
They contain pair or pairs of strings which are used for path conversion when standby database is created
They are valid on standby database only. On primary, they are not effective.
They are always set on primary and standby - their values are reversed
Primary filesystem to Standby filesystem conversion example with non-OMF files
Primary database has following configuration:
-- controlfile: /u01/app/oracle/oradata/DBVONE/control01.ctl -- redo: /u01/app/oracle/oradata/DBVONE/redo2.rdo /u01/app/oracle/oradata/DBVONE/redo3.rdo /u01/app/oracle/oradata/DBVONE/redo1.rdo -- datafiles: /u01/app/oracle/oradata/DBVONE/system01.dbf /u01/app/oracle/oradata/DBVONE/sysaux01.dbf /u01/app/oracle/oradata/DBVONE/undotbs01.dbf /u01/app/oracle/oradata/DBVONE/users01.dbf /u02/app/oracle/oradata/DBVONE/convert_tbs_01.dbf -- tempfiles: /u01/app/oracle/oradata/DBVONE/temp01.dbf -- db_unique_name = DBVONE -- db_create_file_dest is not set -- db_recovery_file_dest = /u01/app/oracle/oradata/fast_recovery_area -- db_create_online_log_dest_[x] is not set -- db_file_name_convert and log_file_name_convert are not set
On standby server we want to place all files on u02 instead of u01. So during the create standby database we would specify following parameters:
For setting convert parameters in the create standby database wizard, we don’t use single quotes. Make sure to not use any trailing slashes and empty spaces.
Note that for parameter db_file_name_convert we have to add one more path conversion pair for primary datafile /u02/app/oracle/oradata/DBVONE/convert_tbs_01.dbf.
Setting db_file_name_convert only to u01,u02 would place this datafile on standby to the same path as on primary: /u02/app/oracle/oradata/DBVONE/convert_tbs_01.dbf. There’s no way to convert it back if it’s placed on standby together with other datafiles. It would be impossible to perform graceful switchover as the datafile would be expected to be in /u01/app/oracle/oradata/DBVONE/convert_tbs_01.dbf on the old primary / new standby.
If the datafile convert_tbs_01.dbf on primary wouldn’t exist, we would set db_file_name_convert to u01,u02.
After the standby database is created, files will have following configuration:
-- controlfile: /u02/app/oracle/oradata/DBVONE/control01.ctl -- redo: /u02/app/oracle/oradata/DBVONE/redo2.rdo /u02/app/oracle/oradata/DBVONE/redo3.rdo /u02/app/oracle/oradata/DBVONE/redo1.rdo -- datafiles: /u02/app/oracle/oradata/DBVONE/system01.dbf /u02/app/oracle/oradata/DBVONE/sysaux01.dbf /u02/app/oracle/oradata/DBVONE/undotbs01.dbf /u02/app/oracle/oradata/DBVONE/users01.dbf /u02/app/oracle/oradata/DBVONE_u02/convert_tbs_01.dbf -- tempfiles: /u02/app/oracle/oradata/DBVONE/temp01.dbf -- db_unique_name = DBVONE -- db_create_file_dest is not set -- db_recovery_file_dest = /u02/app/oracle/oradata/fast_recovery_area -- db_create_online_log_dest_[x] is not set -- db_file_name_convert = u01, u02, /u02/app/oracle/oradata/DBVONE, /u02/app/oracle/oradata/DBVONE_u02 -- log_file_name_convert = u01, u02
After standby database is created, you should set convert parameters on your primary database with opposite values (requires restart):
SQL> alter system set db_file_name_convert ='u02','u01','/u02/app/oracle/oradata/DBVONE_u02','/u02/app/oracle/oradata/DBVONE' scope=spfile;
SQL> alter system set log_file_name_convert = ‘u02','u01' scope=spfile;
This is for proper functionality of failover and switchover.
If you skip this step, Dbvisit will set these parameters during switchover automatically.
Oracle doesn’t use in any way db_unique_name for non-OMF standby database files names. If you would like to create standby database with different db_unique_name you need to provide full path in the convert parameters. For example if we would choose db_unique_name = DBVONEDR for our standby database, we should set:
db_file_name_convert = /u01/app/oracle/oradata/DBVONE, /u02/app/oracle/oradata/DBVONEDR
log_file_name_convert = /u01/app/oracle/oradata/DBVONE, /u02/app/oracle/oradata/DBVONEDR
3 Mixing OMF and Non-OMF files
This configuration is very complex for standby database creation and placement of database files on standby.
If you have identical filesystem structure on standby and on primary (no path conversion done):
You will need to set OMF parameters when creating standby database to the same values as on your primary
There’s no need to set any specific parameters for non-OMF files
If you would like to have different file paths on primary and standby or if you’re converting between ASM and filesystem (path conversion is done):
You will need to set OMF parameters when creating standby database to intended locations
You will need to set
db_file_name_convertandlog_file_name_convertas well