CHAPTER
4
Application Containers and Hints Thereof
This chapter introduces new features of Oracle Database 12c Release 2 related to a completely new feature called Oracle Application Containers. Although container databases (CDBs) aren’t new, Oracle Database 12c Release 2 uses CDBs in a new way with application containers. A database that is scalable provides for easy distribution of resources that are needed to perform the work at hand—new features such as application containers and sharding are central to scalability in Oracle Database 12c Release 2. Agility refers to the flexibility of the database architecture to respond to all sorts of incremental and on-demand changes, and application containers facilitate a new level of agility.
The second part of this chapter revisits an enhancement to another feature introduced in Oracle Database 12c Release 1: cross-container queries. In Oracle Database 12c Release 2, you can add optimizer hints to cross-container queries, including application containers. With the addition of application containers, you can run queries across multiple pluggable databases (PDBs) within the application container, and allowing new hints in those queries makes your application container environment more scalable and agile.
Application Containers
I had difficulty deciding in which chapter to cover application containers, the reason for which will become clear shortly. An application container is a component within a container database that provides an application-specific root container, an application seed, and one or more application-related PDBs (application PDBs). This provides a way to create a single logical container for a given application database. This offers exceptional scalability in that you can now partition off application PDBs into their own application containers, making it easier to manage the entire application.
An application container also offers additional isolation, which I consider more of a security-related feature. When you create an application container, you first create the root of that application container. The root of the application container is a child of the CDB’s root container; you may have several application root containers in a CDB. However, all of the objects that are created within the application root container are only visible to that application root container and the application PDBs attached to that container. They are not visible to the root CDB, nor are they visible to other application containers or individual PDBs. Figure 4-1 provides an example of the relationship between the root CDB, an application container, and the PDBs attached to the root CDB and the application PDBs attached to the application container.
FIGURE 4-1. The root CDB, application containers, and database PDBs
To create an application container from a PDB, you can use one of the following methods:
Clone an existing PDB
Clone an existing non-CDB
Plug in an unplugged PDB
But before we can use one of those methods, the CDB must exist and we must create the application container. I’ll show how to do that in the next few sections.
Creating the Application Container
For purposes of demonstration, we’ll create an application container named HRAPP. The first requirement to create an application container is to connect to the CDB root as an administrative user. We’ll use SYS (connecting as SYSDBA) in this example. Then, we will issue the CREATE PLUGGABLE DATABASE command, along with the AS APPLICATION CONTAINER clause, to create the application container. Because we are logging into the root of the CDB, Oracle will use the CDB seed as the source of the root of the application container that is going to be created. Here are the commands:
NOTE
You need the CREATE PLUGGABLE DATABASE system privilege to use the CREATE PLUGGABLE DATABASE command.
NOTE
Version 13c of Oracle Enterprise Manager (OEM) Cloud Control provides a graphical means of creating, managing, and removing application containers. We won’t cover OEM in this chapter, but Chapter 12 is devoted to OEM new features.
If you are familiar with the CREATE PLUGGABLE DATABASE command (introduced in the previous chapter), you know we could have customized the command a great deal—we are keeping it simple here for the sake of brevity and to focus on the topic at hand.
Once the command has completed, we have a new application root container called HRAPP. Application root containers are much like a CDB within a CDB! We can confirm the status of the application root container by querying the view DBA_PDBS:
Note that the APP_ROOT column is set to YES in the DBA_PDBS view. This indicates that HRAPP is the root of an application container. There are no application containers created yet, so the APP_ROOT_CON_ID column is empty for all PDBs.
When HRAPP is first created, it is not open, so we need to open it. In this example, we change to the new HRAPP container, check its current status, and then start it up:
NOTE
We could just have easily started the container using the ALTER PLUGGABLE DATABASE OPEN command from the root container.
Now that HRAPP is created, the network services that support it have also been created. So, we can also connect to it via SQL*Plus:
Finally, note that you can have many application root containers. However, you cannot create application root containers from any PDB other than the root PDB (CDB$ROOT). In other words, you cannot create an application container from within another application container. That makes sense since you can’t create standard PDBs from within another PDB either—application root containers are much like standard PDBs (although they become CDBs from an application perspective).
NOTE
Currently there is a 2 GB storage limitation on the application root. It is important, then, to control the tablespace space usage of the application root container. This limit does not apply to the application container seed or any PDBs created in the application container.
Creating the Application in the Application Root
We have created the application root container, but this root container is very different from the root of the CDB. Oracle tells you not to create objects in the root container of the CDB. In the case of application containers, Oracle very much does want you to create objects in the application root container of a CDB—but this is because an application root container is really more like a PDB. From an application perspective, the root container is more like a CDB since you can create application PDBs within the application root container. After you’ve looked at Figure 4-1 a few times and tried out these examples, your confusion will disappear!
Keep in mind that the purpose of an application root container is to store the collection of common database objects (and data). So, we need to install those objects somewhere, and that somewhere is the root of the application container.
For the purposes of this continuing example, I will create a schema and some objects in our HRAPP root container that are associated with an application we will call the HR application. We will create a common database user to own the objects and then we will create those objects in that common user’s schema. Do keep in mind that the data model we are creating is very abbreviated and not designed to teach anything about database modeling, database normalization, or how to build a real HR schema or application! Here is the script we will use to set up the application root container:
NOTE
To install an application into an application container, the user installing the application must be a common user with the ALTER PLUGGABLE DATABASE system privilege in the application root container.
So, what have we done here? First, we opened the HRAPP PDB and then we switched to that container. We then created a normal (application) user and also some tables within the application root container. These objects are known as application common objects and will serve as a template that can be used for all application PDBs that we create within this application container (more on application PDBs in a moment). This application is version 1.0, which means you’ll be able to create future versions in the same application root container, such as 1.1, 2.0, and so forth—with the added benefit that not all application users need to be on the same version at the same time. Or a user may never want to upgrade!
Note that we used the ALTER PLUGGABLE DATABASE command along with the BEGIN INSTALL option before we created the HR user or the objects within the user schema. This command instantiates the application (HR in this example) that these objects are associated with. When we issued this command, we gave the install a version number. This is how Oracle provides versioning to the objects in the application container.
After instantiating the application, we proceeded to create the HR user within the HRAPP application and then we created three tables in the HR schema and populated them with test data. Finally, we indicated to Oracle that we are done with the instantiation of the application objects by using the ALTER PLUGGABLE DATABASE command along with the END INSTALL option.
We have created application 1.0 of our database! Quite exciting!
What is the end game for all of this? Why create an application schema in the application root container? It’s to make deployment of copies of this application schema easy to do. Many organizations routinely create and destroy databases for testing purposes. The application container gives you the ability to establish a “gold” copy of a database schema, and deploy it quickly and reliably. Application containers also provide a means of controlling the source schema, enabling you to keep it “pure.”
The material covered in this section makes much more sense in the context of creating application PDBs. So, let’s instantiate the application next!
Creating an Application PDB
Application PDBs reside within the application root container. They are normal PDBs for the most part except that you can synchronize them with the common application objects in the application’s root container. This provides a way for you to easily replicate a specific PDB over and over.
The rules for instantiating an application PDB are pretty simple. You must have the CREATE PLUGGABLE DATABASE system privilege, you must be connected to the root of the application container (use SHOW CON_NAME when in doubt), and the application container must be open.
So, let’s go ahead and create an application PDB. First, we connect to the root of the application container HRAPP, and then we issue a CREATE PLUGGABLE DATABASE command:
This creates the pluggable database RJBHR within the application container HRAPP. We can query the DBA_PDBS view from the CDB root container to see this new container using the same query we ran earlier to check the type and state of each PDB:
In the output, we now see a new PDB called RJBHR. We can see that it’s an application PDB and that its parent container is HRAPP. Now, let’s see if the schema and the schema objects that we added to the root of the application container are contained in the application PDB we just created:
What’s the deal? These application common object things don’t work? They will, but simply creating the PDB isn’t enough to synchronize it with the application root container’s objects. We need to perform one more step to get the application schema objects copied over: synchronize the PDB with the application root. We do so by issuing the ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC command, from within the application PDB we want to synchronize, as shown here:
Now, let’s look at what happens when we issue the previous query again:
How cool is that? The objects associated with our “application” were cloned. You will notice though, if you do some further investigation, that if the tables in the application root container have data in them, that data is synchronized as well along with any constraints, indexes, and so forth on the tables. Now, let’s query one of those tables:
Even the “seed” data we created got moved over. Replication of the data is the default for an object when it is created in an application root. We can choose to replicate the seed data or not replicate it. To change the default behavior, you have two options. First, when you create the object, you can use the new SHARING parameter to control data sharing when you create the object in the application root container. The options are
METADATA Share metadata, copy data—data is unique to each container.
DATA Share metadata, share data—data is stored only in the application root container.
EXTENDED DATA Shares metadata, share data, but each application container can have its own data unique to that instantiation of the application.
NONE The table is not shared.
Here we show the creation of a table and indicate through the use of the SHARING parameter that we do not want to share the table outside of the application root container at all:
Another option is to change the DEFAULT_SHARING database parameter at the PDB level. DEFAULT_SHARING is set to a default value of METADATA in all PDBs. You could use the ALTER SYSTEM command and set the DEFAULT_SHARING parameter to METADATA in the HRAPP application root container if you wanted to have objects not share data by default when they are created. Note that the setting of SHARING=DATA at the object level overrides the setting of the DEFAULT_SHARING parameter at the PDB level, for example.
Installing, Upgrading, and Patching the Application Container
As you saw earlier, to instantiate an application in an application root container, you must first use the ALTER PLUGGABLE DATABASE command with the APPLICATION BEGIN INSTALL option. Then, you finish it using the ALTER PLUGGABLE DATABASE command with the APPLICATION END INSTALL option. This probably gave you your first insight into the fact that Oracle helps you use version control to manage the application objects within the application root container.
Of course, you will need to make changes to the application root container objects from time to time, and you will want those changes to be propagated to the application PDBs that are created based on the application root container. Oracle enables you to do this in either of two ways: an upgrade or a patch.
You upgrade the application root container if you want to make major changes to your application schema (especially if you want to do things such as drop objects). You indicate that you are upgrading the application container by issuing the ALTER PLUGGABLE DATABASE BEGIN UPGRADE command, followed by the commands to create, modify, or remove the application objects that are associated with that upgrade. You then issue the ALTER PLUGGABLE DATABASE END UPGRADE command to complete the upgrade. Finally, you connect to each PDB and issue the ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC command to complete the propagation of the changes to the PDBs.
NOTE
A manual method is available to perform upgrades that will not cause changes to be propagated. You can find more details in the Oracle Database Administrator’s Guide.
Patching of an application container involves minor changes to the container. For example, when patching a container, you can create a new table, but you cannot drop a table. To drop a table, you must upgrade the container. Patching a container works in much the same way as upgrading. You start the patch with the ALTER PLUGGABLE DATABASE BEGIN PATCH statement, make your changes, and then complete the patching work with the ALTER PLUGGABLE DATABASE END PATCH statement.
The main difference between an upgrade and a patch is the amount of work Oracle has to do in the background when processing your changes. When upgrading, Oracle creates a clone of the application root (call it version 1.0), which will be in READ ONLY mode. The clone PDB appears in the DBA_PDBS view and is given a default name by the database when it’s created. The creation of the clone takes additional time, so the ALTER PLUGGABLE DATABASE UPGRADE command takes longer to complete than the ALTER PLUGGABLE DATABASE PATCH command.
During an upgrade, application PDBs are ported over to the clone when the END UPGRADE command is issued, and the clone is open for activity. Thus, the application PDBs remain associated with the version 1.0 clone. During the move of the application PDBs to the version 1.0 clone, there are some momentary accessibility issues. These considerations are documented in the Oracle Database Administrator’s Guide.
When the application PDBs are synchronized with the root CDB, they are ported over from the version 1.0 clone to the upgraded version 2.0 application root container. At that time, they will have access to the new application schema changes that were made. The clone will remain available in the application root container, and new PDBs can be attached to either the version 1.0 clone or the version 2.0 clone.
Creating the Application Seed
Just like the root container of a CDB has a seed database, application root containers can optionally have a seed database. A seed database can speed up the creation of any application PDBs that you might choose to create. The application seed is created much like the application PDB.
The application seed starts out as a copy of the application root seed. To create the application root seed, connect to the application root container (not the root of the CDB) and issue a CREATE PLUGGABLE DATABASE command, using the AS SEED clause. Once the seed container is created, we need to synchronize it with the application root container, just as we did earlier with a new application PDB. Then, we close the seed container and open it in READ ONLY mode. Here are the commands that you would use to create the application seed:
Note that we didn’t give this pluggable database a name. Oracle will always assign a default name to the application seed containers. The naming convention Oracle uses is to prefix the application seed with the name of the PDB, followed by a $ sign, and then end it with the word SEED. So, for the HRAPP application container, the seed database is HRAPP$SEED.
We can return to the root CDB and issue our query against DBA_PDBS again to see the new seed PDB that we created:
Note that we have added HRAPP$SEED and that the columns APPLICATION_PDB and APPLICATION_SEED are both marked YES now. This indicates that this PDB is the seed container within an application container. Also, note the APPLICATION_ROOT_CON_ID column is set to 4. This is the PDB_ID for the HRAPP container, which is the root container of the application container. Of course, this value will vary for different databases.
Now that the seed container is created, it will be constantly updated when changes are made to the root of the application container. That way, you don’t need to resync a newly created PDB to get it synchronized with the application root container—the seed PDB is already synced up!
I hope this overview of application containers has given you enough information to pique your interest in learning more about this new feature in Oracle Database 12c Release 2. There are many more features and much more functionality to explore beyond the scope of this introduction, but I’ve given you a sufficient foundation to get started and discover what this powerful new feature can do for you and your application development teams!
Using Hints in the Containers Query
In Oracle Database 12c Release 1, you could run queries against an aggregate of all tables with the same name owned by a CDB common user. One nice feature of Oracle Multitenant is that you can aggregate data across PDBs within a given CDB. For example, if each of your customers requires separate databases but you need to generate reports across all of your customers, you could create a PDB for each customer and then issue SQL queries across the entire set of PDBs. This is done using the FROM CONTAINERS clause in a SELECT statement.
Because of the expanded use cases that arose from the application containers feature in Oracle Database 12c Release 2, there is a new hint called CONTAINERS that you can use when running a query that has a FROM CONTAINERS clause. With the CONTAINERS hint specifying the DEFAULT_PDB_HINT argument, you can run a SELECT statement that will cross all of the containers of the database and use the embedded hint within each PDB. The following example indicates that the optimizer should not use parallel query as it processes this query across all of the containers in the CDB for the EMPLOYEES table:
The context is important with that query. The syntax of the query is identical whether you’re running it as a common user in a CDB or as a common user in an application container. In a CDB, the EMPLOYEES table must exist in the CDB root and all open PDBs. If you’re in an application container, the query has to be run from the application root container and the table must exist in the application root container and all PDBs in the application container.
Summary
This chapter covered one very extensive new feature and one extension to an existing feature, and those features are related. Using the Oracle Application Containers feature in Oracle Database 12c Release 2, you can create an application container that packages an application’s tables and data into a PDB that is treated like a CDB for the purposes of sharing (cloning) those tables and data to different groups of users with the option of maintaining different versions of the application for those groups of users with Oracle’s built-in version control. In an application container, you also have the option to keep the data only in the application root container, only in each application PDB, or both!
Because of the extended container database functionality provided by Oracle Application Containers in Oracle Database 12c Release 2, the options you can use when running a CONTAINERS query have also been extended. Not only can the CONTAINERS clause apply to the application root container and all of its PDBs, you can also use a CONTAINERS hint in a query using the CONTAINERS clause to specify a hint that is used for the recursive queries run against each PDB in the CDB or application root container.