Oracle Database 12c Tutorial: How to create a pluggable database?

 

Oracle Database 12c Tutorial: How to create a pluggable database? 

In this tutorial, we can start working on using the multi-tenant database feature in Oracle Database 12c by creating pluggable databases (PDB).  How many pluggable database can I create in a Oracle Database 12c instance? For Enterprise edition, the limit is 252 pluggable databases. For Standard edition, you can create only one pluggable database. Step 1: Checking the default settings  First, after you have a Oracle Database 12c instance installed, you can log into the 12c database as usual and check if the database is multitenant enabled: C:\>sqlplus /nolog SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 21 20:50:24 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> connect / as sysdba Connected. SQL> select name, cdb, open_mode, con_id from v$database; NAME CDB OPEN_MODE CON_ID --------- --- -------------------- ---------- ORCL12C YES READ WRITE 0 From the result with CDB=YES, you can see that you are logged into a multitenant database instance.  All object/files with CON_ID=0 are shared across the entire database (all PDBs).  The default container database is CDB$ROOT.  You can run the "show con_name" and "show con_id".  SQL> show con_name con_id CON_NAME CON_ID ------------------------------ CDB$ROOT 1 1

References What is the Net service with pluggable database? How to create users in multitenant database? Recommendations Oracle Multitenant (OTN Home)  Note:1519699.1: Master Note for the Oracle Multitenant Option Note 1511619.1: Oracle Multitenant Option - 12c : Frequently Asked Questions Videos Multitenant Architecture Overview Demo

You can also query using sys_context() function to find the container database's name and id. 

SQL> show con_name con_id
CON_NAME             CON_ID
------------------------------
CDB$ROOT                  1

SQL> select sys_Context('Userenv', 'Con_Name') "current container", 
            sys_Context('Userenv', 'Con_Id') "current container id" from Dual;
current container                       current container id
------------------------------------------------------------
CDB$ROOT                                                   1

How to start the PDBs?
You have to make sure the CDB is started before start any PDB.

By default, there are two PDBs created. One is the default database created during installation (PDBORCL12C) and the other one is a seed database which you can use to create new PDBs. To find out the list of PDBs, you can query the v$pdb or use the show pdbs command: 

SQL> show pdbs
   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
--------- ------------------------------ ---------- ----------
        2 PDB$SEED                       READ ONLY  NO
        3 PDBORCL12C                     MOUNTED
 
SQL> select name, open_mode,con_id from v$pdbs;

NAME         OPEN_MODE      CON_ID
------------ ---------- ----------
PDB$SEED     READ ONLY           2
PDBORCL12C   MOUNTED             3

SQL> col NAME format A12
SQL> select FILE#, ts.name, ts.ts#, ts.con_id
  2  from v$datafile d, v$tablespace ts
  3  where d.ts#=ts.ts#
  4  and d.con_id=ts.con_id
  5  order by 4,3;
     FILE# NAME                TS#     CON_ID
---------- ------------ ---------- ----------
         1 SYSTEM                0          1
         3 SYSAUX                1          1
         5 UNDOTBS1              2          1
         6 USERS                 4          1
         2 SYSTEM                0          2
         4 SYSAUX                1          2
         7 SYSTEM                0          3
         8 SYSAUX                1          3
         9 USERS                 3          3
        10 EXAMPLE               4          3
10 rows selected.

The second SQL query shows the database files associated with each database. You can see that the CDB (CON_ID=1) has SYSTEM, SYSAUX, UNDOTBS1 and USERS files. The seed PDB, pdb$seed(CON_ID=2),  has two SYSETM and SYSAUX files. The pdborcl12c database created by default (CON_ID=3) has SYSTEM, SYSAUX, USERS and EXAMPLE data files.  


If we query the v$datafile view, we can verify the details for each PDBs. The following example list the database files for PDB 

SQL> select name from v$datafile where con_id = 3;
NAME
----------------------------------------------------------------------------
C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBORCL12C\SYSTEM01.DBF
C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBORCL12C\SYSAUX01.DBF
C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBORCL12C\SAMPLE_SCHEMA_USERS01.DBF
C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBORCL12C\EXAMPLE01.DBF

Step 2: Create a new pluggable database 

Now, we can create a pluggable database. If you are not sure that you are in the container database, you can use the following command:

SQL> alter session set container=cdb$root;
Session altered.

The we can create a new pluggable database from the seed database. You can also copy from an existing PDB. 

Why do I get the "ORA-65081: database or pluggable database is not open in read only mode." error?
You need to make sure the exisiting database is in open and read only mode before creating PDB from it. The following command will be helpful.
SQL>alter pluggable database pdb1 close;
Pluggable database altered.
SQL>alter pluggable database pdb2 open read only;
Pluggable database altered.

SQL> create pluggable database pdbdemo admin user admin identified by admin
file_name_convert = ('pdbseed', 'pdbdemo');
Pluggable database created.

SQL> select pdb_name from cdb_pdbs;
PDB_NAME
--------------------------------------------------------------------------------
PDBORCL12C
PDB$SEED
PDBDEMO

SQL> col name format a30
SQL> select Name, Open_Mode, Inst_ID from gv$PDBs;

NAME                           OPEN_MODE     INST_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           1
PDBORCL12C                     MOUNTED             1
PDBDEMO                        MOUNTED             1

SQL> alter pluggable database pdbdemo open;
Pluggable database altered.

SQL> select Name, Open_Mode, Inst_ID from gv$PDBs;

NAME                           OPEN_MODE     INST_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           1
PDBORCL12C                     MOUNTED             1
PDBDEMO                        READ WRITE          1

SQL> select name, con_id from v$active_services order by 1;
NAME                               CON_ID
------------------------------ ----------
SYS$BACKGROUND                          1
SYS$USERS                               1
orcl12c.us.oracle.com                   1
orcl12cXDB                              1
pdbdemo.us.oracle.com                   4
pdborcl12c.us.oracle.com                3
6 rows selected.

If logging into a PDB, the operations are pretty much the same as a database in Oracle Database 11g.  The default tablespace for the newly created PDB from the seed  is SYSTEM. 

SQL> alter session set container=pdbdemo;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDBDEMO

SQL> show user
USER is "SYS"

SQL> select property_value from database_properties
     where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_VALUE
-------------------------------------------------------------------------
SYSTEM

Why do I get the "ORA-65016: FILE_NAME_CONVERT must be specified" error when creating pluggable database?
The file_name_convert specifies how new data filenames are derived from those of the seed or from an exsiting PDB. During the PDB creation Oracle Database 13c actually only copies 2 database files, for the System and the Sysaux tablespaces, to the location of new PDB. The other database files for undo, redo, and so on are global for the whole CDB and belongs to the container called CDB$Root.

Step 3: Dropping PDBs 

If you want to remove a PDB created, you can run the following command in the CDB. The PDB to be deleted have to be closed before deletion: 

SQL> alter session set container=cdb$root;
Session altered.

SQL> drop pluggable database pdbdemo including datafiles;
drop pluggable database seed_copy including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDBDEMO is not closed on all instances.

SQL> alter pluggable database pdbdemo close;
Pluggable database altered.

SQL> drop pluggable database pdbdemo including datafiles;
Pluggable database dropped.