Oracle Multitenant Architecture

Introduction:

From Oracle Database 12c release, Oracle introduced multitenant architecture. With this architecture, Oracle Database can have one single container database (CDB) which consists multiple separate pluggable databases (PDB). Before 12c version, all the oracle databases were non-CDBs. This multitenant architecture solves many problems posed by the traditional non-CDB architecture. This document provides overview about this multitenant architecture.

Components of Multitenant Architecture:

This architecture has two components called Multitenant Container Database (CDB) and Pluggable Database (PDB). Components in this architecture are also known as Containers. A container is either a PDB or the root.

CDB very similar to conventional Oracle database as it contains most of the working parts you are already familiar with (controlfiles, datafiles, undo, tempfiles, redo logs etc.). It also contains the data dictionary for the objects that are owned by the root container and those that are visible to all PDBs. Every CDB has the following containers:

• • Root Container:

    The root container stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container. The root container is named CDB$ROOT.

  • Seed PDB:

    The seed PDB is a system-supplied template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED. You cannot add or modify objects in PDB$SEED.

  • Zero or more user-created PDBs:

    A PDB is a user-created entity that contains the data and code required for a specific set of features. PDB only contains information specific to itself. No need to worry about controlfiles, redo logs and undo etc. Instead it is just made up of datafiles and tempfiles to handle its own objects. This includes its own data dictionary, containing information about only those objects that are specific to the PDB. Data dictionary has been separated into root container (for common objects) and PDB (PDB’s own objects). This separation gives the multitenant flexibility. No PDBs exist at creation of the CDB. You can add PDBs based on your business requirements.

Challenges of conventional Non-CDB Architecture:

• • Big companies may have hundreds or thousands of databases and mostly these databases run on different platforms on multiple physical servers. But database may use only a portion of the server hardware capacity which leads to wasting both hardware and human resources. For example, 100 servers may have one database each, with each database using 10% of hardware resources and 10% of an administrator’s time. A team of DBAs must manage the SGA, database files, accounts, security, and so on of each database separately, while system administrators must maintain 100 different computers.
  • This challenge can be addressed by using virtual machines or placing multiple databases on each server or separating the data logically into schemas. But these approaches still require individual management and application changes. Here Oracle’s multitenant architecture comes into picture for help. Database consolidation is the process of consolidating data from multiple databases into one database on one computer. The Oracle Multitenant option enables you to consolidate data and code without altering existing schemas or applications.
  • The PDB/non-CDB compatibility guarantee means that a PDB behaves the same as a non-CDB as seen from a client connecting with Oracle Net. In application point of view, both non-CDB and PDB behaves in the same manner.

Benefits of the Multitenant Architecture:

The multitenant architecture has benefits beyond database consolidation. These benefits derive from storing the data and metadata specific to a PDB in the PDB itself rather than storing all dictionary metadata in one place.

• • Cost reduction:

    By consolidating hardware and database infrastructure to a single set of background processes, and efficiently sharing computational and memory resources, you reduce costs for hardware and maintenance. For example, 100 PDBs on a single server share one database instance.

  • Easier movement of data and code:

    You can quickly plug a PDB into a CDB, unplug the PDB from the CDB, and then plug this PDB into a different CDB. You can also clone PDBs while they remain available. You can plug in a PDB with any character set and access it without character set conversion. If the character set of the CDB is AL32UTF8, then PDBs with different database character sets can exist in the same CDB.

  • Easier management of database:

    It is easier to apply a patch to one database than to 100 databases, and to upgrade one database than to upgrade 100 databases. The CDB administrator can manage the environment as an aggregate by executing a single operation, such as patching or performing an RMAN backup, for all hosted tenants and the CDB root.

Creating CDB:

The CREATE DATABASE … ENABLE PLUGGABLE DATABASE SQL statement creates a new CDB. If you do not specify the ENABLE PLUGGABLE DATABASE clause, then the newly created database is a non-CDB which cannot contain PDBs. Along with the root container (CDB$ROOT), Oracle Database automatically creates a seed PDB (PDB$SEED).

Creating PDB:

The CREATE PLUGGABLE DATABASE SQL statement creates a PDB. There are several methods to create PDBs. For example, we can create new PDB by using seed PDB (PDB$SEED) or by cloning an existing PDB.

Unplugging and Plugging PDB:

• • To unplug a PDB, you first close it and then generate an XML manifest file. The XML file contains information about the names and the full paths of the tablespaces, as well as data files of the unplugged PDB. This information will be used by the plugging operation.

alter pluggable database pdb_name close immediate;

alter pluggable database pdb1pdb_name unplug into ‘/file_path/file_name.xml’;

• • Plugging the unplugged PDB into new CDB can be done with two methods like “COPY” and “NOCOPY”.
  • Use the data files of the unplugged PDB to plug the PDB into another CDB without any copy.

create pluggable database pdb_name using ‘ile_path/file_name.xml’ NOCOPY TEMPFILE REUSE;

• • Create and define a destination for the new data files, plug the unplugged PDB into the CDB, and then copy the data files of the unplugged PDB.
  • Use the data files of the unplugged PDB to plug the PDB into the CDB and copy the data files to a new location.

create pluggable database pdb_plug_copy using ‘/u01/app/oracle/oradata/pdb2.xml’ COPY FILE_NAME_CONVERT=(‘/old_path’,’/new_path’);