Create a PDB snapshot carousel

Introduction

This feature is introduced in 18c and allows you to make instant snapshot copies of the Pluggable Database (PDB) and keep them in an external binnacle. A PDB snapshot is an archive file (.pdb) compromising of a a copy of PDB snapshot content. You can have a maximum of 8 snapshots per PDB. On reaching the limit, older copies will be overwritten as new instant copies are required.

• Snapshots differ from copy PDB snapshots, which are sparse copies.
• Snapshots will always depend on the CDB where they are hosted.
• You cannot create the snapshot from the root container. You must be connected to the PDB itself
• The database does not HAVE to running in archive log mode.

Example 1 create a manual snapshot and create a new PDB from that snapshot

SQL> select property_value from database_properties where property_name = 'MAX_PDB_SNAPSHOTS';

PROPERTY_VALUE
--------------------------------------------------------------------------------
8

SQL> alter pluggable database pdb1 snapshot pdb1_first_snap;

Pluggable database altered.

SQL>  select con_name, snapshot_name, snapshot_scn, snapshot_time, full_snapshot_path from cdb_pdb_snapshots;

CON_NAME SNAPSHOT_NAME   SNAPSHOT_SCN SNAPSHOT_TIME FULL_SNAPSHOT_PATH
---------- ------------------------------ ------------- ---------------------
PDB1     PDB1_FIRST_SNAP  15656372    1602932322 /u01/app/oracle/oradata/snap_3499944333_15656372.pdb

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/app/oracle/oradata

SQL> alter session set container=cdb$root;

Session altered.

SQL> create pluggable database snap1_pdb1 from pdb1 using snapshot pdb1_first_snap;

Pluggable database created.

Example 2 create an automatic PDB snapshot carousel

SQL> create pluggable database pdbsnap admin user pdbadmin identified by pdbadmin roles=(DBA) snapshot mode every 30 minutes;

SQL> alter pluggable databsase pdbsnap open;

SQL> select pdb_name, snapshot_mode from cdb_pdbs where snapshot_mode = 'AUTO';

PDB_NAME             SNAPSH
-------------------- ------
PDBSNAP              AUTO

--you can view the snapshots as follows
SQL> select con_name, snapshot_name, snapshot_scn, snapshot_time, full_snapshot_path  from cdb_pdb_snapshots;

The PDB snapshots are implemented as dbms_scheduler jobs:

SQL> select job_name, state, to_char(last_start_date,'DD-MON-YYYY HH24:MI:SS') last_start_date, 
to_char(next_run_date,'DD-MON-YYYY HH24:MI:SS') next_run_date, comments 
from DBA_SCHEDULER_JOBS where comments like '%SNAP%' order by state desc; 

JOB_NAME                    STATE      LAST_START_DATE       NEXT_RUN_DATE         COMMENTS
--------------------------- --------------- ----------------------------- -----------------------
PDBSNAP_1114628458_SNAPSHOT SCHEDULED  17-OCT-2020 16:29:52  17-OCT-2020 16:59:52  PDBSNAP snapshot