Creating an Oracle Multitenant container database and PDBs

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               
 
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=hr_prod;
 
Session altered.
 
SQL> show con_name 
 
CON_NAME
------------------------------
HR_PROD