Tuesday, October 11, 2016

OTN Appreciation Day: Oracle Multitenant

I've decided to dedicate my OTN Appreciation Day blog post to Oracle Multitenant.

Why Multitenant? 

Because Database Consolidation is a big challenge that many organizations are faced with.
Before Oracle 12c there were several approaches for implementing Database consolidation such as server consolidation - i.e. taking different databases from different servers and consolidating them into one single box (either virtual of physical). The problem with this approach is that it still leaves the DBAs with several different databases to manage (back up, upgrade, monitor, etc).
Another approach is to consolidate several databases into one single database where each database has its own schema or set of schemas - now, we need to manage only one Oracle Database.
The problem with this approach is that there are several challenges when doing database consolidation, let’s review some of them.

  • Name Collisions - Imagine that you have identical schema names or public synonyms across different databases. As you know public synonym and schema names must be unique in a Database.
  • Security - When consolidating different Database into one Database, a user with strong permissions ("SELECT ANY TABLE" system privilege, "DBA" role, etc.) can view data of all the schemas in the Database, and perhaps you don't want to DBA for one specific application will be able to access data of all the other applications.
  • Upgrades - You cannot perform schema-level upgrade. You can only upgrade the entire Database, but perhaps other applications (other schemas) are not ready for the upgrade 
  • Point In-Time Recovery - Imagine that a user truncated a table in a specific schema - in that case, we cannot use the FLASHBACK TABLE feature or FLASHBACK DROP so we may consider performing point in-time recovery, but the problem here is that we cannot use RMAN or user-managed backup to perform a point in-time recovery for a specific schema, we can only perform point in-time recovery for the entire Database.
In the past, DBAs could configure either a single instance or RAC, but in both cases there was only one database, so there was 1:1 relationship (one instance, one database) between database to instance in case of a single instance, but 1:N relationship (many instances, one database) between database to instance in case of RAC. In Oracle 12c with the multitenant option you can have many different pluggable databases in a single instance environment or a RAC environment.
In a multitenant architecture, these is one instance with many pluggable databases. Each Pluggable Database is a self-contained, independent Database with its own schemas, data, etc. Pluggable Database is a “regular” Database from the application standpoint. In addition, there is one root container which stores Oracle-metadata that is shared across all the PDBs like PL/SQL packages, for example, the DBMS_SYSTEM package resides only in the root container. All the PDBs can be plugged into the root container.

The advantage of Oracle Multitenant architecture is that from one hand, DBAs can manage all of the Pluggable Databases as one - Backup all PDBs at once, create a Data Guard for all the PDBs at once, etc. so it makes DBAs life much easier :) On the other hand, since PDBs are separate entities, this architecture offers database security and isolation. It also allow to perform granular operations such as point in-time recovery at PDB level and Flashback Database at PDB-Level (starting from version 12.2 which is currently available only as part of the Oracle Cloud service).


Oracle introduced a completely new paradigm with 12c Multitenant. This exciting feature solves many Database consolidation challenges that organizations (and DBAs) had to face in the past and it can definitely make the Oracle DBA's life much easier :)

1 comment:

  1. BlueHost is one of the best hosting provider with plans for all of your hosting requirements.