The Geek Diary

How to set the db_file_name_convert and log_file_name_convert parameters

by

db_file_name_convert and log_file_name_convert is used to convert the File Locations of Datafiles, Online and Standby RedoLog Files of a Database to the correct Location on a Database created from this Database having a different File Structure. So it can be used for Physical Standby Databases and RMAN Duplicate/TSPITR-Operations.
The general Setting is one Pair or multiple Pairs of Strings where the second String substitutes the first String:
db_file_name_convert = ‘string1′,’string2′,’string3′,’string4’,…
Since it’s a simple String Substitution, you can only setup the String to be substituted instead of the Path.

Example:

Source Database uses for Datafiles ‘/[path]/[primary_db_unique_name]/’ and the Destination uses ‘/[path]/[standby_db_unique_name]/’ so db_file_name_convert would look like this (same for log_file_name_convert):

db_file_name_convert = '[primary_db_unique_name]','[standby_db_unique_name]'

Note that this is a Static Parameter, so if set later or adjusted you have to restart the Instance for the new Value to become active. When updating the SPFILE, due to the multiple Quotes, use the below syntax

SQL> alter system set db_file_name_convert = '[primary_db_unique_name]','[standby_db_unique_name]'  scope=spfile;

The same is true if you update the Values of the corresponding Data Guard Broker Properties ‘DbFileNameConvert’ and ‘LogFileNameConvert’, eg.

DGMGRL> edit database '[standby_db_unique_name]' set property 'DbFileNameConvert' = "'[primary_db_unique_name]','[standby_db_unique_name]'";

In this example, I want to redirect the files to a slightly different path during a clone from prod to test.

In prod, files are to be found under:

* /u02/oradata/proddb01/datafile

In test, I want to be placed under:

* /u02/oradata/testdb01

Furthermore, some tempfiles are placed differently than regular datafiles in prod. In test I do not need or want multiple destinations; all files should be placed under /u02/oradata/testdb01. Therefore, my db_file_name_convert parameter must have multiple pairs of source and target locations.

For the log files, they could all be placed under similar locations, so the redirection string can simply contain the only thing that will differ: the ORACLE_SID.

When using a pfile:

db_file_name_convert=('/u02/oradata/proddb01/datafile','/u02/oradata/testdb01',
                      '/u02/oradata/proddb01/tempfile',/u02/oradata/testdb01')

log_file_name_convert=('proddb01','testdb01')

When using an spfile:

alter system set db_file_name_convert='/u02/oradata/proddb01/datafile','/u02/oradata/testdb01',
                                      '/u02/oradata/proddb01/tempfile',/u02/oradata/testdb01' scope=spfile;

alter system set log_file_name_convert='proddb01','testdb01' scope=spfile;

It is also supported to use the log_file_name_convert multiple times in the parameter file, like this:

log_file_name_convert=('/u01/app/oracle/oradata/proddb01/onlinelog/','/u04/oradata/testdb01/onlinelog/')
log_file_name_convert=('/u01/app/oracle/flash_recovery_area/proddb01/onlinelog/','/u04/oradata/testdb01/onlinelog/')

You May Also Like