Since Oracle 12c we do have the feature of creating snapshots of existing PDBs.
This feature is excellent for development purposes. On a Oracle Database Appliance it took <3seconds for the creation of a snapshot of an existing PDB (~200GB size) which is far less than the time it would take to create a rman duplicate of the same database. With this fast duplicate it is possible to perform automated integration testing and so on.
But unfortunately it is currently not possible to view the information about the snapshots in the database. Or I haven’t found a way because the view V$PDBS is showing wrong information in the column SNAPSHOT_PARENT_CON_ID (Version 12.1).
It seams Oracle do have some Bugs in 12.1, I’ll verify the situation in 12.2 in another blog post:
Bug 18946166 : LNX64-12102-ACFS-SCF: THE SNAPSHOT_PARENT_CON_ID IS ALWAYS 0 IN V$PDBS
Bug 18521648 : PROVIDE DICTIONARY VIEW RELATED TO SNAPSHOT PDB CLONING
One way of getting the required information is acfsutil.
To get the information about the ACFS filesystems in place (these information are gathered on a ODA X7-2:
oracle@hostname:~/ [+ASM1] acfsutil info fs /u03/app/oracle ACFS Version: 12.2.0.1.0 on-disk version: 46.0 compatible.advm: 12.2.0.0.0 ACFS compatibility: 12.2.0.0.0 flags: MountPoint,Available,AutoResizeEnabled mount time: Tue Jul 3 12:48:39 2018 mount sequence number: 1 allocation unit: 4096 metadata block size: 4096 volumes: 1 total size: 318901321728 ( 297.00 GB ) total free: 302951370752 ( 282.15 GB ) auto-resize increment: 31138512896 ( 29.00 GB ) auto-resize maximum: 0 ( 0.00 ) file entry table allocation: 8650752 primary volume: /dev/asm/reco-262 label: state: Available major, minor: 247, 134145 logical sector size: 512 size: 318901321728 ( 297.00 GB ) free: 302951370752 ( 282.15 GB ) metadata read I/O count: 16682 metadata write I/O count: 32501 total metadata bytes read: 99655680 ( 95.04 MB ) total metadata bytes written: 184176640 ( 175.64 MB ) ADVM diskgroup: RECO ADVM resize increment: 536870912verfiy ADVM redundancy: mirror ADVM stripe columns: 8 ADVM stripe width: 1048576 number of snapshots: 0 snapshot space usage: 0 ( 0.00 ) replication status: DISABLED compression status: DISABLED /opt/oracle/dcs/commonstore ACFS Version: 12.2.0.1.0 on-disk version: 46.0 compatible.advm: 12.2.0.0.0 ACFS compatibility: 12.2.0.0.0 flags: MountPoint,Available,AutoResizeEnabled mount time: Tue Jul 3 12:48:46 2018 mount sequence number: 2 allocation unit: 4096 metadata block size: 4096 volumes: 1 total size: 5368709120 ( 5.00 GB ) total free: 5034381312 ( 4.69 GB ) auto-resize increment: 1073741824 ( 1.00 GB ) auto-resize maximum: 0 ( 0.00 ) file entry table allocation: 262144 primary volume: /dev/asm/commonstore-435 label: state: Available major, minor: 247, 222722 logical sector size: 512 size: 5368709120 ( 5.00 GB ) free: 5034381312 ( 4.69 GB ) metadata read I/O count: 1182 metadata write I/O count: 79 total metadata bytes read: 5144576 ( 4.91 MB ) total metadata bytes written: 864256 ( 844.00 KB ) ADVM diskgroup: DATA ADVM resize increment: 536870912 ADVM redundancy: mirror ADVM stripe columns: 8 ADVM stripe width: 1048576verfiy number of snapshots: 0 snapshot space usage: 0 ( 0.00 ) replication status: DISABLED compression status: DISABLED /u02/app/oracle/oradata/DB1 ACFS Version: 12.2.0.1.0 on-disk version: 46.0 compatible.advm: 12.2.0.0.0 ACFS compatibility: 12.2.0.0.0 flags: MountPoint,Available,AutoResizeEnabled mount time: Thu Jul 5 15:51:45 2018 mount sequence number: 5 allocation unit: 4096 metadata block size: 4096 volumes: 1 total size: 805306368000 ( 750.00 GB ) total free: 15822602240 ( 14.74 GB ) auto-resize increment: 10737418240 ( 10.00 GB ) auto-resize maximum: 0 ( 0.00 ) file entry table allocation: 8650752 primary volume: /dev/asm/datDB1-435 label: state: Available major, minor: 247, 222verfiy
From this output we need the filesystem mount path (/u02/app/oracle/oradata/DB1), now we can get the detailed information about snapshots on this filesystem:
oracle@hostname:~/ [+ASM1] acfsutil snap info /u02/app/oracle/oradata/DB1 snapshot name: 70DD7CB2094239ECE053390AA8C07BBC snapshot location: /u02/app/oracle/oradata/DB1/.ACFS/snaps/70DD7CB2094239ECE053390AA8C07BBC RO snapshot or RW snapshot: RW parent name: /u02/app/oracle/oradata/DB1 snapshot creation time: Fri Jul 13 09:48:43 2018 sDB1ge added to snapshot: 31780864 ( 30.31 MB ) number of snapshots: 1 snapshot space usage: 184131584 ( 175.60 MB )
In this example we see the name of the snapshot and the used space. The name of the snapshot is identical with the GUID in dba_pdbs:
SQL> select guid,pdb_name from dba_pdbs; GUID PDB_NAME -------------------------------- -------- 70DD7CB2094239ECE053390AA8C07BBC DB2
