Oracle Database 12.1 Information about PDB Snapshots

Oracle Database 12.1 Information about PDB Snapshots

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

Leave a Reply

Your email address will not be published. Required fields are marked *