Pluggable Database Cloning using Snapshot Copy

We can clone a PDB using snapshots of the underlying storage via the SNAPSHOT COPY clause of the CREATE PLUGGABLE DATABASE statement.

Performing PDB clones using a snapshot copy reduces the time required to create the clone because it does not include a complete copy of the source data files.

The PDB snapshot copy occupies a fraction of the space consumed by the source Pluggable Database.

The underlying storage needs to support storage level snapshots and examples of this are ACFS and Direct NFS client storage. This is also supported on Exadata using Sparse ASM Disk Groups.

The CLONEDB parameter when set to FALSE enables us to create storage level snapshot copies of a source PDB which is even opened in read-write mode.

Let us have a look at an example of a PDB Snapshot Copy.

PDB1 is the source for the Snapshot Copy and the database is open in READ WRITE mode. CLONEDB parameter is FALSE. Underlying storage is ACFS using 19c Grid Infrastructure.

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
SQL> show parameter clone

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
clonedb 			     boolean	 FALSE
clonedb_dir			     string

 
Create a PDB Snapshot Copy PDB2 from PDB1 source.
 

SQL> create pluggable database pdb2 from pdb1 snapshot copy;

Pluggable database created.

 
Note the datafiles for PDB1 and PDB2 and also note the size of each file.
 

SQL> select pdb_name,GUID from dba_pdbs;

PDB_NAME   GUID
---------- --------------------------------
PDB1	   951745FCC921042AE0536438A8C02A7B
PDB$SEED   95170A7763F65A6FE0536438A8C014F5
PDB2	   957B7E9809F96452E0536438A8C06E13

SQL> select name,bytes/1048576 from v$datafile;

NAME												     BYTES/1048576
---------------------------------------------------------------------------------------------------- -------------
/acfs/app/oradata/CDB1/datafile/o1_mf_system_gtj4ys7c_.dbf						       910
/acfs/app/oradata/CDB1/datafile/o1_mf_sysaux_gtj506h0_.dbf						       670
/acfs/app/oradata/CDB1/datafile/o1_mf_undotbs1_gtj50zq9_.dbf						       280
/acfs/app/oradata/CDB1/datafile/o1_mf_system_gtj5h2mc_.dbf						       270
/acfs/app/oradata/CDB1/datafile/o1_mf_sysaux_gtj5h2o0_.dbf						       330
/acfs/app/oradata/CDB1/datafile/o1_mf_users_gtj510xb_.dbf							 5
/acfs/app/oradata/CDB1/datafile/o1_mf_undotbs1_gtj5h2pk_.dbf						       100
/acfs/app/oradata/CDB1/951745FCC921042AE0536438A8C02A7B/datafile/o1_mf_system_gtj6hoxr_.dbf		       280
/acfs/app/oradata/CDB1/951745FCC921042AE0536438A8C02A7B/datafile/o1_mf_sysaux_gtj6hp12_.dbf		       350
/acfs/app/oradata/CDB1/951745FCC921042AE0536438A8C02A7B/datafile/o1_mf_undotbs1_gtj6hp1c_.dbf		       105
/acfs/app/oradata/CDB1/951745FCC921042AE0536438A8C02A7B/datafile/o1_mf_users_gtj6jy5h_.dbf		       500
/acfs/app/oradata/CDB1/957B7E9809F96452E0536438A8C06E13/datafile/o1_mf_system_gtxbzsqm_.dbf		       280
/acfs/app/oradata/CDB1/957B7E9809F96452E0536438A8C06E13/datafile/o1_mf_sysaux_gtxbzsr1_.dbf		       350
/acfs/app/oradata/CDB1/957B7E9809F96452E0536438A8C06E13/datafile/o1_mf_undotbs1_gtxbzsr4_.dbf		       105
/acfs/app/oradata/CDB1/957B7E9809F96452E0536438A8C06E13/datafile/o1_mf_users_gtxbzsrc_.dbf		       500

 
Now note the actual size on disk of PDB2 Snapshot Copy (133 MB) as compared to PDB1 (1.4 GB)which is the source database.

Even the 133 MB is being consumed by the TEMPFILE while the data files are just consuming 4K each!

SQL> !du -hs /acfs/app/oradata/CDB1/951745FCC921042AE0536438A8C02A7B/datafile/
1.4G	/acfs/app/oradata/CDB1/951745FCC921042AE0536438A8C02A7B/datafile/

SQL> !du -hs /acfs/app/oradata/CDB1/957B7E9809F96452E0536438A8C06E13/datafile/
133M	/acfs/app/oradata/CDB1/957B7E9809F96452E0536438A8C06E13/datafile/

SQL> !du -hs /acfs/app/oradata/CDB1/957B7E9809F96452E0536438A8C06E13/datafile/*
4.0K	/acfs/app/oradata/CDB1/957B7E9809F96452E0536438A8C06E13/datafile/o1_mf_sysaux_gtxbzsr1_.dbf
4.0K	/acfs/app/oradata/CDB1/957B7E9809F96452E0536438A8C06E13/datafile/o1_mf_system_gtxbzsqm_.dbf
133M	/acfs/app/oradata/CDB1/957B7E9809F96452E0536438A8C06E13/datafile/o1_mf_temp_gtxbzsr8_.dbf
4.0K	/acfs/app/oradata/CDB1/957B7E9809F96452E0536438A8C06E13/datafile/o1_mf_undotbs1_gtxbzsr4_.dbf
4.0K	/acfs/app/oradata/CDB1/957B7E9809F96452E0536438A8C06E13/datafile/o1_mf_users_gtxbzsrc_.dbf