Pluggable Database : An Introduction

What is PDB:

Pluggable database , also referred to as Multitenancy, is the new option of Oracle Database 12c that enables dense database consolidation, rapid provisioning and rapid cloning as well as rapid patching and upgrades. It enables the administrators to manage many databases as one and allows resource management between databases.

In 12c, you can create two types of database: container database and non container database. Container database, referred to as CDB, is the database that can contain several PDBs. Non-container database, referred to as non-CDB, is the regular database that existed prior to 12c.

Multitenacy is  just a deployment choice. When you create a database, you choose whether you want to create a CDB or a non-CDB. Once you make that choice and create the  database, nothing else changes. The way you connect to database and the way you interact with database remains the same for both CDB and non-CDB.

Business challenges that PDB address:

Prior to 12c , database consolidation would happen through one of the following two ways:

1) Server or OS consolidation: You would start several database instances on a single server. Each instance would be on a server virtualized environment like  LDOMs or on an OS virtualized environment such as Solaris Containers.The advantages of this approach is that it provides complete isloation and maximum security. No two distinct database users can ever meddle with each other..as there are usually that many instances as the number of databases. This form of database consolidation cannot provide very dense consolidation as it is not sharing any of the database resources: there are as many copies of Oracle background process and SGA as there are databases so the system very soon exhaust its CPU and memory.

2) Consolidation in the database : You would enable schema based consolidation where each database is represented as a schema in a single monolithic database instance.In this approach, you can consolidate lot of database (schema) into one database instance thereby attaining dense database consolidation. However, the disadvantages are its weak or no name space isolation and very weak security model. A database user can view/change data of another database schema.

Pluggable database provides database consolidation within the database. In a way, it is  a supported version of schema based consolidation with all disadvantages of schema based consolidation removed. Additionally,  it also provides additional features like pluggability and rapid cloining.

 Introduction to Multitenancy architecture:

 

At a higher level, an Oracle Database consist of Oracle Database Instance and Oracle Database. The instance consist of group of Oracle background process and a shared memory. The database consist of group of data files; some of which contain the user data and some contain Oracle system data. When you start an Oracle database, you essentially have a bunch of Oracle background processes, a bunch of shared memory and data files for storing the data. And this gets replicated for each database that is created. So, each database needs additional shared memory and series of Oracle database process....which quickly consumes the available server resources.

With multitenant architecture you can create an empty container database and then plug in the databases as PDBs. Now you are using just one SGA and one set of Oracle database processing thereby saving the system resources to enable dense database consolidation

By consolidating existing databases as PDBs, administrators can manage many databases as one. For example, tasks such as backup and disaster recovery are performed at the level of the CDB. The multitenant architecture also provides the flexibility to take backups and perform point-in-time recovery at the PDB level, if required. 

At a physical level Oracle database consist of a bunch of files: data files as well as bootstrap files like control files, spfile and so on. The physical data files implements the logical entity tablespace which in turn implements tables. Tables hold only three kinds of data

1) metadata that describes the oracle System
2) Metadata that describes the user schema
3) Actual user data .

Prior to 12c, the metadata that describes Oracle system and the metadata that describes user schema were stored collectively in a set of tables that are referred to as data dictionary. This data dictionary tables were stored in dedicated tablespace namely System and Sysaux.

Initially when you create the database, the data dictionary tables only have Oracle system related metadata. Later on when user creates its own data, the data dictionary tables contain user and system metadata. And the user tablespace contains only user data.

Now, if you want to transport a user tablespace from one database to another, you can simply copy the data files containing the tablespace from database location to another . However, you will have to go through the data dictionary view to find out user metadata information and then transport it. If the user metadata information was stored seperately from the Oracle sytem metadata,  than you could simply copy user's tablespace metadata  from one location to another to transport a tablespace.

Hence , in 12c the data dictionary tables got horizontally partitioned;  one partition containing Oracle System metadata is stored in CDB$root container  and another  partition container user data related metadata is stored in the PDB. Now, transporting a tablespace from one database to another means copy the data files as well as files containing the metadata information of user schema. The user related metadata is stored in SYSTEM and SYSAUX tablespaces of PDB and the Oracle metadata is stored in SYSTEM and SYSAUX tablespaces of CDB$root

In other words, each data dictionary table now occurs twice, once for Oracle System and once for the user schema. Every query against a data dictionary table is now a union between the two occurrences of that table.

The primary physical difference between CDB’s and non-CDB is the system data files. A non-CDB has only one set of system data files. In contrast the root and each PDB in a CDB has its own SYSTEM and SYSAUX tablespaces which has its own complete set of dictionary tables describing the objects in itself.

There are two ways for provisioning new PDBs in multitenant architecture.  One is by cloining and another is plug in an previously unplugged PDB.   You clone the PDB when you want an extra copy of that PDB and you plug in a PDB when you want to move the PDB from one CDB to another. When you create a new PDB with out explicitly stating the source PDB from which you want to clone, than Oracle creates the new PDB by cloning that PDB from pdb$seed container. In other words, creation of a PDB is internally a clone operation.


  These operations are done using SQL commands. which  can be executed from any client machine and, hence, access privileges to the server machine are not needed. In addition, the SQL commands can be run in parallel thereby reducing execution times. They can also be programmed in PL/SQL to automate the process of provisioning and cloning.