Administering a PDB Snapshot Carousel

16 Administering a PDB Snapshot Carousel

You can configure a PDB snapshot carousel for a specified PDB, create snapshots manually or automatically, and set the maximum number of snapshots.

This section contains the following topics:

About PDB Snapshot Carousel
A PDB snapshot is a point-in-time copy of a PDB. You can create snapshots manually using the SNAPSHOT clause of CREATE PLUGGABLE DATABASE (or ALTER PLUGGABLE DATABASE), or automatically using the EVERY interval clause. A PDB snapshot carousel is a library of PDB snapshots.
Setting the Maximum Number of Snapshots in a PDB Snapshot Carousel
You can set the maximum number of PDB snapshots for a PDB.
Configuring Automatic PDB Snapshots
Configure a PDB for automatic snapshots by using the SNAPSHOT MODE EVERY clause when creating or altering a PDB.
Creating PDB Snapshots Manually
To create a PDB snapshot manually, specify the SNAPSHOT snapshot_name clause in ALTER PLUGGABLE DATABASE or CREATE PLUGGABLE DATABASE.
Dropping a PDB Snapshot
You can drop a PDB snapshot by running an ALTER PLUGGABLE DATABASE statement with the DROP SNAPSHOT clause.

Parent topic: Administering a Multitenant Environment

About PDB Snapshot Carousel

A PDB snapshot is a point-in-time copy of a PDB. You can create snapshots manually using the SNAPSHOT clause of CREATE PLUGGABLE DATABASE (or ALTER PLUGGABLE DATABASE), or automatically using the EVERY interval clause. A PDB snapshot carousel is a library of PDB snapshots.

This section contains the following topics:

Purpose of PDB Snapshot Carousel
A PDB snapshot carousel is a useful way of maintaining a library of recent PDB copies for point-in-time recovery and cloning.
How PDB Snapshot Carousel Works
The carousel for a specific PDB is a circular library of copies for this PDB.
User Interface for PDB Snapshot Carousel
The SNAPSHOT MODE clause controls creation of snapshots, and determines whether creation is manual, automatic, or disabled.

Parent topic: Administering a PDB Snapshot Carousel

Purpose of PDB Snapshot Carousel

A PDB snapshot carousel is a useful way of maintaining a library of recent PDB copies for point-in-time recovery and cloning.

Cloning PDBs for Development and Testing

In a typical development use case, you clone a production PDB for testing. When the CDB is in ARCHIVELOG mode and local undo mode, the source production PDB can be opened in read/write mode and fully functional during the operation. This technique is known as hot cloning. The hot clone is transactionally consistent with the source PDB as of the SCN at the completion of the ALTER PLUGGABLE DATABASE ... OPEN statement.

For example, while the production PDB named pdb1_prod is open and in use, you create a refreshable clone named pdb1_test_master. You then configure pdb1_test_master to create automatic snapshots every day. When you need new PDBs for testing, create a full clone of any snapshot, and then create sparse clones using CREATE PLUGGABLE DATABASE ... SNAPSHOT COPY.

The following figure shows the creation of the clone pdb1_test_full1 from the PDB snapshot taken on April 5. The figure shows three snapshot copy PDBs created from pdb1_test_full1.

Figure 16-1 Automatic Snapshots of a Refreshable Clone PDB

Description of "Figure 16-1 Automatic Snapshots of a Refreshable Clone PDB"

Point-in-Time Restore with PDB Snapshot Carousel

A typical strategy is to take a snapshot of a PDB every day at the same time. Another strategy is to take a snapshot manually before data loads. In either case, a PDB snapshot carousel enables you to restore a PDB using any available snapshot.

For example, a sales history PDB named pdb1_prod generates an automatic snapshot every day at 12:01 a.m. On the daily data load on the afternoon of Monday 4/9, you accidentally load the wrong data, corrupting the PDB. You can create a new production PDB based on the Monday 4/9 snapshot, drop the corrupted PDB, and then retry the data load.

Figure 16-2 Restore a Production PDB Using a Snapshot

Description of "Figure 16-2 Restore a Production PDB Using a Snapshot"

See Also:

"About Cloning a PDB"
Oracle Database SQL Language Reference for CREATE PLUGGABLE DATABASE syntax and semantics

Parent topic: About PDB Snapshot Carousel

How PDB Snapshot Carousel Works

The carousel for a specific PDB is a circular library of copies for this 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.

This section includes the following topics:

Contents of a PDB Snapshot
The data files of the PDB snapshot reside in an archive file on the file system.
Contents of a PDB Snapshot Carousel
The PDB snapshot carousel is the set of all existing snapshots for a PDB.

Parent topic: About PDB Snapshot Carousel

Contents of a PDB Snapshot

The data files of the PDB snapshot reside in an archive file on the file system.

No archived redo log files are included. Typically, the archive is stored in the same directory as the data files for the PDB. For system-generated snapshot names, snap_ is prefixed to a unique identifier, which contains the snapshot SCN. The archive has the file extension .pdb.

The database takes a snapshot using an implicit USING SNAPSHOT clause. You cannot specify the SNAPSHOT COPY clause when taking a snapshot with CREATE PLUGGABLE DATABASE SNAPSHOT.

Note:

A PDB snapshot created with the USING SNAPSHOT clause and a snapshot copy PDB created with the SNAPSHOT COPY clause are two distinct types of snapshots. The USING SNAPSHOT clause creates a full PDB that does not need to be materialized. The SNAPSHOT COPY clause creates a sparse PDB that must be materialized if you want to drop the storage snapshot on which it is based.

Although a snapshot in a PDB snapshot carousel is always a full PDB, you can take a snapshot copy of a snapshot in the carousel. In this case, the snapshot copy is a sparse clone.

Parent topic: How PDB Snapshot Carousel Works

Contents of a PDB Snapshot Carousel

The PDB snapshot carousel is the set of all existing snapshots for a PDB.

The MAX_PDB_SNAPSHOTS property specifies the maximum number of snapshots permitted in the carousel. The current setting is visible in the CDB_PROPERTIES view.

The following figure shows a carousel for cdb1_pdb1. In this example, the database takes a PDB snapshot automatically every day, maintaining a set of 8. After the first 8 snapshots have been created, every new snapshot replaces the oldest snapshot. For example, the Tuesday 4/10 snapshot replaces the Monday 4/2 snapshot; the Wednesday 4/11 snapshot replaces the Tuesday 4/3 snapshot; and so on.

Figure 16-3 PDB Snapshot Carousel

Description of "Figure 16-3 PDB Snapshot Carousel"

Starting Oracle Database 19c, all PDB snapshots in the carousel except the first one can be sparse, and the source PDB can remain in read/write mode. This feature significantly reduces the storage space consumed by the carousel.

See Also:

Oracle Database Licensing Information User Manual for details on which features are supported for different editions and services

Parent topic: How PDB Snapshot Carousel Works

User Interface for PDB Snapshot Carousel

The SNAPSHOT MODE clause controls creation of snapshots, and determines whether creation is manual, automatic, or disabled.

CREATE PLUGGABLE DATABASE Statement

To set the snapshot mode for a PDB, use one of the following values in the SNAPSHOT MODE clause of ALTER PLUGGABLE DATABASE or CREATE PLUGGABLE DATABASE:

MANUAL

This clause, which is the default, enables the creation of manual snapshots of the PDB. To create a snapshot on demand, specify the SNAPSHOT snapshot_name clause in an ALTER PLUGGABLE DATABASE or CREATE PLUGGABLE DATABASE statement.
EVERY snapshot_interval [MINUTES|HOURS]

This clause enables the automatic creation of snapshots after an interval of time. The following restrictions apply to the interval specified:
- The minutes value must be less than 3000.
- The hours value must be less than 2000.
The database assigns each automatic snapshot a system-generated name. Note that manual snapshots are also supported for the PDB when EVERY is specified.
NONE

This clause disables snapshot creation for the PDB.

See Also:

"About Cloning PDBs from PDB Snapshots"
Oracle Database SQL Language Reference for the syntax and semantics of the SNAPSHOT clause

MAX_PDB_SNAPSHOTS Database Property

To set the maximum number of snapshots for a PDB, specify the MAX_PDB_SNAPSHOTS property in ALTER PLUGGABLE DATABASE or CREATE PLUGGABLE DATABASE. The default is for the property is 8, which is also the maximum value. When the maximum allowed number of snapshots has been created, the database purges the oldest snapshot. The CDB_PROPERTIES view shows the setting of MAX_PDB_SNAPSHOTS.

See Also:

Oracle Database SQL Language Reference for the syntax of the ALTER PLUGGABLE DATABASE statement

DBA_PDB_SNAPSHOTS and DBA_PDBS

The following data dictionary views provide snapshot information:

The DBA_PDB_SNAPSHOTS view metadata about the snapshots, including name, creation SCN, creation time, and file name.
The DBA_PDBS view has a SNAPSHOT_MODE and SNAPSHOT_INTERVAL column.

See Also:

Oracle Database Reference to learn about DBA_PDB_SNAPSHOTS and DBA_PDBS

Parent topic: About PDB Snapshot Carousel

Setting the Maximum Number of Snapshots in a PDB Snapshot Carousel

You can set the maximum number of PDB snapshots for a PDB.

The MAX_PDB_SNAPSHOTS database property sets the maximum number of snapshots for every PDB in a PDB snapshot carousel. The default maximum is 8. You cannot set the property to a number greater than 8.

Prerequisites

The PDB must be open in read/write mode.

To set the maximum number of PDB snapshots for a PDB:

In SQL*Plus, ensure that the current container is the PDB for which you want to set the limit.
Optionally, query CDB_PROPERTIES for the current setting of the SET MAX_PDB_SNAPSHOTS property.
Run an ALTER PLUGGABLE DATABASE or ALTER DATABASE statement with the SET MAX_PDB_SNAPSHOTS clause.

Example 16-1 Setting the Maximum Number of PDB Snapshots for a PDB

The following query shows the maximum in the carousel for cdb1_pdb1 (sample output included):

SET LINESIZE 150
COL CON_ID FORMAT 99999
COL PROPERTY_NAME FORMAT a17
COL PDB_NAME FORMAT a9
COL VALUE FORMAT a3
COL DESCRIPTION FORMAT a43

SELECT r.CON_ID, p.PDB_NAME, PROPERTY_NAME, 
       PROPERTY_VALUE AS 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;

CON_ID PDB_NAME  PROPERTY_NAME     VAL DESCRIPTION
------ --------- ----------------- --- -------------------------------------------
     3 CDB1_PDB1 MAX_PDB_SNAPSHOTS 8   maximum number of snapshots for a given PDB

The following SQL statement sets the maximum number of PDB snapshots for the current PDB to 7:

ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=7;

Example 16-2 Dropping All Snapshots in a PDB Snapshot Carousel

To drop all snapshots in a PDB snapshot carousel, set the MAX_PDB_SNAPSHOTS database property to 0 (zero), as shown in the following statement:

ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=0;

This technique is faster than executing ALTER PLUGGABLE DATABASE ... DROP SNAPSHOT snapshot_name for every snapshot.

See Also:

"About Container Access in a CDB"

Parent topic: Administering a PDB Snapshot Carousel

Configuring Automatic PDB Snapshots

Configure a PDB for automatic snapshots by using the SNAPSHOT MODE EVERY clause when creating or altering a PDB.

By default, a PDB is configured for manual snapshots.

Prerequisites

Note the following prerequisites for the ALTER PLUGGABLE DATABASE SNAPSHOT statement:

The CDB must be in local undo mode.
The administrator must have the privileges to create a PDB and drop a PDB.

To configure automatic snapshots when altering a PDB:

In SQL*Plus, log in as an administrator to the PDB whose snapshot mode you intend to configure.
Optionally, query DBA_PDBS to determine the current snapshot mode.
Run ALTER PLUGGABLE DATABASE with the SNAPSHOT MODE EVERY interval clause, specifying either MINUTES or HOURS.

To configure automatic snapshots when creating a PDB:

In SQL*Plus, log in as an administrator to the CDB root or application root.
Optionally, query DBA_PDBS to determine the current snapshot mode.
Run CREATE PLUGGABLE DATABASE with the SNAPSHOT MODE EVERY interval clause, specifying either MINUTES or HOURS.

Example 16-3 Configuring an Automatic Snapshot Every Day for an Existing PDB

This example assumes that you are logged in to the PDB whose snapshot mode you intend to change. Query the data dictionary to confirm that the PDB is currently in MANUAL mode (sample output included):

SELECT SNAPSHOT_MODE "S_MODE", SNAPSHOT_INTERVAL/60 "SNAP_INT_HRS" FROM DBA_PDBS;

S_MODE SNAP_INT_HRS
------ ------------
MANUAL

Change the snapshot mode to every 24 hours:

ALTER PLUGGABLE DATABASE SNAPSHOT MODE EVERY 24 HOURS;

Confirm the change to automatic mode:

SELECT SNAPSHOT_MODE "S_MODE", SNAPSHOT_INTERVAL/60 "SNAP_INT_HRS" FROM DBA_PDBS;

S_MODE SNAP_INT_HRS
------ ------------
AUTO             24

Example 16-4 Creating a PDB That Takes Snapshots Every Two Hours

This example assumes that you are logged in to the CDB root. The following statement creates cdb1_pdb3 from an existing PDB named cdb1_pdb1, and configures it to take snapshots automatically every 2 hours:

CREATE PLUGGABLE DATABASE cdb1_pdb3 FROM cdb1_pdb1
  FILE_NAME_CONVERT=('cdb1_pdb1','cdb1_pdb3')
  SNAPSHOT MODE EVERY 120 MINUTES;

See Also:

Parent topic: Administering a PDB Snapshot Carousel

Creating PDB Snapshots Manually

To create a PDB snapshot manually, specify the SNAPSHOT snapshot_name clause in ALTER PLUGGABLE DATABASE or CREATE PLUGGABLE DATABASE.

Prerequisites

Note the following prerequisites for the ALTER PLUGGABLE DATABASE SNAPSHOT statement:

The CDB must be in local undo mode. You can check the mode by using the following query, which returns TRUE when local undo is enabled:
```
SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='LOCAL_UNDO_ENABLED';
```
The database administrator must have the privileges to create a PDB and drop a PDB.

To create a PDB snapshot:

In SQL*Plus, log in as an administrator to the PDB whose snapshot you intend to create.
Optionally, query DBA_PDBS.SNAPSHOT_MODE to confirm that the snapshot mode is not set to NONE.
Run an ALTER PLUGGABLE DATABASE statement with the SNAPSHOT clause.

Example 16-5 Creating a Snapshot with a User-Specified Name

The following SQL statements create two PDB snapshots of cdb1_pdb1, one before and one after a Wednesday data load:

ALTER PLUGGABLE DATABASE SNAPSHOT cdb1_pdb1_b4wedload;
-- data load
ALTER PLUGGABLE DATABASE SNAPSHOT cdb1_pdb1_afwedload;

The following query of DBA_PDB_SNAPSHOTS shows the locations of two snapshots of the PDB named cdb1_pdb1 (sample output included):

SET LINESIZE 150
COL CON_NAME FORMAT a10
COL SNAPSHOT_NAME FORMAT a20
COL SNAP_SCN FORMAT 9999999
COL FULL_SNAPSHOT_PATH FORMAT a45

SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, 
       SNAPSHOT_SCN AS snap_scn, FULL_SNAPSHOT_PATH 
FROM   DBA_PDB_SNAPSHOTS
ORDER BY SNAP_SCN;

 CON_ID CON_NAME   SNAPSHOT_NAME        SNAP_SCN FULL_SNAPSHOT_PATH
------- ---------- -------------------- -------- ---------------------------------------------
      3 CDB1_PDB1  CDB1_PDB1_B4WEDLOAD   2962078 /disk1/oracle/dbs/snap_3489077498_2962078.pdb
      3 CDB1_PDB1  CDB1_PDB1_AFWEDLOAD   2962938 /disk1/oracle/dbs/snap_3489077498_2962938.pdb

If you do not specify a PDB snapshot name, then the database generates a unique name.

Example 16-6 Creating a Snapshot with a System-Specified Name

The following SQL statement creates a snapshot, but does not specify a name:

ALTER PLUGGABLE DATABASE SNAPSHOT;

The following sample query shows that the database assigned the snapshot a name prefixed with SNAP_:

SET LINESIZE 150
COL CON_NAME FORMAT a10
COL SNAPSHOT_NAME FORMAT a25
COL SNAP_SCN FORMAT 9999999
COL FULL_SNAPSHOT_PATH FORMAT a45

SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, 
       SNAPSHOT_SCN AS snap_scn, FULL_SNAPSHOT_PATH 
FROM   DBA_PDB_SNAPSHOTS
ORDER BY SNAP_SCN;

 CON_ID CON_NAME   SNAPSHOT_NAME             SNAP_SCN FULL_SNAPSHOT_PATH
------- ---------- ------------------------- -------- ---------------------------------------------
      3 CDB1_PDB1  CDB1_PDB1_B4WEDLOAD        2962078 /disk1/oracle/dbs/snap_3489077498_2962078.pdb
      3 CDB1_PDB1  CDB1_PDB1_AFWEDLOAD        2962938 /disk1/oracle/dbs/snap_3489077498_2962938.pdb
      3 CDB1_PDB1  SNAP_3489077498_960130367  2993525 /disk1/oracle/dbs/snap_3489077498_2993525.pdb

See Also:

Parent topic: Administering a PDB Snapshot Carousel

Dropping a PDB Snapshot

You can drop a PDB snapshot by running an ALTER PLUGGABLE DATABASE statement with the DROP SNAPSHOT clause.

To drop all PDB snapshots based on a PDB, set the MAX_PDB_SNAPSHOTS property in the PDB to 0 (zero).

To drop a PDB snapshot:

In SQL*Plus, ensure that the current container is the PDB from which you created the PDB snapshot.
Run an ALTER PLUGGABLE DATABASE statement with the DROP SNAPSHOT clause.

Example 16-7 Dropping a PDB Snapshot

The following SQL statement drops a PDB snapshot named sales_snap:

ALTER PLUGGABLE DATABASE DROP SNAPSHOT sales_snap;

See Also:

"About Container Access in a CDB"

Parent topic: Administering a PDB Snapshot Carousel