CHAPTER 31

Multitenant Container and Pluggable Database Architecture

Exam Objectives

• 063.2.1    Describe the Multitenant Container Database Architecture

• 063.2.2    Explain Pluggable Database Provisioning

The Oracle Multitenant option (also known as pluggable databases) allows database administrators (DBAs) to consolidate many databases into one. To a large extent, the previously separate databases can still be used and administered independently following conversion to the multitenant environment. While end users and developers should not be aware of any change, Oracle Multitenant can at first be somewhat confusing for DBAs. Fear not. Once you have a firm understanding of the architecture, everything will fall into place.

Although not examined, it is certainly helpful to understand the licensing implications of the Oracle Multitenant architecture and its future importance. To use the headline functionality of database consolidation, you must purchase Enterprise Edition licenses plus the Oracle Multitenant option. This is not cheap. However, the Multitenant Single Tenancy functionality is included at no charge in all editions and does offer considerable benefits. The Upgrade Guide release 12.1.0.2 includes this statement: “The non-CDB architecture is deprecated in Oracle Database 12c, and may be desupported and unavailable in a release after Oracle Database 12c Release 2. Oracle recommends use of the CDB architecture.” This unequivocal statement of direction makes it clear that from now on, DBAs should consider Oracle Multitenant for all installations.

Describe the Multitenant Container Database Architecture

Many Oracle sites have a large number of databases. The workload of administering many databases and the hardware and license costs lead many users toward a strategy of server consolidation. This can be done at several levels, such as many databases on one machine, many schemas in one database, or perhaps a virtualized environment of many operating system images, each supporting one database running on a single physical machine. Multitenant is simpler to implement than any of these: There is, physically, one database on one machine. But logically, there are many databases that can be accessed and managed independently. This can solve a number of business problems quickly and simply.

Multitenant Concepts

An Oracle Multitenant environment consists of a container database (CDB) and one or more pluggable databases (PDBs). A container is a set of tablespaces exposed to the world as a logical database. On creation, a CDB will contain a minimum of two containers. The root container, named CDB$ROOT, is the management container for the environment. Generally speaking, no one other than the DBA will ever connect to the root container. The seed container (named PDB$SEED) is a supplied, read-only container that can be cloned to create pluggable containers (named whatever you please).

Here are some important points:

•  There is one database, the CDB.

•  There is one database instance, with one System Global Area (SGA) and one set of background processes.

•  A PDB is nothing more than a set of tablespaces and a service.

•  The PDB services are exposed to users by the database listener.

•  When you connect to a PDB service, the scope of your session is limited to the tablespaces associated with the PDB.

•  Objects defined in the root container may be shared and visible to all PDBs.

•  Objects (including users) defined in a PDB are private to their PDB.

•  Each PDB has its own data dictionary, defining its local objects, with pointers to shared objects defined in the root container’s data dictionary.

A pluggable database is a set of tablespaces, including a SYSTEM tablespace with a data dictionary. This data dictionary defines the objects local to the container, which are users and their schema objects (such as tables). All PL/SQL, SELECT, DML, and DDL commands executed while connected to the container affect only these local objects. When connected to the root container, it is possible (if you have appropriate permissions) to see the whole picture by querying the data dictionaries of all the containers. In the root container, you can create users and roles that are propagated to all containers. The privileges these users have in any one container may, however, be different.

There are some considerations when using Oracle Multitenant with other options:

•  All containers must use the same character set. This is a natural limitation of the fact that there is only one database, the CDB.

•  Data Guard must be configured at the CDB level because there is only one stream of redo. It is, however, possible to exclude some PDBs from standby databases. Note that this is possible only from release 12.1.0.2.

•  Database Vault must be configured individually in each PDB. There is no overlap of realms across PDBs.

•  Encryption must be configured individually per PDB. Each PDB generates and manages its own keys, stored in a shared wallet.

•  Unified audit can be configured at the CDB and PDB levels. When creating a policy in the root container, appending CONTAINER=ALL to the CREATE AUDIT POLICY command will propagate it to all PDBs. In a PDB, any policies created are local to the PDB.

•  GoldenGate and XStream replication are multitenant aware; they can capture and apply changes per PDB. Streams, however, is not supported in a multitenant environment.

While the Oracle Multitenant option is primarily intended for consolidating many previously separate non-CDB databases into one CDB, there is another option: Multitenant Single Tenancy. This uses the CDB/PDB architecture, but has only one pluggable database per CDB. A case can be made for creating all databases in this way. The reason is that while some sites may not need the server consolidation features of Oracle Multitenant, all sites face problems of downtime because of patching and upgrade that can be alleviated in an Oracle Multitenant environment. Patches need to be applied to the root container only; the pluggable container does not need to be patched.

To understand a multitenant environment, you must become familiar with a set of views. In a non-CDB environment, the data dictionary views used are prefixed with USER_, ALL_, and DBA_. The USER views show all objects owned by the user who is querying them. The ALL views show all objects on which the user has privileges, regardless of the schema in which they reside. The DBA views show all objects in the database. There is a fourth set of views, prefixed with CDB_. The CDB views exist in all databases, but they have meaning only in the root container of a CDB. In the root container, the CDB views show all objects in the CDB. In effect, they are a UNION ALL of the DBA views in every container, namely, the root container, the seed container, and all pluggable containers. In a non-CDB database or a pluggable database, the CDB views show the same rows as the DBA views. The column CON_ID in a CDB view identifies the container from which the row came. Containers are uniquely identified by the container ID and also by name. Figure 31-1 shows a query that lists the tablespaces associated with each container. Note that the query was executed while connected to the root container.

Figure 31-1    The mapping of tablespaces to containers in a CDB

Figure 31-1 shows that the CDB contains four containers:

•  Container 1 is the root container, CDB$ROOT. It has five tablespaces: SYSTEM, SYSAUX, UNDOTBS1, TEMP, and USERS.

•  Container 2 is the seed container, PDB$SEED. This has three tablespaces: SYSTEM, SYAUX, and TEMP.

•  Containers 4 and 5 are pluggable containers named PDBA and PDBB. They each have tablespaces named SYSTEM, SYSAUX, TEMP, and other tablespaces for user data.

The unique identifier for a tablespace in a CDB is not its name. It is the name qualified with the container ID. The views joined in the query are V$CONTAINERS, which has one row for every container, and CDB_TABLESPACES, which has one row for every tablespace. The join column is CON_ID. Similar queries against (for example) CDB_DATA_FILES, CDB_USERS, and CDB_TABLES would show the datafiles, users, and tables that exist in each container.

The Drivers for Multitenant

The Oracle Multitenant option is primarily intended to be of use for server consolidation. Many sites have a plethora of databases. This may be because there are many applications and each is running in its own database, or perhaps it is because there are separate databases for production, test, development, and other purposes. In some cases, it may be that these databases do not use the full capacity of the hardware on which they are deployed and do not require full-time administration support. But they do each require storage, memory, CPU resources, and administration time, particularly for patching and upgrades. There may also be licensing implications if the databases are distributed across many machines.

In a non-CDB environment, each database instance has its own SGA, background processes, and data dictionary. They may have their own Oracle Home, or they may be running on different machines. This is potentially a significant overhead. Creating one CDB and running each database as a pluggable container will yield savings; you have only one set of background processes, one SGA (which will usually be much smaller than the total of the individual SGAs), one copy of many data dictionary structures, and one Oracle Home. The CDB architecture enables sharing of these resources with consequent savings.

When consolidating servers, it is vital that there should be no requirement for application changes and that each previously isolated database should remain completely separate from the other databases. If these conditions cannot be met, the consolidation exercise may cost more than it saves. The Oracle Multitenant architecture guarantees that no application changes will be needed and that the previously separate applications are completely isolated from each other. End users and developers will have no way of determining that the database has been converted from a non-CDB database into a pluggable container. The separation of duties between the various application administrators and users is maintained; in addition, to a large extent each PDB can have its own DBA and be administered as though it were a non-CDB.

Perhaps most important is the administration workload. Many experienced DBAs prefer to manage one big database rather than many small ones. This is relevant to one issue in particular: patching and upgrade. Patches and upgrades (henceforth referred to as updates) make changes either to the binaries that run the Oracle instance or to the data dictionary (or to both). If an update affects only the binaries (the simplest form of patch), then Oracle Multitenant hugely reduces the workload; patching the one Oracle Home off which the CDB runs will in effect patch all the PDBs within it. If the update includes scripts that change the data dictionary, then usually no work is required in the PDBs; however, sometimes this is not the case. Shared objects are defined in the root container, with pointers to them in each pluggable container. If these references require updating, this occurs automatically. In the worst case (such as a major upgrade), objects in the PDB may be invalidated. In that case, they must be recompiled. This can be done manually, or it will occur automatically when the object is accessed by a user session. In all cases, Oracle guarantees that applying any sort of update will never leave a previously valid user object in an irrevocably invalid state.

The fact that critical data dictionary structures are defined once only, in the root container, opens the way for an extraordinarily fast and easy method of upgrade. Consider the situation where a CDB is at release 12.1.0.1 and contains many PDBs. To apply the 12.1.0.2 patchset, the DBA can install the new Oracle Home on the same machine and create a new release 12.1.0.2 CDB. Then the DBA can unplug the PDBs from the old 12.1.0.1 CDB and plug them into the new 12.1.0.2 CDB. The downtime involved in this operation may be in single-digit minutes for each PDB. The PDBs can be moved individually, according to whatever schedule is convenient for the DBA and the users. The technique for plugging and unplugging will be detailed in Chapter 32.

The CDB and the Root Container

The CDB is the database, mounted and opened by the instance. The root container is the container from which all the shared resources are managed. Principal among these are as follows:

•  The controlfile

•  The undo tablespace

•  The redo log

•  The instance SGA and background processes

•  The data dictionary

•  Common users and roles

The root container will have a minimum of four tablespaces: SYSTEM, SYSAUX, UNDO, and TEMP. It will also have the common users and roles that are propagated to all the PDBs, and it will have all the Oracle-supplied objects that every PDB requires. These objects include, for example, all the supplied PL/SQL packages. Operations affecting these shared resources can be accomplished only through a session attached to the root container.

The root container is the only container to which you can connect without going via a database listener. If you are logged on to the server machine and you set your ORACLE_SID environment variable to the name of the CDB instance, you can connect to the root container. It is not possible to connect to a PDB in this way because there is only one instance, with only one name. Following a connection to the root container, it is possible to move your session to another container. Figure 31-2 demonstrates this.

Figure 31-2    Moving your session from one container to another

Figure 31-2 shows a Windows user setting the ORACLE_SID environment variable to the name of an instance CDBA and then connecting to this with interprocess communication (IPC), bypassing the database listener. SQL*Plus includes a command SHOW CON_NAME, which shows the name of the container to which the session is currently connected, which is initially the root container. Then the ALTER SESSION command is used to move the session to a pluggable container named JW1.

PDBs: Pluggable Containers

A pluggable container, also referred to as a pluggable database, is a set of tablespaces and objects to be used by developers and end users as though it were an independent database.

PDBs have a SYSTEM tablespace just like non-CDBs. Only the user metadata specific to the PDB is stored in the PDB’s SYSTEM tablespace. In addition, there are logical links to the common objects defined in the root container. The object names are the same in a PDB as in a non-CDB or a CDB, such as OBJ$, TAB$, and SOURCE$. Thus, the PDB appears to an application as a stand-alone database. The DBA for an application in a PDB will, as a general rule (to which there are a few exceptions), not be aware that there may be one or many other PDBs sharing resources in the CDB.

Every PDB has a unique container ID number and container name. These are recorded in the controlfile and exposed through the views V$CONTAINERS and V$PDBS. When connected to a pluggable container, rows referring to containers other than the current container are filtered out. From the root, all containers are visible.

Every pluggable database has a unique global name. This is the name of the container and is used as the default service to be registered with the database listener. Figure 31-3 shows connections to PDBs named JW1 and JW5 through their default service and the listener (using the EZCONNECT syntax) with queries showing that the hosting database and instance, named CDBA, are always the same.

Figure 31-3    Connecting to a container database with two PDB tenants

Explain Pluggable Database Provisioning

A major advantage of moving to Oracle Multitenant architecture is the ease with which pluggable databases can be created, copied, and relocated. This is the process of provisioning.

Techniques for Provisioning Pluggable Containers

There are four techniques for provisioning a container:

•  Create a new PDB from the seed container. This method creates a new PDB consisting of nothing more than the SYSTEM, SYSAUX, and TEMP tablespaces. This would typically be used for a new application implementation.

•  Create a new PDB from a non-CDB. This method takes an existing database, which must be release 12.x, converts it into a pluggable container, and plugs it into a CDB. This would typically be part of a consolidation exercise. It is not possible to convert back to non-CDB.

•  Clone a PDB to another new PDB. The source can be a PDB in the same or a different CDB. This could be used for creating test or development environments from a production PDB.

•  Plug in an unplugged PDB. A PDB can be unplugged from one CDB and then plugged into the same or a different CDB. This can be used to relocate or to upgrade a PDB.

Whichever technique is appropriate, all are usually fast. The clone technique requires copying the datafiles that make up the PDB’s tablespaces. The time for this will be dependent on the size of the datafiles. The other techniques are typically single-digit minutes.

Communications Between Containers

When databases are deployed as non-CDB databases, there will often be reasons to share data between them, whether the databases are on separate servers or even on the same server. This is usually accomplished by using database links to access tables in other databases. Following provisioning them into the same CDB as PDBs, the same method can be used. But since the objects in two PDBs reside within the same container, it is possible to create a fast version of a database link (known as a fast intra-CDB link) under the covers. Usually, a database link will use Transmission Control Protocol (TCP). In an Oracle Multitenant environment, it is possible to optimize database link communications by using IPC. Remember that a PDB does not know where another PDB or non-CDB database resides, so the definition and use of a database link are the same, regardless of where both databases reside. All application users will make use of links as the only way to communicate between containers.

From within the root container, it is possible to query objects in all PDBs, but only in a strictly controlled manner. A common user can be defined in the root and will be propagated to all containers. If this user in each container (including the root) owns a table or a view or synonym with the same definition, all copies of this object can be queried from the root by using the CONTAINERS clause in a SELECT statement. This example queries the DUAL table, which exists in the common SYS schema:

Two-Minute Drill

Describe the Multitenant Container Database Architecture

•  The Oracle Multitenant architecture helps use server resources more efficiently by sharing instance memory and processes as well as SYSTEM tablespace common objects.

•  Upgrading a multitenant container database upgrades all PDBs within the container.

•  A CDB at any point in time can contain zero, one, or up to 252 user-defined PDBs.

•  A CDB is a single instance, regardless of the number of PDBs within the CDB.

•  Across all PDBs within a CDB, the redo log files, undo tablespace, controlfiles, and temporary tablespace are shared.

•  An individual PDB may have its own temporary tablespace.

•  Each PDB has its own SYSTEM tablespace with private metadata. The SYSTEM tablespace has pointers to common Oracle metadata stored in the SYSTEM tablespace within the CDB.

•  The data dictionary views prefixed with DBA_ have corresponding views with the CDB_ prefix. When queries are run in the root container, these show the entire environment.

Explain Pluggable Database Provisioning

•  The CDB is initially provisioned with the root container CDB$ROOT and the seed container PDB$SEED.

•  PDBs can be created from the seed container by cloning an existing PDB, by plugging in a non-CDB database, or by plugging in a previously unplugged PDB.

•  Non-CDBs can be converted to a PDB only once they have been upgraded to Oracle Database 12c. This process cannot be reversed.

•  Table data can be shared between PDBs within a CDB by using an intra-CDB link.

•  An intra-CDB link behaves just like a pre-12c database link and requires no application changes.

Self Test

1.  Identify the correct statements about Oracle Database versions and their compatibility with container databases. (Choose all correct answers.)

A.  Oracle Databases created in 12c are automatically pluggable into any existing CDB.

B.  An Oracle 12c database can be a non-CDB, a CDB, or a PDB.

C.  Oracle 11g databases can be plugged into a container database if you create an XML database description file.

D.  Oracle 11g databases can be easily upgraded to Oracle Database 12c by plugging it into a CDB created with Oracle Database 12c release 1 or newer.

E.  A CDB created with Oracle Database 12c can be converted to a PDB by removing the PDB$SEED user container.

2.  Which of the following database objects are always shared across all PDBs within a system container? (Choose three correct answers.)

A.  The temporary tablespace

B.  The undo tablespace

C.  The database global name

D.  The controlfile

E.  Online redo logfiles

3.  Which of these is a necessary condition for a non-CDB to be plugged into a CDB? (Choose the best answer.)

A.  The CDB and non-CDB must use the same database character set.

B.  GoldenGate replication must not be configured.

C.  All the non-CDB tablespaces with the same names as already existing CDB tablespaces must be renamed to prevent conflicts.

D.  The non-CDB must be release 12c or the terminal release of 11g.

4.  What method is appropriate for a user to establish a session against a PDB? (Choose two correct answers.)

A.  Local users can set their ORACLE_SID environment variable to the PDB name and connect over IPC.

B.  Logon to a PDB is possible only through a database listener.

C.  The user can log on to the root container and then alter the session to connect to an application schema in the PDB.

D.  When connected to one PDB, the user can connect to another PDB in the same CDB through a database link.

Self Test Answers

1.      B. A 12c database can be a container database, a pluggable database, or a traditional non-container database.
    A, C, D and E are incorrect. A is incorrect because there are restrictions on whether a non-CDB is compatible with a given CDB, such as the character set. C and D are incorrect because an 11g database must be upgraded before plugging in. E is incorrect because there is no way to reverse the conversion to the multitenant environment.

2.      B, D, and E. The undo tablespace, the controlfile, and the redo log are used by sessions against all containers and managed only from the root container.
    A and C are incorrect. A is incorrect because each container can create and manage its own temporary tablespaces. C is incorrect because each container always has its own global name.

3.      A. The database character set must be same, or the plug-in will fail
    B, C, and D are incorrect. B is incorrect because GoldenGate is compatible with Oracle Multitenant. C is incorrect because the tablespace name is not a unique identifier. D is incorrect because all 11g databases must be upgraded before plugging in.

4.      B and D. B is correct because establishing a session against a PDB can be done only through a service exposed by a listener. D is correct because once one has a session against one PDB, a session against another can be established through a database link, which will also be established by the listener.
    A and C are incorrect. A is incorrect because only the root container can be contacted using the IPC protocol. C is incorrect because while you can move your session from one container to another, you cannot change the schema as you do so.