Oracle Multitenant: From OMF-PDB to Non-OMF-PDB

Personally, I prefer running Oracle Container Databases with Oracle Managed Files (OMF), because OMF simplify the handling of the filenames, especially when cloning pluggable databases. But if you are running a “mixed environment” with some databases using OMF and others not, it may occur that you have to plugin an “OMF-PDB” as a “Non-OMF-PDB”. Let’s see how it works.

My lab is running Oracle Database 19c (RU 19.8, July 2020) and I’ve got 2 CDBs:

• TSTCDB1: Non-OMF
• TSTCDB2: OMF

Create a Test-PDB in the OMF-CDB

SQL> show parameter db_create_file_dest
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest		     string	 /u02/oradata

SQL> create pluggable database OMFPDB admin user PDBADMIN identified by manager;
Pluggable database created.

SQL> alter pluggable database OMFPDB open;
Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 OMFPDB			  READ WRITE NO

File Locations in OMF

Let’s check the location of the datafiles and tempfiles of the PDB:

SQL> select name from v$datafile where con_id=3;
NAME
------------------------------------------------------------------------------------------
/u02/oradata/TSTCDB2/AD1380CF4AC80F05E0536C38A8C0D4DE/datafile/o1_mf_system_hmo0h9wo_.dbf
/u02/oradata/TSTCDB2/AD1380CF4AC80F05E0536C38A8C0D4DE/datafile/o1_mf_sysaux_hmo0h9ww_.dbf
/u02/oradata/TSTCDB2/AD1380CF4AC80F05E0536C38A8C0D4DE/datafile/o1_mf_undotbs1_hmo0h9ww_.dbf
3 rows selected.

SQL> select name from v$tempfile where con_id=3;
NAME
------------------------------------------------------------------------------------------
/u02/oradata/TSTCDB2/AD1380CF4AC80F05E0536C38A8C0D4DE/datafile/o1_mf_temp_hmo0h9ww_.dbf
1 row selected.

Unplug the PDB

SQL> alter pluggable database OMFPDB close;
Pluggable database altered.

SQL> alter pluggable database OMFPDB unplug into '/home/oracle/omfpdb.xml';
Pluggable database altered.

SQL> drop pluggable database OMFPDB keep datafiles;
Pluggable database dropped.

Plug in the PDB into a “Non-OMF-CDB”

The important thing when plugging in the PDB is to specify the target file name of each data file and temp file of the source database. As the filenames in OMF are system-generated, there is simple pattern for the parameter FILE_NAME_CONVERT.

SQL> create pluggable database NOMFPDB
   2 using '/home/oracle/omfpdb.xml'
   3 copy
   4 file_name_convert=
   5 (
   6 '/u02/oradata/TSTCDB2/AD1380CF4AC80F05E0536C38A8C0D4DE/datafile/o1_mf_system_hmo0h9wo_.dbf','/u01/oradata/TSTCDB1/NOMFPDB/system01.dbf',
   7 '/u02/oradata/TSTCDB2/AD1380CF4AC80F05E0536C38A8C0D4DE/datafile/o1_mf_sysaux_hmo0h9ww_.dbf','/u01/oradata/TSTCDB1/NOMFPDB/sysaux01.dbf',
   8 '/u02/oradata/TSTCDB2/AD1380CF4AC80F05E0536C38A8C0D4DE/datafile/o1_mf_undotbs1_hmo0h9ww_.dbf','/u01/oradata/TSTCDB1/NOMFPDB/undotbs1.dbf',
   9 '/u02/oradata/TSTCDB2/AD1380CF4AC80F05E0536C38A8C0D4DE/datafile/o1_mf_temp_hmo0h9ww_.dbf','/u01/oradata/TSTCDB1/NOMFPDB/temp01.dbf'
  10  );
  SQL> alter pluggable database NOMFPDB open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 NOMFPDB			  READ WRITE NO
	 5 SAMPLEPDB			  READ WRITE NO
	 7 PDB01			  READ WRITE NO

Check the location of the files in the target PDB

SQL> set linesize 80
SQL> column name format a80
SQL> select name from v$datafile where con_id=3;

NAME
---------------------------------------------
/u01/oradata/TSTCDB1/NOMFPDB/system01.dbf
/u01/oradata/TSTCDB1/NOMFPDB/sysaux01.dbf
/u01/oradata/TSTCDB1/NOMFPDB/undotbs1.dbf
3 rows selected.

SQL> select name from v$tempfile where con_id=3;

NAME
-----------------------------------------
/u01/oradata/TSTCDB1/NOMFPDB/temp01.dbf
1 row selected.

That’s it. The PDB is plugged into another CDB and all files are Non-OMF files.

---

Amazon-Partner-Link