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;
Nice post!