Oracle 18c Express Edition: How to create a second database

2018-11-25 Off By Markus Flechtner

Not everything that is technically possible is allowed. And so of course for the Oracle Database 18c Express Edition the restriction is clear: “One instance per environment”. “Environment” means a VM, a physical server etc.. But maybe you can run two XE databases on one server after all? Let’s give it a try.

Deutsche Übersetzung dieses Posts auf www.markusdba.de

And the short answer is: it works. But you can’t open both XE databases at the same time. And that would satisfy the license conditions in my opinion.

A new database is created in Oracle-18c-XE with the dbca. In contrast to the 11.2-Express Edition, where you create a new database with a script,  in 18c-XE there is the dbca known from SE2 and EE:

And, of course, this makes it possible to configure another database. I.e. you can specify the SID, define paths etc.). SID and database name are not preconfigured, so you can choose a different SID than “XE”.

But if you start to create the database and dbca tries to start the second XE instance on the same server, then you will receive  an error message:

The obvious solution is to terminate the existing XE instance.
After that you can then create the second database.

Of course, the same error message also appears on the command line in SQL*Plus if you want to start the second XE database, but another XE instance is already running: 

oracle@abel:~/ [XENCDB] ps -ef |grep smon
oracle 3168 1 0 17:45 ? 00:00:00 xe_smon_XE
oracle 8967 4429 0 18:39 pts/0 00:00:00 grep --color=auto smon

oracle@abel:~/ [XENCDB] echo $ORACLE_SID
XENCDB
oracle@abel:~/ [XENCDB] sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Nov 22 14:55:09 2018
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-00442: Oracle Database Express Edition (XE) single instance violation error
SQL> exit
Disconnected


oracle@abel:~/ [XENCDB] oerr ora 442
00442, 0000,"Oracle Database Express Edition (XE) single instance violation error"
// *Cause: An attempt was made to start more than one Oracle Database Express
// Edition (XE) instance.
// *Action: Upgrade to Oracle Database Standard Edition (SE) or Oracle Database
// Enterprise Edition (EE) if you want to run more than one instance.

Thus the two databases can only be operated alternately.

However, there is a catch: The “second” XE database is not shut down “cleanly” when the server is shut down and it is not started automatically when the server is booted. The script /etc/init.d/oracle-xe, which starts the Oracle components (instance + listener), contains the hardcoded Oracle SID “XE”. After the reboot, the XE instance is always at the top. Of course you can change the script and enter another SID. But then the same applies to the XE instance. All in all this behaviour is OK, because the Oracle 18c Express Edition and the script are only intended for one database per environment.

Links:

Ad (Amazon Link):
CategoryCommon Oracle 18c