We can probably all agree that container databases (PDBs – “multitenant”) architecture has clearly improved DBAs quality of life. Most of the maintenance operations around the database lifecycle have become so simplified that activities such as cloning a database can now be done with a single, very simple, SQL command. And this without giving up all the power and features of Oracle’s RDBMS core.

Many of these operations are already well known to everyone, as they can now replace operations that were previously performed with other techniques (e.g. using SQL to clone a PDB instead of configuring and executing an RMAN active duplicate). But the new architecture has gone further, and offers capabilities that were not available with the previous architecture. One such capability is the ability to create “snapshot copies” (aka. snapshot clones, sparse copies, sparse clones). This feature is available since 12.1, but we will be talking about 19c as it is currently the long term support/release.

The first thing to keep in mind before we start talking about snapshot copies is that it is a technique that has nothing to do with “PDB snapshots”. A “PDB snapshot” is a time-consistent copy of a database, and has no dependency on it. On the other hand, when we make a PDB “snapshot copy”, we do not make a complete copy of the source database datafiles. Instead, the cloned PDB points to the datafiles of the reference database, and only uses its local storage to make the modifications (this is generically called “thin provisioning” technology). Therefore, although the names “PDB snapshot” and “PDB snapshot copy” are very similar, they perform very different functions, and have no relation to each other.

Another interesting point before going into detail on how a “snapshot copy” works is that, in this particular operation, Oracle does not implement the thin provisioning technology. The RDBMS relies on a technology offered by the storage system or file system to implement the “snapshot clones”. That is, we need a storage system or filesystem that provides this capability. Although we will go into more detail later and specify how it works, we can say that when we execute the SQL command to create the PDB “snapshot copy” from another PDB, the database will identify if the storage system offers this capacity, will communicate with it, and execute all the storage actions required to create the PDB “snapshot copy” in a transparent way.  We simply execute the one SQL command and the rest is done for us.

A priori it may seem that this requirement of the storage system or filesystem can be a limitation somehow. Nothing could be further from the truth, as you are about to witness. Now, this is what happens when we launch a “snapshot copy” PDB operation:

SQL> create pluggable database PDBTHIN from PDB1 snapshot copy;

1. The database checks if the storage system allows storage-managed snapshots (I will clarify later which specific technologies these are). More precisely, it asks the filesystem if it accepts to create storage-managed snapshots. If it does, the snapshot copy will be created right away.
2. If the filesystem does not support storage-managed snapshots, and we have defined the database parameter CLONEDB=TRUE, then the database will understand that we will use the thin provisioning capability of the filesystem itself, and the database will be created transparently (as we will see later, most filesystems nowadays – including NFS – support sparse operations).
3. If neither the storage system nor the filesystem itself supports it, then the SQL command to create snapshot clone will result in an error.

Two clarifications on the above process:

• Note 2^nd point. There is a database parameter that may be necessary to work with snapshot clones. The “CLONEDB” parameter tells the database that we will be relying on the filesystem itself, and not on storage-managed snapshots. The parameter is set to FALSE by default because we want Oracle to try storage-based snapshots first – as they tend to have the highest performance and features. We as DBAs have to explicitly set it to TRUE if we know that storage-based snapshots are not possible. And we should not define it to TRUE if we are going to use storage-based snapshots. Remember: the SQL command is identical in both cases. What changes is the infrastructure dynamics underneath.
• When we make a storage system-based snapshot clone, then both the source database and all its snapshot clones must be in the same system. If, on the other hand, we rely on the filesystem itself, then we will see that we can even have snapshot clones not only in different CDBs but even on different servers. Details below.

We are now ready to make the following clarification: not all storage-managed snapshot technologies work in the same way. Yes, I am aware that we are not storage administrators or system administrators. We cannot cover everything. But it is an important clarification as it affects us. Being very reductionist, we can say that there are two storage-managed snapshot technologies:

• “allocate-on-first-write”: clones will contain the blocks that are modified since the moment the clone was created from the parent database.  Because of this, the “parent” database can only be opened at most in read-only mode. We cannot allow modifications to it, as several snapshot copies may depend on its datafiles. An example storage system using this technology is Exadata’s “Sparse Diskgroups”.
• “copy-on-write”: The clones will actually contain the original blocks – at the time of the clone creation-, while the “parent” database can continue to evolve. Thanks to this, it is possible to modify the parent database without fear of breaking the snapshot copies. An example of a storage system using this technology is ACFS.

In both cases, once the “snapshot copy” is created and opened in read-write mode, the modifications made to each “snapshot copy” will be stored in their own local storage.

Again, although the SQL command to create the snapshot copies is identical in both cases, the behavior we obtain is different. Continuing with the same example we described in the previous post, where we created several “snapshot copies” from a refreshable PDB in order to drastically accelerate the time and effort to refresh a non-productive environment, the behavior of the operation depending on the storage system chosen would be:

A.- Employing Exadata Sparse Diskgroups (allocate-on-first-write example): When we need to refresh the refreshable PDB it will be necessary to delete all the snapshot clones first. However, the snapshot clones will be able to use all the advantages of the exadata cells, including “SMART SCAN”.

B.- Using ACFS (copy-on-write example): We will be able to refresh our refreshable PDB even if there are dependent snapshot clones. Obviously, the changes generated by the refresh of the “refreshable PDB” will occupy additional space.

At this point, we usually try to identify which is the good and which is the bad solution. And as always, it depends on our use case. The sparse diskgroups solution can be really powerful if we want the clone snapshots to have all the offloading capabilities in cell (in exadata cases). The limitation of not being able to modify the parent PDB can be remedied by creating another refreshable PDB in parallel. This secondary refreshable PDB will be refreshing itself while the former refreshable PDB remains frozen and serving it’s snapshot copies. Once we need to recreate the snapshot copies, we will just create then on top of the secondary and mostly refreshed PDB, and let the first one sync up.

The ACFS solution can be incredibly flexible. Building a volume within an ASM diskgroup is a breeze, takes minutes, and is supported on all major platforms. If you’re not on an exadata, you don’t have to worry about smart scanning. But even if you are on an exadata, this solution is quite interesting, as it does not require to have a sparse diskgroup (which honestly is not that common), it is easy to configure, and the performance is similar to ASM (after all, we are working with a volume created inside an actual ASM diskgroup).

As this article is already too long, we will expand on snapshots using ACFS and unix filesystems with detailed steps in the next posts.