Beginners Guide to Oracle Database 18c Multitenant Architecture

Challenges

Starting with Oracle Database 12c, the multitenant architecture enables you to have many pluggable databases inside a single Oracle Database instance.

What is the benefit of using the multitenant architecture in Oracle Database 18c?

Currently, many Oracle customers have large numbers of “departmental” applications built on Oracle RDBMS.

• These applications rarely use a significant percentage of the hardware on which they are deployed. A significant number of instances and the amount of storage allocation for all these small databases prevent these from being placed on the same physical and storage server.
• Moreover, they are typically not sufficiently complex to require 100 percent of the attention of a full-time administrator.
• To better exploit hardware and DBA resources, customers would prefer to have most of these departmental applications consolidated onto a single Oracle RDBMS deployment.

The multitenant architecture allows DBAs to consolidate large numbers of small departmental database applications into a single, larger RDBMS installation.

Non-CDB Architecture

In Oracle Database release 11g, the only kind of database that is supported is a non-CDB. The old architecture is referred to as non-CDB architecture. The term non-CDB is shorthand for a pre-release 12.1Oracle database. This type of database requires its own instance, which must have its own background processes and memory allocation for the SGA. It must store the Oracle metadata in its data dictionary. The database administrator can still create Oracle 18c non-CDBs with the same pre-12.1 architecture. These databases are not multitenant container databases; they are non-CDBs.

When you administer small departmental database applications, you must create as many databases as applications, which entails multiplying the number of instances. Consequently, this increases the number of background processes and memory allocation for the SGAs. You must therefore provision enough storage for all data dictionaries of these databases.

When you need to upgrade your applications to a new version, you have to upgrade each database, which is time-consuming for the DBA.

Multitenant Architecture: Benefits

Consolidating many non-CDB databases onto a single platform reduces instance overhead, avoids redundant copies of data dictionaries, and consequently storage allocation. It benefits from fast provisioning, time-saving upgrading, and better security through separation of duties and application isolation. The new multitenant database that consolidates databases together is a multitenant container database or CDB and a database consolidated within a CDB, a pluggable database, or PDB.

• No application change and very fast provisioning: A new database can be provisioned quickly. A clone of a populated database can be created quickly. A populated database can be quickly unplugged from its CDB on one platform and quickly plugged into a CDB on a different platform. A non-CDB can quickly be plugged into a CDB.
• Fast upgrade and patching of the Oracle Database version: The cost (time taken and human effort needed) to upgrade many PDBs is the cost of upgrading a single Oracle Database occurrence. You can also upgrade a single PDB by unplugging it and plugging it into a CDB at a different Oracle database version.
• Secure separation of duties: The administrator of an application can perform the required tasks by connecting to the PDB that implements its back end. However, a user who connects to a PDB cannot see other PDBs. To manage PDBs as entities (for example, to create, drop, or unplug or plug one), a system administrator must connect to the CDB. A user connecting to a CDB must have special privileges.
• Isolation of applications: This task may not be achieved manually unless you use Oracle Database Vault, for example. A good example of isolation is dictionary separation enabling an Oracle database to manage the multiple PDBs separately from each other and from the CDB itself.

Other Benefits of Multitenant Architecture

• The multitenant architecture ensures the backward-compatibility principle. An example is the data dictionary views. The DBA_OBJECTS view shows the same results in a PDB as in a non-CDB for a particular application.
• The multitenant architecture is designed to be fully interoperable with RAC. Each instance in an Oracle RAC opens the CDB as a whole. A session sees only the single PDB it connects to.
• Enterprise Manager integrates CDBs and models the separation of duties of the CDB administrator and the PDB administrator.
  • A CDB can be defined as a target. An Enterprise Manager user can be given the credentials to act as a CDB administrator in such a target.
  • A PDB can be set up as a subtarget of a CDB target. An Enterprise Manager user can be given the credentials to act as a PDB administrator in such a target. An Enterprise Manager user that has been set up with the credentials to act as a PDB administrator for a particular PDB is able to connect to that one PDB and is unaware of the existence of peer PDBs in the same CDB. Moreover, when the intention is to carry out the duties of an application administrator, this Enterprise Manager user is unaware that the environment is a CDB and not a non-CDB.
• Resource Manager is extended with new between-PDB capabilities to allow the management of resources between the PDBs within a CDB. The backward-compatibility principle implies that Resource Manager must function in exactly the same way within a PDB as it does in a non-CDB.
• When you upgrade a whole CDB with n PDBs, you achieve the effect of upgrading n non-CDBs for the cost of upgrading one non-CDB.

Oracle Multitenant Container Database

This image above illustrates the consolidation of three applications that were deployed into three distinct non-CDBs into a single one. It shows a multitenant container database with four containers: the CDB root and three pluggable databases. Each pluggable database has its own dedicated application and is managed either by its own DBA or by the container administrator that is SYS user of the CDB root container, a common user. This common SYS user can manage the CDB root container and every pluggable database.

A pluggable database is a set of database schemas that appears logically to users and applications as a separate database. But at the physical level, the multitenant container database has a database instance and database files, just as a non-CDB does. Nothing changes: neither the client code nor the database objects.

It is easy to plug non-CDBs into a CDB. A CDB avoids redundancy of:

• Background processes
• Memory allocation
• Oracle metadata in several data dictionaries

A CDB that groups several applications ends up with one instance. This instance will have one set of background processes, one SGA allocation, and one data dictionary in the CDB root container, common for all PDBs. Each PDB will maintain its own application data dictionary.

When you must patch or upgrade an application, you can perform maintenance operation only once on the CDB. Consequently, all the applications associated with the CDB will be patched or updated at the same time.

Configurations

What are the possible instances of database configurations?

• Each database instance can be associated with one and only one non-CDB or multitenant container database.
• In an Oracle RAC environment, several instances can be associated to a non-CDB or multitenant container database.
• An instance is associated with an entire CDB.

If there are multiple databases on the same server, then there is a separate and distinct instance for each non-CDB or CDB. An instance cannot be shared between a non-CDB and CDB.

There are three possible configuration options:

Multitenant configuration

Typically more than one PDB per CDB, but can hold zero, one, or many PDBs at any one time, taking advantage of the full capabilities of the Multitenant architecture, which requires the licensed Oracle Multitenant option.

Single-tenant configuration

The special case of the Multitenant architecture, which does not require the licensed option.

Non-CDB

The Oracle Database 11g architecture

Database Objects in a non-CDB

How is Oracle metadata shared between several applications in a non-CDB?

Immediately after the creation of the non-CDB, the only objects in the data dictionary are the Oracle- supplied objects. At this point, there is no user data. The only schemas in the database are those required by the database system.

User-Added Objects to a non-CDB

In the non-CDB, users and user data are added.

The best practice is to add this data in tablespaces that are dedicated to user data. Storing the data in separate tablespaces enables you to protect, secure, and transport the data more easily, even though the user metadata is in the data dictionary along with the Oracle system metadata:

• object definitions
• User definitions
• PL/SQL code
• Other user-created objects

SYSTEM Objects in the USER Container

In a multitenant container database, the concept of containers is introduced to separate the Oracle-supplied objects and the user data including the metadata into distinct containers.

Each container has a SYSTEM tablespace that holds a data dictionary.

• There is a dictionary in the Oracle metadata–only container that has the metadata for the Oraclesupplied objects.
• There is a dictionary in the user container holding the user metadata.

One of the goals of the multitenant architecture is that each container has a one-to-one relationship with an application.

Separating the metadata is the first step, the second is allowing the application or users inside the “user” container to access the Oracle-supplied objects. The user container is called a pluggable database (PDB).

The Oracle objects could have been duplicated in each PDB, but that takes a lot of space and would require every PDB to be upgraded each time an Oracle-supplied object changes, for example, with patches. The Oracle-supplied objects reside in a container called the CDB root container, which is named CDB$ROOT.

Pointers from a PDB to the Oracle-supplied objects allow the “system” objects to be accessed without duplicating them in the PDB. The PDB has the pieces it needs to be a complete environment for a database application. The application can run in the PDB just as it does in a non-CDB.

Provisioning a Pluggable Database

To create a new PDB, use the provided CDB seed PDB. This CDB seed container is named PDB$SEED and is a part of every CDB. When you create a new PDB, the CDB seed PDB is cloned and gives the new PDB the name you specify. This operation is very fast. It is measured in seconds. The time that is taken is mostly for copying the files.

There are different methods to provision pluggable databases:

• Create a new PDB from the PDB$SEED pluggable database: This scenario is useful, for example,for a new application implementation.
• Create a new PDB from a non-CDB: Plug the non-CDBs in a CDB as PDBs as part of migration strategy. It is also a good way to consolidate the non-CDBs into a CDB.
• Clone a non-CDB: Clone the non-CDBs in a CDB as PDBs, as part of migration strategy. This is a good way to keep the non-CDB and therefore have the opportunity to compare the performance between the new PDB and the original non-CDB or at least wait until you consider that the PDB can work appropriately.
• Clone a PDB from another PDB into the same or another CDB: An example of this method is application testing. Relocate a PDB into another CDB so as to dispatch resources.
• Plug an unplugged PDB into another CDB: For example, instead of upgrading a multitenant container database from one release to another, you can unplug a pluggable database from one Oracle Database release and then plug it into a newly created multitenant container database from a higher release.
• Proxy a PDB: A proxy PDB provides fully functional access to another PDB in a remote CDB. This feature enables you to build location-transparent applications that can aggregate data from multiple sources that are in the same data center or distributed across data centers.

Multitenant Container Database Architecture

The image above shows a CDB with four containers: the CDB root, the CDB seed, and two PDBs. The two applications use a single instance and are maintained separately.

At the physical level, the CDB has a database instance and database files, just as a non-CDB does.

• The redo log files are common for the whole CDB. The information it contains is annotated with the identity of the PDB where a change occurs. Oracle GoldenGate can understand the format of the redo log for a CDB. All PDBs in a CDB share the ARCHIVELOG mode of the CDB.
• The control files are common for the whole CDB. The control files are updated to reflect any additional tablespace and datafiles of plugged PDBs.
• An UNDO tablespace is by default local in each container. It is possible to have a single UNDO tablespace shared by all containers. In this case, there is one UNDO tablespace per instance in a RAC database.
• The CDB root or a PDB can have only one default temporary tablespace or tablespace group. Each PDB can have temporary tablespaces for use by local or common users in the PDB.
• Each container has its own data dictionary stored in its proper SYSTEM tablespace, containing its own metadata, and a SYSAUX tablespace.
• The PDBs can create tablespaces within the PDB according to application needs.
• Each datafile is associated with a specific container, named CON_ID.

Containers

To summarize, a CDB is an Oracle database that contains the CDB root, the CDB seed, and possibly several pluggable databases (PDBs). What is a PDB in a CDB? A PDB is the lower part of the horizontally partitioned data dictionary plus the user’s quota-consuming data.

A non-CDB cannot contain PDBs. The multitenant architecture enables an Oracle database to contain a portable collection of schemas, schema objects, and non-schema objects that appear to an Oracle Net client as a separate database. For the PDBs to exist and work, the CDB requires a particular type of container, the CDB root, generated at the creation of the CDB. The CDB root is a system-supplied container that stores common users, which are users that can connect to multiple containers, and system-supplied metadata and data. The source code for system-supplied PL/SQL packages is stored in the CDB root.

There is only one CDB seed PDB in a CDB. The CDB seed PDB is a system-supplied template that is used to create new PDBs. A CDB can contain up to 4,096 PDBs, including the CDB seed, the services being limited to 10,000. The V$CONTAINERS view displays all PDBs, including the CDB root and the CDB seed.

In summary,

CDB root

• The first mandatory container created at CDB creation
• Oracle system–supplied common objects and metadata
• Oracle system–supplied common users and roles

Pluggable database (PDBs)

• Tablespaces (permanent and temporary)
• Schemas / Objects / Privileges
• Created / cloned / unplugged / plugged / proxied
• Particular PDB: CDB seed (PDB$SEED)used for fast provisioning of a new PDB

Tools

There are different tools to create and upgrade container databases. As shown in the table below, you can create a new CDB or new PDBs either using SQL*Plus or Database Configuration Assistant (DBCA) or during the installation of Oracle Database 18c. SQL Developer and EM Cloud Control allow you to create pluggable databases.

After you create a CDB, you can use views to explore the instance, database architecture, files, and pluggable databases of the CDB. Query views directly with SELECT statements using SQL*Plus or indirectly using GUI tools such as Enterprise Manager or SQL Developer.

You can upgrade an Oracle Database release 12c CDB to an Oracle Database 18c CDB with Enterprise Manager or Database Upgrade Assistant (DBUA).

Data Dictionary and Dynamic Views

For backward compatibility, DBA views show the same results in a PDB as in a non-CDB: DBA_OBJECTS shows the objects that exist in the PDB from which you run the query. This implies, in turn, that although the PDB and the CDB root have separate data dictionaries, each data dictionary view in a PDB shows results fetched from both of these data dictionaries. The DBA_xxx views in the CDB root shows, even in a populated CDB, only the Oracle-supplied system—as is seen in a freshly created non-CDB.

To support the duties of the CDB administrator, a family of data dictionary views is supported with names such as CDB_xxx. Each DBA_xxx view has a CDB_xxx view counterpart with an extra column, Con_ID, that shows from which container the listed facts originate. Query the CDB_xxx views from the CDB root and from any PDB. The results from a particular CDB_xxx view are the union of the results from the DBA_xxx view counterpart over the CDB root and all currently open PDBs. When a CDB_xxx view is queried from a PDB, it shows only the information that it shows in its DBA_xxx view counterpart. If you connect to the CDB root and query CDB_USERS, you get the list of users, common and local, of each container. If you query DBA_USERS, you get the list of common users. If you connect to a PDB and query CDB_USERS or DBA_USERS, you get the same list of users, common and local, of the PDB.

The same backward-compatibility principle also implies to each of the familiar V$ views. For example:

SQL> select OBJECT_ID, ORACLE_USERNAME, LOCKED_MODE, CON_ID from V$LOCKED_OBJECT;

OBJECT_ID    ORACLE_USERNAME   LOCKED_MODE  CON_ID
----------   ----------------  -----------  -------
     83711   SYS               3            3          <--- PDB1
     83710   DOM               3            4          <--- PDB2

Terminology

There are different types of database administrators.

• In a non-CDB, the DBA is responsible for all administrative tasks at the database level.
• In a CDB, there are different levels of administration:
  • The DBA responsible for administering the CDB instance, the CDB root, and all PDBs
  • The DBAs responsible for administering their respective PDB

The terminology for entities in a CDB and in PDBs is the following:

• Common users, roles, and profiles exist in all containers and have the same name throughout these containers. Local users, roles, and profiles have a unique name for the container (PDB) in which they reside.
• Common privileges are privileges that are "commonly" granted for all containers in the CDB, rather than privileges that are granted locally within a PDB.
• Common objects exist in Oracle-supplied schemas. Local objects are created in PDBs in local schemas.
• CDB resource management works at the CDB level, and PDB resource management works at the PDB level.
• Audit policies can be created in the CDB root and also in each PDB. There is the same concept for encryption master keys and for Database Vault realms and command rules.
• XStream Out is only available at CDB level and XStream In only at PDB level. XStream consists of Oracle Database components and application programming interfaces (APIs) that enable client applications to receive data changes from an Oracle database and send data changes to an Oracle database. XStream Out provides Oracle Database components and APIs that enable you to share data changes made to an Oracle database with other systems. XStream In provides Oracle Database components and APIs that enable you to share data changes made to other systems with an Oracle database.

Impacts

- If the CDB has a unicode database character set of AL32UTF8, the CDB can contain PDBs with different database character sets, because all character sets can be converted to AL32UTF8. Character set of an existing PDB can be changed to any compatible character set using existing database character set migration steps. There is only one single spfile for the CDB. PDB parameters values are stored in a dictionary table.

- Use a database link to access an object in another PDB.

- If you use Oracle Active Dataguard for reporting purposes, then you do not need to replicate the PDBs. Oracle Database release 12c enables you to implement a subset of PDBs in the standby database by using the STANDBYS clause with a list of the PDBs to replicate. If a user executes the ALTER DATABASE SWITCHOVER TO ... VERIFY statement to switch over a standby database with the subset of PDBs to the primary database, a warning error message appears. However, these standby databases can become primary databases. In the following statement, PDB1 will be created on the standby databases stdby1 and stdby2:

SQL> CREATE PLUGGABLE DATABASE pdb1 … STANDBYS=(stdby1,stdby2);

- In Oracle Database Vault, each PDB has its own Database Vault metadata. Database Vault constructs, such as realms, are isolated within a PDB. Oracle Database release 12.2 introduced protection on common objects with common realms and command rules.

- Each PDB has its own master key used to encrypt data in the PDB. The TDE master encryption key must be transported from the source database keystore to the target database keystore when a PDB is moved from one host to another. For column encryption, each PDB maintains its own ENC$, which is not a metadata-linked object.

- A unified audit configuration is visible and enforced across all PDBs. It enables administrators to avoid configuring auditing separately for each container. This provides the ability to not only create audit policies used by all PDBs but also audit policies used exclusively for each PDB. An audit configuration that is not enforced across all PDBs means it applies only within a PDB and is not visible outside it.

- Heat Map and ADO (Automatic Data Optimization) enable automation of Information Lifecycle Management (ILM) actions, automating movement of data to the appropriate storage format through compression and storage tiering. ADO relies on statistics reported and collected by Heat Map, a tracking activity at both the segment level and the block level. Heat Map and ADO are enabled in CDBs since Oracle Database 12c release 2.

- XStream is a programmatic interface to allow a client application access to the changes in the database, known as XStream Outbound Server. XStream Inbound Server allows a client application to feed changes into the database and takes advantage of the apply process available in the database. Oracle GoldenGate is the logical replication, and XStream is licensed via the Oracle GoldenGate (OGG) license. Capturing changes from the database must always be from a CDB root. The XStream outbound can be configured to capture changes from a PDB or the entire CDB. Applying changes via Oracle GoldenGate is done per PDB. An XStream inbound server is configured to apply changes into a specific PDB and performs all of its work within the context of the PDB. Support in XStream and Oracle GoldenGate applies with no specific restrictions.

- Logminer ad hoc query (V$LOGMNR_CONTENTS, DBMS_LOGMNR) supports customer common objects in PDBs just as they support local objects in PDBs.

- In general, all scheduler objects created by the user can be exported or imported into the PDB using data pump. Predefined scheduler objects are not exported, and that means that any changes made to these objects by the user will have to be applied once again when the database is imported into the PDB. A job defined in a PDB runs only if a PDB is open.