PDBs Snapshot Carousel

What is a PDB Snapshot?

A PDB snapshot is a point-in-time copy of a PDB. The source PDB can be open read-only or read/write while the snapshot is created. If the storage system supports sparse clones, then the snapshot will be created as a sparse copy. Otherwise, the it will be created as a full copy.

What is a PDBs Snapshot Carousel?

A PDB Snapshot Carousel is a circular library of copies for a PDB or Application PDB.

The database creates successive copies in the carousel either on demand or automatically. The database overwrites the oldest snapshot when the snapshot limit is reached.

What is the purpose of PDB Snapshot Carousel.

The “PDB snapshot carousel” serves the purpose of providing a mechanism to manage and rotate Pluggable Database (PDB) snapshots efficiently within an Oracle Multitenant environment. This feature allows database administrators to maintain a series of PDB snapshots over time, enabling easy rollback to a specific point in the past if needed, while also managing storage resources effectively.

For instance, imagine a scenario where you have a production PDB and you need to perform a critical data migration task. Before making any changes, you can take a snapshot of the PDB using the snapshot carousel feature. As you progress with the migration, you can periodically take additional snapshots to capture the database state at different stages. If any issues arise during the migration, you can easily roll back to a previous snapshot to restore the database to a known good state, ensuring minimal downtime and data loss.

Enabling Exadata feature

PDBs Snapshot Carousel is an Oracle Exadata feature introduced in Oracle 18c. Assuming your Oracle license includes Exadata, you can activate this feature as follows:

-- Check that Exadata is enabled
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show parameter _exadata_feature_on;
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
_exadata_feature_on		     boolean	 TRUE


-- Otherwise alter system and restart
ALTER SYSTEM SET "_exadata_feature_on"=true SCOPE=spfile;
shutdown immediate;
startup;

Creating and testing snapshot carousel.

Let’s start by creating a testing table and a scheduled procedure in our pluggable database ORCLPDB in order to track changes in each snapshot.

ALTER SESSION SET CONTAINER = ORCLPDB;

-- table time_tbl is created to monitor the snapshots.
CREATE TABLE time_tbl (d timestamp not null, n number);

-- Insert a new record in time_tbl keeping the current time.
CREATE OR REPLACE PROCEDURE insert_time_tbl AS
BEGIN
    INSERT INTO time_tbl (d) VALUES (sysdate);
    COMMIT;
END;
/

-- Schedule the procedure to be execute every 20 seconds.
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'insert_every_20_seconds',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN insert_time_tbl; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'freq=secondly; interval=20',
        enabled         => TRUE
    );
END;
/

-- Check the testing table.
SQL> column d format a30
SQL> select * from time_tbl order by d desc;

D					N
------------------------------ ----------
20-FEB-24 05.56.29.000000 PM
20-FEB-24 05.56.09.000000 PM
20-FEB-24 05.55.49.000000 PM
20-FEB-24 05.55.29.000000 PM
20-FEB-24 05.55.09.000000 PM

Now we can create our carousel and hop onto it to unleash our inner child and play around a bit.

-- CREATE A PDB SNAPSHOT CAROUSEL -----------------------
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB			  READ WRITE NO

SQL> ALTER SESSION SET CONTAINER = ORCLPDB;

-- Create automatically one snapshot every 5 minutes.           
SQL> ALTER PLUGGABLE DATABASE ORCLPDB SNAPSHOT MODE EVERY 5 MINUTES;    

-- Change the snapshot retention policy
ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS = 5;      -- Set max 5 snapshots (the default is 8)

-- To check the retention policy
SQL> select p.pdb_name, property_name, property_value, description
     from CDB_PROPERTIES r, CDB_PDBS p
     where r.con_id = p.con_id and PROPERTY_NAME like 'MAX_PDB%'
     order by property_name;
PDB_NAME   PROPERTY_NAME   PROPERTY_V DESCRIPTION
---------- --------------- ---------- --------------------------------------------------
ORCLPDB    MAX_PDB_SNAPSHO 5	      maximum number of snapshots for a given PDB
	   TS

ORCLPDB    MAX_PDB_STORAGE UNLIMITED  Maximum Space Usage of Datafiles and Local Tempfil
				      es in Container


-- After a while check the existing snapshots.
SQL> select con_name, snapshot_name, snapshot_scn
     from DBA_PDB_SNAPSHOTS
     order by snapshot_scn desc;
CON_NAME   SNAPSHOT_NAME		  SNAPSHOT_SCN
---------- ------------------------------ ------------
ORCLPDB    SNAP_3579782890_1161543103	       4335231
ORCLPDB    SNAP_3579782890_1161542803	       4333702
ORCLPDB    SNAP_3579782890_1161542503	       4331858
ORCLPDB    SNAP_3579782890_1161542203	       4330276
ORCLPDB    SNAP_3579782890_1161541903	       4328669

We can see that a new snapshot is taken every 5 minutes, replacing the oldest one. The table DBA_PDB_SNAPSHOTS also contains the field SNAPSHOT_TIME, which represents the date and time when the snapshot was taken. Unfortunately, this field is in numeric format.

In addition to the automatic snapshots, at any time we can manually force the creation of a new snapshot.

-- Create a new PDB SNAPSHOT on demand.
ALTER PLUGGABLE DATABASE orclpdb SNAPSHOT pdb_manual_snap;

Let’s now try restoring one of these snapshots into a new container.

-- Create PDBs From PDB Snapshots of a Carousel
! mkdir -p /u01/app/oracle/oradata/ORCL/pdb1_from_snap
ALTER SESSION SET CONTAINER=cdb$root;
ALTER SESSION SET db_create_file_dest='/u01/app/oracle/oradata/ORCL/pdb1_from_snap';
CREATE PLUGGABLE DATABASE pdb1_from_snap FROM ORCLPDB  USING SNAPSHOT SNAP_3579782890_1161542503;
Pluggable database created.

-- Check the new PDB from snapshot.
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB			  READ WRITE NO
	 7 PDB1_FROM_SNAP		  MOUNTED
SQL> ALTER PLUGGABLE DATABASE PDB1_FROM_SNAP OPEN;

Pluggable database altered.

SQL> ALTER SESSION SET CONTAINER=PDB1_FROM_SNAP;

Session altered.

SQL> select * from time_tbl order by d desc;

Once checked, let’s drop the PDB, stop the carousel and drop the snapshots. The game is over!

ALTER PLUGGABLE DATABASE PDB1_FROM_SNAP CLOSE;
ALTER SESSION SET CONTAINER=cdb$root;
DROP PLUGGABLE DATABASE pdb1_from_snap INCLUDING DATAFILES;

-- Disable PDB Snapshot Creation  Carousel
ALTER SESSION SET CONTAINER=orclpdb;

-- this command stops the carousel generation but it does not delete the existing snapshots.
ALTER PLUGGABLE DATABASE orclpdb SNAPSHOT MODE NONE; 

-- Dropping a PDB Snapshot
ALTER PLUGGABLE DATABASE DROP SNAPSHOT SNAP_3579782890_1161542503;

• Oracle Database 19c Administrator’s Guide – Administering a PDB Snapshot Carousel