Making the most of Oracle 18c XE – Pluggable Databases and the Oracle Eco-System

This was going to be a simple post about creating multiple Pluggable Databases (PDBs) in Oracle 18cXE.
But you know what it’s like, you get your lego database set out and then you spot those unusual shaped bricks… and the transparent ones… oh, that one has a wheel on…and get a bit carried away.
What follows is a guide on how to create three concurrent PDBs on an Oracle 18cXE database. However, I have taken the opportunity to investigate other areas of the Express Edition platform and so will be making use of Advanced Compression as well as Oracle Enterprise Manager Express.
In fact, I wouldn’t be surprised if SQLDeveloper put in an appearance as well.
I’ve also included a couple of the more notable members of Oracle’s small-but-perfectly-formed eco-system into the final design. On top of all that, I’ll be performing a magic trick to ensure that I only have to install each piece of software once, even though it may end up in multiple PDBs…

The Environment

The server we’ll be working on is running CentOS7.
It also has an Oracle 18cXE instance which has been newly installed following these steps.

Remote connection to the Container Database’s (CDB) Oracle Enterprise Manager Express instance is possible because we’ve run :

Remote connection via TNS is possible provided the client from which we’re connecting has the following tnsnames.ora entry for it :

…where myserver.mydomain is the address of the server.

When connected to the CDB as SYSTEM, we can see that we currently have two PDBS :

Now, we know that the maximum number of PDBs allowed in 18cXE is three. However, this would appear to exclude the PDB$SEED database.

The Target Environment

I want to end up with three PDBS – one for Development, one for test and one for production.
As I’m doing a bit of “magic” I have borrowed the unofficial motto of the Unseen University to use as the naming convention for my PDBs.
Wizards are renowned for their big dinners and their motto is “Eta Beta Pi” ( Eat a Better Pie).
Wizards are not renowned for their punning.
Anyhow once I’ve finished, I’d like my PDBs to be configured as follows :

• ETA (Development) – including the OraOpenSource Logger and utPLSQL test framework
• BETA (Test) – OraOpenSource Logger and utPLSQL installed
• PI (Production) – OraOpenSource Logger only

All PDBs should have a USERS tablespace in which compression is enabled by default.
Before we go and create any databases though, we need to get rid of the existing XEPDB1 PDB…

Dropping a PDB

Now, we could re-purpose and rename XEPDB1, but as it’s currently empty it’s probably easier just to drop it.

At the moment, we can see that this PDB has the following datafiles :

We want to get rid of these when we drop the PDB.

The PDB is currently open…

…so we need to connect as a user with the ALTER DATABASE and CREATE PLUGGABLE DATABASE privileges.

We can then close the PDB…

…and verify that it’s state has changed…

…before finally dropping it…

The PDB has now been removed…

…along with all of the datafiles…

Now we’ve made a bit of space PDB wise, it’s time to…

Create a PDB from a Seed Database

I’m going to start by creating what will become my Production database (PI).
Speaking of Wizards, we’re going utilise the one in Enterprise Manager Express to do this.

To begin with, we need to connect to EM Express :

Hot Tip – don’t do what I did and connect as SYSTEM if you want to open the PDB once it’s been created ! Use an account with the ALTER DATABASE privilege.

Navigate to the Containers page

In the Containers section of the page choose Create from the Actions menu.

The wizard should now appear

On the General Page (first page) of the Create PDB From Seed wizard, supply values for the following :
PDB Name : pi
Username : pdbadmin
Password : a password for pdbadmin
Confirm password :

Incidentally, you can see the code that will be executed by EM Express if you click the Show SQL button.
In my case this is :

Some of that may change as we press on through the wizard. Click the Next arrow :

Right arrow click to go to storage page :

In our case accept datafile location defaults, and unlimited storage so no changes here.
Click the next arrow again, and we’ll have the chance to edit the default Resource Limits :

Once again, no changes are required in my case.

Finally, I’m going to click OK…and realise my mistake connecting as SYSTEM

On the plus side, it’s not too serious. The PDB has been created…

…it’s just not currently open…

To remedy this, I simply need to connect as a user with ALTER DATABASE and open the offending PDB.
As I have a brand new Database and I’m the DBA I’m going to connect as SYS as SYSDBA and run :

…which should yield the message…

…signalling that PI is open…

PI has the following datafiles :

One for each of the three non-temporary tablespaces created…

Creating the USERS tablespace

We want to add a tablespace called USERS to hold any application data. So, as a user with ALTER DATABASE, make sure we’re connected to the correct container…

…and run…

This should result in a tablespace with a default table compression value set to ENABLED :

…and a new datafile…

Setting the default tablespace

At present, the default tablespace in PI is SYSTEM :

We want to change this to USERS, so …

Ensuring the database starts on reboot

As a final piece of database administration, we want to make sure that PI starts when the instance does. To do this, we need to make sure that the PDB is open…

…and then save the PDB’s state :

Installing OraOpenSource Logger

I’ve setup a tns entry in the tnsnames.ora on my client machine. You’ll notice that it’s almost identical to the one for the CDB, with only the name of the entry itself and the service_name changed to the name of the new PDB :

…where myserver.mydomain is the address of the server.

I’ve downloaded the zip for OraOpenSource Logger v 3.11 from Github and extracted it onto my client machine.
I now navigate to the main directory and connect to the database as system:

NOTE – if you have not yet setup remote connections and you want to perform this installation from the server then download and extract the zip file to the server itself. Then, navigate to the logger311 directory and connect to the database using :

Either way, once you’re connected you can run the creation script for Logger owner schema.
You will be prompted for a schema name, default tablespace and password. Remember the username and password you select because you’ll need them in a moment…

Now, we need to connect as the user we’ve just created ( LOGGER_USER in this case) and run :

This will result in a number of actions concluding with :

NOTE – once we get some application owning schemas we’ll need to grant Logger objects to them.
This can be done by running the following as the logger owner :

…where username is the schema to whom you wish to grant logger.

Once this has run, you can then run the following as the granted user :

…where logger owner is the name of the Logger application owner schema.

Just as an aside, we can see that the logger tables have inherited the default compression settings for the USERS tablespace in which they have been created :

Finally, we have our Production PDB configured. That was a fair bit of work though, and we still have another two PDBs to create.
Fortunately, we can save ourselves some time and effort by…

Cloning a PDB

Next, I want to create my Test PDB ( BETA). As with PI, it needs to have OOS Logger installed and have a USERS tablespace with the same characteristics.
We can achieve this with rather less typing that you might expect simply by cloning the PI PDB.

To do this, we need to be connected as a user with ALTER DATABASE and CREATE PLUGGABLE DATABASE privileges ( I’m using SYS as SYSDBA here).

To start with we need to ensure that PI is in Read-Only mode in order to clone it. So, we check it’s current mode…

OK, so we need to put the PDB into read only mode…

…and then clone it…

We can confirm that the PDB has been created ( it will have a status of “NEW” until it is opened for the first time) :

We can also verify that the datafiles have been created in line with the FILE_NAME_CONVERT clause of the CREATE PLUGGABLE DATABASE command we issued :

At the moment, both of our PDBs are READ ONLY :

To open them :

If we switch to BETA, we can see that OOS Logger has been copied from PI and is already installed :

We need to make sure that Beta starts on database startup :

Installing utPLSQL

Remember that BETA is to be our Test environment and we want to install the utPLSQL PL/SQL testing framework.
First, we need to download the latest version from here ( 3.1.10 at the time of writing).
Once we’ve extracted the zip, we need to follow the installation instructions so…

This creates a schema called UT3 which contains the framework objects :

As before, we can confirm that this software exists only in this PDB :

Cloning a database using SQLDeveloper

If you prefer a more pointy/clicky variation on the theme of PDB cloning then SQLDeveloper is on hand to help (I’m using version 19.2 here).

Once you’ve started SQLDeveloper, from the View menu select DBA

If you don’t already have a connection to your CDB in the DBA Navigator window, add one using the green plus icon.

Now, expand the Connections tree and then the Container Database node and select the BETA node

From the Actions dropdown, select Clone PDB :

This will open the Clone Pluggable Database window :

1. Enter the name of the PDB we want to create (ETA in our case)</li<
2. File Name Conversions : Select Custom Names from the drop-down.
   You should see that the Target files are going to be written to a directory named after the new PDB
3. KeyStorePassword : leave blank
4. Check Open Automatically

The window should now look like this :

If you click on the SQL tab, we should see the code that SQLDeveloper is planning to run based on our selections :

Go back to the Properties Tab and click Apply

We can see that we now have a node for the new PDB :

Simple things please simple minds :

By now it’s no surprise that the new PDB has the same non-oracle users as the PDB we’ve cloned :

Remember to ensure db will open on restart :

Three databases for the price of one. That should keep me quiet for a bit.

Acknowledgements

As is so often the case, I found what I was looking for in an OracleBase article.

The answer to this AskTom question was also rather helpful in explaining why my PDBs weren’t starting when the database was.