First steps with the new technology: Pluggable Database theory


Today I want to discuss the feature “pluggable database” of the new 12c release. Before we start some theoretical: The Oracle CDB (Containter Database) technology, simple described, is an oracle instance (CDB) with some virtual databases (PDB):

cdb_overview

The CDB$ROOT is the root container which is the container database itself. The PDB$SEED is a template Database for creating new PDB’s. A CDB can manage up to 252 PDB’s.

1. Here some facts

  • Dictionary structure
    • The database dictionary exists in multiple levels. At CDB level which hold all data about all PDB’s and some metadata. At PDB level the dictionary acts like in before.
    • This split dictionary configuration enable a quick and smart migration (unplug/plug) of the database to other systems
    • Each PDB sees a read only copy of the oracle system
  • Instance structure
    • Each PDB can started and stopped separatly
    • Parameters can be adjusted at cdb and pdb level. All parameters of the pdb are saved in the transportset. So if you unplug and plug the PDB to another system, the parameters are not lost.
    • Instance processes are shared between cdb and all pdb’s
    • Each CDB has one shared SGA between the CDB and PDB
  • Database structure
    • There is only one active UNDO Tablespace per CDB instance.
    • Onlinelogs and controlfiles are shared between CDB and PDB’s
    • Permamenty and temporary tablespaces can be shared at CDB level or used local at PDB level
  • Security structure
    • The database users SYS and SYSTEM exists right from first plug.
    • Users can be created at CDB level as common user or local user
    • Users created at PDB level are local for the PDB created in
    • New DBA roles:
      • CDB_DBA for CDB administrators
      • PDB_DBA for PDB administrators
  • High availability structure
    • RMAN Backup should be done at CDB level. Point in time recovery per PDB is possible
    • Physical standby, logical standby and DataGuard operates at CDB level
  • Management of PDB’s
    • There is at least one unique service name for each PDB which can be used for user connections

2. Managing CDB and PDB’s
2.1 Connecting to CDB / PDB

CDB (old style): "sqlplus / as sysdba" 
PDB (new style) via listener: "sqlplus sys/@ as sysdba" 
PDB (new style) via alter system: "alter session set container = <PDBNAME>"

2.1.1 Checking available PDB’s

 sqlplus / as sysdba SQL> select pdb_id,pdb_name,dbid from cdb_pdbs
PDB_ID PDB_NAME DBID
 ---------- ---------- ----------
 2 PDB$SEED 4062846046
 3 PDB12C1 2256791230
 4 PDB12C2 1952495537

2.1.2 Connect to PDB via listener
First check listener and service state:

[bash ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 08-JUL-2013 23:07:48 Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
 STATUS of the LISTENER
 ------------------------
 Alias LISTENER
 Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
 Start Date 08-JUL-2013 21:02:10
 Uptime 0 days 2 hr. 5 min. 38 sec
 Trace Level off
 Security ON: Local OS Authentication
 SNMP OFF
 Listener Parameter File /opt/oracle/app/product/12.1.0/dbhome_1/network/admin/listener.ora
 Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 Services Summary...
 Service "CDB12C" has 1 instance(s).
 Instance "CDB12C", status READY, has 1 handler(s) for this service...
 Service "CDB12CXDB" has 1 instance(s).
 Instance "CDB12C", status READY, has 1 handler(s) for this service...
 Service "pdb12c1" has 1 instance(s).
 Instance "CDB12C", status READY, has 1 handler(s) for this service...
 Service "pdb12c2" has 1 instance(s).
 Instance "CDB12C", status READY, has 1 handler(s) for this service...
 The command completed successfully

Add tnsnames.ora entry:

PDB12C1 =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = PDB12C1)
 )
 )

PDB12C2 =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = PDB12C2)
 )
 )

Finally connect via listener

[bash~]$ sqlplus sys/welcome1@PDB12C1 as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 8 22:48:44 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 database_name from v$database;
DATABASE_NAME
 --------------------------------------------------------------------------------
 PDB12C1

2.1.3 Connect to PDB via “alter session”

Starting at CDB level:
 SQL> select database_name from v$database
 DATABASE_NAME
 ---------------
 CDB12C
SQL> alter session set container = PDB12C1;
 Session altered.
SQL> select database_name from v$database;
 DATABASE_NAME
 ---------------
 PDB12C1

2.1.4 Switching from PDB to CDB

SQL> alter session set container = CDB$ROOT; Session altered.
SQL> select database_name from v$database;
 DATABASE_NAME
 ---------------
 CDB12C

2.2 Starting and stopping
2.2.1 CDB start and stop
The start and stop of a CDB is like in pre 12c databases. You can user startup or shutdown command. You can also use “alter database” command to mount/open database.

2.2.2 PDB start and stop
Show status of all PDB’s:

SQL> select con_id,dbid,name,open_mode from v$pdbs; 
CON_ID DBID NAME OPEN_MODE 
---------- ---------- ---------- ---------- 
2 4062846046 PDB$SEED READ ONLY 
3 2256791230 PDB12C1 READ WRITE 
4 1952495537 PDB12C2 MOUNTED

Minimal state of a PDB is “mounted”.

2.2.2.1 Start PDB

SQL> alter pluggable database PDB12C2 open;

Pluggable database altered.

2.2.2.2 Stop PDB

SQL> alter pluggable database PDB12C2 close immediate;
Pluggable database altered.

2.2.3 Manage all PDB’s at once
2.2.3.1 Stop all PDB’s

SQL> alter pluggable database all close immediate; 

Pluggable database altered.
SQL> select con_id,dbid,name,open_mode from v$pdbs;
 CON_ID DBID NAME OPEN_MODE
 ---------- ---------- ---------- ----------
 2 4062846046 PDB$SEED READ ONLY
 3 2256791230 PDB12C1 MOUNTED
 4 1952495537 PDB12C2 MOUNTED

2.2.3.2 Start all PDB’s

SQL> alter pluggable database all open; Pluggable database altered.
SQL> select con_id,dbid,name,open_mode from v$pdbs;
 CON_ID DBID NAME OPEN_MODE
 ---------- ---------- ---------- ----------
 2 4062846046 PDB$SEED READ ONLY
 3 2256791230 PDB12C1 READ WRITE
 4 1952495537 PDB12C2 READ WRITE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s