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 this feature. It will also help in cost reduction. Multitenancy helps in 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 defined.
In the earlier version of Databases we have 3 level of dictionary view relationship (USER_,ALL_,DBA_) but with the introduction of the CDB one more layer of view has increased (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 a container database and can be plugged in another container database. However, at a particular time, it can belong to one and only one container. This has tremendous impact on how upgrades, patching and cloning are done in multi-tenancy databases.
- Oracle Goldengate
Oracle GoldenGate is an Oracle proprietary software for real-time data integration and replication that supports different databases- Oracle, DB2, SQL Server, Ingres, MySQL etc. Even the source and target database…
- MongoDB with C#.Net
Being a C# developer we closely work with relational databases like MS SQL and Oracle. It thus becomes very exciting to explore the world of a prominent NoSQL database like…
- Working with SQL Server Database on Microsoft Azure (Part 3)
This is the third and the final part of the series. We started with learning to register MS Azure and create SQL database & objects. Progressed to migrating the on-premise…
- Dynamic Pie Charts in Tableau
Tableau is arguably one of the most preferred data visualization tools in the Business Intelligence Industry. It converts raw data into easily understandable formats, enabling marketers, researchers, business owners, and…
- HDFS on Mesos Installation
HDFS on Mesos Installation Mesos cluster optimize the resources and bring the whole data-center at one platform where all the resources can be managed efficiently. Setting up mesos cluster with…
- KAFKA-Druid Integration with Ingestion DIP Real Time Data
The following blog explains how we can leverage the power of Druid to ingest the DIP data into Druid (a high performance, column oriented, distributed data store), via Kafka Tranquility…