LATEST TOPICS

Oracle Database 12c Multitenant: Create a Pluggable Database

Oracle Database 12c multitenant architecture allows multiple pluggable databases (PDB) to be hosted in a single container database (CDB) or root database.
In today’s article, I would be discussing the different methods that we can utilize to create a pluggable database.

I will be demonstrating the following three methods for creating a pluggable database. The demonstration assumes that we already have a container database (CDB) ready for hosting the pluggable database.


1. Create Pluggable database using DBCA
2. Create Pluggable database manually using SQL*Plus
3. Cloning a Pluggable database

Create Pluggable Database using DBCA:

I have a container database ‘prodcdb’ to host my pluggable databases. The CDB is already hosting a pluggable database ‘PRODPDB1’.

SQL> select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4103948816 PDB$SEED                       READ ONLY
         3 4276769587 PRODPDB1                       READ WRITE

I would be creating a new pluggable database ‘PRODPDB2’ using DBCA

Starting from Oracle Database 12c, DBCA contains a new option to manage pluggable databases. Select ‘Manage Pluggable Databases’ option from the database operation Menu and click Next.

 
pdb_dbca_01
 
Select ‘Create a Pluggable Database’ option on the Manage Pluggable Databases section and click Next.
 
pdb_dbca_02
 
Select the Container Database (CDB) in which the pluggable database to be hosted and click Next. In my case the CDB is PRODCBD.
 
pdb_dbca_03
 
Select ‘Create a new Pluggable Database’ option and click Next
 
pdb_dbca_04
 
Fill in the Pluggable Database details such as Pluggable Database Name, Data File Location, Admin User, etc. and click Next.
 
pdb_dbca_05
 
Review the Pluggable Database details and click on Finish.
 
pdb_dbca_06
 
Wait while the Pluggable Database is getting created.
 
pdb_dbca_07
 
Once, the Pluggable Database is created click on Ok button on the pop-up dialog box and click on Close button on the main screen to close DBCA.
 
pdb_dbca_08
 

We have created a new pluggable database PRODPDB2 using DBCA. Lets validate the PDB creation.

SQL> select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4103948816 PDB$SEED                       READ ONLY
         3 4276769587 PRODPDB1                       READ WRITE
----------
---------- PRODPDB2 is our new PDB created by DBCA ------------
----------
         4 4149756065 PRODPDB2                       READ WRITE

Create Pluggable Database manually using SQL*Plus:

We can also create s pluggable database manually from SQL command line using the CREATE PLUGGABLE DATABASE statement. In this method, the new pluggable database is created using the SEED pluggable database.

The SEED pluggable database is the one which contains the templates (files) for creating a pluggable database. More information about the SEED database can be found here.

Following is the complete syntax for creating a pluggable database from the SEED database.

CREATE PLUGGABLE DATABASE pdb_name
ADMIN USER admin_user_name IDENTIFIED BY password
  [ pdb_dba_roles ]
  [ default_tablespace ]
  [ file_name_convert ]
  [ pdb_storage_clause ]
  [ path_prefix_clause ]
  [ tempfile_reuse_clause ]
  [ user_tablespaces_clause ]
  [ standbys_clause ]
  [ logging_clause ]
  [ create_file_dest_clause ]

A detailed explanation of the syntax can be found here

In order to create a pluggable database from the SEED database, we need to know the datafile and tempfile location of the SEED database.

Let’s query the datafile and tempfile location of the SEED database.

SQL> select TABLESPACE_NAME, FILE_NAME
  2  from cdb_data_files
  3  where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' );

TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------------------------------------
SYSTEM                         +DATA/PRODCDB/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.280.861402961
SYSAUX                         +DATA/PRODCDB/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.279.861402961

SQL> select NAME
  2  from v$tempfile
  3  where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' )
  4  ;

NAME
--------------------------------------------------------------------------------
+DATA/PRODCDB/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/pdbseed_temp01.dbf

We will be using the FILE_NAME_CONVERT parameter of the CREATE PLUGGABLE DATABASE statement to remap the datafiles and create the pluggable database from SEED database.

Lets create a new pluggable database PRODPDB3 manually from the SEED database.

Connect to container database (CDB) as SYS user

SQL> show user
USER is "SYS"
SQL> show con_name

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

Create the new pluggable database with CREATE PLUGGABLE DATABASE SQL statement.

SQL> CREATE PLUGGABLE DATABASE PRODPDB3
  2  ADMIN USER PRODPDB_admin IDENTIFIED BY Oracle123#
  ---- Since both datafile and tempfile are located in same path, I am using 
  ---- only one replacement in FILE_NAME_CONVERT
  3  FILE_NAME_CONVERT=('+DATA/PRODCDB/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/','+DATA/')
  4  ;

Pluggable database created.

When, we create a pluggable database manually, it doesn’t open for READ-WRITE access by default. We need to open the database manually for READ-WRITE access.

SQL>  select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4103948816 PDB$SEED                       READ ONLY
         3 4276769587 PRODPDB1                       READ WRITE
         4 4149756065 PRODPDB2                       READ WRITE
--------
-------- PRODPDB3 is the new PDB being created manually
--------		 
         5 4199535790 PRODPDB3                       MOUNTED

SQL> alter pluggable database PRODPDB3 open;

Pluggable database altered.

SQL> select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4103948816 PDB$SEED                       READ ONLY
         3 4276769587 PRODPDB1                       READ WRITE
         4 4149756065 PRODPDB2                       READ WRITE
         5 4199535790 PRODPDB3                       READ WRITE

		 

Cloning a Pluggable Database:

We can also create a pluggable database by cloning an existing pluggable database. Creating a pluggable database using cloning is similar to creating a pluggable database from SEED except that in cloning we would be creating a pluggable database from a non-SEED database. In the cloning method, the data from the source database also gets copied to the new pluggable database (Starting from Oracle Database Release 12.1.2.0 we have the option to only clone the structure of a pluggable database with the NO DATA clause).

For cloning a pluggable database, the source pluggable database must be in READ ONLY mode.

Following is the complete syntax for cloning a pluggable database.

CREATE PLUGGABLE DATABASE pdb_name
FROM { src_pdb_name [ @ dblink ] } | { NON$CDB @ dblink }
  [ pdb_storage_clause ]
  [ file_name_convert ]
  [ path_prefix_clause ]
  [ tempfile_reuse_clause ]
  [ SNAPSHOT COPY ]
  [ user_tablespaces_clause ]
  [ standbys_clause ]
  [ logging_clause ]
  [ create_file_dest_clause ]
  [ NO DATA ]

A detailed explanation of the syntax can be fond here

Lets create a new pluggable database ‘PRODPDB4’ by cloning it from the pluggable database ‘PRODPDB3’

First, we need need to open the source pluggable database ‘PRODPDB3’ in READ ONLY mode.

Connect to container database (CDB) as SYS user

SQL> show user
USER is "SYS"
SQL> show con_name

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

Open the source pluggable database in READ ONLY mode

SQL> alter pluggable database PRODPDB3 close;

Pluggable database altered.

SQL> alter pluggable database PRODPDB3 open read only;

Pluggable database altered.

SQL>  select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs where NAME='PRODPDB3';

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         5 4199535790 PRODPDB3                       READ ONLY

Now, we will be creating the new pluggable database PRODPDB4 from PRODPDB3. As in case of a SEED database, we also need to know the datafile and tempfile location of the source pluggable database ‘PRODPDB3’.

SQL> select TABLESPACE_NAME, FILE_NAME
  2  from cdb_data_files
  3  where CON_ID=( select CON_ID from v$pdbs where NAME='PRODPDB3')
  4  ;

TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------------------------------------
SYSTEM                         +DATA/PRODCDB/05E6D1ADF12F1A67E05305E6A8C088D7/DATAFILE/system.296.861524591
SYSAUX                         +DATA/PRODCDB/05E6D1ADF12F1A67E05305E6A8C088D7/DATAFILE/sysaux.297.861524591

SQL> select NAME
  2  from v$tempfile
  3  where CON_ID=( select CON_ID from v$pdbs where NAME='PRODPDB3');

NAME
------------------------------
+DATA/pdbseed_temp01.dbf

SQL>

We will be now using CREATE PLUGGABLE DATABASE with FROM clause and FILE_NAME_CONVERT clause to clone the exiting pluggable database PRODPDB3

SQL> CREATE PLUGGABLE DATABASE PRODPDB4
  2  FROM PRODPDB3
  ---- Since datafile and tempfile are located in two different path, I am using
  ---- two replacements in FILE_NAME_CONVERT
  3  FILE_NAME_CONVERT=('+DATA/PRODCDB/05E6D1ADF12F1A67E05305E6A8C088D7/DATAFILE/','+DATA/','+DATA/pdbseed_','+DATA/')
  4  ;

Pluggable database created.

SQL>

Once, the pluggable database is created, we can open both the source and the new PDB for READ-WRITE access.

SQL> select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4103948816 PDB$SEED                       READ ONLY
         3 4276769587 PRODPDB1                       READ WRITE
         4 4149756065 PRODPDB2                       READ WRITE
--------
-------- PRODPDB3 was the source for cloning
--------		 
         5 4199535790 PRODPDB3                       READ ONLY
--------
-------- PRODPDB4 is the new PDB created by cloning it from PRODPDB3
--------		 
         6 4072086604 PRODPDB4                       MOUNTED

SQL> alter pluggable database PRODPDB3 close;

Pluggable database altered.

SQL> alter pluggable database PRODPDB3 open;

Pluggable database altered.

SQL> alter pluggable database PRODPDB4 open;

Pluggable database altered.

SQL> select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4103948816 PDB$SEED                       READ ONLY
         3 4276769587 PRODPDB1                       READ WRITE
         4 4149756065 PRODPDB2                       READ WRITE
         5 4199535790 PRODPDB3                       READ WRITE
         6 4072086604 PRODPDB4                       READ WRITE

Reference:

Introduction to the Multitenant Architecture
CREATE PLUGGABLE DATABASE

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed