Oracle Database 12c - All about Multitenant Architecture

Oracle Database 12c - All about Multitenant Architecture

For the past several years, we have witnessed significant contributions to Oracle Database. Every version has brought us some ground breaking technology and a leap forward innovation, that is a lot impressive and inspirational. As we already knew, Oracle has announced next major release candidate of Database, 12c at Oracle open world'2012, which is now available for download with all necessary product documentation. It has been completely redesigned from the ground and introduced fundamentally a major architectural change to Oracle Database which is called Multitenant Architecture. Throughout this post, I broadly elaborate it, and discuss its "what, why, and how", also includes its basic command line instructions.

What is Multitenant Architecture?

It is quite simple, Multiple tenants share same resources on a mutual benefit for different purposes at a very broad level. The same applies to Oracle Database where Multiple Databases share a single instance of resources aiming for different purposes on the same Server. This Oracle Database which is built on Multitenant foundation is called Container Database(CDB), and each container(tenant) residing inside is called Pluggable Database(PDB, Container). 

Why is Multitenant Architecture?

Technology has vastly improved as customers can afford servers with hundreds of CPUs and huge chunk of Physical Memory where the Hardware resources can be distributed and managed dynamically. If applications get hold of this, that would be a huge benefit for Enterprises. That is what exactly Oracle has brought to the table, and this is only a beginning. 

Benefits

One of the main reasons for Multitenant Architecture to be in place is "Database Consolidation". Until so far, we have databases deployed across multiple Small Physical machines on various platforms. It could be waste of resources having a database on each machine dedicatedly rather keeping them all on a single powerful machine. By consolidating all Databases onto a Single powerful chip and a Single Oracle Instance, we achieve following benefits

• Cost reduction in terms of Product licensing and Hardware procurement as we require less number of processors and less memory compare to a Non-CDB environment
• Easier Database maintenance, Pluggable Databases can easily be moved or copied locally or remotely across Physical machines in the network, Upgrading Operating System, Upgrading or Patching Database itself is a lot easier and "often" a one shot process, that greatly improves the availability 
• Performance tuning becomes a lot easier as all PDBs in a CDB environment share same Hardware/Platform/Network resources like CPUs/Memory/OS/Bandwidth etc.. For example, It is quite easy to tune one single piece of SGA/PGA on one single instance rather tuning each SGA on each physical machine

What more?

• It is completely transparent to Application, there is no specific configuration required to connect to a Database in Multitenant Architecture over a network. It works the same way as it works with Non CDBs. Every Container or PDB has its own Service name to allow connections from Clients like JDBC/ODBC/XA/SQPLUS etc...
• A Multitenant Container Database is created using either DBCA or "CREATE DATABASE" Command
• A Multitenant Container Database or shortly a CDB, can be created with an empty container, no Pluggable Databases or shortly PDB(s) associated with it, [or] with one or more containers(More than one PDB)
• Memory configurable is sum of memory required for all PDBs, is distributed on demand across all PDBs
• A CDB can contain 253 PDBs including the SEED Container
• Starting up and Shutting down a CDB is no different than a Non-CDB except we need to manually mount and dismount associated PDBs
• The Architecture basically consists following containers
• Root container - It is the core of the CDB, contains all Oracle supplied metadata like packages, core dictionary objects, and common users. It is denoted as CDB$ROOT
• SEED Container (SEED PDB) - This is a template database distributed for new PDBs, whenever we create a new PDB, Oracle copies mandatory datafiles (common datafiles) for creation of PDB from this SEED Database. This is by default in READ-ONLY mode, can't be altered or modified. This is denoted as PDB$SEED
• User defined PDB Container (PDB) - This is the Pluggable Database created for Application purpose, contains only Consumer Data or USER Data
• CDB has common user environment. A User exists in the ROOT Container can login to any PDB with the privileges given. A CDB also has local users exclusive to PDBs. A Common user and role name must start with C## or c## and consists only of ASCII characters.
• If a common user is required to login to a container, "c##" must be supplied as suffix. For example, If a common user "PDBADMIN" has privilege to acces a container PDB, We need to use it as "C##PDBADMIN" in order to login successfully to container PDB
• "CONTAINER=ALL" clause can be used on root container while performing different tasks such as "ALTER SYSTEM", "ALTER DATABASE", "ALTER USER" etc.. This clause sets the specific action or configuration to all available PDBs, as well as future PDBs
• Control files, REDO Logs, and UNDO files are owned by ROOT Container as these Database files are Instance specific. Every PDB has its own set of SYSTEM/SYSAUX/TEMP tablespaces, and also includes the Sample User data such as SCOTT, HR etc..
• ENABLE_PLUGGABE_DATABASE initialization parameter specifies If a particular Database is CDB or Non-CDB
• By default Oracle instance mounts the Pluggable Database, so it has to be manually opened into READ/WRITE Mode. In case of Cluster, it has to be opened in each instance

Basic Monitoring Command Line Instructions

To find If a Database is CDB or Non-CDB

SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;

NAME  CDB  CON_ID

--------- --- ----------

CONDB  YES       0

[OR]

SQL> SHOW PARAMETER enable_pluggable_database

NAME             TYPE           VALUE

------------------------------------       -----------        -------------

enable_pluggable_database     boolean TRUE

To find the Current Database that we are logged in,

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

--------------------------------------------------------------------------------

CDB$ROOT

To find the information about Pluggable Databases,

COL PDB_NAME FOR A30

SELECT PDB_ID,PDB_NAME,STATUS,CON_ID FROM CDB_PDBS;

  PDB_ID PDB_NAME  STATUS    CON_ID

---------- ------------------------------       ------------           ----------

3 PDB           NORMAL 1

2 PDB$SEED  NORMAL 1

4 SALESPDB   NORMAL 1

To login to a Container or a specific PDB

There are two ways we can login into a Container or PDB

1. SQLPLUS/CONNECT - This requires a TNS entry to login to a specific PDB locally or remotely

oracle@ora12c ~]$ sqlplus sys/123@PDB as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 29 12:35:02 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 SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

--------------------------------------------------------------------------------

PDB

2. ALTER SESSION

SQL> ALTER SESSION SET CONTAINER=SALESPDB;

Session altered.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

--------------------------------------------------------------------------------

SALESPDB

To switch between Containers using ALTER SESSION,

1. To switch to SEED Container,

SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

Session altered.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

--------------------------------------------------------------------------------

PDB$SEED

2. To switch to ROOT Container,

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

--------------------------------------------------------------------------------

CDB$ROOT

To mount or dismount and find the status of a PDB,

SQL> ALTER PLUGGABLE DATABASE PDB OPEN;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE PDB CLOSE IMMEDIATE;

Pluggable database altered.

[OR]

SQL> SHUT IMMEDIATE

Pluggable Database closed.

For multiple PDBs,
SQL> ALTER PLUGGABLE DATABASE PDB,PDB1,SALESPDB OPEN;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE PDB,PDB1,SALESPDB CLOSE;
Pluggable database altered.

SQL> SELECT NAME,OPEN_MODE,TOTAL_SIZE/1024/1024/1024 FROM V$PDBS;

NAME       OPEN_MODE  TOTAL_SIZE/1024/1024/1024
------------------------------ ---------- -------------------------
PDB$SEED       READ ONLY .263671875
PDB       READ WRITE .424804688
SALESPDB       MOUNTED  0

To modify any PDB Characteristics,

"ALTER PLUGGABLE DATABASE" is the command replaces "ALTE DATABASE" command in a CDB Environment. For example If the goal is to change the default tablespace of a PDB,

1. Tablespace must be created within the PDB

2. Use the below command to change it,

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

--------------------------------------------------------------------------------

PDB

SQL> CREATE TABLESPACE TS_PDB DATAFILE '/dbs/CONDB/PDB/ts_pdb_01.dbf' SIZE 10M;

Tablespace created.

SQL> ALTER PLUGGABLE DATABASE PDB DEFAULT TABLESPACE TS_PDB;

Pluggable database altered.

To set an initialization Parameter,
A Parameter can be set exclusive to a PDB, or for all current and future PDBs using "ALTER SYSTEM" command.
On the root container:
SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE CONTAINER=ALL;
System altered.

On a specific container or PDB:
SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
System altered.

To create a user in the ROOT Container and accessing other PDBs,

A CDB has common user environment. A User exists in the ROOT Container can login to any PDB with the privileges given and by default it is mapped to all available PDBs and future PDBs. A CDB also has local users exclusive to PDBs.

• A Common user and role name must start with C## or c## and consists only of ASCII characters.
• To login to a specific PDB, A Common user must have CREATE SESSION Privilege on the PDB.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

--------------------------------------------------------------------------------

CDB$ROOT

SQL> CREATE USER C##PDB IDENTIFIED BY PDB;

User created.

SQL> GRANT CREATE SESSION TO C##PDB;

Grant succeeded.

SQL> GRANT CREATE SESSION TO C##PDB CONTAINER=ALL; (ALL Specifies all Containers or PDBs)

Grant succeeded.

SQL> CONNECT C##PDB@PDB

Enter password: 

Connected.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

--------------------------------------------------------------------------------

PDB

SQL> SHOW USER

USER is "C##PDB"

Hope it helps.