38 Creating and Removing PDBs with SQL*Plus

This chapter contains the following topics:

About Creating and Removing PDBs

You can create a pluggable database (PDB) in a multitenant container database (CDB) in the following ways:

  • Create the new PDB by using the seed. See "About a Multitenant Environment" for information about the seed.

  • Create the new PDB by cloning an existing PDB or non-CDB.

  • Plug an unplugged PDB into a CDB.

  • Create the new PDB by using a non-CDB.

You can remove a PDB from a CDB in the following ways:

  • Unplug the PDB from a CDB.

  • Drop the PDB.

This section contains the following topics:

Note:

  • This chapter discusses using SQL statements to create and remove PDBs. An easier way to create and remove PDBs is with the graphical user interface of Database Configuration Assistant (DBCA).

  • In Oracle Database 12c Release 1 (12.1), a CDB can contain up to 253 PDBs, including the seed.

See Also:

Techniques for Creating a PDB

This section describes the techniques that you can use to create a PDB. Creating a PDB is the process of associating the PDB with a CDB. You create a PDB when you want to use the PDB as part of the CDB.

Table 38-1 describes the techniques that you can use to create a PDB.

Table 38-1 Techniques for Creating a PDB

Technique Description More Information

Create a PDB by using the seed

Create a PDB in a CDB using the files of the seed. This technique copies the files associated with the seed to a new location and associates the copied files with the new PDB.

"Creating a PDB Using the Seed"

Create a PDB by cloning an existing PDB or non-CDB

Create a PDB by cloning a source PDB or non-CDB and plugging the clone into the CDB. A source can be a PDB in the local CDB, a PDB in a remote CDB, 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.

"Creating a PDB by Cloning an Existing PDB or Non-CDB"

Create a PDB by plugging an unplugged PDB into a CDB

Create 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.

"Creating a PDB by Plugging an Unplugged PDB into a CDB"

Create a PDB by using a non-CDB

Create a PDB by moving 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.

"Creating a PDB Using a Non-CDB"


All of the techniques described in Table 38-1 use the CREATE PLUGGABLE DATABASE statement to create a PDB. These techniques fall into two main categories: copying and plugging in. Figure 38-1 depicts the options for creating a PDB:

Figure 38-1 Options for Creating a PDB

Description of Figure 38-1 follows
Description of "Figure 38-1 Options for Creating a PDB"

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.

Note:

Creating a PDB by cloning a non-CDB is available starting with Oracle Database 12c Release 1 (12.1.0.2).

The CREATE PLUGGABLE DATABASE Statement

You use the CREATE PLUGGABLE DATABASE statement to create a PDB. All of the techniques described in Table 38-1, "Techniques for Creating a PDB" use this statement.

The following sections describe the clauses for the CREATE PLUGGABLE DATABASE statement and when to use each clause:

See Also:

Oracle Database SQL Language Reference for more information about the CREATE PLUGGABLE DATABASE statement

Storage Limits

The optional STORAGE clause of the CREATE PLUGGABLE DATABASE statement 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 in the default temporary tablespace shared by all PDBs that can be used by sessions connected to the PDB

    Use MAX_SHARED_TEMP_SIZE and a size clause to specify a limit, or set MAX_SHARED_TEMP_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:

Example 38-1 STORAGE Clause That Specifies Storage Limits

This STORAGE clause specifies that the storage used by all tablespaces that belong to the PDB must not exceed 2 gigabytes. It also specifies that the storage used by the PDB sessions in the shared temporary tablespace must not exceed 100 megabytes.

STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)

Example 38-2 STORAGE Clause That Specifies Storage Limits for the Shared Temporary Tablespace Only

This STORAGE clause specifies unlimited storage for all tablespaces that belong to the PDB. It also specifies that the storage used by the PDB sessions in the shared temporary tablespace must not exceed 50 megabytes.

STORAGE (MAXSIZE UNLIMITED MAX_SHARED_TEMP_SIZE 50M)

Example 38-3 STORAGE Clause That Specifies Unlimited Storage

This STORAGE clause specifies that the PDB has unlimited storage for both its own tablespaces and the shared temporary tablespace.

STORAGE UNLIMITED

See Also:

Oracle Database SQL Language Reference for the syntax of the STORAGE clause

File Location of the New PDB

In this section, 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.

  • Starting with Oracle Database 12c Release 1 (12.1.0.2), 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 of these clauses in the same CREATE PLUGGABLE DATABASE statement. In addition, the following initialization parameters can control the location of the new PDB's 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.

When both clauses are used in the same CREATE PLUGGABLE DATABASE statement, and both initialization parameters are set, the precedence order is:

  1. The FILE_NAME_CONVERT clause

  2. The CREATE_FILE_DEST clause

  3. The DB_CREATE_FILE_DEST initialization parameter set in the root

  4. The PDB_FILE_NAME_CONVERT initialization parameter

If FILE_NAME_CONVERT and CREATE_FILE_DEST are both specified, 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.

The following sections describe the PDB file location clauses in more detail:

See Also:

Oracle Database Reference for more information about initialization parameters
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.

Example 38-4 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/')

See Also:

CREATE_FILE_DEST Clause

If the PDB will use Oracle Managed Files, then the CREATE_FILE_DEST clause of the CREATE PLUGGABLE DATABASE statement specifies the default file system directory or Oracle ASM disk group for the PDB's files.

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.

If there is a default Oracle Managed Files location for the CDB set in the root, then the CREATE_FILE_DEST setting overrides the default location for the CDB.

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 root uses Oracle Managed Files, and this clause is not specified, then the PDB inherits the Oracle Managed Files default location from the root.

Note:

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Example 38-5 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 relative directory object paths associated with the PDB are restricted to the specified directory or its subdirectories. Use this clause when you want to ensure that a PDB's files reside in a specific directory and its subdirectories when relative paths are used for directory objects.

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

  • An absolute path that is used as a prefix for all relative directory object paths associated with the PDB.

  • NONE to indicate that paths associated with directory objects are treated as absolute 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.

The PATH_PREFIX clause is ignored when absolute paths are used for directory objects.

The PATH_PREFIX clause does not affect files created by Oracle Managed Files.

Example 38-6 PATH_PREFIX Clause

This PATH_PREFIX clause ensures that all relative directory object paths associated with the PDB are relative 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 (/).

Source File Locations When Plugging In an Unplugged PDB

When you plug an unplugged PDB into a CDB, the CREATE PLUGGABLE DATABASE ... USING statement must be able to identify the PDB's files. An XML file describes the names of an unplugged PDB's source files. The XML file might not describe the names of these files accurately if you transported the unplugged files from one storage system to a different one. The files are in a new location, but the file paths in the XML file still indicate the old location. In this case, use this clause to specify the accurate names of the files. Use this clause only when you are plugging in an unplugged PDB with a CREATE PLUGGABLE DATABASE ... USING statement.

The SOURCE_FILE_NAME_CONVERT clause of the CREATE PLUGGABLE DATABASE ... USING statement specifies how to locate files (such as data files) listed in an XML file describing a PDB if they reside in a location different from that specified in the XML file.

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.

    When you use this clause, ensure that the files you want to use for the PDB reside in the replacement file name patterns. Move or copy the files to these locations if necessary.

  • NONE when no file names need to be located because the PDB's XML file describes the file names accurately. Omitting the SOURCE_FILE_NAME_CONVERT clause is the same as specifying NONE.

You can use the SOURCE_FILE_NAME_CONVERT clause only in a CREATE PLUGGABLE DATABASE statement with a USING clause. Therefore, you can use this clause only when you are plugging in an unplugged PDB.

Example 38-7 SOURCE_FILE_NAME_CONVERT Clause

This SOURCE_FILE_NAME_CONVERT clause uses the files in the /disk2/oracle/pdb7 directory instead of the /disk1/oracle/pdb7 directory. In this case, the XML file describing a PDB specifies the /disk1/oracle/pdb7 directory, but the PDB should use the files in the /disk2/oracle/pdb7 directory.

SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/pdb7/', '/disk2/oracle/pdb7/')

See Also:

Oracle Database SQL Language Reference for the syntax of the SOURCE_FILE_NAME_CONVERT clause

Temp File Reuse

The TEMPFILE REUSE clause of the CREATE PLUGGABLE DATABASE statement specifies that an existing temp file in the target location is reused. When you specify this clause, Oracle Database formats the temp file and reuses it. The previous contents of the file are lost. If this clause is specified, and there is no temp file in the target location, then Oracle Database creates a new temp file for the PDB.

If you do not specify this clause, and the new PDB will not use the CDB's default temporary tablespace, then the CREATE PLUGGABLE DATABASE statement creates a new temp file for the PDB. If a file exists with the same name as the new temp file in the target location, then an error is returned, and the PDB is not created. Therefore, if you do not specify the TEMPFILE REUSE clause, then ensure that such a temp file does not exist in the target location.

Example 38-8 TEMPFILE REUSE Clause

TEMPFILE REUSE

User Tablespaces

Note:

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

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 a number of schemas that each supported a different application, you can use this clause to separate the data belonging to each schema into a separate PDB, assuming 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 of the tablespaces.

  • Specify ALL EXCEPT to include all of the tablespaces, except for the tablespaces listed.

  • Specify NONE to exclude all of the tablespaces.

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 SYSTEM, SYSAUX, 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:

Example 38-9 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: tbs1, tbs2, and tbs3. This USER_TABLESPACES clause includes the tbs2 tablespace, but excludes the tbs1 and tbs3 tablespaces.

USER_TABLESPACES=('tbs2');

Example 38-10 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: tbs1, tbs2, tbs3, tbs4, and tbs5. This USER_TABLESPACES clause includes the tbs1, tbs4, and tbs5 tablespaces, but excludes the tbs2 and tbs3 tablespaces.

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

Example 38-11 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: tbs1, tbs2, tbs3, tbs4, and tbs5. This USER_TABLESPACES clause includes the tbs2 and tbs3 tablespaces, but excludes the tbs1, tbs4, and tbs5 tablespaces.

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

PDB Tablespace Logging

Note:

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

The logging_clause of the CREATE PLUGGABLE DATABASE statement specifies the logging attribute of the PDB. The logging attribute controls whether certain DML operations are logged in the redo log file (LOGGING) or not (NOLOGGING).

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

  • LOGGING, the default, indicates that any future tablespaces created within the PDB will be created with the LOGGING attribute by default.

  • NOLOGGING indicates that any future tablespaces created within the PDB will be created with the NOLOGGING attribute by default.

You can override the default logging attribute by specifying either LOGGING or NOLOGGING at the schema object level--for example, in a CREATE TABLE statement.

The specified attribute is used to establish the logging attribute of tablespaces created within the PDB if the logging_clause is not specified in the CREATE TABLESPACE statement.

The DBA_PDBS view shows the current logging attribute for a PDB.

Example 38-12 Specifying the LOGGING Attribute for the PDB

LOGGING

Example 38-13 Specifying the NOLOGGING Attribute for the PDB

NOLOGGING

See Also:

PDB Inclusion in Standby CDBs

Note:

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

The STANDBYS clause of the CREATE PLUGGABLE DATABASE statement specifies whether the new PDB is included in standby CDBs. You can specify one of the following values for the STANDBYS clause:

  • ALL includes the new PDB in all of the standby CDBs.

  • NONE excludes the new PDB from all of the standby CDBs.

When a PDB is not included in any of the standby CDBs, the PDB's data files are offline and marked as unnamed on all of the standby CDBs. Any new standby CDBs that are instantiated after the PDB has been created must disable the PDB for recovery explicitly to exclude it from the standby CDB. It is possible to enable a PDB on a standby CDB after it was excluded on that standby CDB.

Example 38-14 STANDBYS Clause That Includes the New PDB on All Standby CDBs

STANDBYS=ALL

Example 38-15 STANDBYS Clause That Excludes the New PDB from All Standby CDBs

STANDBYS=NONE

See Also:

Oracle Data Guard Concepts and Administration for more information about configuring PDBs on standby CDBs

Excluding Data When Cloning a PDB

Note:

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

The NO DATA clause of the CREATE PLUGGABLE DATABASE statement specifies that a PDB's data model definition is cloned but not the PDB's data. The dictionary data in the source PDB is cloned, but all user-created table and index data from the source PDB is discarded. This clause is useful for quickly creating clones of a PDB with only the object definitions and no data. Use this clause only when you are cloning a PDB with a CREATE PLUGGABLE DATABASE ... FROM statement.

This clause does not apply to the SYSTEM and SYSAUX tablespaces. If user-created database objects in the source PDB are stored in one of these tablespaces, the database objects will contain data in the cloned PDB.

When the NO DATA clause is included in the CREATE PLUGGABLE DATABASE statement, the source PDB cannot contain the following types of tables:

  • Index-organized tables

  • Advanced Queue (AQ) tables

  • Clustered tables

  • Table clusters

Example 38-16 NO DATA Clause

NO DATA

Preparing for PDBs

Ensure that the following prerequisites are met before creating a PDB:

  • The CDB must exist.

    See Chapter 37, "Creating and Configuring a CDB".

  • The CDB must be in read/write mode.

  • The current user must be a common user whose current container is the root.

  • The current user must have the CREATE PLUGGABLE DATABASE system privilege.

  • You must decide on a unique PDB name for each PDB. Each PDB name must be unique in a single CDB, and each PDB name must be unique within the scope of all the CDBs whose instances are reached through a specific listener.

    The PDB name is used to distinguish a PDB from other PDBs in the CDB. PDB names follow the same rules as service names, which includes being case-insensitive. See Oracle Database Net Services Reference for information about the rules for service names.

  • If you are creating a PDB in an Oracle Data Guard configuration with a physical standby database, then additional tasks must be completed before creating a PDB. See Oracle Data Guard Concepts and Administration for more information.

Creating a PDB Using the Seed

You can use the CREATE PLUGGABLE DATABASE statement to create a PDB in a CDB using the files of the seed. This section describes using this statement to create a new PDB.

This section contains the following topics:

See Also:

Oracle Database SQL Language Reference for more information about the CREATE PLUGGABLE DATABASE statement

About Creating a PDB from the Seed

You can use the CREATE PLUGGABLE DATABASE statement to create a new PDB by using the files of the seed. The statement copies these files to a new location and associates them with the new PDB. Figure 38-2 illustrates how this technique creates a new PDB.

Figure 38-2 Create a PDB Using the Seed Files

Description of Figure 38-2 follows
Description of "Figure 38-2 Create a PDB Using the Seed Files"

When you create a new PDB from the seed, you must specify an administrator for the PDB in the CREATE PLUGGABLE DATABASE statement. The statement creates the administrator as a local user in the PDB and grants the PDB_DBA role locally to the administrator.

When you create a PDB using the seed, you must address the questions in Table 38-2. The table describes which CREATE PLUGGABLE DATABASE clauses you must specify based on different factors.

Table 38-2 Clauses for Creating a PDB From the Seed

Clause Question Yes No

STORAGE

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.

DEFAULT TABLESPACE

Do you want to specify a default permanent tablespace for the PDB?

Specify a DEFAULT TABLESPACE clause with the appropriate limits.

Oracle Database creates a smallfile tablespace and subsequently will assign to this tablespace any non-SYSTEM users for whom you do not specify a different permanent tablespace.

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.

PATH_PREFIX

Do you want to use a PATH_PREFIX clause to ensure that all relative directory object paths associated with the PDB are treated as relative to the specified directory or its subdirectories?

The PATH_PREFIX clause is ignored when absolute paths are used for directory objects.

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.

FILE_NAME_CONVERT

Do you want to use a FILE_NAME_CONVERT clause to specify the target locations of the files?

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 clause

  • Enable 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.

See "File Location of the New PDB".

CREATE_FILE_DEST

Do you want to use a CREATE_FILE_DEST clause to specify the Oracle Managed Files default location for the PDB's files?

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 clause

  • Enable 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.

See "File Location of the New PDB".

TEMPFILE REUSE

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.

USER_TABLESPACES

Do you want to specify which tablespaces are included in the new PDB and which tablespaces are excluded from the new PDB?

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Include the USER_TABLESPACES clause and specify the tablespaces that are included in the new PDB.

Omit the USER_TABLESPACES clause.

logging_clause

Do you want to specify the logging attribute of the tablespaces in the new PDB?

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Include the logging_clause.

Omit the logging_clause.

ROLES

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. See Oracle Database Security Guide for information about predefined Oracle roles.

Omit the ROLES clause.


The ROLES clause can be used only when you are creating a PDB from the seed, but the other clauses described in Table 38-2 are general clauses. See "The CREATE PLUGGABLE DATABASE Statement" for more information about the general clauses.

Creating a PDB from the Seed

You can create a PDB from the seed using the CREATE PLUGGABLE DATABASE statement.

Before creating a PDB from the seed, complete the prerequisites described in "Preparing for PDBs".

To create a PDB from the seed: 

  1. In SQL*Plus, ensure that the current container is the root.

    See "About the Current Container" and "Accessing a Container in a CDB with SQL*Plus".

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

    See "Examples of Creating a PDB from the Seed".

    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. See "Accessing a Container in a CDB with SQL*Plus".

  3. 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.

    See "Modifying the Open Mode of PDBs" for more information.

  4. Back up the PDB.

    A PDB cannot be recovered unless it is backed up.

    Oracle Database Backup and Recovery User's Guide for information about backing up a PDB.

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.

Note:

If an error is returned during PDB creation, then the PDB being created might be in an UNUSABLE state. You can check a PDB's state by querying the CDB_PDBS or DBA_PDBS view, and you can learn more about PDB creation errors by checking the alert log. An unusable PDB can only be dropped, and it must be dropped before a PDB with the same name as the unusable PDB can be created.

Examples of Creating a PDB from the Seed

The following examples create a new PDB named salespdb and a salesadm local administrator given different factors:

Example 38-17 Creating a PDB Using No Clauses

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 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.

Given the preceding factors, the following statement creates the PDB:

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password;

See Also:

Example 38-18 Creating a PDB and Granting Predefined Oracle Roles to the PDB Administrator

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 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.

Given the preceding factors, the following statement creates the PDB:

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

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.

See Also:

Example 38-19 Creating a PDB Using the STORAGE, DEFAULT TABLESPACE, PATH_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, and the storage used by the PDB sessions in the shared temporary tablespace must not exceed 100 megabytes.

  • 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 PDB's relative directory object paths must be treated as relative to a specific directory. Therefore, the PATH_PREFIX clause is required. In this example, the PDB's relative directory object paths must be treated as relative to the /disk1/oracle/dbs/salespdb directory.

  • 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 seed on your system. In this example, Oracle Database copies the files from /disk1/oracle/dbs/pdbseed to /disk1/oracle/dbs/salespdb.

    To view the location of the data files for the seed, run the query in Example 43-7, "Showing the Data Files for Each PDB in a CDB".

  • 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.

Given the preceding factors, the following statement creates the PDB:

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  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/');

See Also:

Creating a PDB by Cloning an Existing PDB or Non-CDB

Note:

Creating a PDB by cloning a non-CDB is available starting with Oracle Database 12c Release 1 (12.1.0.2).

This section contains the following topics:

About Cloning a PDB

You can use the CREATE PLUGGABLE DATABASE statement to clone a PDB from a source PDB or from a non-CDB. This technique clones a source PDB or non-CDB and plugs the clone into the CDB. To use this technique, you must include a FROM clause that specifies the source.

The source is the existing PDB or non-CDB that is copied. The target PDB is the clone of the source. The source can be a PDB in the local CDB, a PDB in a remote CDB, or a non-CDB. The CREATE PLUGGABLE DATABASE statement copies the files associated with the source to a new location and associates the files with the target PDB.

One use of cloning is for testing. Cloning enables you to 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.

Figure 38-3 illustrates how this technique creates a new PDB when the source is a local PDB.

Figure 38-3 Clone a Local PDB

Description of Figure 38-3 follows
Description of "Figure 38-3 Clone a Local PDB"

When the source is a PDB in a remote CDB, you must specify a database link to the remote CDB in the FROM clause. The database link connects either to the root of the remote CDB or to the remote source PDB from the CDB that will contain the new PDB. Figure 38-4 illustrates how this technique creates a new PDB when the source PDB is remote.

Figure 38-4 Creating a PDB by Cloning a Remote PDB

Description of Figure 38-4 follows
Description of "Figure 38-4 Creating a PDB by 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. Figure 38-5 illustrates how this technique creates a new PDB when the source is a remote non-CDB.

Figure 38-5 Creating a PDB by Cloning a Non-CDB

Description of Figure 38-5 follows
Description of "Figure 38-5 Creating a PDB by Cloning a Non-CDB"

Note:

You cannot use the FROM clause in the CREATE PLUGGABLE DATABASE statement to create a PDB from the seed (PDB$SEED). See "Creating a PDB Using the Seed" for information about creating a PDB from the seed.

When you clone a PDB, you must address the questions in Table 38-3. The table describes which CREATE PLUGGABLE DATABASE clauses you must specify based on different factors.

Table 38-3 Clauses for Cloning a PDB

Clause Question Yes No

PATH_PREFIX

Do you want to use a PATH_PREFIX clause to ensure that all relative directory object paths associated with the PDB are treated as relative to the specified directory or its subdirectories?

The PATH_PREFIX clause is ignored when absolute paths are used for directory objects.

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.

FILE_NAME_CONVERT

Do you want to use a FILE_NAME_CONVERT clause to specify the target locations of the files?

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 clause

  • Enable 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.

See "File Location of the New PDB".

CREATE_FILE_DEST

Do you want to use a CREATE_FILE_DEST clause to specify the Oracle Managed Files default location for the PDB's files?

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 clause

  • Enable 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.

See "File Location of the New PDB".

STORAGE

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.

TEMPFILE REUSE

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.

USER_TABLESPACES

Do you want to specify which tablespaces are included in the new PDB and which tablespaces are excluded from the new PDB?

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Include the USER_TABLESPACES clause and specify the tablespaces that are included in the new PDB.

Omit the USER_TABLESPACES clause.

logging_clause

Do you want to specify the logging attribute of the tablespaces in the new PDB?

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Include the logging_clause.

Omit the logging_clause.

NO DATA

Do you want to specify that the data model definition of the source PDB is cloned but not the data of the source PDB?

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Include the NO DATA clause.

Omit the NO DATA clause.

SNAPSHOT COPY

Do you want to clone a PDB using a storage snapshot?

Specify a SNAPSHOT COPY clause to indicate that cloning is to be performed using storage snapshots.

Creating a PDB clone with storage snapshots makes creating a clone nearly instantaneous because it does not require copying the source PDB's data files. SNAPSHOT COPY is supported only if the underlying file system supports storage snapshots.

Omit the SNAPSHOT COPY clause.


Excluding the NO DATA clause and the SNAPSHOT COPY clause, the clauses described in Table 38-3 are general clauses. See "The CREATE PLUGGABLE DATABASE Statement" for more information about the general clauses.

The SNAPSHOT COPY Clause

When you use the SNAPSHOT COPY clause, all of the data files of the source PDB must be stored in the same storage type.

When you use the SNAPSHOT COPY clause to create a clone of a source PDB and the CLONEDB initialization parameter is set to FALSE, the underlying file system for the source PDB's files must support storage snapshots. Such file systems include Oracle Automatic Storage Management Cluster File System (Oracle ACFS) and Direct NFS Client storage.

When you use the SNAPSHOT COPY clause to create a clone of a source PDB and the CLONEDB initialization parameter is set to TRUE, the underlying file system for the source PDB's files can be any local file system, network file system (NFS), or clustered file system that has Direct NFS enabled. However, the source PDB must remain in open read-only mode as long as any clones exist.

Direct NFS Client enables an Oracle database to access network attached storage (NAS) devices directly, rather than using the operating system kernel NFS client. If the PDB's files are stored on Direct NFS Client storage, then the following additional requirements must be met:

  • The source PDB's files must be located on an NFS volume.

  • Storage credentials must be stored in a Transparent Data Encryption keystore.

  • The storage user must have the privileges required to create and destroy snapshots on the volume that hosts the source PDB's files.

  • Credentials must be stored in the keystore using an ADMINISTER KEY MANAGEMENT ADD SECRET SQL statement.

    The following example configures an Oracle Database secret in a software keystore:

    ADMINISTER KEY MANAGEMENT  
      ADD SECRET 'secret' FOR CLIENT 'client_name' 
      USING TAG 'storage_user' 
      IDENTIFIED BY  keystore_password  WITH BACKUP;
    

    Run this statement to add a separate entry for each storage server in the configuration. In the previous example, the following values must be specified:

    • secret is the storage password.

    • client_name is the storage server. On a Linux or UNIX platform, it is the name entered in /etc/hosts or the IP address of the storage server.

    • tag is the username passed to the storage server.

    • keystore_password is the password for the keystore.

    See Oracle Database Advanced Security Guide for more information about managing keystores and secrets.

When you use the SNAPSHOT COPY clause to create a clone of a source PDB, the following restrictions apply to the source PDB as long as any clones exist:

  • It cannot be unplugged.

  • It cannot be dropped.

PDB clones created using the SNAPSHOT COPY clause cannot be unplugged. They can only be dropped. Attempting to unplug a clone created using the SNAPSHOT COPY clause results in an error.

For a PDB created using the SNAPSHOT COPY clause in an Oracle Real Application Clusters (Oracle RAC) environment, each node that must access the PDB's files must be mounted.

Storage clones are named and tagged using the destination PDB's GUID. You can query the CLONETAG column of DBA_PDB_HISTORY view to view clone tags for storage clones.

See Also:

Cloning a Local PDB

This section describes cloning a local PDB. After cloning a local PDB, the source and target PDBs are in the same CDB.

The following prerequisites must be met:

  • Complete the prerequisites described in "Preparing for PDBs".

  • The current user must have the CREATE PLUGGABLE DATABASE system privilege in both the root and the source PDB.

  • The source PDB must be in open read-only mode.

To clone a local PDB: 

  1. In SQL*Plus, ensure that the current container is the root.

    See "About the Current Container" and "Accessing a Container in a CDB with SQL*Plus".

  2. Run the CREATE PLUGGABLE DATABASE statement, and specify the source PDB in the FROM clause. Specify other clauses when they are required.

    See "Examples of Cloning a Local PDB".

    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. See "Accessing a Container in a CDB with SQL*Plus".

  3. 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.

    See "Modifying the Open Mode of PDBs" for more information.

  4. Back up the PDB.

    A PDB cannot be recovered unless it is backed up.

    Oracle Database Backup and Recovery User's Guide for information about backing up a PDB.

Note:

If an error is returned during PDB creation, then the PDB being created might be in an UNUSABLE state. You can check a PDB's state by querying the CDB_PDBS or DBA_PDBS view, and you can learn more about PDB creation errors by checking the alert log. An unusable PDB can only be dropped, and it must be dropped before a PDB with the same name as the unusable PDB can be created.

Examples of Cloning a Local PDB

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

Example 38-20 Cloning a Local PDB Using No Clauses

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.

Given the preceding factors, the following statement clones the pdb2 PDB from the pdb1 PDB:

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;

See Also:

Example 38-21 Cloning a Local PDB With the PATH_PREFIX and FILE_NAME_CONVERT Clauses

This example assumes the following factors:

  • The PDB's relative directory object paths must be treated as relative to a specific directory. Therefore, the PATH_PREFIX clause is required. In this example, the PDB's relative directory object paths must be treated as relative to the /disk2/oracle/pdb2 directory and its subdirectories.

  • 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.

    To view the location of the data files for a PDB, run the query in Example 43-7, "Showing the Data Files for Each PDB in a CDB".

  • 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.

  • 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).

Given the preceding factors, 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/')
  NOLOGGING;

Example 38-22 Cloning a Local PDB Using the FILE_NAME_CONVERT and STORAGE 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.

    To view the location of the data files for a PDB, run the query in Example 43-7, "Showing the Data Files for Each PDB in a CDB".

  • 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, and the storage used by the PDB sessions in the shared temporary tablespace must not exceed 100 megabytes.

  • 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.

Given the preceding factors, 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 MAX_SHARED_TEMP_SIZE 100M);

Example 38-23 Cloning a Local PDB Without Cloning Its Data

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 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.

Assume that the source PDB pdb1 has a large amount of data. The following steps illustrate how the clone does not contain the source PDB's data 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(*)
    ----------
       6001215
    

    The table has over six million rows.

  2. With the root as the current container, change the source PDB to open read-only mode:

    ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;
    
  3. Clone the source PDB with the NO DATA clause:

    CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 NO DATA;
    
  4. Open the cloned PDB:

    ALTER PLUGGABLE DATABASE pdb2 OPEN;
    
  5. 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 or Non-CDB

This section describes creating a PDB by cloning a remote source. The remote source can be a remote PDB or non-CDB. After the cloning operation is complete, the source and the target PDB are in different locations.

The following prerequisites must be met:

  • Complete the prerequisites described in "Preparing for PDBs".

  • The current user must have the CREATE PLUGGABLE DATABASE system privilege in the root of the CDB that will contain the target PDB.

  • The source PDB or source non-CDB must be in open read-only mode.

  • A database link must enable a connection from the CDB that will contain the target PDB to the remote source. If the source is a remote PDB, then the database link can connect to either the root of the remote CDB or to the remote source PDB.

  • The user that the database link connects with at the remote source must have the CREATE PLUGGABLE DATABASE system privilege in the source PDB or in the non-CDB.

  • If the database link connects to the root in a remote CDB, then the user that the database link connects with must be a common user.

  • The source and target platforms must meet these requirements:

    • They must have the same endianness.

    • They must have the same set of database options installed.

  • The source and target must have compatible character sets and national character sets. To be compatible, the character sets and national character sets must meet all of the requirements specified in Oracle Database Globalization Support Guide.

  • If you are creating a PDB by cloning a non-CDB, then both the CDB and the non-CDB must be running Oracle Database 12c Release 1 (12.1.0.2) or later.

To clone a PDB or non-CDB: 

  1. In SQL*Plus, ensure that the current container is the root of the CDB that will contain the new PDB.

    See "About the Current Container" and "Accessing a Container in a CDB with SQL*Plus".

  2. Run the CREATE PLUGGABLE DATABASE statement, and specify the source PDB or the source non-CDB in the FROM clause. Specify other clauses when they are required.

    See Example 38-24, "Creating a PDB by Cloning a Remote PDB Using No Clauses".

    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. See "Accessing a Container in a CDB with SQL*Plus".

  3. If you created the PDB from a non-CDB, then run the ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script. This script must be run before the PDB can be opened for the first time.

    If the PDB was not a non-CDB, then running the noncdb_to_pdb.sql script is not required.

    To run the noncdb_to_pdb.sql script, complete the following steps:

    1. Access the PDB.

      The current user must have SYSDBA administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS SYSDBA at connect time.

      See "Accessing a Container in a CDB with SQL*Plus".

    2. Run the noncdb_to_pdb.sql script:

      @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
      

    The script opens the PDB, performs changes, and closes the PDB when the changes are complete.

  4. 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.

    See "Modifying the Open Mode of PDBs" for more information.

  5. Back up the PDB.

    A PDB cannot be recovered unless it is backed up.

    Oracle Database Backup and Recovery User's Guide for information about backing up a PDB.

Note:

If an error is returned during PDB creation, then the PDB being created might be in an UNUSABLE state. You can check a PDB's state by querying the CDB_PDBS or DBA_PDBS view, and you can learn more about PDB creation errors by checking the alert log. An unusable PDB can only be dropped, and it must be dropped before a PDB with the same name as the unusable PDB can be created.

Example 38-24 Creating a PDB by Cloning a Remote PDB Using No Clauses

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.

Given the preceding factors, the following statement clones the pdb2 PDB from the pdb1 remote PDB:

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1@pdb1_link;

Example 38-25 Creating a PDB by Cloning a Remote Non-CDB

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.

Given the preceding factors, the following statement creates the pdb2 PDB from the remote non-CDB named mydb:

CREATE PLUGGABLE DATABASE pdb2 FROM mydb@mydb_link;

When the source 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 NON$CDB@mydb_link;

See Also:

After Cloning a PDB

The following applies after cloning a PDB:

  • Users in the PDB who used the default temporary tablespace of the source CDB or source non-CDB use the default temporary tablespace of the target CDB. When the source is a PDB, users who used temporary tablespaces local to the PDB continue to use the same local temporary tablespaces. See "About Managing Tablespaces in a CDB".

  • When cloning a remote PDB, user-created common users that existed in the source CDB but not in the target CDB do not have any privileges granted commonly. However, if the target CDB has a common user with the same name as a common user in the PDB, the latter is linked to the former and has the privileges granted to this common user in the target CDB.

    If the target CDB does not have a common user with the same name, then the user account is locked in the target PDB. You have the following options regarding each of these locked users:

    • Close the PDB, connect to the root, and create a common user with the same name. When the PDB is opened in read/write mode, differences in roles and privileges granted commonly to the user are resolved, and you can unlock the user. Privileges and roles granted locally to the user remain unchanged during this process.

    • You can create a new local user in the PDB and use Data Pump to export/import the locked user's data into the new local user's schema.

    • You can leave the user locked.

    • You can drop the user.

See Also:

Creating a PDB by Plugging an Unplugged PDB into a CDB

This section contains the following topics:

About Plugging In an Unplugged PDB

This technique plugs in an unplugged PDB. This technique uses the XML metadata file that describes the PDB and the files associated with the PDB to plug it into the CDB.

The XML metadata file specifies the locations of the PDB's files, and the USING clause of the CREATE PLUGGABLE DATABASE statement specifies the XML metadata file. Figure 38-6 illustrates how this technique creates a new PDB.

Figure 38-6 Plug In an Unplugged PDB

Description of Figure 38-6 follows
Description of "Figure 38-6 Plug In an Unplugged PDB"

An unplugged PDB consists of an XML file that describes the PDB and the PDB's files (such as the data files and wallet file). You can use the CREATE PLUGGABLE DATABASE statement to plug in an unplugged PDB. To do so, you must include a USING clause that specifies the XML file that describes the PDB.

The source CDB is the CDB from which the PDB was unplugged. The target CDB is the CDB into which you are plugging the PDB. The source CDB and target CDB can be the same CDB or different CDBs.

When you plug in an unplugged PDB, you must address the questions in Table 38-4. The table describes which CREATE PLUGGABLE DATABASE clauses you must specify based on different factors.

Table 38-4 Clauses for Plugging In an Unplugged PDB

Clause Question Yes No

AS CLONE

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 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.

PATH_PREFIX

Do you want to use a PATH_PREFIX clause to ensure that all relative directory object paths associated with the PDB are treated as relative to the specified directory or its subdirectories?

The PATH_PREFIX clause is ignored when absolute paths are used for directory objects.

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.

SOURCE_FILE_NAME_CONVERT

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.

NOCOPY, COPY, MOVE, FILE_NAME_CONVERT, CREATE_FILE_DEST

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:

  • In the CREATE PLUGGABLE DATABASE statement, include a FILE_NAME_CONVERT clause that specifies the target locations based on the names of the source files.

  • In the CREATE PLUGGABLE DATABASE statement, 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.

See "File Location of the New PDB".

Specify NOCOPY.

STORAGE

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.

TEMPFILE REUSE

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.

USER_TABLESPACES

Do you want to specify which tablespaces are included in the new PDB and which tablespaces are excluded from the new PDB?

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Include the USER_TABLESPACES clause and specify the tablespaces that are included in the new PDB.

Omit the USER_TABLESPACES clause.

logging_clause

Do you want to specify the logging attribute of the tablespaces in the new PDB?

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Include the logging_clause.

Omit the logging_clause.


You can use the AS CLONE clause and SOURCE_FILE_NAME_CONVERT clause only when you are plugging in an unplugged PDB, but the other clauses described in Table 38-4 are general clauses. See "The CREATE PLUGGABLE DATABASE Statement" for more information about the general clauses.

Plugging In an Unplugged PDB

To plug in an unplugged PDB, the following prerequisites must be met:

  • Complete the prerequisites described in "Preparing for PDBs".

  • The XML file that describes the PDB must exist in a location that is accessible to the CDB.

    The USING clause must specify the XML file.

    If the PDB's XML file is unusable or cannot be located, then you can use the DBMS_PDB.RECOVER procedure to generate an XML file using the PDB's data files. See Oracle Database PL/SQL Packages and Types Reference for more information about this procedure.

  • The files associated with the PDB (such as the data files and wallet file) must exist in a location that is accessible to the CDB.

  • The source and target CDB platforms must meet the following requirements:

    • They must have the same endianness.

    • They must have the same set of database options installed.

  • The CDB that contained the unplugged PDB and the target CDB must have compatible character sets and national character sets. To be compatible, the character sets and national character sets must meet all of the requirements specified in Oracle Database Globalization Support Guide.

You can use the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether these requirements are met. Step 2 in the following procedure describes using this function.

Note:

To plug in a PDB: 

  1. In SQL*Plus, ensure that the current container is the root of the CDB into which you want to plug the PDB.

    See "About the Current Container" and "Accessing a Container in a CDB with SQL*Plus".

  2. (Optional) Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether the unplugged PDB is compatible with the CDB.

    1. If the PDB is not yet unplugged, then run the DBMS_PDB.DESCRIBE procedure to produce an XML file that describes the PDB.

      If the PDB is already unplugged, then proceed to Step b.

      For example, to generate an XML file named salespdb.xml in the /disk1/oracle directory, run the following procedure:

      BEGIN
        DBMS_PDB.DESCRIBE(
          pdb_descr_file => '/disk1/oracle/salespdb.xml',
          pdb_name       => 'SALESPDB');
      END;
      /
      

      If the PDB is in a remote CDB, then you can include @database_link_name in the pdb_name parameter, where database_link_name is the name of a valid database link to the remote CDB or to the PDB. For example, if the database link name to the remote CDB is rcdb, then set the pdb_name value to SALESPDB@rcdb.

    2. Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function.

      When you run the function, set the following parameters:

      - pdb_descr_file - Set this parameter to the full path to the XML file.

      - pdb_name - Specify the name of the new PDB. If this parameter is omitted, then the PDB name in the XML file is used.

      For example, to determine whether a PDB described by the /disk1/usr/salespdb.xml file is compatible with the current CDB, run the following PL/SQL block:

      SET SERVEROUTPUT ON
      DECLARE
        compatible CONSTANT VARCHAR2(3) := 
          CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
                 pdb_descr_file => '/disk1/usr/salespdb.xml',
                 pdb_name       => 'SALESPDB')
          WHEN TRUE THEN 'YES'
          ELSE 'NO'
      END;
      BEGIN
        DBMS_OUTPUT.PUT_LINE(compatible);
      END;
      /
      

      If the output is YES, then the PDB is compatible, and you can continue with the next step.

      If the output is NO, then the PDB is not compatible, and you can check the PDB_PLUG_IN_VIOLATIONS view to see why it is not compatible.

  3. If the PDB is not unplugged, then unplug it.

    See "Unplugging a PDB from a CDB".

  4. Run the CREATE PLUGGABLE DATABASE statement, and specify the XML file in the USING clause. Specify other clauses when they are required.

    See "Examples of Plugging In an Unplugged PDB".

    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. See "Accessing a Container in a CDB with SQL*Plus".

  5. 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.

    See "Modifying the Open Mode of PDBs" for more information.

  6. Back up the PDB.

    A PDB cannot be recovered unless it is backed up.

    Oracle Database Backup and Recovery User's Guide for information about backing up a PDB.

Note:

If an error is returned during PDB creation, then the PDB being created might be in an UNUSABLE state. You can check a PDB's state by querying the CDB_PDBS or DBA_PDBS view, and you can learn more about PDB creation errors by checking the alert log. An unusable PDB can only be dropped, and it must be dropped before a PDB with the same name as the unusable PDB can be created.

Examples of Plugging In an Unplugged PDB

The following examples plug in an unplugged PDB named salespdb using the /disk1/usr/salespdb.xml file given different factors:

Example 38-26 Plugging In an Unplugged PDB Using the NOCOPY Clause

This example assumes the following factors:

  • The new PDB is not based on the same unplugged PDB that was used to create an existing PDB in the CDB. Therefore, the AS CLONE clause is not required.

  • The PATH_PREFIX clause is not required.

  • The XML file accurately describes the current locations of the files. Therefore, the SOURCE_FILE_NAME_CONVERT clause is not required.

  • The files are in the correct location. Therefore, NOCOPY is included.

  • Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.

  • A file with the same name as the temp file specified in the XML file exists in the target location. Therefore, the TEMPFILE REUSE clause is required.

Given the preceding factors, the following statement plugs in the PDB:

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml' 
  NOCOPY 
  TEMPFILE REUSE;

Example 38-27 Plugging In an Unplugged PDB Using the AS CLONE and NOCOPY Clauses

This example assumes the following factors:

  • The new PDB is based on the same unplugged PDB that was used to create an existing PDB in the CDB. Therefore, the AS CLONE clause is required. The AS CLONE clause ensures that the new PDB has unique identifiers.

  • The PATH_PREFIX clause is not required.

  • The XML file accurately describes the current locations of the files. Therefore, the SOURCE_FILE_NAME_CONVERT clause is not required.

  • The files are in the correct location. Therefore, NOCOPY is included.

  • Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.

  • A file with the same name as the temp file specified in the XML file exists in the target location. Therefore, the TEMPFILE REUSE clause is required.

Given the preceding factors, the following statement plugs in the PDB:

CREATE PLUGGABLE DATABASE salespdb AS CLONE USING '/disk1/usr/salespdb.xml' 
  NOCOPY
  TEMPFILE REUSE;

Example 38-28 Plugging In an Unplugged PDB Using the SOURCE_FILE_NAME_CONVERT, NOCOPY, and STORAGE Clauses

This example assumes the following factors:

  • The new PDB is not based on the same unplugged PDB that was used to create an existing PDB in the CDB. Therefore, the AS CLONE clause is not required.

  • The PATH_PREFIX clause is not required.

  • The XML file does not accurately describe the current locations of the files. Therefore, the SOURCE_FILE_NAME_CONVERT clause is required. In this example, the XML file indicates that the files are in /disk1/oracle/sales, but the files are in /disk2/oracle/sales.

  • The files are in the correct location. Therefore, NOCOPY is included.

  • 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, and the storage used by the PDB sessions in the shared temporary tablespace must not exceed 100 megabytes.

  • A file with the same name as the temp file specified in the XML file exists in the target location. Therefore, the TEMPFILE REUSE clause is required.

Given the preceding factors, the following statement plugs in the PDB:

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml' 
  SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/sales/', '/disk2/oracle/sales/')
  NOCOPY
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  TEMPFILE REUSE;

Example 38-29 Plugging In an Unplugged PDB With the COPY, PATH_PREFIX, and FILE_NAME_CONVERT Clauses

This example assumes the following factors:

  • The new PDB is not based on the same unplugged PDB that was used to create an existing PDB in the CDB. Therefore, the AS CLONE clause is not required.

  • The PDB's relative directory object paths must be treated as relative to a specific directory. Therefore, the PATH_PREFIX clause is required. In this example, the PDB's relative directory object paths must be treated as relative to the /disk2/oracle/sales directory and its subdirectories.

  • The XML file accurately describes the current locations of the files. Therefore, the SOURCE_FILE_NAME_CONVERT clause is not required.

  • The files are not in the correct location. Therefore, COPY or MOVE must be included. In this example, the files are copied.

    The CREATE_FILE_DEST clause is not 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. In this example, the files are copied from /disk1/oracle/sales to /disk2/oracle/sales.

  • 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.

Given the preceding factors, the following statement plugs in the PDB:

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml' 
  COPY
  PATH_PREFIX = '/disk2/oracle/sales/'
  FILE_NAME_CONVERT = ('/disk1/oracle/sales/', '/disk2/oracle/sales/');

Example 38-30 Plugging In an Unplugged PDB Using the SOURCE_FILE_NAME_CONVERT, MOVE, FILE_NAME_CONVERT, and STORAGE Clauses

This example assumes the following factors:

  • The new PDB is not based on the same unplugged PDB that was used to create an existing PDB in the CDB. Therefore, the AS CLONE clause is not required.

  • The PATH_PREFIX clause is not required.

  • The XML file does not accurately describe the current locations of the files. Therefore, the SOURCE_FILE_NAME_CONVERT clause is required. In this example, the XML file indicates that the files are in /disk1/oracle/sales, but the files are in /disk2/oracle/sales.

  • The files are not in the correct final location for the PDB. Therefore, COPY or MOVE must be included. In this example, MOVE is specified to move the files.

    The CREATE_FILE_DEST clause is not 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. In this example, the files are moved from /disk2/oracle/sales to /disk3/oracle/sales.

  • 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, and the storage used by the PDB sessions in the shared temporary tablespace must not exceed 100 megabytes.

  • 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.

Given the preceding factors, the following statement plugs in the PDB:

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml' 
  SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/sales/', '/disk2/oracle/sales/')
  MOVE
  FILE_NAME_CONVERT = ('/disk2/oracle/sales/', '/disk3/oracle/sales/')
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M);

See Also:

After Plugging in an Unplugged PDB

The following applies after plugging in an unplugged PDB:

  • Users in the PDB who used the default temporary tablespace of the source CDB use the default temporary tablespace of the target CDB. Users who used temporary tablespaces local to the PDB continue to use the same local temporary tablespaces. See "About Managing Tablespaces in a CDB".

  • User-created common users that existed in the source CDB but not in the target CDB do not have any privileges granted commonly. However, if the target CDB has a common user with the same name as a common user in the PDB, the latter is linked to the former and has the privileges granted to this common user in the target CDB.

    If the target CDB does not have a common user with the same name, then the user account is locked in the target PDB. You have the following options regarding each of these locked users:

    • Close the PDB, connect to the root, and create a common user with the same name. When the PDB is opened in read/write mode, differences in roles and privileges granted commonly to the user are resolved, and you can unlock the user. Privileges and roles granted locally to the user remain unchanged during this process.

    • You can create a new local user in the PDB and use Data Pump to export/import the locked user's data into the new local user's schema.

    • You can leave the user locked.

    • You can drop the user.

See Also:

Creating a PDB Using a Non-CDB

This section describes moving a non-CDB into a PDB. You can accomplish this task in the following ways:

  • Creating a PDB by cloning a non-CDB

    Starting with Oracle Database 12c Release 1 (12.1.0.2), you can create a PDB by cloning a non-CDB. This method 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.

    See "Creating a PDB by Cloning an Existing PDB or Non-CDB" for instructions.

    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 Oracle Database 12c Release 1 (12.1.0.2) to use this technique. See Oracle Database Upgrade Guide for information about upgrading.

  • Use the DBMS_PDB package to generate an XML metadata file.

    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.

    "Using the DBMS_PDB Package on a Non-CDB" describes using this technique.

    To use this technique, the non-CDB must be an Oracle Database 12c non-CDB. If your current non-CDB uses an Oracle Database release before Oracle Database 12c, then you must upgrade the non-CDB to Oracle Database 12c to use this technique. See Oracle Database Upgrade Guide for information about upgrading.

  • Use Oracle Data Pump export/import.

    You export the data from the non-CDB and import it into a PDB.

    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, 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.

    See Chapter 15, "Transporting Data".

  • Use GoldenGate replication.

    You replicate the data from the non-CDB to a PDB. When the PDB catches up with the non-CDB, you fail over to the PDB.

    See the Oracle GoldenGate documentation.

Using the DBMS_PDB Package on a Non-CDB

This section describes using the DBMS_PDB package on a non-CDB to enable you to plug the non-CDB into a CDB.

This section contains the following topics:

About Using the DBMS_PDB Package on a Non-CDB

This technique creates a PDB from a non-CDB. You run the DBMS_PDB.DESCRIBE procedure on the non-CDB to generate the XML file that describes the database files of the non-CDB. After the XML file is generated, you can plug in the non-CDB in the same way that you can plug in an unplugged PDB. Specifically, you specify the USING clause in the CREATE PLUGGABLE DATABASE statement. When the non-CDB is plugged in to a CDB, it is a PDB.

Figure 38-7 Plug In a Non-CDB Using the DBMS_PDB.DESCRIBE Procedure

Description of Figure 38-7 follows
Description of "Figure 38-7 Plug In a Non-CDB Using the DBMS_PDB.DESCRIBE Procedure"

Note:

To use this technique, the non-CDB must be an Oracle Database 12c non-CDB.

Using the DBMS_PDB Package to Create an Unplugged PDB

This section describes moving a non-CDB into a PDB by using the DBMS_PDB.DESCRIBE procedure.

To move a non-CDB into a PDB using the DBMS_PDB package: 

  1. Create the CDB if it does not exist.

    See Chapter 37, "Creating and Configuring a CDB".

  2. Ensure that the non-CDB is in a transactionally-consistent state and place it in read-only mode.

    See "Opening a Database in Read-Only Mode".

  3. Connect to the non-CDB, and run the DBMS_PDB.DESCRIBE procedure to construct an XML file that describes the non-CDB.

    The current user must have SYSDBA administrative privilege. The user must exercise the privilege using AS SYSDBA at connect time.

    For example, to generate an XML file named ncdb.xml in the /disk1/oracle directory, run the following procedure:

    BEGIN
      DBMS_PDB.DESCRIBE(
        pdb_descr_file => '/disk1/oracle/ncdb.xml');
    END;
    /
    

    After the procedure completes successfully, you can use the XML file and the non-CDB's database files to plug the non-CDB into a CDB.

  4. Shut down the non-CDB.

    See "Shutting Down a Database".

  5. Plug in the non-CDB.

    Follow the instructions in "Creating a PDB by Plugging an Unplugged PDB into a CDB" to plug in the non-CDB.

    For example, the following SQL statement plugs in a non-CDB, copies its files to a new location, and includes only the tbs3 user tablespace from the non-CDB:

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

    Do not open the new PDB. You will open it in step 7.

    The USER_TABLESPACES clause enables you to separate data that was used for multiple tenants in a non-CDB into different PDBs. You can use multiple CREATE PLUGGABLE DATABASE statements with this clause to create other PDBs that include the data from other tablespaces that existed in the non-CDB.

  6. Run the ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script. This script must be run before the PDB can be opened for the first time. See "Creating a PDB Using a Non-CDB".

    If the PDB was not a non-CDB, then running the noncdb_to_pdb.sql script is not required.

    To run the noncdb_to_pdb.sql script, complete the following steps:

    1. Access the PDB.

      The current user must have SYSDBA administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS SYSDBA at connect time.

      See "Accessing a Container in a CDB with SQL*Plus".

    2. Run the noncdb_to_pdb.sql script:

      @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
      

    The script opens the PDB, performs changes, and closes the PDB when the changes are complete.

  7. 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.

    See "Modifying the Open Mode of PDBs" for more information.

  8. Back up the PDB.

    A PDB cannot be recovered unless it is backed up.

    Oracle Database Backup and Recovery User's Guide for information about backing up a PDB.

Note:

If an error is returned during PDB creation, then the PDB being created might be in an UNUSABLE state. You can check a PDB's state by querying the CDB_PDBS or DBA_PDBS view, and you can learn more about PDB creation errors by checking the alert log. An unusable PDB can only be dropped, and it must be dropped before a PDB with the same name as the unusable PDB can be created.

Unplugging a PDB from a CDB

This section contains the following topics:

About Unplugging a PDB

Unplugging a PDB disassociates the PDB from a CDB. You unplug a PDB when you want to move the PDB to a different CDB or when you no longer want the PDB to be available.

To unplug a PDB, connect to the root and use the ALTER PLUGGABLE DATABASE statement to specify an XML file that will contain metadata about the PDB after it is unplugged. The SQL statement creates the XML file, and it contains the required information to enable a CREATE PLUGGABLE DATABASE statement on a target CDB to plug in the PDB.

The PDB must be closed before it can be unplugged. When you unplug a PDB from a CDB, the unplugged PDB is in mounted mode. The unplug operation makes some changes in the PDB's data files to record, for example, that the PDB was successfully unplugged. Because it is still part of the CDB, the unplugged PDB is included in an RMAN backup of the entire CDB. Such a backup provides a convenient way to archive the unplugged PDB in case it is needed in the future.

To completely remove the PDB from the CDB, you can drop the PDB. The only operation supported on an unplugged PDB is dropping the PDB. The PDB must be dropped from the CDB before it can be plugged back into the same CDB. A PDB is usable only when it is plugged into a CDB.

See Also:

Unplugging a PDB

The following prerequisites must be met:

  • The current user must have SYSDBA or SYSOPER administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS SYSDBA or AS SYSOPER at connect time.

  • The PDB must have been opened at least once.

  • The PDB must be closed. In an Oracle Real Application Clusters (Oracle RAC) environment, the PDB must be closed on all instances.

Note:

If you are unplugging in a PDB that includes data that was encrypted with Transparent Data Encryption, then follow the instructions in Oracle Database Advanced Security Guide.

To unplug a PDB: 

  1. In SQL*Plus, ensure that the current container is the root.

    See "About the Current Container" and "Accessing a Container in a CDB with SQL*Plus".

  2. Run the ALTER PLUGGABLE DATABASE statement with the UNPLUG INTO clause, and specify the PDB to unplug and the name and location of the PDB's XML metadata file.

Example 38-31 Unplugging PDB salespdb

This ALTER PLUGGABLE DATABASE statement unplugs the PDB salespdb and creates the salespdb.xml metadata file in the /oracle/data/ directory:

ALTER PLUGGABLE DATABASE salespdb UNPLUG INTO '/oracle/data/salespdb.xml';

Dropping a PDB

The DROP PLUGGABLE DATABASE statement drops a PDB. You can drop a PDB when you want to move the PDB from one CDB to another or when you no longer need the PDB.

When you drop a PDB, the control file of the CDB is modified to eliminate all references to the dropped PDB. Archived redo log files and backups associated with the PDB are not removed, but you can use Oracle Recovery Manager (RMAN) to remove them.

When dropping a PDB, you can either keep or delete the PDB's data files by using one of the following clauses:

  • KEEP DATAFILES, the default, retains the data files.

    The PDB's temp file is removed even when KEEP DATAFILES is specified because the temp file is no longer needed.

  • INCLUDING DATAFILES removes the data files from disk.

    If a PDB was created with the SNAPSHOT COPY clause, then you must specify INCLUDING DATAFILES when you drop the PDB.

The following prerequisites must be met:

  • The PDB must be in mounted mode, or it must be unplugged.

    See "Modifying the Open Mode of PDBs".

    See "Unplugging a PDB from a CDB".

  • The current user must have SYSDBA or SYSOPER administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS SYSDBA or AS SYSOPER at connect time.

Caution:

This operation is destructive.

To drop a PDB: 

  1. In SQL*Plus, ensure that the current container is the root.

    See "About the Current Container" and "Accessing a Container in a CDB with SQL*Plus".

  2. Run the DROP PLUGGABLE DATABASE statement and specify the PDB to drop.

Example 38-32 Dropping PDB salespdb While Keeping Its Data Files

DROP PLUGGABLE DATABASE salespdb
  KEEP DATAFILES;

Example 38-33 Dropping PDB salespdb and Its Data Files

DROP PLUGGABLE DATABASE salespdb
  INCLUDING DATAFILES;