In the previous chapter we explained the reasons why the snapshot carousel feature still has significant limitations in Oracle 19c Release. While I particularly think the feature concept is certainly interesting, these limitations will probably prevent us from implementing such architecture.

However, if we could know the SQL commands that snapshot carousel executes – since it is basically just a PDB operations orchestrator -, we would have the possibility to make a similar implementation that avoids the limitations. After all, these limitations does not belong to multitenant, but only to the snapshot carousel related DB code.

Fortunately, our friend the alert log will help us to achieve this, since every time we perform a snapshot carousel operation, all the PDB operations will be fully reflected in it. That is, we can make a small table of equivalence between a given snapshot carousel command, and the SQL commands that are executed in the background in order to implement it.

Let’s take the simplest example, which is the creation of a manual snapshot:

alter session set container=PDB1;
alter pluggable database snapshot MANUALSNAP;

The snapshot creation command translates into the following three SQL commands executed in series:

1. A hot clone of the PDB on which we are creating the snapshot.
2. An unplug of that clone, in “.PDB” format.
3. The “drop” of the PDB to complete the unplug operation.

As we can see, they operations that we can easily replicate. And this is a key point, since we can make the changes we need to adjust the operation to our needs – in the example above, we can unplug directly to an XML file and keep both the datafiles and the manifest file in ASM storage. Moreover, we are not obliged to restore the snapshot in order to use it. We can directly open/consume the PDB in the ASM location where it is located; something that is also not possible with snapshot carousel in 19c. Thanks to these two subtle changes, I now have a consistent copy of my database at a point in time, ready to be opened at any time. I have therefore made the following equivalency/transformation:

However, snapshot carousel offers a complete solution that automates the snapshot lifecycle. This includes deleting the oldest snapshots when creating a new snapshot (manually or automatically). So let’s review what this lifecycle looks like, and how we could implement it manually with simple steps:

Lastly, the procedure to simulate the restore of a snapshot using simple commands. In this last equivalence we have several options on how to manipulate the original PDB :

Depending on the scenario and the objectives we are looking for with the “PDB restore”, we could perform different set of actions on the original PDB.

Finally, I’d like to stress one thing; snapshot carousel – or our manual implementation of it – is not a replacement for Oracle’s canonical backup & recovery techniques. We can see it as a parallel and complementary mechanism that can help to swiftly open a copy of the database at a point in time in the past, and/or to have a catalogue of PDB copies on different moments in time prepared refresh non-productive environments.