Oracle Database Material

Multi-tenant container database architecture in Oracle Database 12cr1

The Oracle Database 12c Release1 introduced the Oracle Multi-tenant architecture, which allowed consolidation of multiple pluggable databases (PDBs) together in a multi-tenant container database (CDB). Various PDBs store data in objects independently of other PDBs and are maintained separately. Because PDBs isolate data and operations, each PDB is a self-contained, fully functional traditional Oracle Database from an application point of view. However, from an operational point of view, a CDB is a database having a single set of background processes and a shared memory area (SGA); these are shared by all the PDBs in the CDB. This architecture eliminates replication overheads, making the most efficient use of available resources. Any existing pre-Oracle Database 12c database (non-CDB) can be simply adopted as a PDB without having to make any changes to the database or application. Consequently, by consolidating multiple non-CDBs as PDBs, administrators can manage many databases as one.

Containers in a CDB (Pre 12.2)

A container is a collection of schemas, objects, and related structures in a multitenant container database (CDB). Every CDB has the following containers:

• Root

Every CDB has one and only one CDB root container, also called the “CDB root” or simply “root”. The root container is named CDB$ROOT.  While the root stores Oracle-supplied metadata and common users, it does not store user data. . All PDBs belong to the root.

• Seed PDB

Every CDB has one and only one seed PDB, named PDB$SEED, which is a system-supplied template for the creation of PDBs that support applications. Since the Seed PDB itself is not intended to support an application, objects cannot be added to or modified in it.

• User-created PDBs

Every CDB can have zero or more user-created PDBs. A user-created PDB, or simply “PDB”, is a user-created entity that contains the data and code specific to a particular application. Each PDB’s data is isolated in its own container. For example, sales and human resources applications can have their own dedicated PDBs.

The following figure shows a CDB CDB1 with four containers:

• The root (CDB$ROOT)
• The seed (PDB$SEED)
• User-created PDB PDB1 having its own dedicated application APP1
• User-created PDB PDB2 having its own dedicated application SALES

Limitations in Oracle 12cR1

Although Oracle 12cR1 Multi-tenant allowed database administrators to manage many databases as one by consolidating them as PDBs in a multi-tenant CDB, the same did not hold true for application administrators. In Oracle 12cR1, common objects exist in Oracle-supplied schemas in the CDB root only. If multiple PDBs shared an application, there was no provision for a single master definition of the application on top of PDBs. For example, let us say there are four user-created PDBs (PDB_North, PDB_South, PDB_East, and PDB_West) representing databases of regional offices of the same organization, each using the same application SALES.

In such a case, a full copy of the database components of the application must be stored individually in each PDB. Also, an application upgrade script must be run on each tenant PDB individually. Hence, the need to extend the benefits of managing many as one to the application administrator.

Application containers

Oracle Database 12.2 introduces the concept of Multi-tenant Application Containers that enable many PDBs to share application objects such as code, metadata, and data. As a result, application administrators can now efficiently manage many application PDBs as one in a single Application Container while securely isolating their individual customer-specific data. This capability is ideally suited for SaaS, franchise, and other applications that are typically shared across different constituents, but require secure isolation of each constituent’s data.

An application container is a special type of PDB that stores data for one or more user-created applications and shares application metadata and common data among various application PDBs contained within it.  An application container has the following containers:

• Application root: Every application container has exactly one application-specific root container which is a hybrid between the CDB root and a PDB. Like a PDB, it belongs to the CDB root and shares descriptions of Oracle-supplied common objects in the CDB root. At the same time, it  acts like a mini CDB root container that houses a master application definition containing common objects for one or more applications that are shared by application PDBs belonging to it. Such objects are not visible to the CDB root, other application roots, or PDBs that do not belong to the application root.
• Application PDBs: Every application container can have zero or more user-created application PDBs plugged into the application root. The master application definition stored in the application root is visible to all application PDBs within the application container. Local objects can be created in application PDBs outside of the application definition if needed. When changes are made to the application in the application root, individual tenant PDBs can synchronize with the new version on their own schedule.
• An application seed PDB: Every application container can have an optional application PDB tied to the application root that serves as a user-created template to create new application PDBs rapidly. Once an application definition has been installed in the Application Root, the Application Seed can be created, and thereafter new Application PDBs can be provisioned simply by cloning the Application Seed.

Containers in a CDB with Application Container(s) (12.2.0.1)

With the introduction of Application Containers, multi-tenant architecture has been revised as follows:

• There is only one CDB root (CDB$Root), and one CDB seed PDB (PDB$Seed) in a CDB.
• A CDB can have zero or more application containers.
• For every application container, there is always one application root PDB that serves as a repository for a master definition of an application back end.
• A CDB can have zero or more user created PDBs. A user created PDB can be:
  • A Standard or Regular PDB – The same as in Oracle 12cR1 PDB. It belongs to the CDB root container and can support one or more independent applications. It does not belong to an application container and hence cannot use common objects belonging to other applications.
  • An Application PDB – Belongs to exactly one application container, wherein it is federated with other application PDBs sharing the same application(s) .
  • An application seed PDB: An optional preconfigured user-created PDB template belonging to exactly one application container.

The following figure shows a CDB CDB1 with an Application Container. In this variation, CDB1 has following containers:

• The CDB root (CDB$ROOT)
• The CDB seed (PDB$SEED)
• A user-created regular PDB PDB1 supporting its own dedicated application APP1. It does not belong to an application container.
• An application container for the SALESapplication. It contains:
  • An Application Root PDB (APP_ROOT): It stores common objects for the SALES application; e.g., the structure of the table used to store customer information
  • Four application PDBs (PDB_North, PDB_South, PDB_East, PDB_West): Databases supporting the SALESapplication for various regional offices of an organization. While sharing the structure of the customers table stored in the Application Root, they can store region specific customer data in the table.
  • An Application Seed PDB (APP_ROOT$SEED): A user-created PDB template used to create new application PDBs rapidly.

In the above example, an Application Container has been used for a company that builds and maintains a sales application that is used by its various regional offices. The structure of the information about its customers is consistent across all regions and hence is stored in the Application Root. Since each office has different customers and therefore different customer data, there is a separate application PDB for each regional office.  A metadata-linked table is used to store customer information so that structure of the table is the same in each application PDB, but the customer data is different.

Application Containers: Other Features

• Application Master: With the introduction of application containers, a master application definition can be installed just once in the Application Root in an Application Container. It is visible to all application PDBs within the application container.
• Application Maintenance: Since there is only one master copy of the application, maintenance operations (such as applying an application patch) need to be performed in the Application Root once only and a new master definition will propagate to individual tenant PDBs after they have synchronized with the new version. Since individual Application PDBs can choose to synchronize on their own schedule, the impact of application upgrade is restricted merely to the tenants being upgraded and a business-wide upgrade can be performed in a phased manner, leading to a much more agile application development process .
• Rapid Provisioning: An application PDB can be provisioned instantly by several methods:
  • By cloning a CDB Seed
  • By cloning an Application seed PDB defined in the application container
  • By cloning an Application PDB defined in the application container
  • By unplugging/plugging, or cloning local and remote regular PDBs to join an application container
• Application Root Replica: A particular application can be installed in several application containers by creating copies of the master application root, called application root replicas, in the same or different (even remote) CDBs. Using proxy-PDB, changes in the master application root can be propagated to its replicas in different CDBs.
• Reporting: Queries can be executed across the application PDBs
  • Within a CDB
  • Across CDBs.
• Application Versions: Different applications tagged with distinct application names and application versions can be installed in an application container.

Use Cases for Application Containers

• Applications with a high density of customers: Similar types of customers using the same application can be are grouped within an application container.
• Logical Data Warehouse: Dimension tables can be created in the application root while the fact table is logically partitioned across multiple application PDBs based on some partitioning key (e.g. region). As a result, ETLs can be efficiently performed for every region without any impact on one another. Moreover, a query executed across the application PDBs can effectively prune the partitions, and therefore the application PDBs, based on the key that is passed to it.

Application Container Views

The following views give information about applications. They can be queried from the application root container and in some cases the application container PDBs.

• DBA_APPLICATIONS – Provides information about the applications in the current application container.
• DBA_APP_PDB_STATUS – Provides information about applications in all the application PDBs in the current application container. This view can be used to show which version of an application has been synced to which application PDBs.
• DBA_APP_VERSIONS – Displays information about all application versions installed in an application container.
• DBA_APP_ERRORS – Describes all application error messages in the application container.
• DBA_APP_PATCHES – Describes all the application patches in the Application Container.
• DBA_APP_STATEMENTS – Describes all statements from all the applications in the Application Container.

Source: https://www.toadworld.com/platforms/oracle/b/weblog/archive/2017/06/26/oracle-multi-tenant-application-containers-part-i