CLONE PDB IN LOCAL CDB USING CONVERT IN FILESYSTEM WITH OMF ENABLED ORACLE 19c

Cloning a PDB is often a requirement in oracle database since the launch of multitenant from 12c. But as days pass on, in the recent oracle versions, it is compulsory to have multitenant systems due to containerization of all applications to utilize all the system resources efficiently

Source CDB: DB9ZX | Destination CDB: DB9ZX (Both 19c)

In this post, we will perform PDB clone in the same CDB with OMF enabled datafiles in filesystem

Prechecks:

Check if archive log mode is enabled. If not enable it.

kIsH@Xhydra<>select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

Check if local undo is enabled.

kIsH@Xhydra<>select * from database_properties where PROPERTY_NAME like '%UNDO%';

PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
LOCAL_UNDO_ENABLED
TRUE
true if local undo is enabled

Clone PDB ZXPDB1 ===> ZXPDB5

Check the status of PDB

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ZXPDB1                         READ WRITE NO
         4 ZXPDB2                         READ WRITE NO
         5 ZXPDB3                         READ WRITE NO
         6 ZXPDB4                         READ WRITE NO

Check the current container

SQL> show con_name

CON_NAME
------------------------------
ZXPDB1

Check the datafiles under PDB

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/apps01/oradata/DB9ZX/D8F8C25E9C2EFAD3E055000000000001/datafile/o1_mf_system_k1o
wybbn_.dbf

/apps01/oradata/DB9ZX/D8F8C25E9C2EFAD3E055000000000001/datafile/o1_mf_sysaux_k1o
wybd5_.dbf

/apps01/oradata/DB9ZX/D8F8C25E9C2EFAD3E055000000000001/datafile/o1_mf_undotbs1_k
1owybd9_.dbf

/apps01/oradata/DB9ZX/D8F8C25E9C2EFAD3E055000000000001/datafile/o1_mf_users_k1ow
zxpw_.dbf

Restart the PDB in open read only mode to restrict any writes to the source PDB

SQL> shu immediate;
Pluggable Database closed.
SQL> startup open read only;
Pluggable Database opened.

Again change the container to CDB root because cloning cannot be performed from PDB

SQL> alter session set container=CDB$ROOT;

Session altered.

Create the clone PDB using the below syntax. Here FILE_NAME_CONVERT is the parameter which convert the source datafile location and files to a new directory for new PDB.

If a new directory for the Clone PDB datafiles are required, then create a new directory else skip this step

Note:ORA-16000: database or pluggable database open for read-only access error will be thrown if you try to clone PDB in PDB itself

ORA-01276: Cannot add file .File has an Oracle Managed Files file name. error will be thrown if base directory is specified to convert since database is OMF enabled (‘/apps01/oradata/DB9ZX’,’/apps01/oradata/DBZXPDB5′)

SQL> create pluggable database ZXPDB5
  2  from
  3  ZXPDB1 FILE_NAME_CONVERT=('/apps01/oradata/DB9ZX/D8F8C25E9C2EFAD3E055000000000001/datafile/o1_mf_','/apps01/oradata/DBZXPDB5');

Pluggable database created.

Change the session to source PDB and restart the PDB in open read write mode

SQL> alter session set container=ZXPDB1;

Session altered.

SQL> shu immediate;
Pluggable Database closed.
SQL> startup;
Pluggable Database opened.

Set the container to new cloned PDB and startup PDB in open read write mode

SQL> alter session set container=ZXPDB5;

Session altered.

SQL> startup;
Pluggable Database opened.
SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ZXPDB1                         READ WRITE NO
         4 ZXPDB2                         READ WRITE NO
         5 ZXPDB3                         READ WRITE NO
         6 ZXPDB4                         READ WRITE NO
         7 ZXPDB5                         READ WRITE NO

Check the cloned datafiles location

SQL> !ls -lrt /apps01/oradata/DBZXPDB5*
-rw-r-----. 1 oracle oinstall   5251072 Feb 27 03:06 /apps01/oradata/DBZXPDB5users_k1owzxpw_.dbf
-rw-r-----. 1 oracle oinstall  37756928 Feb 27 03:24 /apps01/oradata/DBZXPDB5temp_k1owybdc_.dbf
-rw-r-----. 1 oracle oinstall 346038272 Feb 27 03:29 /apps01/oradata/DBZXPDB5sysaux_k1owybd5_.dbf
-rw-r-----. 1 oracle oinstall 104865792 Feb 27 03:30 /apps01/oradata/DBZXPDB5undotbs1_k1owybd9_.dbf
-rw-r-----. 1 oracle oinstall 283123712 Feb 27 03:30 /apps01/oradata/DBZXPDB5system_k1owybbn_.dbf

Leave a Reply