Creating and Configuring a PDB

A CDB supports multiple techniques for creating PDBs.

The created PDB automatically includes a full data dictionary including metadata and internal links to system-supplied objects in the CDB root. You must define every PDB from a single root: either the CDB root or an application root.

Each PDB has a globally unique identifier (GUID). The PDB GUID is primarily used to generate names for directories that store the PDB’s files, including both Oracle Managed Files directories and non-Oracle Managed Files directories.

Methods for Creating a PDB

You can create a PDB with various techniques, all of which require the CREATE PLUGGABLE DATABASE statement.

Creating a PDB is the process of associating it with a CDB or an application container.

The following table describes the creation techniques. An additional technique, which is not covered in this manual, is to use the DUPLICATE command in Recovery Manager to copy a PDB from one CDB to another CDB.

Techniques for Creating a PDB

TechniqueDescription
Create a PDB from scratchCreate a PDB in a CDB using the files of the PDB seed or application seed. This technique copies the files associated with the seed to a new location and associates the copied files with the new PDB. This is the default creation mechanism. The other techniques require either a source database (PDB or non-CDB) or XML.
Clone an existing PDB or non-CDBCreate a PDB by cloning a source PDB or non-CDB. A source can be a PDB in the local CDB, a PDB in a remote CDB, a PDB in a local or remote application container, or a non-CDB. This technique copies the files associated with the source to a new location and associates the copied files with the new PDB.
Relocate a PDB to a different CDBCreate a PDB by relocating it from one CDB to another. This technique moves the files associated with the PDB to a new location.
Plug an unplugged PDB into a CDBCreate a PDB by using the XML metadata file that describes the PDB and the files associated with the PDB to plug it into the CDB.
Reference a PDB as a proxy PDBCreate a PDB as a proxy PDB by referencing a different PDB with a database link. The referenced PDB can be in the same CDB as the proxy PDB, or it can be in a different CDB.
Create a PDB from a non-CDB, and then plug the PDB into a CDBCreate a PDB by adopting a non-CDB into a PDB. You can use the DBMS_PDB package to create an unplugged PDB from an Oracle Database 12c non-CDB. You can then plug the unplugged PDB into the CDB.

You can unplug a PDB when you want to plug it into a different CDB. You can unplug or drop a PDB when you no longer need it. An unplugged PDB is not usable until it is plugged into a CDB.

Current Container and PDB Creation

You can use the CREATE PLUGGABLE DATABASE statement to create PDBs, application containers, application seeds, and application PDBs.

When you create a PDB, the current container—CDB root or application root—determines the association of the PDB. The SQL statements that create PDBs and application PDBs are the same. For example, when you run CREATE PLUGGABLE DATABASE statement in the CDB root, the PDB belongs to the CDB root. When you run CREATE PLUGGABLE DATABASE statement in an application root, the application PDB belongs to the application root.

When the CDB root is the current container, create an application root by running a CREATE PLUGGABLE DATABASE statement with the AS APPLICATION CONTAINER clause. When cloning, relocating, or plugging in a PDB to an application container, the application name and version of the PDB must match the application name and version of the application container.

Options for Creating a PDB from a Non-CDB

You have multiple options for moving a non-CDB into a PDB.

You can accomplish this task in the following ways:

  • Clone a non-CDB

This is the simplest way to create a PDB using a non-CDB, but it requires copying the files of the non-CDB to a new location.

Both the CDB and the non-CDB must be running Oracle Database 12c Release 1 (12.1.0.2) or later. If your current non-CDB uses an Oracle Database release before Oracle Database 12c Release 1 (12.1.0.2), then you must upgrade the non-CDB to a later release to use this technique.

  • Generate an XML metadata file by using the DBMS_PDB package

The XML metadata file describes the database files of the non-CDB so that you can plug it into a CDB.

This method requires more steps than creating a PDB by cloning a non-CDB, but it enables you to create a PDB using a non-CDB without moving the non-CDB files in some situations.

To use this technique, the non-CDB must run Oracle Database 12c or later. If your current non-CDB uses a release before Oracle Database 12c, then you must upgrade to a later release.

  • Export the data from the non-CDB and import it into a PDB using Oracle Data Pump

When you import, specify the connect identifier for the PDB after the user name. For example, if the connect identifier for the PDB is hrpdb, then enter the following when you run the Oracle Data Pump Import utility:

impdp user_name@hrpdb ...

If the Oracle Database release of the non-CDB is Oracle Database 11g Release 2 (11.2.0.3) or later, then you can use full transportable export/import to move the data. When transporting a non-CDB from an Oracle Database 11g Release 2 (11.2.0.3) or later Oracle Database 11g database to Oracle Database 12c or later, the VERSION Data Pump export parameter must be set to 12.0.0.0.0 or higher.

If the Oracle Database release of the non-CDB is before Oracle Database 11g Release 2 (11.2.0.3), then you can use transportable tablespaces to move the data, or you can perform a full database export/import.

  • Replicate data from the non-CDB to a PDB using GoldenGate

When the PDB catches up with the non-CDB, you fail over to the PDB.

See the Oracle GoldenGate documentation.

PDB Storage

However you choose to create a PDB, you must decide on the tablespaces and files that will store the data.

Storage Limits

The optional STORAGE clause of the CREATE PLUGGABLE DATABASE statement specifies storage limits for PDBs.

The STORAGE clause specifies the following limits:

  • The amount of storage that can be used by all tablespaces that belong to the PDB

Use MAXSIZE and a size clause to specify a limit, or set MAXSIZE to UNLIMITED to indicate no limit.

  • The amount of storage that can be used by unified audit OS spillover (.bin format) files in the PDB

Use MAX_AUDIT_SIZE and a size clause to specify a limit, or set MAX_AUDIT_SIZE to UNLIMITED to indicate no limit.

  • The amount of diagnostics (trace files and incident dumps) in the Automatic Diagnostic Repository (ADR) that can be used by the PDB

Use MAX_DIAG_SIZE and a size clause to specify a limit, or set MAX_DIAG_SIZE to UNLIMITED to indicate no limit.

If STORAGE UNLIMITED is set, or if there is no STORAGE clause, then there are no storage limits for the PDB.

The following are examples that use the STORAGE clause.

STORAGE Clause That Specifies a Storage Limit

This STORAGE clause specifies that the storage used by all tablespaces that belong to the PDB must not exceed 2 gigabytes.

STORAGE (MAXSIZE 2G)

STORAGE Clause That Specifies Unlimited Storage

This STORAGE clause specifies unlimited storage for all tablespaces that belong to the PDB.

STORAGE (MAXSIZE UNLIMITED)

Default Tablespace

The DEFAULT TABLESPACE clause of the CREATE PLUGGABLE DATABASE statement specifies the default tablespace for the new PDB.

Oracle Database will assign the default tablespace to any non-SYSTEM users who do not have a different permanent tablespace specified.

When you create the PDB from the PDB seed or an application seed and specify the DEFAULT TABLESPACE clause, Oracle Database creates a smallfile tablespace and sets it as the default tablespace for the PDB. When you create the PDB using a method other than the using the PDB seed or application seed, such as cloning a PDB or plugging in an unplugged PDB, the default tablespace must be a tablespace that already exists in the source PDB.

DEFAULT TABLESPACE Clause

DEFAULT TABLESPACE sales

User Tablespaces

The USER_TABLESPACES clause of the CREATE PLUGGABLE DATABASE statement specifies which tablespaces are available in the new PDB.

You can use this clause to separate the data for multiple schemas into different PDBs. For example, when you move a non-CDB to a PDB, and the non-CDB had several schemas that each supported a different application, you can use this clause to separate the data belonging to each schema into a separate PDB. This technique assumes that each schema used a separate tablespace in the non-CDB.

You can use this clause to specify one of the following options:

  • List one or more tablespaces to include.
  • Specify ALL, the default, to include all tablespaces.
  • Specify ALL EXCEPT to include all tablespaces, except for the tablespaces listed.
  • Specify NONE to exclude all tablespaces.
  • If the creation mode of the user tablespaces must be different from the creation mode for the Oracle-supplied tablespaces (such as SYSTEM and SYSAUX), then specify one of the following in the USER_TABLESPACES clause:
    • COPY: The files of the tablespaces are copied to a new location.
    • MOVE: The files of the tablespaces are moved to a new location.
    • NOCOPY: The files of the tablespaces are not copied or moved.
    • SNAPSHOT COPY: The tablespaces are cloned with storage snapshots.
    • NO DATA: The data model definition of the tablespaces is cloned but not the tablespaces’ data.

When the compatibility level of the CDB is 12.2.0 or higher, the tablespaces that are excluded by this clause are created offline in the new PDB, and they have no data files associated with them. When the compatibility level of the CDB is lower than 12.2.0, the tablespaces that are excluded by this clause are offline in the new PDB, and all data files that belong to these tablespaces are unnamed and offline.

This clause does not apply to the SYSTEMSYSAUX, or TEMP tablespaces. Do not include these tablespaces in a tablespace list for this clause.

The following are examples that use the USER_TABLESPACES clause.

USER_TABLESPACES Clause That Includes One Tablespace

Assume that the non-CDB or PDB from which a PDB is being created includes the following tablespaces: tbs1tbs2, and tbs3. This USER_TABLESPACES clause includes the tbs2 tablespace, but excludes the tbs1 and tbs3 tablespaces.

USER_TABLESPACES=('tbs2')

USER_TABLESPACES Clause That Includes a List of Tablespaces

Assume that the non-CDB or PDB from which a PDB is being created includes the following tablespaces: tbs1tbs2tbs3tbs4, and tbs5. This USER_TABLESPACES clause includes the tbs1tbs4, and tbs5 tablespaces, but excludes the tbs2 and tbs3 tablespaces.

USER_TABLESPACES=('tbs1','tbs4','tbs5')

USER_TABLESPACES Clause That Includes All Tablespaces Except for Listed Ones

Assume that the non-CDB or PDB from which a PDB is being created includes the following tablespaces: tbs1tbs2tbs3tbs4, and tbs5. This USER_TABLESPACES clause includes the tbs2 and tbs3 tablespaces, but excludes the tbs1tbs4, and tbs5 tablespaces.

USER_TABLESPACES=ALL EXCEPT('tbs1','tbs4','tbs5')

USER_TABLESPACES in a Different Creation Mode

This example shows a full CREATE PLUGGABLE DATABASE statement that plugs in a non-CDB and only includes the tbs3 user tablespace from the non-CDB. The example copies the files for Oracle-supplied tablespaces (such as SYSTEM and SYSAUX) to a new location, but moves the files of the tbs3 user tablespace.

CREATE PLUGGABLE DATABASE ncdb USING '/disk1/oracle/ncdb.xml'
  COPY
  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/', '/disk2/oracle/ncdb/')
  USER_TABLESPACES=('tbs3') MOVE;

PDB File Locations

In the CREATE PLUGGABLE DATABASE statement, you can specify the locations of files used by the new PDB.

The term “file name” means both the name and the location of a file. The CREATE PLUGGABLE DATABASE statement has the following clauses that indicate the file names of the new PDB being created:

  • The FILE_NAME_CONVERT clause specifies the names of the PDB’s files after the PDB is created.

Use this clause when the files are not yet at their ultimate destination, and you want to copy or move them during PDB creation. You can use this clause in any CREATE PLUGGABLE DATABASE statement.

  • The CREATE_FILE_DEST clause specifies the default Oracle Managed Files file system directory or Oracle ASM disk group for the PDB’s files.

Use this clause to enable Oracle Managed Files for the new PDB, independent of any Oracle Managed Files default location specified in the root for the CDB. You can use this clause in any CREATE PLUGGABLE DATABASE statement.

When necessary, you can use both clauses in the same CREATE PLUGGABLE DATABASE statement. In addition, the following initialization parameters can control the location of the new PDB files:

  • The DB_CREATE_FILE_DEST initialization parameter set in the root

This initialization parameter specifies the default location for Oracle Managed Files for the CDB. When this parameter is set in a PDB, it specifies the default location for Oracle Managed Files for the PDB.

  • The PDB_FILE_NAME_CONVERT initialization parameter

This initialization parameter maps names of existing files to new file names when processing a CREATE PLUGGABLE DATABASE statement.

The following table shows the precedence order when both clauses are used in the same CREATE PLUGGABLE DATABASE statement, and both initialization parameters are set. For each clause and initialization parameter, the table also shows whether the files created by the CREATE PLUGGABLE DATABASE statement will use Oracle Managed Files or not.

Summary of File Location Clauses and Initialization Parameters

Clause or Initialization ParameterPrecedence OrderWill the Files Created by CREATE PLUGGABLE DATABASE Use Oracle Managed Files?
FILE_NAME_CONVERT clause1No
CREATE_FILE_DEST clause2Yes
DB_CREATE_FILE_DEST initialization parameter3Yes
PDB_FILE_NAME_CONVERT initialization parameter4No

Regarding the use of Oracle Managed Files, the table only applies to files created by the CREATE PLUGGABLE DATABASE statement. Files created for the PDB after the PDB has been created might or might not use Oracle Managed Files.

In addition, if FILE_NAME_CONVERT and CREATE_FILE_DEST are both specified in the CREATE PLUGGABLE DATABASE statement, then the FILE_NAME_CONVERT setting is used for the files being placed during PDB creation, and the CREATE_FILE_DEST setting is used to set the DB_CREATE_FILE_DEST initialization parameter in the PDB. In this case, Oracle Managed Files controls the location of the files for the PDB after PDB creation.

FILE_NAME_CONVERT Clause

If the PDB will not use Oracle Managed Files, then the FILE_NAME_CONVERT clause of the CREATE PLUGGABLE DATABASE statement specifies how to generate the names of files (such as data files) using the names of existing files.

You can use this clause to specify one of the following options:

  • One or more file name patterns and replacement file name patterns, in the following form:
'string1' , 'string2' , 'string3' , 'string4' , ...

The string2 file name pattern replaces the string1 file name pattern, and the string4 file name pattern replaces the string3 file name pattern. You can use as many pairs of file name pattern and replacement file name pattern strings as required.

If you specify an odd number of strings (the last string has no corresponding replacement string), then an error is returned. Do not specify more than one pattern/replace string that matches a single file name or directory.

  • NONE when no files should be copied or moved during PDB creation. Omitting the FILE_NAME_CONVERT clause is the same as specifying NONE.

You can use the FILE_NAME_CONVERT clause in any CREATE PLUGGABLE DATABASE statement.

When the FILE_NAME_CONVERT clause is not specified in a CREATE PLUGGABLE DATABASE statement, either Oracle Managed Files or the PDB_FILE_NAME_CONVERT initialization parameter specifies how to generate the names of the files. If you use both Oracle Managed Files and the PDB_FILE_NAME_CONVERT initialization parameter, then Oracle Managed Files takes precedence. The FILE_NAME_CONVERT clause takes precedence when it is specified.

File name patterns specified in the FILE_NAME_CONVERT clause cannot match files or directories managed by Oracle Managed Files.

FILE_NAME_CONVERT Clause

This FILE_NAME_CONVERT clause generates file names for the new PDB in the /oracle/pdb5 directory using file names in the /oracle/dbs directory.

FILE_NAME_CONVERT = ('/oracle/dbs/', '/oracle/pdb5/')
CREATE_FILE_DEST Clause

The CREATE_FILE_DEST clause of the CREATE PLUGGABLE DATABASE statement enables Oracle Managed Files for the PDB and specifies the default file system directory or Oracle ASM disk group for the PDB files.

The PDB data files and temp files are restricted to the specified directory and its subdirectories. If a file system directory is specified as the default location in this clause, then the directory must exist. Also, the user who runs the CREATE PLUGGABLE DATABASE statement must have the appropriate privileges to create files in the specified directory. Alternatively, you can specify the name of a directory object that exists in the CDB root (CDB$ROOT). The directory object points to the file system directory used by CREATE_FILE_DEST.

If there is a default Oracle Managed Files location for the CDB set in the CDB root, then the CREATE_FILE_DEST setting overrides the CDB root’s setting, and the specified CREATE_FILE_DEST setting is used for the PDB.

If CREATE_FILE_DEST=NONE is specified, then Oracle Managed Files is disabled for the PDB.

When the CREATE_FILE_DEST clause is set to a value other than NONE, the DB_CREATE_FILE_DEST initialization parameter is set implicitly in the PDB with SCOPE=SPFILE.

If the CDB root uses Oracle Managed Files, and this clause is not specified, then the PDB inherits the Oracle Managed Files default location from the CDB root.

CREATE_FILE_DEST Clause

This CREATE_FILE_DEST clause specifies /oracle/pdb2/ as the default Oracle Managed Files file system directory for the new PDB.

CREATE_FILE_DEST = '/oracle/pdb2/'
Restrictions on PDB File Locations

The PATH_PREFIX clause of the CREATE PLUGGABLE DATABASE statement ensures that all directory object paths associated with the PDB are restricted to the specified directory or its subdirectories.

This clause also ensures that the following files associated with the PDB are restricted to the specified directory: the Oracle XML repository for the PDB, files created with a CREATE PFILE statement, and the export directory for Oracle wallets. Use this clause when you want to ensure that a PDB’s files reside in a specific directory and its subdirectories.

You can use this clause to specify one of the following options:

  • An absolute path that is used as a prefix for all file paths associated with the PDB.
  • The name of a directory object that exists in the CDB root (CDB$ROOT). The directory object points to the absolute path to be used for PATH_PREFIX.
  • NONE to indicate that there are no restrictions for the file paths. Omitting the PATH_PREFIX clause is the same as specifying NONE.

After a PDB is created, its PATH_PREFIX setting cannot be modified.

You can use the PATH_PREFIX clause in any CREATE PLUGGABLE DATABASE statement.

PATH_PREFIX Clause

This PATH_PREFIX clause ensures that all file paths associated with the PDB are restricted to the /disk1/oracle/dbs/salespdb/ directory.

PATH_PREFIX = '/disk1/oracle/dbs/salespdb/'

Be sure to specify the path name so that it is properly formed when file names are appended to it. For example, on UNIX systems, be sure to end the path name with a forward slash (/).

Service Name Conversion

An important aspect of PDB creation is managing the renaming of database services.

When the service name of a new PDB conflicts with an existing service name in the CDB, plug-in violations can result. The SERVICE_NAME_CONVERT clause of the CREATE PLUGGABLE DATABASE statement renames the user-defined services of the new PDB based on the service names of the source PDB. Using this clause, you can rename services and avoid plug-in violations.

You can use this clause to specify one of the following options:

  • One or more service names and replacement service names, in the following form:
'string1' , 'string2' , 'string3' , 'string4' , ...

The string2 service name replaces the string1 service name, and the string4 service name replaces the string3 service name. You can use as many pairs of service names and replacement service names as required.

If you specify an odd number of strings (the last string has no corresponding replacement string), then an error is returned.

  • NONE when no service names need to be renamed. Omitting the SERVICE_NAME_CONVERT clause is the same as specifying NONE.

You can use the SERVICE_NAME_CONVERT clause in any CREATE PLUGGABLE DATABASE statement, except for a CREATE PLUGGABLE DATABASE statement that creates a PDB from the PDB seed. The PDB seed cannot have user-defined services. However, you can use this statement for a CREATE PLUGGABLE DATABASE statement that creates an application PDB from an application seed in an application container.

SERVICE_NAME_CONVERT Clause

This SERVICE_NAME_CONVERT clause uses renames the salesrep service to salesperson.

SERVICE_NAME_CONVERT = ('salesrep','salesperson')

Summary of Clauses for Creating a PDB

When you create a PDB with the CREATE PLUGGABLE DATABASE statement, various clauses are available based on different factors.

One factor is the technique you are using to create the PDB. You can determine which clauses to use by answering a series of questions.

The following table describes which CREATE PLUGGABLE DATABASE clauses to specify based on different factors.

Clauses for Creating a PDB

QuestionYesNoClause Can Be Used Only When
Do you want to create an application container instead of a PDB?Specify the AS APPLICATION CONTAINER clause.Omit the AS APPLICATION CONTAINER clause.Creating an application container in a CDB
Are you plugging a PDB into a CDB that contains one or more PDBs that were created by plugging in the same PDB?Specify the AS CLONE clause to ensure that Oracle Database generates a unique PDB DBID, GUID, and other identifiers expected for the new PDB. The PDB is plugged in as a clone of the unplugged PDB to ensure that all of its identifiers are unique.Omit the AS CLONE clause.Plugging in an unplugged PDB
Do you want to create an application seed in an application container?Specify the AS SEED clause.Omit the AS SEED clause.Creating an application seed in an application container
Do you want to use a CREATE_FILE_DEST clause to specify the Oracle Managed Files default location for the PDB files? When creating a PDB from the PDB seed or an application seed, the source files are the files associated with the seed.Include a CREATE_FILE_DEST clause that specifies the default file system directory or Oracle ASM disk group for the PDB’s files.Omit the CREATE_FILE_DEST clause. Use one of these techniques to specify the target locations of the files: FILE_NAME_CONVERT clauseEnable Oracle Managed Files for the CDB for it to determine the target locations.Specify the target locations in the PDB_FILE_NAME_CONVERT initialization parameter.Creating a PDB from the PDB seed or an application seed Cloning a PDB Relocating a PDB Plugging in an unplugged PDB
Do you want to specify a default permanent tablespace for the PDB?Specify a DEFAULT TABLESPACE clause with the appropriate limits. Oracle Database will assign to this tablespace any non-SYSTEM users for whom you do not specify a different permanent tablespace. When creating a PDB from the PDB seed or an application seed, Oracle Database creates a smallfile tablespace and sets it as the default tablespace. When using a technique other than creation from the PDB seed or an application seed, the specified tablespace must exist in the source PDB.Omit the DEFAULT TABLESPACE clause. If you do not specify this clause, then the SYSTEM tablespace is the default permanent tablespace for non-SYSTEM users. Using the SYSTEM tablespace for non-SYSTEM users is not recommended.Creating a PDB from the PDB seed or an application seed Cloning a PDB Relocating a PDB Plugging in an unplugged PDB
Do you want to use a FILE_NAME_CONVERT clause to specify the target locations of the files? When creating a PDB from the PDB seed or an application seed, the source files are the files associated with the seed.Include a FILE_NAME_CONVERT clause that specifies the target locations of the files based on the names of the source files.Omit the FILE_NAME_CONVERT clause. Use one of these techniques to specify the target locations of the files: CREATE_FILE_DEST clauseEnable Oracle Managed Files for the CDB for it to determine the target locations.Specify the target locations in the PDB_FILE_NAME_CONVERT initialization parameter.Creating a PDB from the PDB seed or an application seed Cloning a PDB Relocating a PDB Creating a proxy PDB (Only applies to data files in the SYSTEM and SYSAUX tablespaces.) Plugging in an unplugged PDB
Is the PDB a reference PDB with a dependent proxy PDB, and is the host name of its listener changing?Include a HOST clause and specify the host name of the listener for the PDB being created. For example, you might have a listener network for the physical host name and default port and configure a second listener bound to a virtual host name and virtual IP address with a nondefault port number.Omit the HOST clause.Creating a PDB from the PDB seed or an application seed Cloning a PDB Relocating a PDB Plugging in an unplugged PDB
Do you want to specify the logging attribute of the tablespaces in the new PDB?Include the logging_clause.Omit the logging_clause.Creating a PDB from the PDB seed or an application seed Cloning a PDB Plugging in an unplugged PDB
Do you want to copy or move the files to a new location?Specify COPY to copy the files to a new location. COPY is the default. Specify MOVE to move the files to a new location. Use one of these techniques to specify the target location: Include a FILE_NAME_CONVERT clause that specifies the target locations based on the names of the source files.Include a CREATE_FILE_DEST clause that specifies the Oracle Managed Files default location for the PDB’s files.Enable Oracle Managed Files for it to determine the target locations.Specify the target locations in the PDB_FILE_NAME_CONVERT initialization parameter.Specify NOCOPY.Plugging in an unplugged PDB
Do you want to specify that the data model definition of the source PDB is cloned but not the data of the source PDB?Include the NO DATA clause.Omit the NO DATA clause.Cloning a PDB
Do you want to use multiple parallel execution servers to parallelize PDB creation?To let the CDB choose the degree of parallelism, include or omit the PARALLEL clause. To specify the degree of parallelism, specify the PARALLEL clause with an integer. For example, specify PARALLEL 4 to indicate a degree of parallelism of 4.Specify PARALLEL 0 or PARALLEL 1.Creating a PDB from the PDB seed or an application seed Cloning a PDB
Do you want to use a PATH_PREFIX clause to restrict file paths for the PDB for the following: directory objects, the Oracle XML repository for the PDB, files created with a CREATE PFILE statement, and the export directory for Oracle wallets? The PATH_PREFIX clause does not affect files created by Oracle Managed Files.Include a PATH_PREFIX clause that specifies an absolute path.Set the PATH_PREFIX clause to NONE or omit it.Creating a PDB from the PDB seed or an application seed Cloning a PDB Relocating a PDB Plugging in an unplugged PDB
Is the PDB a reference PDB with a dependent proxy PDB, and is the port number of its listener changing to a value other than 1521?Include a PORT clause and specify the port number of the listener for the PDB being created. For example, you might have a listener network for the physical host name and default port and configure a second listener bound to a virtual host name and virtual IP address with a nondefault port number.Omit the PORT clause.Creating a PDB from the PDB seed or an application seed Cloning a PDB Relocating a PDB Plugging in an unplugged PDB
Do you want to be able to refresh the PDB to propagate changes from the source PDB to the clone PDB? A refreshable PDB must be opened in read-only mode.Include a REFRESH MODE MANUAL or REFRESH MODE EVERY minutes clause.Omit the REFRESH MODE clause or include a REFRESH MODE NONE clause.Cloning a PDB
Do you want to grant predefined Oracle roles to the PDB_DBA role locally in the PDB? The new administrator for the PDB is granted the PDB_DBA common role locally in the PDB. By default, the CREATE PLUGGABLE DATABASE statement does not grant the administrator or the role any privileges.Include the ROLES clause and specify the predefined Oracle roles to grant to the PDB_DBA role. The specified roles are granted to the PDB_DBA role locally in the PDB. The user who runs the CREATE PLUGGABLE DATABASE statement does not need to be granted the specified roles.Omit the ROLES clause.Creating a PDB from the PDB seed or an application seed Creating a proxy PDB
Do you want to use a SERVICE_NAME_CONVERT clause to rename the user-defined services of the new PDB based on the service names of the source PDB?Include a SERVICE_NAME_CONVERT clause that specifies the new name of a service and the service name it is replacing. Specify multiple service names and replacement service names if necessary.Omit the SERVICE_NAME_CONVERT clause.Creating a PDB from the application seed, but not a PDB seed Cloning a PDB Relocating a PDB Creating a proxy PDB (Only applies to data files in the SYSTEM and SYSAUX tablespaces.) Plugging in an unplugged PDB
Do you want to clone a PDB using a storage-managed snapshot (not a snapshot generated by ALTER PLUGGABLE DATABASE SNAPSHOT)?Specify a SNAPSHOT COPY clause to clone a PDB using storage-managed snapshots. SNAPSHOT COPY is supported only if the underlying file system supports storage snapshots. A snapshot copy is nearly instantaneous because it does not require copying the full data files of the source PDB. However, you cannot unplug a snapshot copy PDB from the CDB root or application root. Also, if a snapshot copy PDB exists, then you cannot drop the storage snapshot on which the snapshot copy PDB is based. The process of materializing transforms a snapshot copy PDB, which uses sparse files, into a full PDB. Materialize a PDB by running the ALTER PLUGGABLE DATABASE MATERIALIZE command.Omit the SNAPSHOT COPY clause.Cloning a PDB
Do you want to enable PDB-level snapshots using ALTER PLUGGABLE DATABASE SNAPSHOT?Specify a SNAPSHOT MODE clause in the ALTER PLUGGABLE DATABASE SNAPSHOT command, and specify MANUAL or EVERY snapshot_interval [MINUTES|HOURS].Omit the SNAPSHOT MODE clause or specify SNAPSHOT MODE NONE.Creating a PDB from the PDB seed or an application seed Cloning a PDB Relocating a PDB Creating a proxy PDB (Only applies to data files in the SYSTEM and SYSAUX tablespaces.) Plugging in an unplugged PDB
Are all source files in a single directory with new file names that would require multiple SOURCE_FILE_NAME_CONVERT entries?Specify the SOURCE_FILE_DIRECTORY with the full absolute path to the source files.Omit the SOURCE_FILE_DIRECTORY clause.Plugging in an unplugged PDB using an XML file directly. This clause does not apply to plugging in an unplugged PDB with a .pdb archive file.
Do the contents of the XML file accurately describe the locations of the source files?Omit the SOURCE_FILE_NAME_CONVERT clause.Use the SOURCE_FILE_NAME_CONVERT clause to specify the source file locations.Plugging in an unplugged PDB using an XML file directly. This clause does not apply to plugging in an unplugged PDB with a .pdb archive file.
Do you want to include the new PDB in one or more standby CDBs?Specify ALLALL EXCEPT, or a list of standby CDBs. When creating a remote clone, you can set the initialization parameter STANDBY_PDB_SOURCE_FILE_DBLINK to the name of the database link that points to the source PDB data files. The operation copies the data files only if the source PDB is open read-only.Omit the STANDBYS clause or specify NONE.Creating a PDB from the PDB seed or an application seed Cloning a PDB Relocating a PDB Plugging in an unplugged PDB
Do you want to limit the amount of storage that the PDB can use?Specify a STORAGE clause with the appropriate limits.Omit the STORAGE clause, or specify unlimited storage using the STORAGE clause.Creating a PDB from the PDB seed or an application seed Cloning a PDB Relocating a PDB Plugging in an unplugged PDB
Do you want to reuse the temp file if a temp file exists in the target location?Include the TEMPFILE REUSE clause.Omit the TEMPFILE REUSE clause. Ensure that there is no file with the same name as the new temp file in the target location.Creating a PDB from the PDB seed or an application seed Cloning a PDB Relocating a PDB Plugging in an unplugged PDB
Do you want to specify which tablespaces are included in the new PDB and which tablespaces are excluded from the new PDB?Include the USER_TABLESPACES clause and specify the tablespaces that are included in the new PDB.Omit the USER_TABLESPACES clause.Plugging in an unplugged PDB
Do you want to plug an unplugged PDB into a CDB?Include the USING filename clause. If you are plugging in a PDB to a primary CDB in a Data Guard scenario, then set the STANDBY_PDB_SOURCE_FILE_DIRECTORY initialization parameter to a standby location that contains the source data files for instantiating the PDB. If not found, then the standby database tries to locate the files in the OMF location. If not found in the OMF location, then copy the data files to the OMF location, and restart redo apply on the standby database.Omit the USING filename clause.Plugging in an unplugged PDB
Do you want to create a new PDB based on a PDB snapshot?Include the USING SNAPSHOT clause and specify either the PDB snapshot name, SCN, or timestamp. The result is a full, standalone PDB. 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. To create PDB-level snapshots manually, specify the SNAPSHOT clause of CREATE PLUGGABLE DATABASE (or ALTER PLUGGABLE DATABASE). Specifying the EVERY interval clause configures the PDB to create snapshots automatically.Exclude the USING SNAPSHOT clause.Cloning a PDB snapshot
Do you want to clone a PDB that resides in Oracle ASM by splitting a mirror?Include the USING MIRROR COPY clause and specify the name of the mirror copy and the source PDB.Omit the USING MIRROR COPY clause.Cloning a PDB that uses Oracle ASM storage

Creating a PDB

Using the CREATE PLUGGABLE DATABASE statement, you can create a PDB from the PDB seed, and you can create an application PDB from an application seed or the PDB seed.

To create a PDB:

  1. In SQL*Plus, ensure that the current container is the CDB root or an application root.

When the current container is the CDB root, the PDB is created in the CDB using the files of the PDB seed.

When the current container is an application root, the application PDB is created in the application container using the files of the application seed. If there is no application seed in the application container, then the application PDB is created in the application container using the files of the PDB seed.

  • Run the CREATE PLUGGABLE DATABASE statement, and specify a local administrator for the PDB. Specify other clauses when they are required.

After you create the PDB, it is in mounted mode, and its status is NEW. You can view the open mode of a PDB by querying the OPEN_MODE column in the V$PDBS view. You can view the status of a PDB by querying the STATUS column of the CDB_PDBS or DBA_PDBS view.

A new default service is created for the PDB. The service has the same name as the PDB and can be used to access the PDB. Oracle Net Services must be configured properly for clients to access this service.

  • Open the new PDB in read/write mode.

You must open the new PDB in read/write mode for Oracle Database to complete the integration of the new PDB into the CDB. An error is returned if you attempt to open the PDB in read-only mode. After the PDB is opened in read/write mode, its status is NORMAL.

  • Back up the PDB.

A PDB cannot be recovered unless it is backed up.

A local user with the name of the specified local administrator is created and granted the PDB_DBA common role locally in the PDB. If this user was not granted administrator privileges during PDB creation, then use the SYS and SYSTEM common users to administer to the PDB.

Creating a PDB: Examples

These examples create a new PDB named salespdb and a salesadm local administrator given different factors.

In addition to creating the salespdb PDB, this statement grants the PDB_DBA role to the PDB administrator salesadm and grants the specified predefined Oracle roles to the PDB_DBA role locally in the PDB.

In each example, the root to which the new PDB belongs depends on the current container when the CREATE PLUGGABLE DATABASE statement is run:

  • When the current container is the CDB root, the new PDB is created in the CDB root.
  • When the current container is an application root in an application container, the new PDB is created as an application PDB in the application root.

Creating a PDB Using No Clauses: Example

This example shows the simplest way to create a PDB.

This example assumes the following factors:

  • Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.
  • The PDB does not require a default tablespace.
  • The PATH_PREFIX clause is not required.
  • The FILE_NAME_CONVERT clause and the CREATE_FILE_DEST clause are not required.

Either Oracle Managed Files is enabled for the CDB, or the PDB_FILE_NAME_CONVERT initialization parameter is set. The files associated with the PDB seed or application seed will be copied to a new location based on the Oracle Managed Files configuration or the initialization parameter setting.

  • There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.
  • No predefined Oracle roles need to be granted to the PDB_DBA role.

The following statement creates the PDB:

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY pwd;

Creating a PDB and Granting Predefined Oracle Roles to the PDB Administrator: Example

This example uses the ROLES parameter to grant a predefined role.

This example assumes the following factors:

  • Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.
  • The PDB does not require a default tablespace.
  • The PATH_PREFIX clause is not required.
  • The FILE_NAME_CONVERT clause and the CREATE_FILE_DEST clause are not required.

Either Oracle Managed Files is enabled, or the PDB_FILE_NAME_CONVERT initialization parameter is set. The files associated with the PDB seed or application seed will be copied to a new location based on the Oracle Managed Files configuration or the initialization parameter setting.

  • There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.
  • The PDB_DBA role should be granted the following predefined Oracle role locally: DBA.

The following statement creates the PDB:

CREATE PLUGGABLE DATABASE salespdb 
  ADMIN USER salesadm IDENTIFIED BY password
    ROLES=(DBA);

Creating a PDB Using Multiple Clauses: Example

This example creating a PDB using the STORAGEDEFAULT TABLESPACEPATH_PREFIX, and FILE_NAME_CONVERT clauses.

This example assumes the following factors:

  • Storage limits must be enforced for the PDB. Therefore, the STORAGE clause is required. Specifically, all tablespaces that belong to the PDB must not exceed 2 gigabytes.
  • A default permanent tablespace is required for any non-administrative users for which you do not specify a different permanent tablespace. Specifically, this example creates a default permanent tablespace named sales with the following characteristics:
    • The single data file for the tablespace is sales01.dbf, and the statement creates it in the /disk1/oracle/dbs/salespdb directory.
    • The SIZE clause specifies that the initial size of the tablespace is 250 megabytes.
    • The AUTOEXTEND clause enables automatic extension for the file.
  • The path prefix must be added to the PDB directory object paths. Therefore, the PATH_PREFIX clause is required. In this example, the path prefix /disk1/oracle/dbs/salespdb/ is added to the PDB’s directory object paths.
  • The CREATE_FILE_DEST clause will not be used, Oracle Managed Files is not enabled, and the PDB_FILE_NAME_CONVERT initialization parameter is not set. Therefore, the FILE_NAME_CONVERT clause is required. Specify the location of the data files for the PDB seed or application seed on your system. In this example, Oracle Database copies the files from /disk1/oracle/dbs/pdbseed to /disk1/oracle/dbs/salespdb.
  • There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.
  • No predefined Oracle roles need to be granted to the PDB_DBA role.

The following statement creates the PDB:

CREATE PLUGGABLE DATABASE salespdb 
  ADMIN USER salesadm IDENTIFIED BY password
  STORAGE (MAXSIZE 2G)
  DEFAULT TABLESPACE sales 
    DATAFILE '/disk1/oracle/dbs/salespdb/sales01.dbf' SIZE 250M 
    AUTOEXTEND ON
    PATH_PREFIX = '/disk1/oracle/dbs/salespdb/'
    FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', 
                         '/disk1/oracle/dbs/salespdb/');

Cloning a PDB

Cloning means creating a new PDB from a source PDB or from a non-CDB.

A typical use case is development testing. You can create one or more clones of a PDB or non-CDB and safely test them in isolation. For example, you might test a new or modified application on a cloned PDB before using the application with a production PDB.

This technique creates a new PDB from a source PDB or non-CDB. The process automatically plugs the new PDB into the CDB.

To use this technique, you must specify the source in a CREATE PLUGGABLE DATABASE statement. The source can be any of the following:

  • Local PDB
  • PDB in a remote CDB
  • Non-CDB

Cloning a Local PDB: Examples

The following examples clone a local source PDB named pdb1 to a target PDB named pdb2 given different factors.

In each example, the root to which the new PDB belongs depends on the current container when the CREATE PLUGGABLE DATABASE statement is run:

  • When the current container is the CDB root, the database creates the PDB in the CDB root.
  • When the current container is an application root in an application container, the database creates an application PDB in the application root.
Cloning a Local PDB Using No Clauses: Example

This example shows the simplest way to clone a PDB.

This example assumes the following factors:

  • The PATH_PREFIX clause is not required.
  • The FILE_NAME_CONVERT clause and the CREATE_FILE_DEST clause are not required.

Either Oracle Managed Files is enabled, or the PDB_FILE_NAME_CONVERT initialization parameter is set. Therefore, the FILE_NAME_CONVERT clause is not required. The files will be copied to a new location based on the Oracle Managed Files configuration or the initialization parameter setting.

  • Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.
  • There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.

The following statement clones the pdb2 PDB from the pdb1 PDB:

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;
Cloning a Local PDB Using DBCA: Example

This example clones a PDB using the silent mode of DBCA. Hot cloning is supported.

This example assumes the following factors:

  • The source CDB is a single-instance database with the SID orcl.
  • The source PDB is pdb1. You intend for pdb1 to remain open during the cloning operation, which means that local undo and ARCHIVELOG mode are enabled in the CDB. Otherwise, DBCA closes the PDB during the clone operation, and after receiving confirmation, opens the source PDB in read-only mode.
  • The new PDB is pdb2.
  • You are running DBCA in noninteractive mode.

The following command clones the pdb2 PDB from the pdb1 PDB:

./dbca -silent 
  -createpluggabledatabase 
  -sourcedb orcl 
  -createpdbfrom PDB 
  -pdbName pdb2 
  -sourcepdb pdb1
Cloning a Local PDB with the PATH_PREFIX Clause: Example

This example explains how to clone a local PDB with the PATH_PREFIXFILE_NAME_CONVERT, and SERVICE_NAME_CONVERT clauses.

This example assumes the following factors:

  • The path prefix must be added to the PDB’s directory object paths. Therefore, the PATH_PREFIX clause is required. In this example, the path prefix /disk2/oracle/pdb2/ is added to the PDB’s directory object paths.
  • The FILE_NAME_CONVERT clause is required to specify the target locations of the copied files. In this example, the files are copied from /disk1/oracle/pdb1 to /disk2/oracle/pdb2.

The CREATE_FILE_DEST clause is not used, and neither Oracle Managed Files nor the PDB_FILE_NAME_CONVERT initialization parameter is used to specify the target locations of the copied files.

  • Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.
  • There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.
  • The PDB that is being cloned (pdb1) has two user-defined services: salesrep_ca and orders_ca for the sales representatives and order entry personnel in California. The new services will be for the sales representatives and order entry personnel in Oregon, and the service names will be renamed to salesrep_or and orders_or, respectively, in the cloned PDB (pdb2).
  • Future tablespaces created within the PDB will be created with the NOLOGGING attribute by default. This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

The following statement clones the pdb2 PDB from the pdb1 PDB:

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 
  PATH_PREFIX = '/disk2/oracle/pdb2/'
  FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/')
  SERVICE_NAME_CONVERT = ('salesrep_ca','salesrep_or','orders_ca','orders_or')
  NOLOGGING;
Cloning a Local PDB Using the STORAGE Clause: Example

This example clones a local PDB using the FILE_NAME_CONVERTSTORAGE, and SERVICE_NAME_CONVERT clauses.

This example assumes the following factors:

  • The PATH_PREFIX clause is not required.
  • The FILE_NAME_CONVERT clause is required to specify the target locations of the copied files. In this example, the files are copied from /disk1/oracle/pdb1 to /disk2/oracle/pdb2.

The CREATE_FILE_DEST clause is not used, and neither Oracle Managed Files nor the PDB_FILE_NAME_CONVERT initialization parameter is used to specify the target locations of the copied files.

  • Storage limits must be enforced for the PDB. Therefore, the STORAGE clause is required. Specifically, all tablespaces that belong to the PDB must not exceed 2 gigabytes.
  • The source PDB (pdb1) has two user-defined services: salesrep_ca and orders_ca for the sales representatives and order entry personnel in California. The new services will be for the sales representatives and order entry personnel in Oregon, and the service names will be renamed to salesrep_or and orders_or, respectively, in the cloned PDB (pdb2).
  • There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.

The following statement clones the pdb2 PDB from the pdb1 PDB:

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 
  FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/')
  STORAGE (MAXSIZE 2G)
  SERVICE_NAME_CONVERT = ('salesrep_ca','salesrep_or','orders_ca','orders_or');
Cloning a Local PDB with the NO DATA Clause: Example

This example clones the data model definition of the PDB, but does not clone the data in the PDB.

This example assumes the following factors:

  • The NO DATA clause is required because the goal is to clone the data model definition of the source PDB without cloning its data.
  • The PATH_PREFIX clause is not required.
  • The FILE_NAME_CONVERT clause and the CREATE_FILE_DEST clause are not required.

Either Oracle Managed Files is enabled, or the PDB_FILE_NAME_CONVERT initialization parameter is set. Therefore, the FILE_NAME_CONVERT clause is not required. The process copies the files to a new location based on the Oracle Managed Files configuration or the initialization parameter setting.

  • Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.
  • There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.

Assume that the source PDB pdb1 has a large amount of data. The following steps illustrate how the clone does not contain the data of the source PDB when the operation is complete:

  1. With the source PDB pdb1 as the current container, query a table with a large amount of data:
SELECT COUNT(*) FROM tpch.lineitem;
 
  COUNT(*)
----------
  60001215

The table has over sixty million rows.

  • Clone the source PDB with the NO DATA clause:
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 NO DATA;
  • Open the cloned PDB:
ALTER PLUGGABLE DATABASE pdb2 OPEN;
  • With the cloned PDB pdb2 as the current container, query the table that has a large amount of data in the source PDB:
SELECT COUNT(*) FROM tpch.lineitem;
  COUNT(*)
----------
         0

The table in the cloned PDB has no rows.

Cloning a Remote PDB

You can clone a local PDB by running a CREATE PLUGGABLE DATABASE statement, and specifying a database link to the remote PDB in the FROM statement.

When the source is a PDB is in a remote CDB, you must use a database link to clone the PDB into the local CDB.

The database link must exist in the local CDB (not the remote CDB). When you issue the CREATE PLUGGABLE DATABASE statement from the root of the local CDB, you must specify a database link to the remote CDB that contains the PDB being cloned in the FROM clause. The database link connects from the local CDB to either to the root of the remote CDB or to the remote source PDB.

Cloning a Remote PDB: Examples

These examples clone a remote PDB or non-CDB given different factors.

In each example, the root to which the new PDB belongs depends on the current container when the CREATE PLUGGABLE DATABASE statement is run:

  • When the current container is the CDB root, the new PDB is created in the CDB root.
  • When the current container is an application root in an application container, the new PDB is created as an application PDB in the application root.
Cloning a Remote PDB Using No Clauses: Example

This example clones a remote source PDB named pdb1 to a target PDB named pdb2 given different factors.

This example assumes the following factors:

  • The database link name to the remote PDB is pdb1_link.
  • The PATH_PREFIX clause is not required.
  • The FILE_NAME_CONVERT clause and the CREATE_FILE_DEST clause are not required.

Either Oracle Managed Files is enabled, or the PDB_FILE_NAME_CONVERT initialization parameter is set. The files will be copied to a new location based on the Oracle Managed Files configuration or the initialization parameter setting.

  • Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.
  • There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.

The following statement clones the pdb2 PDB from the pdb1 remote PDB:

CREATE PLUGGABLE DATABASE pdb2 FROM [email protected]_link;
Cloning a Remote PDB Using DBCA: Example

This example uses DBCA to clone a PDB named pdb1 from a remote CDB to the local CDB, where it will be renamed clonepdb1.

This following silent command clones rempdb1 to loccdb1:

./dbca -silent 
  -createPluggableDatabase
  -createFromRemotePDB
  -sourceDB remcdb1  
  -remotePDBName rempdb1 
  -remoteDBConnString remcdb1host:1521/reminst 
  -remoteDBSYSDBAUserName remSYS 
      -remoteDBSYSDBAUserPassword remsyspwd 
  -dbLinkUsername c##adminuser_remcdb1 
      -dbLinkUserPassword pwd4dblinkusr 
  -sysDBAUserName locSYS 
      -sysDBAPassword locsyspwd
  -pdbName clonepdb1

Cloning a Non-CDB

The procedure for cloning a non-CDB is very similar to the procedure for cloning a remote PDB.

When the source is a non-CDB, you must specify a database link to the non-CDB in the FROM clause.

Cloning a Remote Non-CDB: Example

This example creates a new PDB by cloning a remote source non-CDB named mydb to a target PDB named pdb2 given different factors.

This example assumes the following factors:

  • The database link name to the remote non-CDB is mydb_link.
  • The PATH_PREFIX clause is not required.
  • The FILE_NAME_CONVERT clause and the CREATE_FILE_DEST clause are not required.

Either Oracle Managed Files is enabled, or the PDB_FILE_NAME_CONVERT initialization parameter is set. The files will be copied to a new location based on the Oracle Managed Files configuration or the initialization parameter setting.

  • Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.
  • There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.

The following statement creates the pdb2 PDB from the remote non-CDB named mydb:

CREATE PLUGGABLE DATABASE pdb2 FROM [email protected]_link;

When the source database is a non-CDB, you can substitute NON$CDB for the name of the non-CDB. For example, the following statement is equivalent to the previous example:

CREATE PLUGGABLE DATABASE pdb2 FROM [email protected]_link;

Refreshable Clone PDBs

The CREATE PLUGGABLE DATABASE ... REFRESH MODE statement clones a source PDB and configures the clone to be refreshable. Refreshing the clone PDB updates it with redo accumulated since the last redo log apply.

Purpose of Refreshable Clone PDBs

The cloning operation for production PDBs can take significant time.

If PDBs are cloned infrequently to avoid a drag on the system, then the cloned data becomes stale. A refreshable clone PDB solves this problem. When a refreshable clone PDB is stale, you can close it and then refresh it with recent redo. When not being refreshed, a refreshable clone PDB can be open read-only. A typical practice is to maintain a “golden master” refreshable clone of a production PDB, take PDB-level snapshots, and then create clones from the PDB snapshots for development and testing.

You can reverse the roles for source and clone PDBs using an ALTER PLUGGABLE DATABASE ... SWITCHOVER statement. This capability is useful in the following situations:

  • Planned switchover

The CDB hosting the source PDB may experience significantly more overhead than the CDB hosting the clone PDB. To achieve load balancing, you can reverse the roles, making the clone the new source PDB, and the source PDB the new clone.

  • Unplanned switchover

The source PDB may suffer an unplanned failure. In this case, you can make the clone PDB the new source PDB, and resume normal operations.

To create a refreshable clone PDB:

  1. In SQL*Plus, ensure that the current container is the CDB root or an application root.

When the current container is the CDB root, the PDB is created in the CDB. When the current container is an application root, the application PDB is created in the application container.

  • Execute the CREATE PLUGGABLE DATABASE statement.

The following statement creates pdb1_ref_cln from pdb1:

CREATE PLUGGABLE DATABASE pdb1_ref_cln FROM [email protected]_link REFRESH MODE EVERY 60 MINUTES;

Cloning PDBs from PDB Snapshots

You can create PDBs from PDB snapshots by executing the CREATE PLUGGABLE DATABASE … USING SNAPSHOT statement.

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. A clone from a PDB snapshot is a full, standalone PDB.

Cloning a PDB from a PDB Snapshot: Scenario

This scenario creates a new PDB from a PDB snapshot by executing CREATE PLUGGABLE DATABASE ... USING SNAPSHOT.

To clone a PDB from a PDB snapshot:

  1. In SQL*Plus, ensure that the current container is the CDB root or an application root.

When the current container is the CDB root, the PDB is created in the CDB. When the current container is an application root, the application PDB is created in the application container.

  • Execute the CREATE PLUGGABLE DATABASE ... USING SNAPSHOT statement.

The following statement clones the pdb1_copy PDB from the PDB snapshot named pdb1_wed_4_1201:

CREATE PLUGGABLE DATABASE pdb1_copy FROM pdb1 
  USING SNAPSHOT pdb1_wed_4_1201;

Creating and Materializing Snapshot Copy PDBs

You can clone a PDB from snapshots of the underlying storage. The PDB files are sparse, but you can materialize the files to create a standalone PDB.

You can create a snapshot copy PDB by executing a CREATE PLUGGABLE DATABASE ... FROM ... SNAPSHOT COPY statement. The source PDB is specified in the FROM clause.

A snapshot copy reduces the time required to create the clone because it does not include a complete copy of the source data files. Furthermore, the snapshot copy PDB occupies a fraction of the space of the source PDB.

Storage clones are named and tagged using the GUID of the target PDB. To view clone tags for storage clones, query the DBA_PDB_HISTORY.CLONETAG column.

To create a snapshot copy PDB:

  1. In SQL*Plus, ensure that the current container is the CDB root or an application root.

When the current container is the CDB root, the PDB is created in the CDB. When the current container is an application root, the application PDB is created in the application container.

  • Execute the CREATE PLUGABBLE DATABASE … SNAPSHOT COPY statement.

The following statement clones the pdb1_snap_copy PDB from pdb1:

CREATE PLUGGABLE DATABASE pdb1_snap_copy FROM pdb1 SNAPSHOT COPY;

As long as pdb1_snap_copy exists, you cannot drop the storage snapshot on which pdb1_snap_copy is based.

Materializing a Snapshot Copy PDB

You can materialize a snapshot copy PDB by running an ALTER PLUGGABLE DATABASE statement with the MATERIALIZE clause. Materializing a snapshot copy PDB copies all data blocks.

Materializing a snapshot copy PDB transforms the snapshot copy PDB, which uses sparse files, into a full PDB, which does not use sparse files. The materialized PDB is no longer dependent on the source PDB, which can be dropped or changed to a different open mode.

For example, if pd1_snap_copy is a snapshot copy PDB, then you can materialize it into a standalone PDB by running an ALTER PLUGGABLE DATABASE MATERIALIZE command. After materialization, pdb1_snap_copy no longer depends on the storage-level snapshot, enabling you to drop it.

To materialize a PDB snapshot:

  1. In SQL*Plus, ensure that the current container is the snapshot copy PDB that is being materialized.
  2. Run an ALTER PLUGGABLE DATABASE statement with the MATERIALIZE clause.

Materializing a Snapshot Copy PDB

The following SQL statement materializes a snapshot copy PDB:

ALTER PLUGGABLE DATABASE MATERIALIZE;

Creating a Split Mirror Clone PDB

In Oracle ASM, a split mirror is the process of detaching a point-in-time media copy from a parent copy. After the split, updates to the parent do not affect the child copy.

Starting in Oracle Database 18c, the parent copy can be a PDB rather than a storage volume. The split mirror clone PDB resides on the same media as the parent. The principal use case is to rapidly provision test and development PDBs in an Oracle ASM environment.

Mirror refresh is refreshing a split mirror clone PDB with changes from the parent PDB. In effect, this operation is equivalent to deleting the mirror split, and then taking a new mirror split.

To drop a split mirror clone PDB, enter ALTER PLUGGABLE DATABASE ... DROP MIRROR COPY.

To create a split mirror clone PDB:

  1. Start SQL*Plus, and connect to the CDB root.
  2. Prepare the source PDB by issuing the ALTER PLUGGABLE DATABASE ... PREPARE MIRROR COPY statement.
  3. Create a clone PDB from the source PDB by issuing the CREATE PLUGGABLE DATABASE ... FROM ... USING MIRROR COPY statement.
  4. Optionally, query V$ASM_DBCLONE_INFO view to see the relationship between the source PDB, the cloned PDB, and their file groups.