8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : PDB Snapshot Carousel in Oracle Database 18c
Oracle 18c introduced the concept of a snapshot carousel, which is a series of point-in-time copies, or snapshots, of a PDB.
- Introduction
- Prerequisites
- Creating a PDB Snapshot Carousel
- Manual Snapshots
- Recovering From a PDB Snapshot
- PDB Snapshot Jobs
- Considerations
- Appendix
Related articles.
Introduction
Oracle 12c allowed creation of PDB clones using storage snapshot copies, but as you can imagine they required specific storage functionality to support these snapshots. The functionality discussed in this article is nothing to do with those snapshot copies, and doesn't rely on storage snapshots.
Oracle 12.2 enhanced the unplug/plugin functionality to allow the creation of PDB archive files, which are a zipped copy of the PDB datafiles along with the PDB description XML file.
In Oracle 18c a PDB snapshot is effectively just a PDB archive file, as described above, but the database remains available during their creation. A PDB snapshot is therefore a point-in-time copy of a PDB, which can be used for recovery purposes, or to produce new PDBs based on that specific point in time. These snapshots can be created manually or automatically. The resulting archive files are placed in the same location as the PDB datafiles, or if Oracle Managed Files (OMF) is used, they are placed in the location specified by the DB_CREATE_FILE_DEST
parameter.
A snapshot carousel is a collection of snapshots for a PDB. The maximum number of snapshots collected is defined by the MAX_PDB_SNAPSHOTS
parameter, which has a default value of 8. Once the maximum number of snapshots is reached, the next snapshot creation will trigger the removal of the oldest snapshot, making it seem like these snapshots are overwritten in a circular manner, hence the term "carousel".
Prerequisites
This feature is currently restricted to Enterprise Edition on Engineered Systems, like Exadata, and Enterprise Edition or above on Oracle Database Cloud Services, as described here. There is a workaround for testing by enabling the "_exadata_feature_on" initialisation parameter.
export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES sqlplus / as sysdba <<EOF alter system set "_exadata_feature_on"=true scope=spfile; shutdown immediate; startup; exit; EOF
If you are using Oracle Managed Files (OMF) you don't need to worry about file name conversion. If you aren't using OMF, all PDB creation statements will require file name conversion using the FILE_NAME_CONVERT
or CREATE_FILE_DEST
settings in the CREATE PLUGGABLE DATABASE
statements.
You are going to need enough space to keep multiple copies of your PDB on disk, so I'm not sure this will be a great option for larger databases.
Creating a PDB Snapshot Carousel
Support for PDB snapshots can be defined during PDB creation as part of the CREATE PLUGGABLE DATABASE
statement, or after PDB creation using the ALTER PLUGGABLE DATABASE
statement. The SNAPSHOT MODE
clause accepts one of the following settings.
NONE
: The PDB does not support snapshots.MANUAL
: The PDB supports snapshots, but they are only created manually requested.EVERY n HOURS
: A snapshot is automatically created every "n" hours. Where "n" is between 1 and 1999.EVERY n MINUTES
: A snapshot is automatically created every "n" minutes. Where "n" is between 1 and 2999.
The following example creates a new PDB, which automatically takes a snapshot every 24 hours. The same approach could be used when creating a clone of an existing PDB.
CONN / AS SYSDBA CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_admin IDENTIFIED BY Password1 SNAPSHOT MODE EVERY 24 HOURS; ALTER PLUGGABLE DATABASE pdb2 OPEN; ALTER PLUGGABLE DATABASE pdb2 SAVE STATE;
I'm using OMF, so there is no need for the FILE_NAME_CONVERT
setting.
The snapshot settings are displayed using the SNAPSHOT_MODE
and SNAPSHOT_INTERVAL
columns in the CDB_PDBS
view. Notice the SNAPSHOT_INTERVAL
values are displayed in minutes. The following query is available as the pdb_snapshot_mode.sql script.
COLUMN pdb_name FORMAT A10 COLUMN snapshot_mode FORMAT A15 SELECT p.con_id, p.pdb_name, p.snapshot_mode, p.snapshot_interval FROM cdb_pdbs p ORDER BY 1; CON_ID PDB_NAME SNAPSHOT_MODE SNAPSHOT_INTERVAL ---------- ---------- --------------- ----------------- 2 PDB$SEED MANUAL 3 PDB1 MANUAL 4 PDB2 AUTO 1440 SQL>
You must be connected to the PDB to alter the snapshot settings. If you attempt to alter the snapshot setting from the root container you will get the following error message.
CONN / AS SYSDBA ALTER PLUGGABLE DATABASE pdb2 SNAPSHOT MODE EVERY 30 MINUTES; * ERROR at line 1: ORA-65118: operation affecting a pluggable database cannot be performed from another pluggable database SQL>
In the following example we alter the snapshot settings using the ALTER PLUGGABLE DATABASE
command.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb2; ALTER PLUGGABLE DATABASE SNAPSHOT MODE EVERY 2999 MINUTES; @pdb_snapshot_mode CON_ID PDB_NAME SNAPSHOT_MODE SNAPSHOT_INTERVAL ---------- ---------- --------------- ----------------- 4 PDB2 AUTO 2999 SQL> ALTER PLUGGABLE DATABASE SNAPSHOT MODE EVERY 1999 HOURS; @pdb_snapshot_mode CON_ID PDB_NAME SNAPSHOT_MODE SNAPSHOT_INTERVAL ---------- ---------- --------------- ----------------- 4 PDB2 AUTO 119940 SQL> ALTER PLUGGABLE DATABASE SNAPSHOT MODE MANUAL; @pdb_snapshot_mode CON_ID PDB_NAME SNAPSHOT_MODE SNAPSHOT_INTERVAL ---------- ---------- --------------- ----------------- 4 PDB2 MANUAL SQL> ALTER PLUGGABLE DATABASE SNAPSHOT MODE NONE; @pdb_snapshot_mode CON_ID PDB_NAME SNAPSHOT_MODE SNAPSHOT_INTERVAL ---------- ---------- --------------- ----------------- 4 PDB2 NONE SQL> ALTER PLUGGABLE DATABASE SNAPSHOT MODE EVERY 24 HOURS; @pdb_snapshot_mode CON_ID PDB_NAME SNAPSHOT_MODE SNAPSHOT_INTERVAL ---------- ---------- --------------- ----------------- 4 PDB2 AUTO 1440 SQL>
The current MAX_PDB_SNAPSHOTS
parameter value is displayed using the CDB_PROPERTIES
view. The following query is available as the max_pdb_snapshots.sql script.
SET LINESIZE 150 TAB OFF COLUMN property_name FORMAT A20 COLUMN pdb_name FORMAT A10 COLUMN property_value FORMAT A15 COLUMN description FORMAT A50 SELECT pr.con_id, p.pdb_name, pr.property_name, pr.property_value, pr.description FROM cdb_properties pr JOIN cdb_pdbs p ON pr.con_id = p.con_id WHERE pr.property_name = 'MAX_PDB_SNAPSHOTS' ORDER BY pr.property_name; CON_ID PDB_NAME PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ---------- ---------- -------------------- --------------- -------------------------------------------------- 4 PDB2 MAX_PDB_SNAPSHOTS 8 maximum number of snapshots for a given PDB SQL>
You must be connected to the PDB to alter the MAX_PDB_SNAPSHOTS
parameter. If you attempt to alter the snapshot setting from the root container you will get the following error message.
CONN /AS SYSDBA ALTER PLUGGABLE DATABASE pdb2 SET MAX_PDB_SNAPSHOTS=4; * ERROR at line 1: ORA-65046: operation not allowed from outside a pluggable database SQL>
Once connected to the PDB we can set the MAX_PDB_SNAPSHOTS
parameter to a value between 0 and 8, with 0 causing all existing snapshots to be removed.
CONN /AS SYSDBA ALTER SESSION SET CONTAINER = pdb2; -- Value 0 removes all existing snapshots for the PDB. ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=0; @max_pdb_snapshots CON_ID PDB_NAME PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ---------- ---------- -------------------- --------------- -------------------------------------------------- 4 PDB2 MAX_PDB_SNAPSHOTS 0 maximum number of snapshots for a given PDB SQL> ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=8; @max_pdb_snapshots CON_ID PDB_NAME PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ---------- ---------- -------------------- --------------- -------------------------------------------------- 4 PDB2 MAX_PDB_SNAPSHOTS 8 maximum number of snapshots for a given PDB SQL>
Manual Snapshots
Provided the SNAPSHOT_MODE
is not set to NONE, we can create manual snapshots using the ALTER PLUGGABLE DATABASE SNAPSHOT
command when connected to the PDB. Running the command from the root container results in an error.
CONN / AS SYSDBA ALTER PLUGGABLE DATABASE pdb2 SNAPSHOT; * ERROR at line 1: ORA-65118: operation affecting a pluggable database cannot be performed from another pluggable database SQL>
The following example creates a manual snapshot with a system generated name, and a manual snapshot with a user-defined snapshot name.
-- Connect to the PDB. CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb2; -- System generated snapshot name. ALTER PLUGGABLE DATABASE SNAPSHOT; -- User-defined snapshot name. ALTER PLUGGABLE DATABASE SNAPSHOT my_snapshot;
Information about the available snapshots is displayed using the CDB_PDB_SNAPSHOTS
view. The following query is available as the pdb_snapshots.sql script.
SET LINESIZE 150 TAB OFF COLUMN con_name FORMAT A10 COLUMN snapshot_name FORMAT A30 COLUMN snapshot_scn FORMAT 9999999 COLUMN full_snapshot_path FORMAT A50 SELECT con_id, con_name, snapshot_name, snapshot_scn, full_snapshot_path FROM cdb_pdb_snapshots ORDER BY con_id, snapshot_scn; CON_ID CON_NAME SNAPSHOT_NAME SNAPSHOT_SCN FULL_SNAPSHOT_PATH ---------- ---------- ------------------------------ ------------ -------------------------------------------------- 4 PDB2 SNAP_688979926_996491289 1764864 /u02/oradata/snap_688979926_1764864.pdb 4 PDB2 MY_SNAPSHOT 1765251 /u02/oradata/snap_688979926_1765251.pdb SQL>
The example below shows how to manually drop snapshots. It works equally well for snapshots with user-defined or system generated names.
-- Connect to the PDB. CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb2; -- System generated snapshot name. ALTER PLUGGABLE DATABASE DROP SNAPSHOT SNAP_688979926_996491289; -- User-defined snapshot name. ALTER PLUGGABLE DATABASE DROP SNAPSHOT my_snapshot; @pdb_snapshots no rows selected SQL>
We'll make sure there is at least one snapshot available again.
-- Connect to the PDB. CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb2; -- System generated snapshot name. ALTER PLUGGABLE DATABASE SNAPSHOT; @pdb_snapshots CON_ID CON_NAME SNAPSHOT_NAME SNAPSHOT_SCN FULL_SNAPSHOT_PATH ---------- ---------- ------------------------------ ------------ -------------------------------------------------- 4 PDB2 SNAP_688979926_996491631 1765754 /u02/oradata/snap_688979926_1765754.pdb SQL>
Recovering From a PDB Snapshot
Some references suggest you can drop a PDB and recover it from a snapshot, but all snapshots are deleted when the PDB is dropped, so you can't do this directly. You can however create a new PDB from snapshot.
CONN / AS SYSDBA ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u02/oradata'; CREATE PLUGGABLE DATABASE pdb2_copy FROM pdb2 USING SNAPSHOT SNAP_688979926_996491631 SNAPSHOT MODE EVERY 24 HOURS; ALTER PLUGGABLE DATABASE pdb2_copy OPEN; @pdb_snapshot_mode CON_ID PDB_NAME SNAPSHOT_MODE SNAPSHOT_INTERVAL ---------- ---------- --------------- ----------------- 2 PDB$SEED MANUAL 3 PDB1 MANUAL 4 PDB2 AUTO 1440 7 PDB2_COPY AUTO 1440 SQL>
Notice, we not only created the new pluggable database from snapshot, but we also set it to have its own snapshot carousel.
Let's remove the copy PDB.
CONN / AS SYSDBA ALTER PLUGGABLE DATABASE pdb2_copy CLOSE; DROP PLUGGABLE DATABASE pdb2_copy INCLUDING DATAFILES;
PDB Snapshot Jobs
Automatic PDB snapshots are managed using scheduled jobs, whose names include the PDB name and the word "SNAPSHOT". We can see
CONN / AS SYSDBA SET LINESIZE 200 COLUMN owner FORMAT A10 COLUMN job_name FORMAT A30 COLUMN repeat_interval FORMAT A50 SELECT owner, job_name, repeat_interval FROM dba_scheduler_jobs WHERE job_name LIKE '%SNAPSHOT' ORDER BY owner, job_name; OWNER JOB_NAME REPEAT_INTERVAL ---------- ------------------------------ -------------------------------------------------- SYS PDB2_688979926_SNAPSHOT FREQ = HOURLY; INTERVAL = 24 SQL>
We can amend the schedule to make it more precise. I don't know if this is supported, but it definitely works.
BEGIN DBMS_SCHEDULER.set_attribute ( name => 'PDB2_688979926_SNAPSHOT', attribute => 'REPEAT_INTERVAL', value => 'FREQ=HOURLY; INTERVAL=24; BYMINUTE=15;'); END; / SELECT owner, job_name, repeat_interval FROM dba_scheduler_jobs WHERE job_name LIKE '%SNAPSHOT' ORDER BY owner, job_name; OWNER JOB_NAME REPEAT_INTERVAL ---------- ------------------------------ -------------------------------------------------- SYS PDB2_688979926_SNAPSHOT FREQ=HOURLY; INTERVAL=24; BYMINUTE=15; SQL>
We can also check out the job action to see what the job is actually doing. I've neatened up the output a little.
COLUMN job_action FORMAT A75 SELECT job_action FROM dba_scheduler_jobs WHERE job_name = 'PDB2_688979926_SNAPSHOT'; JOB_ACTION --------------------------------------------------------------------------- declare cur integer := sys.dbms_sql.open_cursor(security_level => 2); begin sys.dbms_sql.parse(c => cur, statement => 'alter pluggable database snapshot', language_flag => sys.dbms_sql.native, container => 'PDB2'); sys.dbms_sql.close_cursor(c=>cur); end; SQL>
We can see the job is using DBMS_SQL.PARSE
to run the snapshot command locally in the PDB, just like we can do manually.
Considerations
There are a number of things to consider when using the snapshot carousel.
- As mentioned previously, this feature is currently restricted to Enterprise Edition on Engineered Systems, like Exadata, and Enterprise Edition or above on Oracle Database Cloud Services, as described here.
- A PDB archive is zip of the PDB data files. You have to consider the impact of these archive operations running on your database server. If your server is already under high load, adding these operations into the mix may not be a great idea.
- The archives take up disk space. You will need to factor that into your calculation about what snapshot settings are appropriate.
- There is no control over the location of the PDB archives. If you are using OMF they are placed in the location specified by the
DB_CREATE_FILE_DEST
parameter. Otherwise they are placed in the same directory as the PDB data files. It would be nice if a separate location parameter were available. - There is no direct way to control when the snapshots are scheduled. The schedule is based on the time the snapshot settings were set.
- This functionality can be used with application PDBs under application containers.
Appendix
The container database instance was built using the following commands. The command to remove the instance is included, so you can clean up when you are finished.
# Empty container (cdb1). dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \ -characterSet AL32UTF8 \ -sysPassword SysPassword1 \ -systemPassword SysPassword1 \ -createAsContainerDatabase true \ -numberOfPDBs 1 \ -pdbName pdb1 \ -pdbAdminPassword SysPassword1 \ -databaseType MULTIPURPOSE \ -memoryMgmtType auto_sga \ -totalMemory 2048 \ -storageType FS \ -datafileDestination "/u02/oradata/" \ -redoLogFileSize 50 \ -emConfiguration NONE \ -ignorePreReqs export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES # Set required parameters. sqlplus / as sysdba <<EOF alter system set db_create_file_dest = '/u02/oradata'; alter system set "_exadata_feature_on"=true scope=spfile; shutdown immediate; startup; exit; EOF # Delete the instances. #dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1
For more information see:
- Administering a PDB Snapshot Carousel
- Multitenant : PDB Snapshot Carousel
- Multitenant : All Articles
Hope this helps. Regards Tim...