CHAPTER
3
Single-Tenant, Multitenant, and Application Containers
In the previous chapter we detailed how to create a container database, an indispensable foundation of the multitenant architecture. However, at this point you may be wondering whether multitenant is for you. Perhaps you have heard the term “multitenant option,” which suggests that additional licensing is required, or you exclude yourself because you are running Oracle Database Standard Edition. On the other hand, you are also aware that the old architecture, known as non-CDB (non–container database), is now deprecated. So what should you do?
These are important questions and concerns that we will consider before going on to describe the multitenant features in detail. Of course, to describe what is available with the different editions and options, we will make mention of specific features—but don’t worry, because we will return to address the features thoroughly in later chapters.
Multitenant Architecture Is Not an Option
Let’s make it clear from the get-go: the multitenant architecture is available in 12c for all editions, with no additional licensing option required. The main characteristic of multitenant is the separation of dictionaries: system metadata and system data are in CDB$ROOT, whereas user data and metadata are in a pluggable database (PDB). This new architecture is defined at the database creation stage with ENABLE PLUGGABLE DATABASE. It is available at no additional cost for the Enterprise Edition or Standard Edition. At the time of writing, there is no XE edition for 12c, but we expect that when it is released, it will be multitenant as well.
You need to be aware of one limitation regarding multitenant: You cannot create more than one PDB per CDB unless you have purchased a multitenant option license. This means that without purchasing the option, your multitenant database has at maximum one tenant—it is a single-tenant configuration. Although the multitenant architecture was obviously introduced with the multitenant option in view (hence its name), it is also available and usable without this.
If you don’t intend to purchase the multitenant option, or if you are using Standard Edition, you may think that you don’t need to delve into this new architecture and acquire new skills for its administration. But you need to rethink this approach. In this chapter we will explain the advantages of using a single-tenant database, but before we get to this, there is one fundamental reason to move ahead with it.
Non-CDB Deprecation
Chapter 1 introduced multitenant as a major change in the Oracle Database. There are likely scores of places in the Oracle Database code where the developers had to introduce additional operations for the multitenant context, but the existing code for non-CDB is still there. Obviously, as the software evolves in the future, new features will be implemented on the new architecture as a priority. The old architecture is still supported, so you can create non-CDB databases in 12c and obtain support and fixes, but if you want to benefit from all the latest features (and innovation of development focus), then it is recommended that you move to the multitenant architecture. This is exactly what deprecation means, as it relates to the non-CDB architecture, and the “Oracle Database Upgrade Guide” is clear that “deprecated features are features that are no longer being enhanced but are still supported for the full life of the release.”
Is it bad to use deprecated features? Absolutely not! But it’s a bad idea to completely ignore new features that are introduced to replace the deprecated ones. For example, when you start work on a brand-new project, the usual recommendation is to avoid using deprecated features, because you want to benefit from all the latest features available, and because deprecated features may become desupported in the foreseeable future. However, if you are upgrading a current application that will likely exist for only a few more years, you probably just want to keep it as it is, without major changes.
For multitenant, the same principle holds, and you probably won’t move all your existing production databases to multitenant architecture when upgrading to 12c; instead, you’ll retain some of them as non-CDB. Still, it is a good idea that you begin to learn about and create CDB databases, perhaps for a test environment to begin with, so that you build your familiarity with multitenant. With this approach, you have time to learn and adapt your scripts to this new architecture, not because you need it immediately, but because it’s sure to be in your future.
Our message about the non-CDB deprecation is this: Don’t worry; the non-CDB that you know will still be around for years, and you can stay with it as long as you are not comfortable with multitenant. It is still supported in 12c and will probably continue to be for several future releases. But that should not prevent you from learning and trying out multitenant on noncritical databases.
Noncompatible Features
Another reason to keep the non-CDB architecture for your database is to make use of one of the few features that is not yet supported in multitenant. There are two possible reasons for such a scenario:
The feature/functionality itself was already deprecated when multitenant arrived, so it has not been enhanced to work in a CDB. This is the case, for example, with Oracle Streams. Oracle has deprecated this and recommends using an additional alternative product, Golden Gate. Oracle Streams is still supported in 12c, but only with the non-CDB architecture. Note that alternatives will be covered in Chapter 13.
Some new features have been developed independently of multitenant and may not be immediately available in CDB. That was the case with Information Lifecycle Management features (Heat Map and Automatic Data optimization), which arrived in 12.1, but only for non-CDB. This limitation has now been removed with the release of 12.2. Another example is sharding, a new feature in 12.2, which is not compatible with multitenant at the time of writing. Even features that are available in multitenant may be available only at the CDB level. This is the case with Real Application Testing in 12.1, for example, which cannot be used at the PDB level, although this limitation has been overcome with the release of 12.2.
NOTE
At the time of writing, features such as Change Notification, Continuous Query Notification, and Client Side Result Cache are not yet available for a CDB in release 12.2.
Single-Tenant in Standard Edition
In the Standard Edition, you don’t have access to the multitenant option that allows for more than one user PDB; this is an inherent limitation of this edition. Furthermore, in the Standard Edition, features that are not available are disabled at installation time. For example, the following shows a container database with one pluggable database, PDB, in addition to the seed:
Creating more pluggable databases is explicitly disallowed:
The maximum number of PDBs in a Standard Edition CDB is two: one PDB$SEED (that you are not allowed to change except for modifying the undo tablespace) and one user PDB.
Data Movement
Given the limited features available in the Standard Edition, you may ask if it’s better to have a single-tenant CDB rather than a non-CDB. When you create a CDB, you immediately see the overhead: three containers for only one database, which means more datafiles to store the CDB$ROOT and the PDB$SEED. Multitenant is designed for consolidation, but here we see the opposite, because each database uses more space. But you must remember that multitenant is also beneficial for agility in data movement, and this is a great feature even for single-tenant.
There are three ways in which to transport data. The first is a very flexible, but very slow, method: logical transport through Data Pump. Data is extracted by Data Manipulation Language (DML), target tables are created by Data Definition Language (DDL), rows are inserted by DML, and then indexes are rebuilt. When you want something faster than this, you can use transportable tablespaces, where data is shipped physically with the datafiles and only metadata is created logically. This is generally quick, except when you have thousands of tables, as many enterprise resource planners (ERPs) do, and it still takes considerable time to create those tables, even though most are empty. Note, however, that the transportable tablespaces’ import functionality is not available in Standard Edition.
In multitenant architecture, the user metadata is stored separately from the system metadata. Each PDB has its own SYSTEM tablespace that contains this user metadata only, meaning that it can be transported physically. This is a key feature that PDBs provide: the ability to transport by unplug/plug. This mechanism is even superior to transportable tablespaces, which is why Oracle product manager Bryn Llewellyn calls it the “third generation of Data Pump” in his Oracle multitenant white paper. The really good news is that plug/unplug operations are allowed in the Standard Edition and in remote cloning, so you can clone a PDB into a new CDB with a simple command.
Thanks to the agility of PDBs, the small overhead of the CDB structure pales against the ease and efficiency of database movement and cloning operations across servers and versions. This more than makes up for the missing transportable tablespaces functionality in the Standard Edition.
Security
Multitenant has been developed to cater to large numbers of PDBs, such as in cloud environments, and for this reason, many new security and isolation features have been introduced. We will cover those features in Chapter 6, but some of them also are very interesting from the single-tenant perspective, and perhaps the best example is the ALTER SYSTEM lockdown. In a developer database, you may be tempted to give ALTER SYSTEM privileges to the developers in case they want to test new settings, kill their own sessions, and the like. But this privilege is definitely too broad and powerful, as with ALTER SYSTEM you can basically do whatever you like on the system. With the new PDB lockdown profiles you can, alternatively, grant ALTER SYSTEM and enable or disable exactly those operations and access to parameters you deem appropriate.
Lockdown profiles and OS credentials are also very interesting for those applications in which the application owner must be granted powerful privileges. In multitenant, you can give the CREATE DIRECTORY privilege and constrain the absolute path where the PDB users (even the DBA) can create a directory. Multitenant architecture brings a separation of roles between root and the PDB, and you will appreciate this even in the Standard Edition.
Consolidation with Standard Edition 2
Because you can have only one PDB per CDB, you will probably have a number of CDBs on a server. Even among different CDBs, each PDB must have a unique name because, as you will see in Chapter 5, its name will be a service registered to the listener, and you may not want one listener per CDB in this context. Take care when naming CDBs, because you may be tempted to differentiate them with a trailing number, but it is recommended that you ensure that the ORACLE_SID remains unique for each server even when ignoring trailing numerics. Figure 3-1 shows the warning displayed in the Database Configuration Assistant (DBCA).
FIGURE 3-1. Warning when only suffix numbers differ in ORACLE_SID
This advice probably results from Real Application Clusters (RAC) considerations, where the database name includes an instance number suffix. And remember that in Standard Edition 2, you can have high availability, because RAC is still available on a two-socket total cluster.
Because several CDBs are on the same server, you don’t want one instance to use all the CPU available. Standard Edition 2 limits each database to run, at maximum, 16 user processes on CPU, but you can lower this by decreasing the CPU_COUNT parameter. This instance caging was not available in the Standard Edition before 12.1.0.2, but it is now. It’s a side effect of the new SE2 limitation that we can turn to our advantage to ensure that critical databases have enough resources.
Another consideration with several single-tenant CDBs is the overhead of having one PDB$SEED for each CDB. Because PDB$SEED is needed only to create new PDBs, you might surmise that it is not needed after you have created your PDB. Although you may be tempted to drop it, you must remember that dropping this is not a supported operation. However, there is something you can do to avoid to backing up PDB$SEED every day, and that is to configure BACKUP OPTIMIZATION ON, so that it will be skipped as any read-only tablespace. Just be careful that you don’t have an external expiration policy set that is shorter than the RMAN one if you do this.
We hope that we have convinced you that multitenant is also important in the future of Standard Edition. And even if it seems like a paradox at the outset, you can also consider consolidation with Standard Edition 2 alongside single-tenant agility.
Single-Tenant in Enterprise Edition
If you are using the Enterprise Edition without the multitenant option, you can, and should, run single-tenant container databases. If you haven’t read the preceding section about the Standard Edition, you should do so now, because all that has been said there is relevant for Enterprise Edition, and we will only detail here what is possible specifically in Enterprise Edition.
As a sample, here is an operation that can be done in multitenant that you cannot do in a non-CDB:
In non-CDB, or in a CDB at root level, when you close the database, you have to shut down the instance. Here, the statements have been run within a PDB and the shutdown closes only the PDB, but the instance, which is common, is still up. Then it is possible to open the PDB again. You can open and close a PDB as many times as you want without restarting the instance. This may sound inconsequential, but when you think about operations that require mount mode and a closed database, you soon realize its value.
Flashback PDB
The flashback database feature in the Enterprise Edition can revert the state of the database to a previous point in time; this was available only at the CDB level in 12.1. But with the release of 12.2, you can flashback a PDB—this will be covered in Chapter 8.
For now, let’s consider a test database in which the developers perform continuous integration tests. They often need to revert to the initial data before each run, but a restore or Data Pump import can take a long time to complete. The quickest way to resume the same set of data is to create a guaranteed restore point for the initial state and execute flashback database between each run. This is a fast operation except when you have to restart the instance. But in multitenant, as outlined in the previous section, this is not required, and you can now close, flashback, and open the database in mere seconds, which makes the operation possible to run hundreds or thousands of times.
Maximum Number of PDBs
With the Enterprise Edition, you must be careful when you create a PDB, because creating more than one PDB will activate the usage of the multitenant option. If you haven’t licensed this option, you must prevent this from happening. There are no easy ways to achieve this in 12.1, but 12.2 introduced the “max_pdbs” parameter which, when set on CDB$ROOT, is the maximum number of user PDBs allowed. It defaults to 4098 in Enterprise Edition but should be set to 1 when the option has not been purchased.
In the Enterprise Edition you must monitor the usage of features to be sure that only licensed options are used. The feature name of CDB is “Oracle Multitenant” (or “Oracle Pluggable Databases” in 12.1.0.2 because of Bug 20718081).
Figure 3-2 shows the Enterprise Manager Database Express interface, which indicates that multitenant is used. This means that the database is a CDB. It does not provide further indication of the usage of the option beyond this, because a single-tenant is still counted as a multitenant database.
FIGURE 3-2. Multitenant feature usage from the Enterprise Manager Express
More detail is available from the DBA_FEATURE_USAGE_STATISTICS view, where the AUX_COUNT column shows the total number of user PDBs (not including PDB$SEED).
This example comes from a CDB in which two PDBs have been created. Basically, AUX_COUNT is calculated as the number of PDBs that have CON_ID > 2, so that PDB_SEED is excluded. If AUX_COUNT is 1, you don’t need the option. In the preceding example, you must either license the option or drop (move it to a new CDB) one PDB.
What should you do if you have created more than one PDB by mistake? Don’t worry, because AUX_COUNT is only the latest value with no history for past values. You can just move the additional PDB to another CDB that you create for it, and then drop the additional PDB to restore the configuration to single-tenant.
The next run of the feature usage sampling will bring back the AUX_COUNT = 1, and you can even run the sampling manually if you want:
At this point, we have listed some interesting features that multitenant architecture brings to single-tenant. Nonetheless, the full advantage of multitenant becomes clear only when you consolidate several PDBs in a container database.
Using the Multitenant Option
When you use the multitenant option, you can create hundreds, or even thousands, of PDBs. You can do this in a test CDB to provide a database to each developer, or in a cloud service to provide on-demand databases with Database as a Service (DBaaS). The additional cost of the option can be balanced by the benefit of consolidation, because it enables you to share expensive compute resources at a high level (disk, memory, and background processes). The multitenant option also brings the agility of lightweight PDBs within a consolidated CDB, and you can administer (backup, upgrade, and so on) as one.
Of course, you will likely not put all your databases into the same CDB, for a number of reasons. First, a CDB is a specific version, so when a new patch set is released, you will probably create a new CDB running in that new version, and then move your PDBs to it. This is the first reason to have several CDBs, and you can think of multiple PDBs in the same way as having different Oracle Homes. A second reason is that you don’t want to mix environments (for example, production CDBs will not hold test PDBs). Third, you may also have different availability requirements: one CDB protected in Data Guard and/or in RAC. Changing the availability features for one PDB is as easy as moving it to the appropriate CDB. What is clear is that you cannot consolidate everything and will probably have multiple CDBs to manage.
On the other hand, this does not mean that your CDBs will have only a few PDBs. First, you will probably create different PDBs for the schemas that were consolidated into the same database, aware that multitenant offers better isolation than schema consolidation. Furthermore, the amazing cloning capabilities covered in Chapter 9 will cause you to think differently about managing development databases. You can offer many more environments for development, without increasing the complexity of the CDBs you manage.
Of course, multitenant is new, and our recommendation is to learn it slowly. Take your time, and do not attempt to manage hundreds of PDBs immediately.
Application Containers
You may have a case where your CDB will contain multiple PDBs that run the same application—for example, you may provide your application as a service to multiple customers. In this scenario, you can provision one PDB for each customer, and each will have its own data, but with the same data model. You might immediately think about what Oracle did with the metadata and object links, where the common metadata and objects are stored once in the root only, and ask whether the same can be done for your application. Well, the good news is that in 12.2 with the multitenant option, you can do that with application containers. You create one PDB that will behave as the root for your application, and then create another PDB for each “application tenant” that links to that application root.
We will not go into the details of application containers here, because probably very few readers of this book are SaaS providers (Software as a Service). However, a quick example will demonstrate this feature and serve as an occasion to strengthen what we said in Chapter 1 about metadata and object links. Note that with the introduction of application containers in 12.2, “object links” are now called “data links” and “common data” is called “extended data.”
First, we create the application root, which is itself a PDB, with the AS APPLICATION CONTAINER clause:
Then we connect to it and declare that we are starting installation of the application:
Next we can create the required tablespaces and users:
Now that we have the application owner, we can create the application schemas. In this example, we will create one common metadata table (same structure but different data for each application tenant) and a common data table (same data for all tenants), to show the new syntax.
When this is done, we declare the end of application installation:
You can see that, in this example, we have provided an application name and version. This is for the initial install, but similar syntax is available to manage patches and upgrades during the application lifecycle.
Now we create a PDB for each application tenant; we must be connected to the application root for this:
For the moment, the PDB belongs to the application root, but it is completely empty. The linkage to the application root common metadata and data is achieved by syncing this, as follows:
At this point everything is now ready; our users and tables are there, as you can see:
Finally, let’s connect back to CDB$ROOT and check the information about our containers:
The application containers are identified by and have the CON_ID of their application root. Now our application will have its lifecycle. As we did begin install we can begin upgrade and begin patch.
Application containers can go even further: you can automatically partition your application into several PDBs thanks to the container map that we will cover in Chapter 12.
Consolidation with Multitenant Option
The multitenant option brings two new levels to database consolidation on a server. PDBs can belong to an application root, which belongs to the CDB. Figure 3-3 depicts some example use cases. Without the multitenant option, only single-tenant is possible; thus PDB1 and PDB2 in the example stand alone in their CDB01 and CDB02 container databases. With the multitenant option, you can have several PDBs per CDB, as illustrated by PDB3 to PDB5. Finally, you have the possibility of managing common metadata and data in one place for PDBs that belong to the same application (APP1 to APP4).
FIGURE 3-3. Different levels of consolidation on a server
In addition, of course, the schema level provides logical separation in each PDB, and tablespaces provide physical separation.
Summary
This chapter ends Part I, which introduced multitenant and its constituent concepts and components. We explained why Oracle Corporation developed this new architecture. As a database professional, you appreciate the Oracle Database because of its reliability, and a major architecture change can raise concerns that this would be compromised. However, as we outlined in Chapter 1, multitenant is not so much a revolution as a logical extension to the transportable tablespaces functionality that has been in place, and working very well, for a number of years. We endeavored to explain that the metadata and object links (data links) are new features and simply provide internal flags to indicate that the code has to switch to the root container to obtain information.
In Chapter 2, we detailed the different ways of creating a CDB, which is new in part, but shares much in common with the approach used for databases and instances you have known for years. Finally, after reading Chapter 3, we hope that you now realize and appreciate that multitenant is not just for big shops or cloud providers. It is clear that multitenant is the wave of the future for the Oracle Database, and although it brings many changes, it includes a raft of benefits, even for Standard Edition users.
We have named a few features, such as fast provisioning, availability, and cloning, and we hope that this has awakened your curiosity. Let’s turn our attention next to daily practicalities and a consideration of database administration in the context of multitenant.
