Create Pluggable Database Tips

Question:  What are the Oracle 12c Pluggable databases?  How does a pluggable database work?

Answer:  A pluggable database is a new construct whereby you can encapsulate a sub-set of Oracle data tables and indexes along with its associated metadata from the data dui dictionary.  You start by creating a "root" instance database, called a container database (CDB).  This container is used to hold many pluggable database "tenants" (PDB's), in a type of architectural separation.

The most important part of this separation is the "split" data dictionary, whereby everything needed for a PDB is self-contained in this sub-set of the metadata.

 Unlike a traditional database where only one database can exist per instance, the Oracle 12c pluggable databases allow multiple databases within an instance.  Oracle pluggable databases ease the movement into database consolidation because the data dictionary information (obj$, tab$ and source$) are independent of any container database.  All PDB's within the container share a common LGWR process.

This container can then be populated with sub-sets of a schema and then be can be un-plugged from one instance and re-added to another instance quickly.  Because the table definitions, index definitions, constraints and data all reside within the PDB, they can be easily copied and moved between instances and servers.   Oracle has termed "multi-tenancy" to describe the process of creating a CDB that contains many "tenant" PDB's.

The pluggable databases provide significant reduction in overhead on systems (like Exadata) where we'd like to host multiple databases, but can't consolidate them due to conflicts that prevent them running under the same instance (public synonyms / database links, schema name collision, etc). There are several benefits to pluggable databases:

1 - Easy fast cloning:  Simply copy the PDB very quickly

2 - Easy upgrades:  Just copy/move the PDB to a container running a higher release of Oracle

There is a new pluggable database administrator role in 12c called the CDB Administrator role.  The new CDB interface has the v$active_services view to tell you which container you are currently attached to:

SQL> show con_name

select
   name,
   con_id
from
   v$active_services
order by
   name;

select
   pdb
from
   dba_services;

select
   sys_context('userenv','con_name') "CONTAINER_NAME" FROM dual;

This is the syntax for creating a PDB:

create pluggable database
  cont01_plug01
admin user
   app_admin identified by mypass
file_name_convert = ('/pdbseed/', '/cont01plug01/');

alter pluggable database cont01_plug01 open;
-- alter pluggable database ALL open;

Oracle guru Mike Rajendran offers up these syntax examples of Oracle pluggable databases and notes the new data dictionary views v$pdbs

--*************************************
-- Check current environment of CDB and PDB
-- ************************************

set echo on
SET LINESIZE 200
SET PAGESIZE 10000
SET SERVEROUTPUT ON
COLUMN "DB DETAILS" FORMAT A100\
SELECT
 'DB_NAME: ' ||sys_context('userenv', 'db_name')||
 ' / CDB?: ' ||(select cdb from v$database)||
 ' / AUTH_ID: ' ||sys_context('userenv', 'authenticated_identity')||
 ' / USER: ' ||sys_context('userenv', 'current_user')||
 ' / CONTAINER: '||nvl(sys_Context('userenv', 'con_Name'), 'NON-CDB')
 "DB DETAILS"
 FROM DUAL
 /

DB DETAILS
---------------------------------------------------------------------------
DB_NAME: c02p / CDB?: YES / AUTH_ID: SYS / USER: SYS / CONTAINER: CDB$ROOT

-- ******************************************
REM Check how many PDBs are in the current container
-- ******************************************
SET SERVEROUTPUT ON
COLUMN "RESTRICTED" FORMAT A10
select
   v.name,
   v.open_mode,
   nvl(v.restricted, 'n/a') "RESTRICTED",
   d.status
from
   v$pdbs     v
inner join
   dba_pdbs d
using (GUID)
order by v.create_scn;

NAME                            OPEN_MODE RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED                        READ ONLY  NO         NORMAL
C0NT1PLUG1                      READ WRITE NO         NORMAL