One of the important new features in Oracle12c RDBMS is the concept of container databases (CDB) containing several pluggable databases (PDB).
This enables customers to easily consolidate databases and will be an important feature for a Cloud database (12c, c = Cloud). The overhead in system resource usage will be reduced as the PDBs will share background processes and memory, e.g. SGA. Up to 253 PDBs can be created within one CDB (creation of up to 252 PDBs require licensing the Multitenant EE option).
Let’s try to create a new PDB called LBV01 within a previously created CDB called “CDB121A” (was created using DBCA).
First we connect to the CDB database “CDB121A”:
[oracle@linux-4]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 19:33:48 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select * from dba_pdbs;
PDB_ID PDB_NAME DBID CON_UID GUID STATUS CREATION_SCN
---------- ------------ ---------- ---------- -------------------------------- ------------- ------------
3 PDB 3242270891 3242270891 E03A36488ADB472FE045000000000001 NORMAL 1913013
2 PDB$SEED 4061891658 4061891658 E03A02BF074241D5E045000000000001 NORMAL 1720753
One PDB (named PDB) was created during the creation of the CDB using DBCA. The PDB$SEED databaase is used as template for creating new PDSs.
With one command we now can create the LBV01 PDB:
SQL> CREATE PLUGGABLE DATABASE lbv01 ADMIN USER lbv01dba IDENTIFIED BY oracle;
Pluggable database created.
SQL> select * from dba_pdbs;
PDB_ID PDB_NAME DBID CON_UID GUID STATUS CREATION_SCN
---------- ------------ ---------- ---------- -------------------------------- ------------- ------------
3 PDB 3242270891 3242270891 E03A36488ADB472FE045000000000001 NORMAL 1913013
2 PDB$SEED 4061891658 4061891658 E03A02BF074241D5E045000000000001 NORMAL 1720753
4 LBV01 828603468 828603468 E03B4EBDC3C55889E045000000000001 NEW 1918740
This operation took less than 30 seconds on my VBOX image.
The freshly created PDB is not OPEN, as we see from the STATUS above (STATUS = NEW), now let’s open it:
SQL> alter pluggable database lbv01 open;
Pluggable database altered.
SQL> select * from dba_pdbs;
PDB_ID PDB_NAME DBID CON_UID GUID STATUS CREATION_SCN
---------- ------------ ---------- ---------- -------------------------------- ------------- ------------
3 PDB 3242270891 3242270891 E03A36488ADB472FE045000000000001 NORMAL 1913013
2 PDB$SEED 4061891658 4061891658 E03A02BF074241D5E045000000000001 NORMAL 1720753
4 LBV01 828603468 828603468 E03B4EBDC3C55889E045000000000001 NORMAL 1918740
Now the PDB named LBV01 is OPEN (STATUS = NORMAL in above listing).
NOTICE: The wellknown SHUTDOWN / STARTUP commands will operate on the CDB level (and affect all existing PDBs in the CDB).
Remember to operate on single PDBs using the “ALTER PLUGGABLE DATABASE … OPEN” and “ALTER PLUGGABLE DATABASE … CLOSE” statements.
Let’s try to connect LBV01 (I created a TNS alias in my local tnsnames.ora with the SERVICE_NAME = LBV01 first):
SQL> conn lbv01dba/oracle@lbv01
Connected.
SQL>
We’ve demonstrated that it’s very easy to create a new PDB within an existing CDB, based on the default PDB$SEED database.
In future blog posts we’ll look further into this and other new features of 12c R1 RDBMS.