UNDERSTANDING ORACLE MULTITENANT 12C DATABASE
Overview of Oracle Multitenant Databases
Database 12c Enterprise Edition introduces Multitenant, a new design that empowers clients to effortlessly merge numerous databases, without changing their applications. This new design gives the advantage of overseeing various databases as one, yet holds the confinement and asset prioritization of discrete databases. Multitenant empowers fast provisioning and works well with other functionalities like Oracle Real Application Clusters and Active Data Guard.
What is Multitenant?
The new design is termed as multitenant, as multiple tenants share the same resources on a mutual benefit. The same is applied to Oracle Database where the multitenant foundation called as Container Database (CDB), has various tenants known as Pluggable Database (PDB).
Advantages of Multitenant
In the earlier releases of databases, we were not able to utilize the full hardware or other resources on the server/virtual machine where the database was running. Oracle tried to overcome this and to make sure that the hardware was being used to its full capacity, they came up with Multitenancy. This shall also help in cost reduction. Multitenancy allows easier upgrades and patching activities, numerous databases clubbed as one which even give performance tuning benefits as we have single sga/memory structure to tune.
In Oracle multitenancy architecture, the container database is a single instance which comprises of background and memory structures same like non–container databases, but the memory would also be shared with all pluggable databases, which distinguishes the multitenant design with the earlier conventional databases. It is like earlier Oracle databases, as it contains the majority of the working parts like (controlfiles, datafiles, undo, tempfiles, archivefiles,redofiles etc.)
The architecture basically consists of the following containers:
• It is the core of the CDB, contains all Oracle supplied metadata like packages, core dictionary objects, and common users. It can be identified as CDB$ROOT. There is only one root container for a CDB.
• Control files, REDO Logs, and UNDO files are owned by ROOT Container as these Database files are Instance specific
• It is a template database which is used to create new PDBs, Oracle copies mandatory datafiles (common datafiles) for creation of PDB from this SEED Database.
• This is by default in READ-ONLY mode and cannot be altered or modified.
• This is identified as PDB$SEED.
User Defined PDB
• It is the custom PDBs which may be created for various applications, teams or functionalities.
• There can be zero or more PDB’s in a CDB.
• One PDB can be associated with only CDB at a time.
• A CDB can contain 253 PDBs including the SEED Container.
• Every PDB has its own set of SYSTEM/SYSAUX/TEMP tablespaces. If we don’t assign any TEMP table space to PDB it will use the TEMP of ROOT Container.
• New custom table spaces/data files can be created in PDB as per requirement.
• When a Container database instance is started all PDB’s comes automatically in mounted state. We will need to manually open the PDB’s into Read-Write mode.
For all above containers we have container ID’s defined.
In the earlier version of Databases, we have 3 level of dictionary view relationship (USER_,ALL_,DBA_), but one more layer of view has increased (CDB_) with the introduction of the CDB.
Let’s go through some Basic Commands.
Connect to CDB
sqlplus “/as sysdba”
SQL> sho con_name
— — — — — — — — — —
Connect to PDB Using Alter Session Command
Connect to CDB as described above then connect to PDB using following command.
alter session set container=pdb2;
Find Database is CDB or Non-CDB
SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;
NAME CDB CON_ID
— — — — — — — -
CDB1 YES 0
Checking the Status of PDBs
SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;
— — — — — — — — — — — — — -
PDB$SEED READ ONLY PDB READ WRITE
Pluggable database implies that database can be unplugged from one container and plugged into another. However, at one point of time, it can only belong to one single container. This has tremendous impact on how upgrades, patching and cloning are done in multi-tenancy databases.