38 Creating and Removing PDBs with SQL*Plus

You can create PDBs in several different ways, including using the seed, cloning an existing PDB or non-CDB, and plugging in an unplugged PDB. You can also remove PDBs from a CDB.

38.1 About Creating and Removing PDBs

You can create a pluggable database (PDB) in a multitenant container database (CDB) by using the seed, cloning an existing PDB or non-CDB, plugging an unplugged PDB into a CDB, or using a non-CDB. You can remove a PDB from a CDB by unplugging the PDB or dropping the PDB.

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:

"About a Multitenant Environment"
Oracle Database 2 Day DBA and the DBCA online help for more information about DBCA

38.1.1 Techniques for Creating a PDB

You can use various techniques 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 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).

See Also:

38.1.2 The CREATE PLUGGABLE DATABASE Statement

You use the CREATE PLUGGABLE DATABASE statement to create a PDB. To use this statement, you must understand the clauses for the statement and when to use each clause.

All of the techniques described in Table 38-1 use this statement.

See Also:

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

38.1.2.1 Storage Limits

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

The STORAGE clause specifies 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.

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.

STORAGE (MAXSIZE 2G)

Example 38-2 STORAGE Clause That Specifies Unlimited Storage

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

STORAGE (MAXSIZE UNLIMITED)

See Also:

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

38.1.2.2 File Location of the New PDB

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

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

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

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

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

Table 38-2 Summary of File Location Clauses and Initialization Parameters

Clause or Initialization Parameter	Precedence Order	Will the Files Created by CREATE PLUGGABLE DATABASE Use Oracle Managed Files?
`FILE_NAME_CONVERT` clause	1	No
`CREATE_FILE_DEST` clause	2	Yes
`DB_CREATE_FILE_DEST` initialization parameter	3	Yes
`PDB_FILE_NAME_CONVERT` initialization parameter	4	No

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

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

Note:

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

See Also:

Oracle Database Reference for more information about the DB_CREATE_FILE_DEST initialization parameter
Oracle Database Reference for more information about the PDB_FILE_NAME_CONVERT initialization parameter

38.1.2.2.1 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-3 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:

Oracle Database SQL Language Reference for the syntax of the FILE_NAME_CONVERT clause
Example 43-7
Using Oracle Managed Files
Oracle Database Reference for information about the PDB_FILE_NAME_CONVERT initialization parameter

38.1.2.2.2 CREATE_FILE_DEST Clause

The CREATE_FILE_DEST clause of the CREATE PLUGGABLE DATABASE statement enables Oracle Managed Files for the PDB and specifies the default file system directory or Oracle ASM disk group for the PDB's files. The PDB’s data files and temp files are restricted to the specified directory and its subdirectories.

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

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

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

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

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

Note:

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

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

See Also:

"Using Oracle Managed Files"

38.1.2.3 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-5 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 (/).

See Also:

38.1.2.4 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 CREATE PLUGGABLE DATABASE statement has the following clauses that indicate location of the source files for the new PDB being created:

The SOURCE_FILE_NAME_CONVERT clause 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. The clause contains information about each file listed in the XML file.
Starting with Oracle Database 12c Release 1 (12.1.0.2), the SOURCE_FILE_DIRECTORY clause specifies the source directory of the files that will be used to create the new PDB. The clause specifies a directory that contains all of the files listed in the XML file. Using this clause is convenient when you have a large number of data files and specifying a SOURCE_FILE_NAME_CONVERT pattern for each file is not feasible.

These clauses are mutually exclusive. You cannot use both clauses in a single CREATE PLUGGABLE DATABASE statement.

You can use these clauses only when you are plugging in an unplugged PDB with the CREATE PLUGGABLE DATABASE... USING statement.

38.1.2.4.1 SOURCE_FILE_NAME_CONVERT Clause

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

38.1.2.4.2 SOURCE_FILE_DIRECTORY Clause

The SOURCE_FILE_DIRECTORY clause of the CREATE PLUGGABLE DATABASE statement specifies the source directory of the files that will be used to create the new PDB. The clause specifies a directory that contains all of the files listed in the XML file for the unplugged PDB.

When you plug in a PDB, if the source files are all present in a single directory, then you can specify the directory name in this clause. The directory is scanned to find the appropriate files based on the unplugged PDB’s XML file.

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

The absolute path of the source file directory.
NONE when no files should be copied or moved during PDB creation. Omitting the SOURCE_FILE_DIRECTORY clause is the same as specifying NONE.

You can use the SOURCE_FILE_DIRECTORY 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.

You can specify this clause for configurations that use Oracle Managed Files and for configurations that do not use Oracle Managed Files.

Example 38-7 SOURCE_FILE_DIRECTORY Clause

This SOURCE_FILE_DIRECTORY clause generates file names for the new PDB by using the source files in the /oracle/pdb5/ directory.

SOURCE_FILE_DIRECTORY = '/oracle/pdb5/'

See Also:

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

38.1.2.5 SERVICE_NAME_CONVERT Clause

The SERVICE_NAME_CONVERT clause of the CREATE PLUGGABLE DATABASE statement renames the user-defined services of the new PDB based on the service names of the source PDB.

When the service name of a new PDB conflicts with an existing service name in the CDB, plug-in violations can result. Use the SERVICE_NAME_CONVERT clause to rename services and avoid these violations.

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

One or more service names and replacement service names, in the following form:
```
'string1' , 'string2' , 'string3' , 'string4' , ...
```
The string2 service name replaces the string1 service name, and the string4 service name replaces the string3 service name. You can use as many pairs of service names and replacement service names as required.

If you specify an odd number of strings (the last string has no corresponding replacement string), then an error is returned.
NONE when no service names need to be renamed. Omitting the SERVICE_NAME_CONVERT clause is the same as specifying NONE.

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

Note:

This clause does not apply to the default service for the PDB. The default service has the same name as the PDB.

Example 38-8 SERVICE_NAME_CONVERT Clause

This SERVICE_NAME_CONVERT clause uses renames the salesrep service to salesperson.

SERVICE_NAME_CONVERT = ('salesrep','salesperson')

38.1.2.6 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, 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-9 TEMPFILE REUSE Clause

TEMPFILE REUSE

38.1.2.7 User Tablespaces

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

Note:

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

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-10 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-11 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-12 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');

38.1.2.8 PDB Tablespace Logging

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

Note:

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

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-13 Specifying the LOGGING Attribute for the PDB

LOGGING

Example 38-14 Specifying the NOLOGGING Attribute for the PDB

NOLOGGING

See Also:

"Controlling the Writing of Redo Records"
Oracle Database SQL Language Reference for more information about the logging attribute

38.1.2.9 PDB Inclusion in Standby CDBs

The STANDBYS clause of the CREATE PLUGGABLE DATABASE statement specifies whether the new PDB is included in standby CDBs.

Note:

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

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-15 STANDBYS Clause That Includes the New PDB on All Standby CDBs

STANDBYS=ALL

Example 38-16 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

38.1.2.10 Excluding Data When Cloning a PDB

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.

Note:

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

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:

Advanced Queue (AQ) tables
Clustered tables
Table clusters

Example 38-17 NO DATA Clause

NO DATA

See Also:

Oracle Database SQL Language Reference

38.2 Preparing for PDBs

Prerequisites must be met before creating a PDB.

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

The CDB must exist.

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

See Also:

"About the Current Container"

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

See Also:

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

38.3.1 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 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-3. The table describes which CREATE PLUGGABLE DATABASE clauses you must specify based on different factors.

Table 38-3 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-3 are general clauses. See "The CREATE PLUGGABLE DATABASE Statement" for more information about the general clauses.

38.3.2 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:

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

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.

Example 38-18 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:

Using Oracle Managed Files
Oracle Database Reference for information about the PDB_FILE_NAME_CONVERT initialization parameter
Oracle Database Security Guide for guidelines about choosing passwords

Example 38-19 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);

See Also:

Using Oracle Managed Files
Oracle Database Reference for information about the PDB_FILE_NAME_CONVERT initialization parameter
Oracle Database Security Guide for guidelines about choosing passwords

Example 38-20 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.
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.
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)
  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:

Oracle Database SQL Language Reference for more information about the DEFAULT TABLESPACE clause
Oracle Database Security Guide for guidelines about choosing passwords

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

You can create a PDB by cloning a local PDB, a remote PDB, or a non-CDB.

Note:

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

38.4.1 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 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 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 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-4. The table describes which CREATE PLUGGABLE DATABASE clauses you must specify based on different factors.

Table 38-4 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".
`SERVICE_NAME_CONVERT`	Do you want to use a `SERVICE_NAME_CONVERT` clause to rename the user-defined services of the new PDB based on the service names of the source PDB?	Include a `SERVICE_NAME_CONVERT` clause that specifies the new name of a service and the service name it is replacing. Specify multiple service names and replacement service names if necessary.	Omit the `SERVICE_NAME_CONVERT` clause.
`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-4 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.

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.

Note:

As long as any clones created from a source PDB with the SNAPSHOT COPY clause exist, do not drop the source PDB. When the source PDB is dropped, any such clones that are closed cannot be opened, and opened clones will raise errors.

See Also:

Oracle Automatic Storage Management Administrator's Guide for more information about Oracle ACFS
Oracle Grid Infrastructure Installation Guide for your operating system for information about Direct NFS Client
Oracle Database Advanced Security Guide for more information about Transparent Data Encryption
My Oracle Support Note 1597027.1 for more information about supported platforms for snapshot cloning of PDBs:
```
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1597027.1
```
"Determining the Current Container ID or Name"

38.4.2 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:

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

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-21 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:

Using Oracle Managed Files
Oracle Database Reference for information about the PDB_FILE_NAME_CONVERT initialization parameter

Example 38-22 Cloning a Local PDB With the PATH_PREFIX, FILE_NAME_CONVERT, and SERVICE_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.
Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.
There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.
The PDB that is being cloned (pdb1) has two user-defined services: salesrep and orders. To keep the service names in the CDB unique, these service names will be renamed to salesperson and orderentry, respectively, in the cloned PDB (pdb2).
Future tablespaces created within the PDB will be created with the NOLOGGING attribute by default. This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

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/')
  SERVICE_NAME_CONVERT = ('salesrep','salesperson','orders','orderentry')
  NOLOGGING;

Example 38-23 Cloning a Local PDB Using the FILE_NAME_CONVERT, STORAGE, and SERVICE_NAME_CONVERT Clauses

This example assumes the following factors:

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

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

To view the location of the data files for a PDB, run the query in Example 43-7.
Storage limits must be enforced for the PDB. Therefore, the STORAGE clause is required. Specifically, all tablespaces that belong to the PDB must not exceed 2 gigabytes.
The PDB that is being cloned (pdb1) has two user-defined services: salesrep and orders. To keep the service names in the CDB unique, these service names will be renamed to salesperson and orderentry, respectively, in the cloned PDB (pdb2).
There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.

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)
  SERVICE_NAME_CONVERT = ('salesrep','salesperson','orders','orderentry');

Example 38-24 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:

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.
With the root as the current container, change the source PDB to open read-only mode:
```
ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;
```

Clone the source PDB with the NO DATA clause:

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 NO DATA;

Open the cloned PDB:
```
ALTER PLUGGABLE DATABASE pdb2 OPEN;
```
With the cloned PDB pdb2 as the current container, query the table that has a large amount of data in the source PDB:
```
SELECT COUNT(*) FROM tpch.lineitem;

  COUNT(*)
----------
         0
```
The table in the cloned PDB has no rows.

38.4.3 Cloning a Remote PDB or Non-CDB

You can create 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.
- The database options installed on the source platform must be the same as, or a subset of, the database options installed on the target platform.
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 remote PDB or non-CDB:

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

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

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;

Example 38-25 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-26 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;

See Also:

Using Oracle Managed Files
Oracle Database Reference for information about the PDB_FILE_NAME_CONVERT initialization parameter

38.4.4 After Cloning a PDB

Certain rules regarding users and tablespaces apply after cloning a PDB.

The following applies after cloning a PDB:

Users in the PDB who used the default temporary tablespace of the source non-CDB or PDB use the default temporary tablespace of the cloned PDB. Users who used non-default temporary tablespaces in the non-CDB or PDB continue to use the same local temporary tablespaces in the cloned PDB. 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:

"Managing Services Associated with PDBs"
Example 43-9
Oracle Database Concepts for information about common users and local users
Oracle Database Security Guide for information about creating a local user
Oracle Database Utilities for information about using Oracle Data Pump with a CDB

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

You can create a PDB by plugging an unplugged PDB into a CDB.

38.5.1 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 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-5. The table describes which CREATE PLUGGABLE DATABASE clauses you must specify based on different factors.

Table 38-5 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.
`SERVICE_NAME_CONVERT`	Do you want to use a `SERVICE_NAME_CONVERT` clause to rename the user-defined services of the new PDB based on the service names of the source PDB?	Include a `SERVICE_NAME_CONVERT` clause that specifies the new name of a service and the service name it is replacing. Specify multiple service names and replacement service names if necessary.	Omit the `SERVICE_NAME_CONVERT` clause.
`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.
`SOURCE_FILE_DIRECTORY`	Are all of the source files in a single directory with new file names that would require multiple `SOURCE_FILE_NAME_CONVERT` entries?	Specify the `SOURCE_FILE_DIRECTORY` with the full absolute path to the source files.	Omit the `SOURCE_FILE_DIRECTORY` clause.
`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-5 are general clauses. See "The CREATE PLUGGABLE DATABASE Statement" for more information about the general clauses.

38.5.2 Plugging In an Unplugged PDB

You must meet prerequisites before 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.
- The database options installed on the source platform must be the same as, or a subset of, the database options installed on the target platform.
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:

If you are plugging in a PDB that includes data that was encrypted with Transparent Data Encryption, then follow the instructions in Oracle Database Advanced Security Guide.
If you are plugging in a Database Vault-enabled PDB, then follow the instructions in Oracle Database Vault Administrator's Guide.

To plug in a PDB:

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".
(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 2.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.
If the PDB is not unplugged, then unplug it.

See "Unplugging a PDB from a CDB".
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".
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.
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:

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-27 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 or SOURCE_FILE_DIRECTORY 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-28 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 or SOURCE_FILE_DIRECTORY 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-29 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 or SOURCE_FILE_DIRECTORY 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, and the SOURCE_FILE_NAME_CONVERT clause is used.
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.
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)
  TEMPFILE REUSE;

Example 38-30 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 or SOURCE_FILE_DIRECTORY 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-31 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 or SOURCE_FILE_DIRECTORY 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, and the SOURCE_FILE_NAME_CONVERT clause is used.
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.
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);

Example 38-32 Plugging In an Unplugged PDB Using the SOURCE_FILE_DIRECTORY, 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 or SOURCE_FILE_DIRECTORY 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, and the SOURCE_FILE_DIRECTORY clause is used.
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.
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_DIRECTORY = '/disk2/oracle/sales/'  
  MOVE
  FILE_NAME_CONVERT = ('/disk2/oracle/sales/', '/disk3/oracle/sales/')
  STORAGE (MAXSIZE 2G);

See Also:

Using Oracle Managed Files
Oracle Database Reference for information about the PDB_FILE_NAME_CONVERT initialization parameter

38.5.3 After Plugging in an Unplugged PDB

Certain rules regarding users and tablespaces apply 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 PDB use the default temporary tablespace of the target PDB. Users who used non-default temporary tablespaces in the source PDB continue to use the same local temporary tablespaces in the target PDB. 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:

"Managing Services Associated with PDBs"
Example 43-9
Oracle Database Concepts for information about common users and local users
Oracle Database Security Guide for information about creating common users and local users in a CDB
Oracle Database Utilities for information about using Oracle Data Pump with a CDB

38.6 Creating a PDB Using a Non-CDB

You can create a PDB using a non-CDB in different ways.

38.6.1 About Creating a PDB Using a Non-CDB

You can move 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 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.

38.6.2 Using the DBMS_PDB Package on a Non-CDB

You can use the DBMS_PDB package on a non-CDB to enable you to plug the non-CDB into a CDB.

38.6.2.1 About Using the DBMS_PDB Package on a Non-CDB

This technique creates a PDB from a non-CDB using the DBMS_PDB.DESCRIBE procedure.

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

38.6.2.2 Using the DBMS_PDB Package to Create an Unplugged PDB

You can move 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:

Create the CDB if it does not exist.

See Creating and Configuring a CDB.
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".
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.
Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether the non-CDB is compatible with the CDB.

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 non-CDB described by the /disk1/oracle/ncdb.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/oracle/ncdb.xml',
           pdb_name       => 'NCDB')
    WHEN TRUE THEN 'YES'
    ELSE 'NO'
END;
BEGIN
  DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
```
If the output is YES, then the non-CDB is compatible, and you can continue with the next step.

If the output is NO, then the non-CDB is not compatible, and you can check the PDB_PLUG_IN_VIOLATIONS view to see why it is not compatible. All violations must be corrected before you continue. For example, any version or patch mismatches should be resolved by running an upgrade or the datapatch utility. After correcting the violations, run DBMS_PDB.CHECK_PLUG_COMPATIBILITY again to ensure that the non-CDB is compatible with the CDB.
Shut down the non-CDB.

See "Shutting Down a Database".
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 8.

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

See Also:

"After Plugging in an Unplugged PDB"

38.7 Unplugging a PDB from a CDB

You can unplug a PDB from a CDB.

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

Figure 38-8 Unplug a PDB

Description of "Figure 38-8 Unplug a 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:

"Dropping a PDB"
"Modifying the Open Mode of PDBs" for information about closing a PDB
"Using the ALTER SYSTEM Statement to Modify a PDB" for information about initialization parameters and unplugged PDBs
Oracle Database Security Guide for information about common users and local users

38.7.2 Unplugging a PDB

You must meet prerequsites when 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:

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".
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-33 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';

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

When KEEP DATAFILES is specified, the PDB must be unplugged.
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.

Note:

This operation is destructive.

To drop a PDB:

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".
Run the DROP PLUGGABLE DATABASE statement and specify the PDB to drop.

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

DROP PLUGGABLE DATABASE salespdb
  KEEP DATAFILES;

Example 38-35 Dropping PDB salespdb and Its Data Files

DROP PLUGGABLE DATABASE salespdb
  INCLUDING DATAFILES;

See Also:

"Unplugging a PDB from a CDB"
"The SNAPSHOT COPY Clause"
Oracle Database SQL Language Reference
Oracle Database Backup and Recovery User's Guide for information about RMAN