38 Creating and Removing PDBs with SQL*Plus

Techniques for Creating a PDB

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

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

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.

The CREATE PLUGGABLE DATABASE Statement

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

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

• Storage Limits

• File Location of the New PDB

• Restrictions on PDB File Locations

• Source File Locations When Plugging In an Unplugged PDB

• Temp File Reuse

• User Tablespaces

• PDB Tablespace Logging

• PDB Inclusion in Standby CDBs

• Excluding Data When Cloning a PDB

STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)

STORAGE (MAXSIZE UNLIMITED MAX_SHARED_TEMP_SIZE 50M)

STORAGE UNLIMITED

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

CREATE_FILE_DEST = '/oracle/pdb2/'

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

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

TEMPFILE REUSE

USER_TABLESPACES=('tbs2');

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

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

LOGGING

NOLOGGING

STANDBYS=ALL

STANDBYS=NONE

NO DATA

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

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

Creating a PDB from the Seed

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

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

To create a PDB from the seed: 

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

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

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

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

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

   A new default service is created for the PDB. The service has the same name as the PDB and can be used to access the PDB. Oracle Net Services must be configured properly for clients to access this service. See "Accessing a Container in a CDB with SQL*Plus".

3. Open the new PDB in read/write mode.

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

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

4. Back up the PDB.

   A PDB cannot be recovered unless it is backed up.

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

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

Examples of Creating a PDB from the Seed

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

• Example 38-17, "Creating a PDB Using No Clauses"

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

• Example 38-19, "Creating a PDB Using the STORAGE, DEFAULT TABLESPACE, PATH_PREFIX, and FILE_NAME_CONVERT Clauses"

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password;

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

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

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

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"

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

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

The SNAPSHOT COPY Clause

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

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

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

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

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

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

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

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

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

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

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

  • secret is the storage password.

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

  • tag is the username passed to the storage server.

  • keystore_password is the password for the keystore.

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

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

• It cannot be unplugged.

• It cannot be dropped.

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

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

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

Cloning a Local PDB

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

The following prerequisites must be met:

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

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

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

To clone a local PDB: 

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

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

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

   See "Examples of Cloning a Local PDB".

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

   A new default service is created for the PDB. The service has the same name as the PDB and can be used to access the PDB. Oracle Net Services must be configured properly for clients to access this service. See "Accessing a Container in a CDB with SQL*Plus".

3. Open the new PDB in read/write mode.

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

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

4. Back up the PDB.

   A PDB cannot be recovered unless it is backed up.

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

Examples of Cloning a Local PDB

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

• Example 38-20, "Cloning a Local PDB Using No Clauses"

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

• Example 38-22, "Cloning a Local PDB Using the FILE_NAME_CONVERT and STORAGE Clauses"

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 
  PATH_PREFIX = '/disk2/oracle/pdb2'
  FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/')
  NOLOGGING;

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 
  FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/')
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M);

Cloning a Remote PDB or Non-CDB

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

The following prerequisites must be met:

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

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

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

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

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

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

• The source and target platforms must meet these requirements:

  • They must have the same endianness.

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

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

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

To clone a PDB or non-CDB: 

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

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

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

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

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

   A new default service is created for the PDB. The service has the same name as the PDB and can be used to access the PDB. Oracle Net Services must be configured properly for clients to access this service. See "Accessing a Container in a CDB with SQL*Plus".

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

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

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

   1. Access the PDB.

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

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

   2. Run the noncdb_to_pdb.sql script:

      @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
      

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

4. Open the new PDB in read/write mode.

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

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

5. Back up the PDB.

   A PDB cannot be recovered unless it is backed up.

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

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1@pdb1_link;

CREATE PLUGGABLE DATABASE pdb2 FROM mydb@mydb_link;

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

CREATE PLUGGABLE DATABASE pdb2 FROM NON$CDB@mydb_link;

After Cloning a PDB

The following applies after cloning a PDB:

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

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

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

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

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

  • You can leave the user locked.

  • You can drop the user.

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

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

Plugging In an Unplugged PDB

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

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

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

  The USING clause must specify the XML file.

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

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

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

  • They must have the same endianness.

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

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

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

To plug in a PDB: 

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

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

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

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

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

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

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

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

   2. Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function.

      When you run the function, set the following parameters:

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

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

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

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

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

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

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

   See "Unplugging a PDB from a CDB".

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

   See "Examples of Plugging In an Unplugged PDB".

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

   A new default service is created for the PDB. The service has the same name as the PDB and can be used to access the PDB. Oracle Net Services must be configured properly for clients to access this service. See "Accessing a Container in a CDB with SQL*Plus".

5. Open the new PDB in read/write mode.

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

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

6. Back up the PDB.

   A PDB cannot be recovered unless it is backed up.

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

Examples of Plugging In an Unplugged PDB

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

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

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

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

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

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

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

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

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

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

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

After Plugging in an Unplugged PDB

The following applies after plugging in an unplugged PDB:

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

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

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

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

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

  • You can leave the user locked.

  • You can drop the user.

Using the DBMS_PDB Package on a Non-CDB

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

This section contains the following topics:

• About Using the DBMS_PDB Package on a Non-CDB

• Using the DBMS_PDB Package to Create an Unplugged PDB

About Using the DBMS_PDB Package on a Non-CDB

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

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

Description of "Figure 38-7 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.

See Also:

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

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.

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.

To unplug a PDB: 

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

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

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

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