8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 18c » Here

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.

Multitenant : PDB Snapshot Carousel

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.

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.

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:

Hope this helps. Regards Tim...

Back to the Top.