PDB Snapshot Carousel Oracle 18.3

A new feature with Oracle 18c is the PDB snapshot carousel. As indicated by its name a PDB snapshot is a copy of a PDB at a specific point in time. You have the possibility to create up to eight snapshots, when you reach the maximum number of snapshots, the last snapshot is over written. The snapshot carousel is obviously the name of all your PDB snapshots.

We have the possibility to create automatic snapshots using the “snapshot mode every” clause when you create or alter a PDB. For example you can change the snapshot mode from a PDB to every  3 hours:

SQL> alter session set container=pdb;

Session altered.

SQL> select snapshot_mode,snapshot_interval/60 from dba_pdbs;

SNAPSH SNAPSHOT_INTERVAL/60
------ --------------------
MANUAL

SQL> alter pluggable database snapshot mode every 3 hours;

Pluggable database altered.

SQL> select snapshot_mode,snapshot_interval/60 from dba_pdbs;

SNAPSH SNAPSHOT_INTERVAL/60
------ --------------------
AUTO			  3

To return to manual mode, just type:

SQL> alter pluggable database snapshot mode manual;

Pluggable database altered.

We can create PDB snapshots manually, you can use a specific name or not:

SQL> alter pluggable database snapshot pdb_snap;

Pluggable database altered.

SQL> alter pluggable database snapshot;

Pluggable database altered.

We can query the dba_pdb_snapshots view to display the PDB snapshots location:

SQL> SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, 
SNAPSHOT_SCN AS snap_scn, FULL_SNAPSHOT_PATH 
FROM   DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN;

CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN

FULL_SNAPSHOT_PATH

3        PDB	  PDB_SNAP    1155557
/home/oracle/oradata/DB18/pdb/snap_2263384607_1155557.pdb

3        PDB	  SNAP_2263384607_987432172  1155823
/home/oracle/oradata/DB18/pdb/snap_2263384607_1155823.pdb

If you want to drop a snapshot, you have two methods:

You delete the snapshot with the following alter pluggable statement:

SQL> alter pluggable database drop snapshot SNAP_2263384607_987432172;

Pluggable database altered.

Otherwise you set the MAX_PDB_SNAPSHOTS property to zero in the PDB:

You can query the CDB_PROPERTIES and CDB_PDBS to display the parameter value:

SELECT r.CON_ID, p.PDB_NAME, PROPERTY_NAME,
  	PROPERTY_VALUE AS value, DESCRIPTION
  	FROM   CDB_PROPERTIES r, CDB_PDBS p
  	WHERE  r.CON_ID = p.CON_ID
  	AND    PROPERTY_NAME LIKE 'MAX_PDB%'
  	AND    description like 'maximum%'
  	ORDER BY PROPERTY_NAME

CON_ID	PDB_NAME	PROPERTY_NAME	VALUE	           DESCRIPTION
  3		  PDB     MAX_PDB_SNAPSHOTS    8    maximum number of snapshots for a given PDB

And if you set it to zero all your PDB snapshots will be dropped:

SQL> alter session set container=pdb;

Session altered.

SQL> alter pluggable database set max_pdb_snapshots = 0;

Pluggable database altered.

SQL> SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, 
SNAPSHOT_SCN AS snap_scn, FULL_SNAPSHOT_PATH 
FROM   DBA_PDB_SNAPSHOTS
ORDER BY SNAP_SCN;

no rows selected

But the main interest of the snapshot PDBS is to create new PDBS from a productive environment based on a point in time of the production PDB.

So we create a PDB snapshot named PDB_SNAP:

SQL> alter pluggable database snapshot pdb_snap;

Pluggable database altered.

And now we create a PDB from the PDB_SNAP snapshot:

SQL> create pluggable database PDB2 from PDB using snapshot PDB_SNAP create_file_dest='/home/oracle/oradata/DB18/pdb2';

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB		                  READ WRITE NO
	 4 APPPSI			  READ WRITE NO
	 5 PDB2 			  READ WRITE NO

We have also the possibility to change the snapshot mode:

SQL> alter session set container=pdb;

Session altered.

SQL> SELECT SNAPSHOT_MODE "S_MODE", SNAPSHOT_INTERVAL/60 "SNAP_INT_HRS" 
     FROM DBA_PDBS;

S_MODE SNAP_INT_HRS
------ ------------
MANUAL


SQL> ALTER PLUGGABLE DATABASE SNAPSHOT MODE EVERY 1 HOURS;

Pluggable database altered.

SQL> SELECT SNAPSHOT_MODE "S_MODE", SNAPSHOT_INTERVAL/60 "SNAP_INT_HRS" 
     FROM DBA_PDBS;

S_MODE SNAP_INT_HRS
------ ------------
AUTO		  1

We have the possibility to create a PDB that creates snapshots every 15 minutes :

SQL> create pluggable database pdb_new from pdb
  2  file_name_convert=('pdb','pdb_new')
  3  snapshot mode every 15 minutes;

Pluggable database created.

There is a pre requisite for configuring automatic PDB snapshots: the CDB must be in local undo mode.

Finally the snapshots are correctly created in my environment every 15 minutes:

oracle@localhost:/home/oracle/oradata/DB183/pdb/ [DB183] ls -lrt snap*
-rw-r--r--. 1 oracle dba 65690276 Oct  1 15:04 snap_3893567541_798493.pdb
-rw-r--r--. 1 oracle dba 65740202 Oct  1 15:19 snap_3893567541_801189.pdb
-rw-r--r--. 1 oracle dba 65823279 Oct  1 15:34 snap_3893567541_803706.pdb

And to verify if it is correct , I had created in my pdb_new environment a location table in my psi schema with two records at 15H20:

SQL> create table psi.location (name varchar2(10));

Table created.

SQL> insert into psi.location values ('London');

1 row created.

SQL> insert into psi.location values('Paris');

1 row created.

SQL> commit;

And we create a new pdb from the snap to verify if the data are correct:

SQL> create pluggable database pdb_psi from pdb_new 
     using snapshot SNAP_45745043_988386045 
     create_file_dest='/home/oracle/oradata/DB183/pdb_psi';

Pluggable database created.

We open pdb_psi and we check:

SQL> alter session set container=pdb_psi;

Session altered.

SQL> select * from psi.location;

NAME
----------
London
Paris

This feature might be very useful for testing purposes, imagine you have a production PDB, you only have to create a refreshable clone named PDB_MASTER and configure it to create daily snapshots. If you need a PDB for testing you only have to create a clone from any snapshot.

Conclusion

All those tests have been realized on an Linux x86-64 server, with Oracle 18.3 Enterprise Edition. My DB183 database has been initialized with the “_exadata_feature_on”  hidden parameter to avoid the “ORA-12754 Feature PDB Snapshot Carousel is disabled due to missing capability” error message.

If you have a look at the Database Licensing User Manual:

https://docs.oracle.com/en/database/oracle/oracle-database/18/dblic/Licensing-Information.html#GUID-B6113390-9586-46D7-9008-DCC9EDA45AB4

You will see that PDB Carousel (and a lot of interesting new features in Oracle 18.3) are only available for Engineered System or in Cloud and not for Enterprise Edition for third party hardware. I really hope Oracle will change this behavior in the future releases.