artemegorov - stock.adobe.com
There are good reasons to move your company's databases to the Oracle Multitenant architecture. For example, doing so can reduce database administration workloads and enable more databases to be deployed on the same server. With that in mind, in a series of articles, let's look at how to manage an Oracle Multitenant environment.
Here, I'll go over how to create a multi-tenant Oracle container database (CDB) and the pluggable databases (PDBs) that run inside it as part of the optional architecture, which was introduced with Oracle Database 12c in 2013 and is now also supported in the 18c and 19c versions of the database management system. A container database can host multiple PDBs, hence the Oracle Multitenant name.
I'm using the 18.3 release of Oracle Database 18c in an Oracle Linux 7.4 virtual machine running on the VirtualBox hypervisor to do all the administrative work presented in this series. I've already set up the database server and installed the Oracle software, so I'm ready to get started.
The first step is the container database. If you're an Oracle database administrator (DBA), you should be familiar with the Database Configuration Assistant; for those who aren't already in the know, DBCA is Oracle's Java-based utility for creating and configuring databases. After launching it, I chose the Create a database option and then on the second screen, the Typical configuration one.
Since this is just a testbed installation, I used the default values provided by DBCA. Also, by default, the Create as Container database option was selected for me. If I wanted a conventional database that didn't implement the Multitenant architecture, I would have unchecked this box. In addition to leaving it checked, I entered a name for my first pluggable database as shown in Figure 1.
A simple process to set up an Oracle CDB
That's all there is to it. After I clicked the Next button and then Finish, DBCA began working to create my container database. Once that's done, I needed to verify that it was a multi-tenant database and that the first pluggable database was also created. To do so, I issued the following query in Oracle's SQL*Plus command-line interface -- the results showed that the database is indeed a CDB with multiple containers:
SQL> select con_id,name,pdb_count,member_cdb from v$containers;
CON_ID NAME PDB_COUNT MEM
------- ---------------------------------- -------------
1 CDB$ROOT 1 NO
2 PDB$SEED 0 NO
3 FIRSTPDB 0 NO
The root container always has a container ID of 1; it stores system metadata and information on "common users" who can access and manage all of the containers in a CDB. The second container is a seed PDB that contains a pluggable database template. And the initial PDB is listed with a container ID of 3. Reflecting that, the root container has a PDB count of 1; the seed PDB isn't part of the count. By comparison, if this wasn't a multi-tenant database, the query would return a single row with matching container and database names.
As is, my organization can use the container database under Oracle's standard single-tenant database license. If I create another PDB in it, we'll need to license the extra-cost Oracle Multitenant option for on-premises versions of Oracle Database Enterprise Edition. With that, I can configure up to 252 PDBs in a regular database server and 4,096 in one that runs on an Oracle engineered system.
Adding pluggable databases to a CDB
In the Oracle Multitenant architecture, the CDB is just a shell that doesn't contain any real data, tables or other database objects for applications to use. Before we can store data, we need to create PDBs, which look like separate databases to applications.
An easy way to understand this architecture is to think of an apartment building. The building is the container, and each apartment houses a different tenant. You fill your apartment with your belongings and don't want other tenants to be able to use them without permission, so there's a lock on your door. Similarly, Oracle Multitenant doesn't enable users of one pluggable database to access a different PDB unless a DBA has set up database links to connect them, as in conventional Oracle databases.
The easiest way to create a new pluggable database inside a container database is to use DBCA again. On the initial screen, I selected the option to manage pluggable databases, as shown in Figure 2.
The next screen includes options to create, delete, unplug or configure a PDB. After selecting the Create option, I have the choice of whether to create the pluggable database from another PDB or by plugging in one that had been unplugged from some other CDB. I selected the first option, as shown in Figure 3, using the seed PDB as the basis for my new database.
On the next screen -- shown in Figure 4 -- I provided a name for the PDB and an administrator username and password. For the remainder of the DBCA wizard, I accepted the default values and clicked the Next button. When I reached the summary screen, I pressed the Finish button, and DBCA got to work creating the HR_PROD pluggable database.
Command-line approach to PDB creation
For those DBAs who like to use the command line instead, you can easily create a PDB in SQL*Plus with the CREATE PLUGGABLE DATABASE command. The example below creates the same PDB as I did above and then opens it for use:
SQL> create pluggable database hr_prod
2 admin user hradmin identified by Password1;
Pluggable database created.
SQL> alter pluggable database hr_prod open;
Pluggable database altered.
Next, I used the following series of commands to verify that the new PDB exists and alter my user session to work in it instead of the root container in the container database. Once my session is in the PDB, I can create tables and populate it with data.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FIRSTPDB READ WRITE NO
4 HR_PROD READ WRITE NO
SQL> show con_name
SQL> alter session set container=hr_prod;
SQL> show con_name