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
