When working with CI/CD (Continuous Integration/Continuous Delivery or Deployment, depending on context) pipelines it is imperative to ensure short feedback loops. If it takes too long for pipelines to complete there is a risk developers skip the use of the pipeline in favour of higher velocity with all the associated problems that CI tries to address in the first place. This post describes how to use Automatic Storage Management (ASM) Cluster File System (ACFS) to quickly create space efficient snapshots of Pluggable Databases in Oracle’s Base VM Service using Oracle Database 19c (only). An update for Oracle Database 23c will follow in a separate post.
A bit of history about cloning Oracle databases
Before the introduction of Container Databases (CDBs) in Oracle, cloning databases could occasionally take longer than acceptable for CI pipelines. The CDB architecture, introduced in Oracle 12.1, changed the situation quite a bit. Container databases are logically divided into so-called Pluggable Databases (PDBs), providing many benefits to developers and administrators alike. One of these benefits is namespace isolation combined with the ability to clone a PDB independently of its CDB.
This article assumes that a “gold-copy” PDB has been created in a CDB using ACFS for storage. It acts as the source of the clone operation described next.
Cloning Pluggable Databases on ASM Cluster File System
The Oracle 19c Multitenant Administration Guide dedicates an entire chapter to cloning Pluggable Databases: chapter 7 discusses the various options available to administrators and DevOps engineers. This article discusses cloning PDBs from snapshot copies for a very simple reason: a snapshot copy reduces the time required to create the clone because it does not include a complete copy of the source data files. Furthermore, the snapshot copy PDB occupies a fraction of the space of the source PDB thanks to the copy-on-write feature. For snapshots to work the database’s storage must support snapshots, obviously. Oracle ACFS is an example for a snapshot-capable file system.
More details about the configuration
The database featured in this article is hosted in Oracle Cloud Infrastructure. The Base Database Service provides a convenient way for testing ACFS snapshots as long as you don’t select “Logical Volume Manager” for storing you database files. ACFS is built on top of ASM – without ASM you cannot have ACFS. Running databases on ACFS however requires a full Grid Infrastructure installation, Oracle Restart is not supported. Please refer to the Licensing Guide to learn more about the Multitenant Option and limits related to the number of Pluggable Databases you can use with your system.
The database featured in this article is used for testing code changes as part of a CI pipeline execution, it is not a production instance. It will only ever be recreated with production data using a well defined, automated workflow. There is no need of a backup of this system as it can be recreated anytime, and it won’t ever be cloned. If your use case is different please ensure you spend enough time testing your configuration and all scenarios to ensure things work out as you’d expect.
This article was written using Grid Infrastructure & database 19.18.0 on said Base Database Service system. The ACFS file system was added via an automation script after the Base VM system has been provisioned. To ensure new PDBs are created on ACFS db_create_file_dest is set to the ACFS mount point, for example /u02/.
Example workflow cloning a PDB on ACFS
The PDB acting as the source for all clone operations has been created on ACFS. This is a prerequisite, otherwise snapshot cloning won’t be available as *.clonedb remained at its default of false. The ACFS file system properties are shown in the following listing:
[oracle@acfsdemo ~]$ acfsutil info fs /u02
/u02
ACFS Version: 19.0.0.0.0
on-disk version: 49.0
compatible.advm: 19.0.0.0.0
ACFS compatibility: 19.0.0.0.0
flags: MountPoint,Available,KiloSnap
creation time: Wed Mar 8 11:10:44 2023
mount time: Wed Mar 8 11:19:25 2023
mount sequence number: 1
number of nodes: 1
allocation unit: 4096
metadata block size: 4096
volumes: 1
total size: 107374182400 ( 100.00 GB )
total free: 29998956544 ( 27.94 GB )
file entry table allocation: 262144
primary volume: /dev/asm/snapvol-187
label:
state: Available
major, minor: 251, 95746
logical sector size: 512
size: 107374182400 ( 100.00 GB )
free: 29998956544 ( 27.94 GB )
metadata read I/O count: 28523
metadata write I/O count: 47861
total metadata bytes read: 228380672 ( 217.80 MB )
total metadata bytes written: 4382646272 ( 4.08 GB )
ADVM diskgroup: DATA
ADVM resize increment: 67108864
ADVM redundancy: unprotected
ADVM stripe columns: 8
ADVM stripe width: 1048576
number of snapshots: 0
snapshot space usage: 0 ( 0.00 )
replication status: DISABLED
compression status: DISABLED
[oracle@acfsdemo ~]$
The PDB used as source for the clone operation features a Swingbench Order Entry schema. For the sake of this article the Swingbench PDB acts as the “golden master” PDB. As part of the imaginary CI pipeline’s execution the PDB is cloned before changes to the (cloned PDB’s) SOE schema are made. Schema changes are applied using a standard tool such as Liquibase after linting and syntax checking found no issues with the code. Eventually the remaining pipeline steps are executed until the cloned PDB can be discarded after a successful run.
Let’s have a look how this could be done in practical terms. The actual way of cloning your source PDB depends on your setup, the command line version of the process is shown here so I can show you timings. Be advised that using the SQL commands shown in the next example implies that your new Pluggable Database won’t show up in the OCI GUI!
[oracle@acfsdemo ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 8 14:35:25 2023
Version 19.18.0.0.0
...
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORACDB_PDB1 READ WRITE NO
4 SRCPDB READ WRITE NO
SQL> set timing on
SQL> create pluggable database clone1 from srcpdb
2 snapshot copy
3 keystore identified by "theVerySecretKeystorePasswordNotThisOneOfCourse";
Pluggable database created.
Elapsed: 00:00:36.89
SQL> alter pluggable database clone1 open instances=all;
Pluggable database altered.
Elapsed: 00:00:03.33
SQL> alter pluggable database clone1 save state;
Pluggable database altered.
Elapsed: 00:00:00.42
In case your CDB does not use Transparent Data Encryption (TDE) the keystore does not need to be referenced in the clone command. It only takes a few seconds for the ACFS snapshot to be taken and the PDB to be opened. The exact time does of course depend on your storage configuration and the size of the database. This particular PDB was approximately 72 GB in size:
SQL> select name from v$datafile where con_id = (select con_id from v$pdbs where name = 'SRCPDB'); NAME --------------------------------------------------------------------------------------------------- /u02/ORACDB_FRA1RB/F661F65063011443E0534801000AE1E9/datafile/o1_mf_system_l0jrqmh8_.dbf /u02/ORACDB_FRA1RB/F661F65063011443E0534801000AE1E9/datafile/o1_mf_sysaux_l0jrqvvk_.dbf /u02/ORACDB_FRA1RB/F661F65063011443E0534801000AE1E9/datafile/o1_mf_undotbs1_l0jrrffb_.dbf /u02/ORACDB_FRA1RB/F661F65063011443E0534801000AE1E9/datafile/o1_mf_users_l0jrqlxn_.dbf /u02/ORACDB_FRA1RB/F661F65063011443E0534801000AE1E9/datafile/o1_mf_data_l0jty4vg_.dbf SQL> !du -sh /u02/ORACDB_FRA1RB/F661F65063011443E0534801000AE1E9/ 72G /u02/ORACDB_FRA1RB/F661F65063011443E0534801000AE1E9/
Tests with much larger data sets showed the time needed for cloning a 500 GB PDB is not that much higher in the current setup.
ACFS Snapshots
Grid Infrastructure 19c creates an ACFS snapshot, in other words a very space efficient way of cloning the PDB. You can see that a snapshot was taken in the output of the acfsutil command shown here:
[oracle@acfsdemo ~]$ acfsutil info fs /u02
/u02
ACFS Version: 19.0.0.0.0
on-disk version: 49.0
compatible.advm: 19.0.0.0.0
ACFS compatibility: 19.0.0.0.0
flags: MountPoint,Available,KiloSnap
creation time: Wed Mar 8 11:10:44 2023
mount time: Wed Mar 8 11:19:25 2023
mount sequence number: 1
number of nodes: 1
allocation unit: 4096
metadata block size: 4096
volumes: 1
total size: 107374182400 ( 100.00 GB )
total free: 24617168896 ( 22.93 GB )
file entry table allocation: 262144
primary volume: /dev/asm/snapvol-187
label:
state: Available
major, minor: 251, 95746
logical sector size: 512
size: 107374182400 ( 100.00 GB )
free: 24617168896 ( 22.93 GB )
metadata read I/O count: 29275
metadata write I/O count: 48900
total metadata bytes read: 233062400 ( 222.27 MB )
total metadata bytes written: 4389122048 ( 4.09 GB )
ADVM diskgroup: DATA
ADVM resize increment: 67108864
ADVM redundancy: unprotected
ADVM stripe columns: 8
ADVM stripe width: 1048576
number of snapshots: 1
snapshot space usage: 2666496 ( 2.54 MB )
replication status: DISABLED
compression status: DISABLED
Right after its creation the snapshot takes 2.54 MB rather than the full size of the source PDB, a very significant saving in space. Snapshot space usage will increase if changes to the database are made. For the most part that is not a problem if developers follow DevOps best practices of making small, incremental changes but you should plan ahead and allocate sufficient space. Once the code deployment as part of the CI pipeline has completed and all unit tests are done the PDB can be discarded. Doing so also removes the ACFS snapshot as well.
Summary
Oracle Multitenant Option offers lots of potential for implementing modern application development principles. Combined with space efficient snapshots taken quickly, CI pipelines can be implemented without worrying about the time it takes to run the pipeline even with reasonably large databases.