This is the written version of a lesson from my SQL Course, in which I introduce students to the Multitenant Architecture.
The MULTITENANT architecture was introduced in version 12c and included for the first time in an express edition in version 18c.
This architecture was developed by Oracle with the main goal of database consolidation, so, the multitenant option is useful when you need to have more than one database, for example, if you want or need to have a separate database for each application you develop or support.
So, what are the main benefits?
Imagine that you have many applications, and you want or need each one of them to connect to its own database, like in the image above. This means that you need to have many databases, and it also means that you will need to have many database instances.
As you remember from a previous lesson, a database instance is composed of the memory structures and background processes needed to manage and support a database.
This means that you would need a server with a lot of resources, because, in this example, it would need 11 times the memory you would need for a single database, and if a single database instance creates about 20 background processes, for the 11 databases you will have more than 200 background processes running on the server.
Also, you would usually need to backup each database separately, and when you need to patch or upgrade databases you would have to do it 11 times as well.
With the Multitenant option, however, you only need one database instance, like in the image above, and that instance can support a big number of databases, so you would need less memory on the server and you would have fewer background processes running.
Also, in a multitenant environment you can backup all of the databases in a single operation, and you patch or upgrade all of them at once as well, so, among the main benefits, we have less resources needed and easier administration.
The Container Database or CDB
A database that has the multitenant option enabled is called a Container Database, or CDB, and one that doesn’t have it enabled is a non-CDB.
Since this was introduced in version 12c, all databases in previous versions are non-CDBs, but because it is actually optional, there can be non-CDBs in newer versions as well.
Each database that is part of a CDB is called a “container”, so the CDB is a database of containers, which includes the “ROOT” and a number of pluggable databases, or PDBs.
The root is where Oracle stores some metadata and common users.
A CDB always includes also a “seed” PDB, which is a template that can be used to create new PDBs.
And finally, a CDB can have zero or more user-created PDBs, which are the actual databases that will store user data and to which our applications will connect.
So, you can imagine a CDB from the logical standpoint, as something like this image:
This CDB has 4 containers: The root, the seed, and 2 user-created PDBs which are “hrpbd” and “salespdb”, and each one of these PDBs appear to a user or application as a “normal” or traditional non-CDB, like those we had in previous versions.
As you can see, we can have local users, which can only work with a single PDB, and common users, which can work with all of the containers in which they have been granted the necessary privileges.
And these are called “PLUGGABLE” databases, because they can be easily unplugged from one CDB and plugged into another CDB without needing to make any modification to the application that uses that database, which is pretty interesting!
And in Oracle 12c release 2 Oracle took it to another level with the introduction of application containers.
An application container is essentially a sub-container that groups several application-specific PDBs, which can then be seen as a single component.
In the example in above image, we could have the same tables in cust_pdb1 and cust_pdb2 but with different data in each PDB, because each PDB supports the application for a different customer, but we could also have some “common objects”, such as tables, whose data is shared between the different PDBs that are plugged into the same application container.
These PDBs are called “application PDBs”.
And as you see in the image, we could still have PDBs that are not plugged into any application container, but into the CDB directly, like hrpdb , so having application containers is optional.
This makes the architecture a little more complex, but we, as developers, don’t usually need to know the fine details of these things, because from the SQL standpoint, this is actually transparent to us.
And that’s it!
This is all you, as a developer, need to know about the multitenant architecture by now.
See you later!