Database consolidation and the new Multitenant architecture

Consolidation is the key enabler for moving databases to on-cloud models. An efficient consolidation strategy can provide elastic sharing of resources and maximize resource utilization in a consolidated stack. A database hosted on a public or private cloud must guarantee tenant isolation and security. In addition to the elementary requirements of cloud deployments, database provisioning on the cloud should be quick and easy.

Oracle Database 12c introduces a multitenant architecture that meets the challenges of cloud deployments. The new tenant-based architecture allows one or more application databases (known as pluggable databases) to run within a single database (known as the container database). Each pluggable database is completely shielded from all other pluggable databases running within the same container database. The architecture provides a unique mechanism to segregate the system and application metadata at the container and pluggable level respectively. In a single-tenant architecture, a root container can have just one pluggable database. In a multitenant architecture, a root container can have more than one pluggable database. While single-tenant is free of cost and available in Oracle Database Standard Edition, multitenant can be licensed in Oracle Database Enterprise Edition only.

Starting with Oracle Database 12.1.0.2, the non-tenant or standalone architecture of Oracle Database is deprecated which means that it will not be further enhanced.

Let us get familiarized with the terms of the new world—the container and pluggable databases.

An Oracle 12c container database (CDB) provides the instance that can be shared by multiple databases. A CDB instance has the memory and a set of background processes. From the server subsystem, the container database is the only database visible.

A pluggable database (PDB) is similar to a pre 12c database that serves as an application database backend. A pluggable database contains the application tablespaces.

The following diagram shows the Oracle Database 12c multitenant architecture:

A container database may contain one or more pluggable database containers. With reference to the preceding architecture, let us drill into the specifics of new components.

A container database (CDB) consists of one root container (known as CDB$ROOT), one seed pluggable database (known as PDB$SEED), and multiple pluggable databases. Similar to the previous releases, a container database has an instance and set of files. The database instance is of the container, which means that the System Global Area (SGA) is common for all the pluggable databases. Also, there is a single copy of background processes at the container instance level only, and not replicated for each PDB.

A pluggable database is a database that stores the application data. As of Oracle Database 12c Release 1, a multitenant database can have a maximum of 252 pluggable databases. A PDB service is created at the time of provisioning that runs within the CDB service and gets auto-registered with the CDB listener. Since a PDB runs as a secured service within the container database administration service, it cannot be authenticated by the server operating system. However, you can create a user-defined service using DBMS_SERVICE for OS authentication and TNS connection.

The redo logs and archive logs are at the container level. Every time a pluggable database has to make a redo entry, the request is tagged with the PDB identity. The identity in this case is nothing but the container identifier (CON_ID). Each container in a container database is assigned a unique container id. The root has the container id 1; the seed PDB has 2 and each pluggable database is assigned a container id in a sequential fashion. The container id is quite a significant element as the common redo is logically virtualized by annotating each and every entry with the container id. All the dictionary views and metadata views have an additional column CON_ID to identify the container to which the information belongs.

The control file and server parameter file are at the root container level. Starting with Oracle 12c, there are two categories of parameters—CDB modifiable and PDB modifiable. The V$PARAMETER dictionary view has an additional column ISPDB_MODIFIABLE. The value of the column is N for CDB-modifiable and Y for PDB-modifiable parameters.

The SYSTEM and SYSAUX tablespaces at the root container level store the system metadata that is specific just to the Oracle Database. The application metadata for each pluggable database is stored in each PDB's respective SYSTEM and SYSAUX tablespaces. The UNDO tablespace is at the root container level. Once again, similar to redo, each undo entry is tagged with the container identifier or CON_ID. With one undo for the entire container, you might be interested in evaluating the performance implications of the system. Well, undo management in multitenant consolidation is not too different from schema consolidation. The redo and undo entries are indexed by the container ids, thus speeding up the concurrent record access for a specific PDB.

Although the multitenant architecture shows the TEMP tablespace at the container level, but it can be created for each of the pluggable database too. Users can create tablespaces within the pluggable databases as usual, whenever required.

The Oracle Database 12c Multitenant architecture – features

Having mentioned the challenges of consolidation and cloud deployments, let us take a look at the capabilities enabled by the multitenant architecture.

Multitenant for Consolidation

In the past, enterprises have been following multiple approaches to consolidate databases to achieve tenant isolation and manageability. You could have an enormous physical server and then extract virtualized homes for each application. The virtualized home is the application's world of operation and shares the server subsystem. However the overhead of managing the heterogeneous pieces can be a potential pain point. The consolidation density is limited by the fact that the memory allocated to a virtualized home remains intact whether or not it is getting used.

You can create multiple databases on a server for each application. An aggregation of databases is possible until the server memory is exhausted. Keep in mind that each database instance creates it's own copy of background processes, which holds the CPU cycles, thus reducing the consolidation density.

One of the most efficient approaches before Oracle 12c was to adopt schema-based consolidation. You can achieve the highest consolidation density as multiple schemas are part of a single database. The downside of schema-based consolidation is that tenant isolation was not guaranteed, while security and manageability were a big concern.

The multitenant approach in Oracle Database 12c allows the effective sharing of server resources, the operating system, and even the database. The advanced sharing of resources makes it suitable to implement and deploy on the cloud. It guarantees the tenant isolation as each pluggable database appears remote to other pluggable databases sharing the same container. A common SGA and a single set of background processes optimally utilize the server memory, thus maximizing consolidation. Management capabilities add value to the new architecture in Oracle 12c.

Plug/unplug

A pluggable database can be unplugged from the current container and plugged into another compatible container. Data mobility across the containers becomes easier and quicker as you just have to work with the PDB metadata and not move the application data. The unplug operation captures the PDB lineage in an XML manifest file, allowing a PDB to be plugged into another container database.

The feature comes in handy in data center operations when the databases are expected to move quickly to different service levels, without impacting business continuity. Another very important use case of the plug/unplug feature is when you are required to upgrade or patch subsets of PDBs in a container. You can unplug a PDB from a 12.1 container and plug it in a Oracle 12.x container database.

Manage Many as One

The multitenant architecture enables the "manage many as one" capability. Organizations who are adopting a Multitenant architecture, are expected to see a significant reduction in operational expenses by managing multiple databases as one. Here is the list of multitenant operations that apply to all the pluggable databases:

• Backing up the container database backs up the root container including all the pluggable databases. However, point in time recovery at the pluggable database level is possible.

  Note

  You can also backup just the CDB$ROOT or a particular PDB

• In a data guard setup, all pluggable databases are auto-discovered at the standby site. Therefore, a high availability of all PDBs is maintained by implementing the data guard at the container level.

  Note

  In Oracle Database 12.1.0.2, you can provision a PDB on a primary site but disable its recovery at the standby site by specifying STANDBYS=NONE

• Upgrading and patching the container database upgrades or patches all the pluggable databases. However, if you are required to upgrade or patch a subset of pluggable databases, you can unplug and plug into a different container of a higher release or patch set.

Rapid provisioning

Pluggable databases can be quickly provisioned either locally within a container or from a remote container. PDB provisioning doesn't involve copying the system metadata or the creation of background processes, thereby speeding up the creation process.

A pluggable database can be provisioned in different ways as listed here:

• A fresh PDB from seed (PDB$SEED)—creating a brand new pluggable database from the seed PDB is a mere copy of SYSTEM and SYSAUX files from the seed location to the target PDB location on the database server.
• Clone/copy an existing PDB—an existing PDB can be cloned locally within the current container. Operationally, cloning a PDB is copying the files to a new PDB location. The PDB remote cloning feature is available from Oracle Database 12c (12.1.0.2).
• Snapshot Cloning—Oracle Database 12c supports snapshot cloning of pluggable databases on copy-on-write file systems. By virtue of the copy-on-write feature, snapshot cloning is an extremely fast method of creating copies of pluggable databases.

Oracle Database 12.1.0.2 introduced new enhancements to PDB cloning in a multitenant container database. The enhancements are briefly described here:

• Schema consolidation to PDB-based consolidation—you can create a new pluggable database by specifying just the tablespaces to be available in the new PDB. You can specify tablespaces in the USER_TABLESPACE clause at the time of PDB creation. Pre 12c databases, which earlier used the schema consolidation approach, will benefit from this new feature while moving to Oracle 12c Multitenant.
• Metadata-only clone—You can clone a PDB data model, and not its data, by specifying NO DATA at the time of cloning.
• Remote clone non-CDB as PDB—You can directly clone an Oracle 12c non-CDB as the PDB in a multitenant container database.

CDB Resource Management

Resource management is an essential exercise in a multitenant container database that hosts multiple application databases, with a common SGA and background process. With multiple pluggable databases, scenarios may arise where databases may compete for server resources. The request congestion might impact the performance of critical databases, thus impacting business SLAs. The Database Resource Manager (DBRM) feature has been enhanced in Oracle 12c to extend support to the multitenant architecture.

In a multitenant environment, you can create CDB plan directives to manage the allocation of the CPU as shares, and parallel execution slaves across the multiple pluggable databases. In the event of resource congestion, pluggable databases follow the CDB resource plan to prioritize the requests.

Within a pluggable database, you can create the usual database resource management plans to control the consumption of the available resources across consumer groups.

Common users and local users

Starting with Oracle 12c Multitenant architecture, there will be two families of users, namely common users and local users. The container DBA creates a common user in the root container. Local users are the ones that are visible and operational within a single pluggable database. All the users until Oracle Database 11g will fall under the category of local users.

Here are the important points regarding common and local users:

• All common users must start with C##. SYS is an exception.
• Pluggable databases must grant CONNECT and CREATE SESSION privilege to the common user to allow access to the container.
• The static views CDB_USERS and DBA_USERS have a new column COMMON to differentiate common and local users.
• A common user can be used to execute a generic script across multiple containers (using catcon.pl).
• With Oracle 12c (12.1.0.2), common users can query common objects in root the (CDB$ROOT) from multiple containers using the CONTAINERS clause.