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 :
exec dbms_xdb_config.SetListenerLocalAccess(false);
Remote connection via TNS is possible provided the client from which we’re connecting has the following tnsnames.ora entry for it :
XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mydomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )
…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 :
select sys_context('userenv', 'con_name') from dual; SYS_CONTEXT('USERENV','CON_NAME') -------------------------------------------------------------------------------- CDB$ROOT select con_id, pdb_name, status from dba_pdbs; CON_ID PDB_NAME STATUS ---------- ------------------------------ ---------- 3 XEPDB1 NORMAL 2 PDB$SEED NORMAL
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 :
select df.name from v$datafile df inner join dba_pdbs pdb on pdb.con_id = df.con_id and pdb.pdb_name = 'XEPDB1'; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/XE/XEPDB1/system01.dbf /opt/oracle/oradata/XE/XEPDB1/sysaux01.dbf /opt/oracle/oradata/XE/XEPDB1/undotbs01.dbf /opt/oracle/oradata/XE/XEPDB1/users01.dbf
We want to get rid of these when we drop the PDB.
The PDB is currently open…
select name, open_mode from v$pdbs where name = 'XEPDB1' NAME OPEN_MODE ------------------------------ ---------- XEPDB1 READ WRITE
…so we need to connect as a user with the ALTER DATABASE and CREATE PLUGGABLE DATABASE privileges.
We can then close the PDB…
alter pluggable database xepdb1 close;
…and verify that it’s state has changed…
select open_mode from v$pdbs where name = 'XEPDB1'; OPEN_MODE ---------- MOUNTED
…before finally dropping it…
drop pluggable database xepdb1 including datafiles;
The PDB has now been removed…
select con_id, pdb_name, status from dba_pdbs; CON_ID PDB_NAME STATUS ---------- ------------------------------ ---------- 2 PDB$SEED NORMAL
…along with all of the datafiles…
ls -l /opt/oracle/oradata/XE/XEPDB1/ total 0
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 :
create pluggable database "PI" admin user "PDBADMIN" identified by ********** file_name_convert = ('/opt/oracle/oradata/XE/pdbseed/', '/opt/oracle/product/18c/dbhomeXE/XE/datafile/pi/'); alter pluggable database "PI" open read write; -- declare bind variables var b1 varchar2(4000); var b2 varchar2(4000); var b3 number; var b4 number; var b5 number; var b6 number; var b7 number; -- init bind values exec :b1 := 'DEFAULT_CDB_PLAN'; exec :b2 := 'pi'; exec :b3 := 1; exec :b4 := 100; exec :b5 := 100; exec :b6 := ; exec :b7 := ; begin sys.dbms_resource_manager.clear_pending_area(); sys.dbms_resource_manager.create_pending_area(); sys.dbms_resource_manager.create_cdb_plan_directive( plan => :b1, pluggable_database => :b2, shares => :b3, utilization_limit => :b4, parallel_server_limit => :b5, memory_min => :b6, memory_limit => :b7); sys.dbms_resource_manager.validate_pending_area(); sys.dbms_resource_manager.submit_pending_area(); end; /;
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…
select con_id, pdb_name, status from dba_pdbs order by con_id; CON_ID PDB_NAME STATUS ---------- ------------------------------ ---------- 2 PDB$SEED NORMAL 3 PI NEW
…it’s just not currently open…
select open_mode from v$pdbs where name = 'PI' / OPEN_MODE ---------- MOUNTED
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 :
alter pluggable database pi open read write;
…which should yield the message…
Pluggable database altered.
…signalling that PI is open…
select open_mode from v$pdbs where name = 'PI'; OPEN_MODE ---------- READ WRITE
PI has the following datafiles :
select df.name from v$datafile df inner join dba_pdbs pdb on pdb.con_id = df.con_id and pdb.pdb_name = 'PI' / NAME -------------------------------------------------------------------------------- /opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/system01.dbf /opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/sysaux01.dbf /opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/undotbs01.dbf
One for each of the three non-temporary tablespaces created…
alter session set container = pi; select tablespace_name, status, contents from dba_tablespaces; TABLESPACE_NAME STATUS CONTENTS ------------------------------ --------- --------------------- SYSTEM ONLINE PERMANENT SYSAUX ONLINE PERMANENT UNDOTBS1 ONLINE UNDO TEMP ONLINE TEMPORARY
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…
alter session set container = pi; select sys_context('userenv', 'con_name') from dual;
…and run…
create tablespace users datafile '/opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/users01.dbf' size 512M autoextend on / alter tablespace users default table compress for oltp;
This should result in a tablespace with a default table compression value set to ENABLED :
select tablespace_name, def_tab_compression from dba_tablespaces; TABLESPACE_NAME DEF_TAB_ ------------------------------ -------- SYSTEM DISABLED SYSAUX DISABLED UNDOTBS1 DISABLED TEMP DISABLED USERS ENABLED
…and a new datafile…
select df.name from v$datafile df inner join dba_pdbs pdb on pdb.con_id = df.con_id and pdb.pdb_name = 'PI' / NAME -------------------------------------------------------------------------------- /opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/system01.dbf /opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/sysaux01.dbf /opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/undotbs01.dbf /opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/users01.dbf
Setting the default tablespace
At present, the default tablespace in PI is SYSTEM :
select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE' / PROPERTY_VALUE -------------------------------------------------------------------------------- SYSTEM
We want to change this to USERS, so …
alter pluggable database default tablespace users; Pluggable database altered. select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE' / PROPERTY_VALUE -------------------------------------------------------------------------------- USERS
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…
select open_mode from v$pdbs where name = 'PI'; OPEN_MODE ---------- READ WRITE
…and then save the PDB’s state :
alter pluggable database pi save 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 :
PI = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mydomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PI) ) )
…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:
cd logger311 sqlplus system@pi
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 :
sqlplus system@localhost:1521/pi
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…
@create_user.sql Name of the new logger schema to create [LOGGER_USER] : Tablespace for the new logger schema [USERS] : Temporary Tablespace for the new logger schema [TEMP] : Enter a password for the logger schema [] : User created. User altered. Grant succeeded. LOGGER_USER user successfully created. Important!!! Connect as the LOGGER_USER user and run the logger_install.sql script. Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0
Now, we need to connect as the user we’ve just created ( LOGGER_USER in this case) and run :
@logger_install.sql
This will result in a number of actions concluding with :
************************************************* Now executing LOGGER.STATUS... Project Home Page : https://github.com/oraopensource/logger/ Logger Version : 3.1.1 Debug Level : DEBUG Capture Call Stack : TRUE Protect Admin Procedures : TRUE APEX Tracing : Disabled SCN Capture : Disabled Min. Purge Level : DEBUG Purge Older Than : 7 days Pref by client_id expire : 12 hours For all client info see : logger_prefs_by_client_id PL/SQL procedure successfully completed. ************************************************* PL/SQL procedure successfully completed. View altered.
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 :
@scripts/grant_logger_to_user.sql <username>
…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 :
@scripts/create_logger_synonyms.sql <logger owner>
…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 :
select table_name, compression, compress_for from dba_tables where owner = 'LOGGER_USER' order by 1; TABLE_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------------------------ LOGGER_LOGS ENABLED ADVANCED LOGGER_LOGS_APEX_ITEMS ENABLED ADVANCED LOGGER_PREFS ENABLED ADVANCED LOGGER_PREFS_BY_CLIENT_ID ENABLED ADVANCED
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…
select open_mode from v$pdbs where name = 'PI'; OPEN_MODE ---------- READ WRITE
OK, so we need to put the PDB into read only mode…
alter pluggable database pi close; alter pluggable database pi open read only; select open_mode from v$pdbs where name = 'PI'; OPEN_MODE ---------- READ ONLY
…and then clone it…
create pluggable database beta from pi file_name_convert = ( '/opt/oracle/product/18c/dbhomeXE/XE/datafile/PI', '/opt/oracle/product/18c/dbhomeXE/XE/datafile/BETA') / Pluggable database created.
We can confirm that the PDB has been created ( it will have a status of “NEW” until it is opened for the first time) :
select pdb_name, status from dba_pdbs; PDB_NAME STATUS ------------------------------ ---------- BETA NEW PDB$SEED NORMAL PI NORMAL
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 :
select df.name from v$datafile df inner join dba_pdbs pdb on pdb.con_id = df.con_id and pdb.pdb_name = 'BETA' / NAME -------------------------------------------------------------------------------- /opt/oracle/product/18c/dbhomeXE/XE/datafile/BETA/system01.dbf /opt/oracle/product/18c/dbhomeXE/XE/datafile/BETA/sysaux01.dbf /opt/oracle/product/18c/dbhomeXE/XE/datafile/BETA/undotbs01.dbf /opt/oracle/product/18c/dbhomeXE/XE/datafile/BETA/users01.dbf
At the moment, both of our PDBs are READ ONLY :
select name, open_mode from v$pdbs / NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PI READ ONLY BETA MOUNTED
To open them :
alter pluggable database all except pdb$seed open read write force;
If we switch to BETA, we can see that OOS Logger has been copied from PI and is already installed :
alter session set container = beta; select object_type, count(object_name) from dba_objects where owner = 'LOGGER_USER' group by object_type order by 2 desc; OBJECT_TYPE COUNT(OBJECT_NAME) ----------------------- ------------------ INDEX 8 TABLE 4 VIEW 3 SEQUENCE 2 TRIGGER 2 JOB 2 LOB 2 PROCEDURE 1 PACKAGE BODY 1 PACKAGE 1 10 rows selected.
We need to make sure that Beta starts on database startup :
alter pluggable database beta save state;
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…
conn sys@beta as sysdba @install_headless_with_trigger.sql
This creates a schema called UT3 which contains the framework objects :
select object_type, count(object_name) from dba_objects where owner = 'UT3' group by object_type order by 2 desc; OBJECT_TYPE COUNT(OBJECT_NAME) ----------------------- ------------------ TYPE 112 TYPE BODY 78 INDEX 53 TABLE 30 PACKAGE 25 PACKAGE BODY 25 SYNONYM 15 LOB 11 SEQUENCE 4 TRIGGER 1 10 rows selected.
As before, we can confirm that this software exists only in this PDB :
alter session set container=cdb$root; select username from dba_users where oracle_maintained = 'N'; no rows returned
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 :
- Enter the name of the PDB we want to create (ETA in our case)</li<
- 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 - KeyStorePassword : leave blank
- 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 :
alter pluggable database eta save state;
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.