How to Create New Pluggable Databases in Oracle 12c

You can create new Pluggable Databases (PDBs) in Oracle 12c with traditional Structured Query Language (SQL) or with the Database Configuration Assistant (DBCA). You may note that when launching the DBCA now, a new option appears on the main screen: Manage Pluggable Databases. When you select this option, the DBCA walks you through many different activities that you can exercise against a PDB, such as

  • Create

  • Unplug

  • Delete

  • Configure

The next activities walk you through creating a new PDB by using SQL from the SQL*Plus. However, using SQL offers one important benefit. To use the DBCA, you must be on the server itself to launch the tool from OS that houses the CDB.

How to create a new PDB by using the seed on Linux in Oracle 12c

This method copies the files for the seed to a new location and associates the copied files with the new PDB, which will be called DEVPDB2. Although you have many options for creating PDBs, this example is one of the simplest ways to get up and running. Using this method leaves you with a PDB with no customizations.

  1. Log in to your CDB using SQL*Plus as SYSDBA. To make sure you’re in the correct location, type

    <show con_name>

    You should see something like this:

    CON_NAME
    ------------------------------
    CDB$ROOT

    The out-of-the box file location for PDBs is in a subdirectory under the oradata directory for the CDB.

  2. Create a subdirectory for the new PDB under the CDB file location from the OS oracle software owner by typing

    <mkdir /u01/app/oracle/oradata/devcdb/devpdb2>

    If this command succeeds, you get no output. You can list the new directory by typing

    <ls –l /u01/app/oracle/oradata/devcdb |grep devpdb2>

    You should see something like this:

    drwxr-xr-x. 2 oracle oinstall   4096 Aug 17 01:56 devpdb2
  3. Back in SQL*Plus as SYSDBA, create pluggable database command by typing

    <CREATE PLUGGABLE DATABASE devpdb2 ADMIN USER pdb2dba identified by "oracle"
    DEFAULT TABLESPACE USERS
    DATAFILE '/u01/app/oracle/oradata/devcdb/devpdb2/users01.dbf'
    SIZE 250M AUTOEXTEND ON
    FILE_NAME_CONVERT=(
    '/u01/app/oracle/oradata/devcdb/pdbseed/',
    '/u01/app/oracle/oradata/devcdb/devpdb2/');>

    You should see this:

    Pluggable database created.

    The new PDB is left in a mount state.

  4. Show the new PDB and open it by typing

    <show pdbs>
    <alter pluggable database devpdb2 open;>

    You should see this:

    CON_ID CON_NAME            OPEN MODE RESTRICTED
    ------- ------------------------------ ---------- ----------
       2 PDB$SEED            READ ONLY NO
       3 DEVPDB1            READ WRITE NO
       4 DEVPDB2            MOUNTED
    Pluggable database altered.
  5. Verify the status by typing

    <show pdbs>

    You should see this:

CON_ID CON_NAME            OPEN MODE RESTRICTED
------ ------------------------------ ---------- ----------
   2 PDB$SEED            READ ONLY NO
   3 DEVPDB1            READ WRITE NO
   4 DEVPDB2            READ WRITE NO

How to create a new PDB by cloning an existing PDB on Linux in Oracle 12c

This method copies the files for the new PDB from the existing DEVPDB1 to a new location. In this example it is called PDB DEVPDB3. Again, you have many options for creating PDBs, but this example is one of the simplest ways to get up and running. Consult Oracle documentation for different options. Using this method leaves you with a PDB with all customizations of the source PDB.

DEVPDB1 has been customized with a unique tablespace MY_DATA, within which there is a table HR.EMP. These customizations carry over to the new DEVPDB3.

  1. Log in to your CDB using SQL*Plus as SYSDBA. To make sure you’re in the correct location, type

    <show con_name>

    You should see something like this:

    CON_NAME
    ------------------------------
    CDB$ROOT

    The out-of-the box file location for PDBs is in a subdirectory under the oradata directory for the CDB.

  2. Create a subdirectory for the new PDB under the CDB file location from the OS oracle software owner by typing

    <mkdir /u01/app/oracle/oradata/devcdb/devpdb3>

    If this command succeeds, you get no output. You can list the new directory by typing

    <ls –l /u01/app/oracle/oradata/devcdb |grep devpdb3>

    You should see something like this:

    drwxr-xr-x. 2 oracle oinstall   4096 Aug 17 02:18 devpdb3
  3. The source PDB needs to be put into read only mode. Do this by typing

    <alter pluggable database devpdb1 close immediate;>

    You should see this:

    Pluggable database altered.

    Then type

    < alter pluggable database devpdb1 open read only;>

    You should see this:

    Pluggable database altered.
  4. Run the clone command by typing

    < CREATE PLUGGABLE DATABASE devpdb3 FROM devpdb1
    FILE_NAME_CONVERT=(
    '/u01/app/oracle/oradata/devcdb/devpdb1/',
    '/u01/app/oracle/oradata/devcdb/devpdb3/');>

    You should see this:

    Pluggable database created.

    The new PDB is left in a mount state.

  5. Show all the PDBs and their status by typing

    <show pdbs>

    You should see this:

    CON_ID CON_NAME            OPEN MODE RESTRICTED
    ------ ------------------------------ ---------- ----------
       2 PDB$SEED            READ ONLY NO
       3 DEVPDB1            READ ONLY NO
       4 DEVPDB2            READ WRITE NO
       5 DEVPDB3            MOUNTED
  6. Open the source DEVPDB1 read write and open the new DEVPDB3 by typing

    <alter pluggable database devpdb1 close immediate;>
    <alter pluggable database devpdb1 open;>
    <alter pluggable database devpdb3 open;>

    You see something like this for each command:

    Pluggable database altered.
  7. Show the new status of the PDBs by typing

    <show pdbs>

    You should see this:

    CON_ID CON_NAME            OPEN MODE RESTRICTED
    ------ ------------------------------ ---------- ----------
       2 PDB$SEED            READ ONLY NO
       3 DEVPDB1            READ WRITE NO
       4 DEVPDB2            READ WRITE NO
       5 DEVPDB3            READ WRITE NO

    The final check is to see that the custom tablespace and table are in the new PDB.

  8. Connect to the container database. One way to do this is through the root CDB by typing

    < alter session set container = devpdb3;>

    You see this:

    Session altered.

    You can double-check your container by typing

    <show con_name>

    You see this:

    CON_NAME
    --------------
    DEVPDB3
  9. Check your tablespaces by typing

    <select tablespace_name, file_name
    from dba_data_files;>

    You should see something like this:

    TABLESPACE_NAME FILE_NAME
    --------------- ----------------------------------------------------------------
    SYSTEM     /u01/app/oracle/oradata/devcdb/devpdb3/system01.dbf
    SYSAUX     /u01/app/oracle/oradata/devcdb/devpdb3/sysaux01.dbf
    USERS      /u01/app/oracle/oradata/devcdb/devpdb3/SAMPLE_SCHEMA_users01.dbf
    EXAMPLE     /u01/app/oracle/oradata/devcdb/devpdb3/example01.dbf
    MY_DATA     /u01/app/oracle/oradata/devcdb/devpdb3/my_data01.dbf
  10. Check the HR.EMP table by typing

    < select owner, table_name, tablespace_name
    from dba_tables
    where owner = 'HR'
    and table_name = 'EMP';>

    You see something like this:

OWNER      TABLE_NAME           TABLESPACE_NAME
--------------- ------------------------------ ---------------
HR       EMP              MY_DATA
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.