FILE_NAME_CONVERT vs PDB_FILE_NAME_CONVERT

After last week’s post, I got some feedback from you, my subscribers and readers, and you asked me to elaborate a bit about FILE_NAME_CONVERT and PDB_FILE_NAME_CONVERT. What is the difference between the two, and how can these be used. Let’s look in more detail at the two and find out!

FILE_NAME_CONVERT

FILE_NAME_CONVERT is not an init.ora parameter. This is a CLAUSE in the CREATE PLUGGABLE DATABASE statement or in the PDB cloning statement, when Oracle Managed Files (OMF) is not enabled. If OMF is enabled, then you cannot use FILE_NAME_CONVERT clause.

You would use this clause to determine how the database (the CDB) will generate the names of the files for the new PDB (either brand new PDB or cloned PDB).

This is how the FILE_NAME_CONVERT clause looks like:

FILE_NAME_CONVERT = ( 'filename_pattern1', 'replace_filename_pattern1',
                      'filename_pattern2', 'replace_filename_pattern2',
                      … )
                      | NONE

The filename_pattern1 is the pattern for the PDB$SEED database or the source PDB for the clone, or even the XML file that is used for plugging a PDB into a CDB. The replace_filename_pattern1 should be the replacement string for filename_pattern1 when Oracle generates the new names for the files associated with the new PDB.

What happens during PDB creation if FILE_NAME_CONVERT is not set or is set to NONE?

When FILE_NAME_CONVERT clause is not set, Oracle will try to use OMF files for renaming the datafiles. If you are not using OMF, then Oracle will look for PDB_FILE_NAME_CONVERT parameter in the spfile. If this parameter is not set either, then Oracle will return an error.

Example:

--example for non-OMF database, creating a PDB from scratch

CREATE PLUGGABLE DATABASE testpdb2 
ADMIN USER DRADMIN IDENTIFIED BY password
ROLES=(DBA)
DEFAULT TABLESPACE users 
DATAFILE '/u01/app/oracle/oradata/CDB1/testpdb2/sales01.dbf'
SIZE 250M AUTOEXTEND ON
PATH_PREFIX = '/u01/app/oracle/oradata/CDB1/testpdb2/'
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/CDB1/datafile/', 
                     '/u01/app/oracle/oradata/CDB1/testpdb2');


--example for non-OMF database, cloning a PDB

CREATE PLUGGABLE DATABASE testpdb3 from testpdb1
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/CDB1/testpdb1/', 
                     '/u01/app/oracle/oradata/CDB1/testpdb3')
PATH_PREFIX = '/u01/app/oracle/oradata/CDB1/testpdb3/';

PDB_FILE_NAME_CONVERT

PDB_FILE_NAME_CONVERT is an init.ora parameter that maps the names of exiting files to new file names. This parameter is used during the CREATE PLUGGABLE DATABASE statement (either new PDB or clone PDB) or in the ENABLE PLUGGABLE DATABASE of the CREATE DATABASE statement, when the FILE_NAME_CONVERT clause is not specified or is set to NONE, and OMF is not in use.

If OMF is enabled, then you cannot use PDB_FILE_NAME_CONVERT parameter.

This is how the parameter setting looks like:

PDB_FILE_NAME_CONVERT = 'string1' , 'replace_string1',
                        'string2' , 'replace_string2', …

String1 is the existing file name pattern. Replace_string1 is the new file name pattern that will replace String1. String2 is the existing file name pattern. Replace_string2 is the new file name pattern that will replace String2. And so on.

This parameter can be altered at the session or system level, and at the PDB level.

Example:

ALTER SESSION SET PDB_FILE_NAME_CONVERT=
'/u01/app/oracle/oradata/CDB1/testpdb1/',
'/u01/app/oracle/oradata/CDB1/testpdb4');

CREATE PLUGGABLE DATABASE testpdb4 from testpdb1;

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the best price, only $18.99 CAD available until the end of the month!