Oracle 18c Express Edition: How to create a Non-Container-Database

2018-11-06 Off By Markus Flechtner

Oracle 18c Express Edition (XE) comes with a container database as default database. Up to 3 pluggable databases are free so XE is a good start for getting familiar with the Multitenant architecture. However, you can still run a Non-Container-Database with Oracle 18c XE. You can simply use the Database Configuration Assistant (dbca) to create such a database.

Note: I do not recommend to run Oracle 18c XE as Non-CDB. The Non-CDB-architecture is deprecated and can be desupported after Oracle 19c. 18c XE is a good choice to get started with the Multitenant architecture which has been around since 2013.

I’m running Oracle 18c Express Edition on my Centos 7.5 VM:

oracle@merlin:~/ [XE] cat /etc/centos-release

CentOS Linux release 7.5.1804 (Core)

I used the script “/etc/init.d/oracle-xe-18c configure” to configure database and listener. The environment is set; listener and database are up and running:

oracle@merlin:~/ [XE] echo $ORACLE_HOME
/opt/oracle/product/18c/dbhomeXE
oracle@merlin:~/ [XE] echo $PATH
/opt/oracle/product/18c/dbhomeXE/bin:/opt/oracle/product/18c/dbhomeXE/ctx/bin:/usr/bin:/usr/local/bin:/sbin:/usr/sbin:/usr/local/sbin:/home/oracle/.local/bin:/home/oracle/bin
oracle@merlin:~/ [XE] echo $ORACLE_SID
XE

oracle@merlin:~/ [XE] lsnrctl status
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 06-NOV-2018 19:41:55
Copyright (c) 1991, 2018, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=merlin.markusdba.mac)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                06-NOV-2018 19:38:48
Uptime                    0 days 0 hr. 3 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/merlin/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=merlin.markusdba.mac)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=merlin.markusdba.mac)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "78d7442f6eca245ce0536238a8c07dc9" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "78d8f5a2080027e8e0536238a8c06b74" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "78d8f5a2080227e8e0536238a8c06b74" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
[..]
The command completed successfully
oracle@merlin:~/ [XE] sqlplus "/ as sysdba"
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Nov 6 19:43:54 2018
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL> show pdbs
    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED  READ ONLY  NO
3 APPPDB  READ WRITE NO
4 FO3XPDB  READ WRITE NO
5 PLAYPDB  READ WRITE NO
SQL> exit
Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

As only one XE instance can run on an environment, I have to remove this database first. I’ll use dbca (GUI) for this step:

oracle@merlin:~/ [XE] dbca

Here are some screenshots:

After deleting the Container database we can create a Non-Container-Database.

Let’s start dbca again:

oracle@merlin:~/ [XE] dbca

OK, we want to create a database ==> “Next”

Let’s switch from “Typical Configuration” to “Advanced Configuration”.

No changes on this screen, just klick “Next”

As the database instance should be named “XE”, you have to change the global database name to “XE.<yourdomain>”, or simply “XE”. The SID will automatically change to “XE”. As “Create as Container Database” is the default setting, you have to uncheck this checkbox if you want to create a Non-CDB.

Let’s change the file location to “/opt/oracle/oradata/{DB_UNIQUE_NAME}” before we continue.

No changes on this screen, just klick “Next”. (Of course, you could configure the FRA and enable Archiving, but my database runs in Noarchivelog mode).

We will use the default listener (which was configured during the creation of the CDB), so we can continue without any changes on this page.

I’m running my XE database with the maximum amount of memory allowed (2 GB), so I will not change anything on this page (and on the other tabs).

==> “Next”

If you don’t want to configure Enterprise Manager Database Express then you can uncheck the checkbox. Otherwise ==>”Next”.

I tend to use simple passwords for my test databases, so Oracle complains. ==> “Yes”!

==> “Next”

Let’s klick “Finish” to start the creation of the database:

After some time:

And the result is a Non-Container-Database:

oracle@merlin:~/ [XE] sqlplus "/ as sysdba"
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Nov 6 19:56:00 2018
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> select cdb from v$database;

CDB
---
NO

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/XE/system01.dbf
/opt/oracle/oradata/XE/sysaux01.dbf
/opt/oracle/oradata/XE/undotbs01.dbf
/opt/oracle/oradata/XE/users01.dbf

SQL> exit
Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Once again, I do not recommend to run 18c XE as a Non-CDB. From application point of view there is basically no difference between a PDB and a Non-CDB.

Of course, you can use DBCA not only to create a Non-CDB, but