Now that we have an understanding of the multitenant architecture from part 1, we want to add some practical experience starting with the creation of CDBs and PDBs.  My test environment will be Oracle VirtualBox v.5.1.22 guest running an Oracle Enterprise Linux 7.3 guest with Oracle version 12.2.0.1.0 software installed.

Creating CDBs and PDBs

NOTE – If you create a CDB under Oracle 12c release 1, the following is not supported:

  • Flashback Transaction Query
  • Flashback Transaction Back out
  • Database Change Notification
  • Continuous Query Notification
  • Client Side Cache
  • Heat map
  • Automatic Data Optimization
  • Oracle Streams
  • Oracle Fail Safe release 4.1.0 that ships with Oracle Database 12.1.0.2.

The preferred CDB creation method is through the Database Configuration Assistant (DBCA).  The DBCA wizard will guide you through the process and automatically execute the proper catalog scripts once the CDB is created.  The DBCA can also be used to generate all the scripts you need to create the CDB manually based on choices made through the DBCA create database wizard.  The DBCA can also generate a CDB creation template that you can use to run DBCA in other environments if you copy the template over.

Once you login to the OS as the Oracle software owner and set your environment, execute $ORACLE_HOME/bin/dbca

create_cdb1

The obligatory Oracle splash screen appears

create_cdb2

Choose the ‘create database’ option and proceed to the next screen

create_cdb3

We are going to choose ‘typical configuration’ here to save time.  On this screen, we can select the storage type, DB file location, character set, and designate the administrative (SYS and SYSTEM) passwords.  More importantly, we have the option to create the new database as a CDB or non-CDB database.  Since CDB is our goal here, we check the CDB box and designate the name of our inclusive pluggable database, then proceed.

create_cdb4

The summary screen appears to confirm our choices.

create_cdb5

If you like, you can watch the progress bar and the progress of the individual steps as the database is being built

create_cdb6

Acknowledge the finish screen when the process completes to exit the DBCA

create_cdb7

What if we had selected the ‘advanced configuration’ option earlier?  All the options under Typical configuration are automatically disabled.

create_cdb_adv1

Now we need to select the type of database (single-instance, RAC, or RAC one-node) and the characteristics (custom, general purpose, or data warehouse).  We’ll select single-instance and general purpose, then proceed.

create_cdb_adv2

There are some interesting choices on the next screen.  After providing a global database name and SID, you can choose a CDB or non-CDB database.  If you choose a CDB, you can opt for an empty CDB with no PDBs (except the PDB$SEED) or you can choose to create one or more PDBs at the same time.  Another option that is newly available in 12c release 2 is the option to create a separate UNDO tablespace for each PDB.

create_cdb_adv3

Storage options come next

create_cdb_adv4

Recovery options on this screen.  I chose to create a flash recovery area with the recommended size and enable archiving.

create_cdb_adv5

Listener selection is next.  There are no existing database listeners in the system, so I opt to create one with a default name and port number.  It is recommended to avoid using the default 1521 port for security reasons, but since this is a closed system, there should be no concerns.

create_cdb_adv6

At the next screen, I chose not to install Database Vault or Label Security

create_cdb_adv7

The next screen is multi-tabbed with options for memory allocation, sizing of processes, selection of character sets,  connection mode, and installation of sample schemas.  I choose the defaults in each section.

create_cdb_adv8

I opted to configure Enterprise Manager database express.  We may want to view our CDB options for backup and recovery later in a separate post.

create_cdb_adv9

Specify the administrative user credentials at the next screen

create_cdb_adv10

This screen prompts you with options to create the database, create a template of your choices for additional CDBs of this type, or the generation of scripts for manual database creation.  I opt for all three.  Why not?

create_cdb_adv11

Confirm your choices on the summary screen

create_cdb_adv12

If you like, you can watch the progress bar and the progress of the individual steps as the database is being built

create_cdb_adv13

Acknowledge the finish screen when the process completes to exit the DBCA

create_cdb_adv14

Creating a PDB

If you have a CDB and want to create a PDB or add a PDB, the DBCA is also the preferred method.

To create a new PDB, start DBCA. Once you get past the 12c splash screen, select ‘Manage pluggable databases’ on the first screen, then proceed.

create_pdb1

This screen contains all the PDB options you have in your current configuration such as create, delete, unplug, or configure.  The delete, unplug, and configure options will only by available if there is an existing PDB.  Select ‘create a pluggable database’, then proceed.

create_pdb2

Designate the CDB that will host your new PDB and give the admin account credentials for the designated CDB, then proceed.

create_pdb3

Here you can designate whether to create a new PDB from the ‘seed’ or create a new PDB from an existing PDB.  We are going to use the seed since we don’t have an existing PDB.

create_pdb4

Give a name to the new PDB and the credentials of the new administrative account for the PDB.  It is interesting to note here that the wizard is not looking for a typical SYS or SYSTEM account since these accounts already exist at the CDB level.

create_pdb5

Storage options are next as well as opting to create a default user tablespace.  The default user tablespace will be called USERS if you choose this option.  it is interesting to note here that even though the option is not given, the new PDB will have its own UNDO tablespace by default.

create_pdb6

Confirm your choices on the summary screen, then proceed.

create_pdb7

Watch the progress of the PDB creation.

create_pdb8

Acknowledge the finish screen when the process completes to exit the DBCA

create_pdb9

Other methods to create a PDB include:

  • Using the ‘create pluggable database’ command to create the PDB while connected to the CDB.
  • Plug an existing PDB into the CDB.
  • Using Oracle Cloud Control to guide you through the creation of the PDB.
  • Cloning an existing PDB to a new PDB with, or without data.
  • Creating an empty PDB and using Oracle Data Pump to import data from one database source into a PDB.

Starting and stopping the CDB

Starting and stopping a CDB is just like starting and stopping a non-CDB database.  Use the SQL*Plus utility (or Oracle Enterprise Manager) and issue the startup and shutdown commands as usual.  All of the options still exist such as startup force, shutdown abort and shutdown immediate.

Starting and stopping the PDB

First, determine the state of the PDB before issuing start or stop commands by connecting to the CDB with an account having SYSDBA, SYSOPER, SYSBACKUP or SYSDG privileges:

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
 --------------------       ------------------
 PDB$SEED      READ ONLY
 JON12PDB1      MOUNTED

The PDB$SEED database is ALWAYS read only.  As the template database, it needs to maintain its integrity.  Since the PDB is already mounted, you just need to open it:

SQL> alter pluggable database JON12PDB1 open;

Pluggable database altered.

You can execute the commands directly without the ‘pluggable database’ syntax by changing to the PDB within the session first:

SQL> alter session set container=JON12PDB1;

Session altered.

SQL> shutdown
 Pluggable Database closed.

I would not recommend this method, however, since it would be easy to forget what container you are pointing to and issue a shutdown mistakenly against the CDB could make you think about updating your resume.

Other methods:

  • Use the ‘alter pluggable database’ command to start and stop the PDB when connected to the CDB with an account having SYSDBA, SYSOPER, SYSBACKUP or SYSDG privileges.
  • Using the SQL*Plus ‘startup pluggable database’ and ‘shutdown pluggable database’ commands when connected to the CDB with an account having SYSDBA, SYSOPER, SYSBACKUP or SYSDG privileges.

PDB startup options (make sure you are connected to the PDB using the ‘alter session set container’ command while in the CDB in the example above):

Open (default) – Open the PDB normally (startup pluggable database JON12PDB1 😉

Force – Force the PDB open (startup pluggable database JON12PDB1 force;)

Upgrade – Open the PDB in upgrade mode (startup pluggable database JON12PDB1 upgrade;)

Restricted – Open the PDB in restricted mode (startup pluggable database JON12PDB1 restricted;)

Read only – Open the PDB in read only mode (startup pluggable database JON12PDB1 open read only;)

For those who don’t want to have to execute separate startup commands for the PDBs after starting the CDB, there is a means by which you can set the default state per PDB after the CDB is started.

For example, after starting the CDB, set the state of your desired PDB, albeit OPEN (read/write) or OPEN READ ONLY, execute the SAVE STATE command from the CDB:

alter pluggable database <PDB_NAME> save state;

Now when the CDB starts, the respective PDB will automatically go to the saved state.  You can view the saved state of each PDB in the DBA_PDB_SAVED_STATES view.  Please note that the saved state feature for PDBs is only on Oracle software versions 12.1.0.2 or higher.

To clear the saved state of the PDB, issue the DISCARD STATE command:

alter pluggable database <PDB_NAME> discard state;

This command will set the state of the particular PDB back to the MOUNTED default.  Changing the saved state of the desired PDB is a simple matter of placing the PDB in the desired state and issuing the SAVE STATE command again.

PDB open options:

Open (default) – Open the PDB in read write mode (alter pluggable database JON12PDB1 open;)

Open read only – Open the PDB in read only mode (alter pluggable database JON12PDB1 open read only;)

Open resetlogs – Open the PDB after a point-in-time-recovery (PITR) (alter pluggable database JON12PDB1 open resetlogs;)

Open restricted – Open the PDB in restricted mode (alter pluggable database JON12PDB1 open restricted;)

Force – Force shutdown and restart (alter pluggable database JON12PDB1 open force;)

Upgrade  – Open in upgrade mode (alter pluggable database JON12PDB1 open upgrade;)

PDB shutdown options (make sure you are connected to the PDB using the ‘alter session set container’ command while in the CDB in the example above):

Normal (default) – Close the PDB normally, waiting for transactions to end (shutdown)

Shutdown Immediate – Close the PDB. Rollback any pending transaction. Flush the SGA (shutdown immediate)

Abort – Shutdown the PDB without delay. Dirty buffers may be written to disk (shutdown abort)

Transactional – Shutdown the PDB after all active transactions have completed (shutdown transactional)

PDB close options:

Close (default) – Close the PDB normally (alter pluggable database JON12PDB1 close;)

Immediate – Force the PDB closed (alter pluggable database JON12PDB1 close immediate;)

 

Connecting to the CDB/PDB

Accessing the CDB is the same as accessing a non-CDB database and we have already discussed accessing the PDB while connected to the CDB, but what about accessing the PDB directly from outside the CDB?  As I mentioned in my last post, listener services are automatically created for the CDB and each PDB on creation.  Here you can see the JON12CDB and JON12PDB1 services when checking the listener status.

listener_status

Make sure the CDB is started and the PDB is open.  You can query the v$pdbs view from the CDB to see the status of all the enclosed PDBs.  Once you are sure the PDB is open, you can add an entry to your tnsnames.ora file to access the PDB directly (The entry for the CDB should already be present):

JON12PDB1 =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = JON12PDB1)
 )
 )

You should then be able to access your PDB directly:

pdb_remote_connection

Dropping a CDB and PDB

Dropping or removing a CDB is the same as dropping a non-CDB database.  The only difference in the case of dropping a CDB is that all PDB databases beneath it will be removed as well.

Dropping or removing a PDB is a bit more complicated when performing the task manually, but not by much.  When connected to the CDB that contains the PDB, use the ‘alter pluggable database’ command to first close the PDB, then issue the ‘drop pluggable database <PDB_name> including datafiles;’ to complete the process.  Using the DBCA to drop a PDB is even easier as it performs the closing of the PDB automatically, if necessary.

Other PDB removal options:

  • The SQL prompt, using the drop pluggable database command.
  • Using Oracle Enterprise Manager Cloud Control to remove the PDB
  • Unplugging the PDB
  • Deleting the entire CDB.

 

Querying the CDB and PDB

Once you are connected to the CDB or PDB, queries function the same as they have in non-CDB databases.  What is more important in this stage of our multitenant experience is knowing about multitenant-specific commands and objects.  For instance, when you are connected to a CDB, you can use the command ‘show pdbs’ to get all associated PDBs listed with their current state:

SQL> show pdbs

CON_ID CON_NAME     OPEN MODE  RESTRICTED
 ---------- ------------------------------ ---------- ----------
 2 PDB$SEED     READ ONLY  NO
 3 JON12PDB1     READ WRITE NO

Remember the PDB$SEED PDB is ALWAYS open in read only mode.

The container ID (CON_ID) is an important identifier in the multitenant environment.  The ‘show con_id’ command displays the CON_ID of the current CDB or PDB you are in.  Notice the value changes when I switch from the CDB to the PDB with the ‘alter session set container’ command:

SQL> show con_id

CON_ID
 ------------------------------
 1
 SQL> alter session set container=jon12pdb1;

Session altered.

SQL> show con_id

CON_ID
 ------------------------------
 3

NOTE – Once you alter your session to switch from the CDB to a PDB, you cannot switch back to the CDB and must exit your session and open a new one or open a different session to access the CDB once more.

The ‘show con_name’ command is similar to the ‘show con_id’ except that the name and not the ID is displayed:

SQL> show con_name

CON_NAME
 ------------------------------
 CDB$ROOT

SQL> alter session set container=jon12pdb1;

Session altered.

SQL> show con_name

CON_NAME
 ------------------------------
 JON12PDB1

 

If you need a list of the PDB IDs and names, query the v$pdbs view:
SQL> select con_id, name
2  from v$pdbs;

CON_ID NAME
 ---------- ----------
 3 JON12PDB1

In some views, the CON_ID column has been added for multitenant identification as in the case of v$tablespace:

select a.con_id, a.name, b.name
 from v$tablespace a, v$pdbs b
 where a.con_id=b.con_id order by 1;

CON_ID NAME       NAME
 ---------- ---------- ----------
 2 TEMP       PDB$SEED
 2 UNDOTBS1   PDB$SEED
 2 SYSAUX     PDB$SEED
 2 SYSTEM     PDB$SEED
 3 TEMP       JON12PDB1
 3 UNDOTBS1   JON12PDB1
 3 SYSAUX     JON12PDB1
 3 USERS      JON12PDB1
 3 SYSTEM     JON12PDB1

NOTE – This is a listing from the CDB.  If you are connected to a PDB, you will only see the tablespaces for that particular PDB.

In other views, some filtering has been enabled, such as dba_tables.  In this case, you will see only tables associated with the particular CDB or PDB you are connected to:

SQL> select count(*)
 2  from dba_tables;

COUNT(*)
 ----------
 2106

SQL> alter session set container=jon12pdb1;

Session altered.

SQL> select count(*)
 2  from dba_tables;

COUNT(*)
 ----------
 2105

The difference in the counts here is that when you are connected to the PDB, you cannot see the CDB-specific objects.  The CDB_TABLES view allows you to identify tables as belonging to a CDB or PDB:

select a.con_id, b.name, count(*)
 from cdb_tables a, v$containers b
 where a.con_id=b.con_id
 group by a.con_id, b.name;

CON_ID NAME  COUNT(*)
 ---------- ---------- ----------
 1 CDB$ROOT     2106
 3 JON12PDB1     2105

This view lets you see the CDB and enclosed PDB tables when connected to the CDB.  However, it does not work the same when connected to the PDB:

SQL> alter session set container=jon12pdb1;

Session altered.

SQL> select a.con_id, b.name, count(*)
 from cdb_tables a, v$containers b
 where a.con_id=b.con_id
 group by a.con_id, b.name;
 CON_ID NAME  COUNT(*)
 ---------- ---------- ----------
 3 JON12PDB1     2105

 

Now that you are on the PDB, the view only lets you see tables associated with that particular PDB.

Here are some other admin views available:

V$CONTAINERS – Contains information on the containers in the database including the ROOT and all PDBs.

V$PDB_INCARNATION – Provides information on incarnations specific to given PDBs.

V$PDBS – Provides information on PDBs within the database

DBA_CONTAINER_DATA – Displays various container data for container data objects.

DBA_HIST_PDB_INSTANCE – Contains workload repository records for PDBs

DBA_PDB_HISTORY – Contains the history of a PDB. Only visible from within a specific PDB.

DBA_PDB_SAVED_STATES – Contains the saved state of a PDB.

DBA_PDBS Contains a list of the PDBs in the database. Only visible from the ROOT container.

PDB_ALERTS Provides information on specific PDB alerts.

PDB_PLUG_IN_VIOLATIONS Provides information on incompatibilities between the CDB and the PDB.

 

We’ve covered quite a bit of territory in this post.  Next up, cloning and migrating PDBs

 

Advertisements

One thought on “Oracle Multitenant – Part 2 – Creating, connecting to, removing, and querying CDBs and PDBs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s