After seven years, the much anticipated Oracle 18c Express Edition (XE) has finally seen the light of day.
Reaction to the new version can be summed up as…
Gerald Venzl, the person we have to thank for this new and vastly improved version of XE, has already published an installation guide.
At this point you may well ask yourself that what – apart from gratuitous puppy pics and cheesy-snack-based puns – is the difference between that post and this.
Well, if you’re a long-time user of 11gXE and you’re looking to upgrade then you will find 18cXE a rather different proposition.
The introduction of Multitenant databases aside, 18cXE differs greatly from it’s predecessor in terms of it’s functional scope.
Wheras 11gXE was – broadly speaking – functionally equivalent to Oracle Standard Edition, the approach for 18cXE has been to shoe-horn in as many Enterprise Edition features as possible.
No doubt, this will leave you anxious to play with the new version. However, there are some “home comforts” that were present in the old version that you’ll need to configure yourself this time around.
What I’m going to go through is :
- Installing 18cXE on a Red Hat compatible distro (CentOS7)
- Connecting to the database and exploring the containers
- Checking the TNS Listener
- Manual and Automatic Startup and Shutdown of the database and listener
- Setting and persisting the Oracle environment variables
- Accessing Enterprise Manager Express
- Installing the HR demo application in a Pluggable Database (PDB)
- Configuring the firewall to allow remote access to Oracle
The steps documented here have been performed on a vanilla installation of CentOS7. As such, they should work pretty much unaltered for other Red Hat based distros based on or similar to Red Hat Enterprise Linux (RHEL) version 7.
Before all of that though…
Resource Limits for 18cXE
As with previous versions of XE, there are limitations on the system resources that will be used. These include :
- 2 CPU cores ( up from 1 in 11gXE)
- 2 GB Ram ( 1 GB in 11g)
- 12GB of User Data ( 11GB in 11g)
- A maximum of 3 PDBs
In addition, you can only install one instance of XE per host. However, it does seem to be possible to install XE alongside other Oracle Editions on the same host.
One final point to note – the amount of space taken up by the initial installation is not insignificant. The space usage in the $ORACLE_BASE is :
sudo du -h -d1 5.3G ./product 76K ./oraInventory 0 ./checkpoints 12M ./diag 20M ./cfgtoollogs 4.9M ./admin 0 ./audit 3.4G ./oradata 8.7G .
This is worth bearing in mind when sizing your environment.
Additionally, if you’re tight on space, you may also consider removing the rpm files once the installation is complete as this frees up 2.4GB (although not in $ORACLE_BASE).
Speaking of rpm files…
Downloading and installation
Head over to the Downloads page and download the Oracle Database 18c Express Edition for Linux x64 version.
If you’re running a Red Hat compatible distro that’s not Oracle Linux, you’ll also need the Oracle Database Preinstall RPM for RHEL and CentOS. I’m running on CentOS7 so I’ll get the Release 7 version of this file.
At this point, we should now have two rpm files :
-rw-rw-r--. 1 mike mike 18244 Dec 25 17:37 oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm -rw-rw-r--. 1 mike mike 2574155124 Dec 25 17:37 oracle-database-xe-18c-1.0-1.x86_64.rpm
Next, we need to become root for a bit. If you’re worried that all this power might go to your head, fear not, I’ll let you know when we can become mere mortals again. For now though :
sudo -s [sudo] password for mike: #
Now we can install the RPMs. The preinstall first (note that you need to have an internet connection available when running this)…
yum localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
…This results in :
Loaded plugins: fastestmirror, langpacks Examining oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm: oracle-database-preinstall-18c-1.0-1.el7.x86_64 Marking oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package oracle-database-preinstall-18c.x86_64 0:1.0-1.el7 will be installed --> Processing Dependency: compat-libcap1 for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64 Loading mirror speeds from cached hostfile * base: mirrors.vooservers.com * extras: mirror.sov.uk.goscomb.net * updates: mirrors.vooservers.com base | 3.6 kB 00:00:00 extras | 3.4 kB 00:00:00 updates | 3.4 kB 00:00:00 --> Processing Dependency: compat-libstdc++-33 for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64 --> Processing Dependency: ksh for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64 --> Processing Dependency: libaio-devel for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64 --> Running transaction check ---> Package compat-libcap1.x86_64 0:1.10-7.el7 will be installed ---> Package compat-libstdc++-33.x86_64 0:3.2.3-72.el7 will be installed ---> Package ksh.x86_64 0:20120801-139.el7 will be installed ---> Package libaio-devel.x86_64 0:0.3.109-13.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================ Package Arch Version Repository Size ================================================================================================================ Installing: oracle-database-preinstall-18c x86_64 1.0-1.el7 /oracle-database-preinstall-18c-1.0-1.el7.x86_64 55 k Installing for dependencies: compat-libcap1 x86_64 1.10-7.el7 base 19 k compat-libstdc++-33 x86_64 3.2.3-72.el7 base 191 k ksh x86_64 20120801-139.el7 base 885 k libaio-devel x86_64 0.3.109-13.el7 base 13 k Transaction Summary ================================================================================================================ Install 1 Package (+4 Dependent packages) Total size: 1.1 M Total download size: 1.1 M Installed size: 4.0 M Is this ok [y/d/N]:
Enter ‘y’ and…
Downloading packages: (1/4): compat-libcap1-1.10-7.el7.x86_64.rpm | 19 kB 00:00:00 (2/4): compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm | 191 kB 00:00:00 (3/4): libaio-devel-0.3.109-13.el7.x86_64.rpm | 13 kB 00:00:00 (4/4): ksh-20120801-139.el7.x86_64.rpm | 885 kB 00:00:00 ---------------------------------------------------------------------------------------------------------------- Total 1.8 MB/s | 1.1 MB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : compat-libstdc++-33-3.2.3-72.el7.x86_64 1/5 Installing : libaio-devel-0.3.109-13.el7.x86_64 2/5 Installing : compat-libcap1-1.10-7.el7.x86_64 3/5 Installing : ksh-20120801-139.el7.x86_64 4/5 Installing : oracle-database-preinstall-18c-1.0-1.el7.x86_64 5/5 Verifying : ksh-20120801-139.el7.x86_64 1/5 Verifying : compat-libcap1-1.10-7.el7.x86_64 2/5 Verifying : oracle-database-preinstall-18c-1.0-1.el7.x86_64 3/5 Verifying : libaio-devel-0.3.109-13.el7.x86_64 4/5 Verifying : compat-libstdc++-33-3.2.3-72.el7.x86_64 5/5 Installed: oracle-database-preinstall-18c.x86_64 0:1.0-1.el7 Dependency Installed: compat-libcap1.x86_64 0:1.10-7.el7 compat-libstdc++-33.x86_64 0:3.2.3-72.el7 ksh.x86_64 0:20120801-139.el7 libaio-devel.x86_64 0:0.3.109-13.el7 Complete!
Now for the main event…
yum localinstall oracle-database-xe-18c-1.0-1.x86_64.rpm
…which results in ( after quite a while) …
Loaded plugins: fastestmirror, langpacks Examining oracle-database-xe-18c-1.0-1.x86_64.rpm: oracle-database-xe-18c-1.0-1.x86_64 Marking oracle-database-xe-18c-1.0-1.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package oracle-database-xe-18c.x86_64 0:1.0-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================ Package Arch Version Repository Size ================================================================================================================ Installing: oracle-database-xe-18c x86_64 1.0-1 /oracle-database-xe-18c-1.0-1.x86_64 5.2 G Transaction Summary ================================================================================================================ Install 1 Package Total size: 5.2 G Installed size: 5.2 G Is this ok [y/d/N]:
Once again, enter ‘y’…then go and get a coffee (or other bevarage if you prefer), this next bit takes a while…
Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : oracle-database-xe-18c-1.0-1.x86_64 1/1 [INFO] Executing post installation scripts... [INFO] Oracle home installed successfully and ready to be configured. To configure Oracle Database XE, optionally modify the parameters in '/etc/sysconfig/oracle-xe-18c.conf' and then execute '/etc/init.d/oracle-xe-18c configure' as root. Verifying : oracle-database-xe-18c-1.0-1.x86_64 1/1 Installed: oracle-database-xe-18c.x86_64 0:1.0-1 Complete!
Finally, we need to run the configuration.
NOTE : I quit the previous session and began a new one as root before running this.
sudo -s /etc/init.d/oracle-xe-18c configure
…once again this takes a while to complete, which is fair enough because there’s a bit going on :
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database XE.
Enter SYS user password:
*********
Enter SYSTEM user password:
*********
Enter PDBADMIN User Password:
**********
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
31% complete
34% complete
38% complete
41% complete
43% complete
Completing Database Creation
47% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/XE.
Database Information:
Global Database Name:XE
System Identifier(SID):XE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/XE/XE.log" for further details.
Connect to Oracle Database using one of the connect strings:
Pluggable database: frea.virtualbox:1522/XEPDB1
Multitenant container database: frea.virtualbox:1522
Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE
At this point we can stop being root.
Connecting to the database
First up, we need to make sure that the appropriate environment variables are set. So run the following, entering XE when prompted for the ORACLE_SID…
. oraenv ORACLE_SID = [mike] ? XE ORACLE_BASE environment variable is not being set since this information is not available for the current user ID mike. You can set ORACLE_BASE manually if it is required. Resetting ORACLE_BASE to its previous value or ORACLE_HOME The Oracle base has been set to /opt/oracle/product/18c/dbhomeXE
Now we should be able to connect to the database via sqlplus :
sqlplus system
We can now confirm that the database is up :
select instance_name, version, status from v$instance; INSTANCE_NAME VERSION STATUS ---------------- ----------------- ------------ XE 18.0.0.0.0 OPEN
One significant new feature of 18c XE as compared with it’s predecessor is the capability to use the database as a container (CDB) for zero or more Pluggable Databases (PDBs).
In the case of XE, you can have up to three PDBs and we can see that one has already been created as part of the installation :
select con_id, name
from v$containers;
CON_ID NAME
---------- ------------------------------
1 CDB$ROOT
2 PDB$SEED
3 XEPDB1
In this case :
- CDB$ROOT is the Container Database
- PDB$SEED is a read-only template for creating PDBS
- XEPDB1 is a PDB
In the CDB, we can see details of the PDB seed database and the PDB itself :
select con_id, name, open_mode
from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 XEPDB1 READ WRITE
However, if we switch to the PDB…
alter session set container = XEPDB1;
…the same query returns information only about the current PDB…
select con_id, name, open_mode
from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
3 XEPDB1 READ WRITE
If you want to check which PDB you are in you can use :
select sys_context('userenv', 'con_name') from dual;
In the CDB this should return :
CDB$ROOT
in our PDB however, we should get :
XEPDB1
Checking the Listener
For ongoing administration operations from the OS, you’ll need to add your user to a couple of groups. In my case, my user is “mike” :
sudo usermod -a -G dba mike sudo usermod -a -G oinstall mike
Once you’ve added these groups to your user you need to log off and log on again for them to take effect.
You should now be able to check the status of the Net Listener by means of the lsnrctl utility.
Having first run oraenv as before to set your environment…
lsnrctl status
When the listener is up, you should get something like :
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 26-DEC-2018 20:38:31 Copyright (c) 1991, 2018, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=frea.virtualbox)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production Start Date 26-DEC-2018 19:24:54 Uptime 0 days 1 hr. 13 min. 37 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/frea/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=frea.virtualbox)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/product/18c/dbhomeXE/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "7de2a3259d9c3747e0530f84f25ce87c" 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... Service "xepdb1" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... The command completed successfully
If however, it’s not running, you’ll get :
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 26-DEC-2018 20:40:30 Copyright (c) 1991, 2018, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=frea.virtualbox)(PORT=1522))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 2: No such file or directory
This brings us onto…
Starting and Stopping Oracle
The first time you restart the server after the installation, you will find that neither the database nor the TNS Listener are running.
To start them up from the command line you can run :
sudo /etc/init.d/oracle-xe-18c start
To shut them down, it’s :
sudo /etc/init.d/oracle-xe-18c stop
If, like me, you are configuring your server for the sole or main purpose of running Oracle, then you may want the database and listener to start when the server does.
To do this, switch to root…
sudo -s
…and set the oracle-xe-18c service to start on boot…
systemctl daemon-reload systemctl enable oracle-xe-18c
The output will probably be something like :
oracle-xe-18c.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig oracle-xe-18c on
[root@frea mike]# systemctl status oracle-xe-18c
oracle-xe-18c.service - SYSV: This script is responsible for taking care of configuring the RPM Oracle XE Database and its associated services.
Loaded: loaded (/etc/rc.d/init.d/oracle-xe-18c; bad; vendor preset: disabled)
Active: inactive (dead)
Docs: man:systemd-sysv-generator(8)
If you then reboot the server, you should be able to confirm that the service is up by running…
systemctl status -l oracle-xe-18c
…which should return something like …
oracle-xe-18c.service - SYSV: This script is responsible for taking care of configuring the RPM Oracle XE Database and its associated services.
Loaded: loaded (/etc/rc.d/init.d/oracle-xe-18c; bad; vendor preset: disabled)
Active: active (exited) since Fri 2018-12-28 13:20:23 GMT; 1min 48s ago
Docs: man:systemd-sysv-generator(8)
Process: 3475 ExecStart=/etc/rc.d/init.d/oracle-xe-18c start (code=exited, status=0/SUCCESS)
Tasks: 0
Dec 28 13:19:59 frea.virtualbox systemd[1]: Starting SYSV: This script is responsible for taking care of configuring the RPM Oracle XE Database and its associated services....
Dec 28 13:19:59 frea.virtualbox oracle-xe-18c[3475]: Starting Oracle Net Listener.
Dec 28 13:19:59 frea.virtualbox su[3510]: (to oracle) root on none
Dec 28 13:20:02 frea.virtualbox oracle-xe-18c[3475]: Oracle Net Listener started.
Dec 28 13:20:02 frea.virtualbox oracle-xe-18c[3475]: Starting Oracle Database instance XE.
Dec 28 13:20:02 frea.virtualbox su[3864]: (to oracle) root on none
Dec 28 13:20:23 frea.virtualbox oracle-xe-18c[3475]: Oracle Database instance XE started.
Dec 28 13:20:23 frea.virtualbox systemd[1]: Started SYSV: This script is responsible for taking care of configuring the RPM Oracle XE Database and its associated services..
Setting Oracle Environment Variables
You’ll have noticed that, up until now, we have to set some environment variables every time we want to interact with the database from the server command line.
To do this, we need to run :
. oraenv
When you run this script, you will always get asked for ORACLE_SID value :
. oraenv ORACLE_SID = [mike] ? XE The Oracle base has been set to /opt/oracle
We can see that the oraenv script affects four environment variables :
echo $ORACLE_SID XE echo $ORACLE_HOME /opt/oracle/product/18c/dbhomeXE echo $ORACLE_BASE /opt/oracle echo $PATH ...snip.../opt/oracle/product/18c/dbhomeXE/bin
You have some options as to how you can manage these environment variables.
One option is to setup some environment variables to prevent oraenv prompting for the SID every time it’s run.
Another is to set the environment variables automatically for all sessions.
Stopping oraenv prompting for input
To do this we need to ensure that the ORACLE_SID environment variable is set prior to invoking the script and also that the ORAENV_ASK variable is set to NO.
We can see the result of this with the following quick test :
export ORACLE_SID=XE export ORAENV_ASK=NO . oraenv The Oracle base has been set to /opt/oracle
To set these environment variables automatically, we can simply define them in the /etc/profile.d/sh.local script :
sudo nano /etc/profile.d/sh.local
Add the two variable assigments :
export ORACLE_SID=XE export ORAENV_ASK=NO
You will need to logout and login again for this change to take effect.
Setting the environment variables automatically
If you want to dispense with the need to call the oraenv script altogether, you can simply add a script with a .sh extension to the /etc/profile.d directory…
nano /etc/profile.d/set_oraenv.sh
…which should contain…
export ORACLE_SID=XE export ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE export ORACLE_BASE=/opt/oracle export PATH=$PATH:/opt/oracle/product/18c/dbhomeXE/bin
Next time you fire up a shell ( assuming you’re using bash – the default on CentOS), these environment variables will be set.
Enterprise Manager Express
Whilst 11gXE came with a database home page which consisted of an Apex application to administer XE, 18c does not come with Apex.
The good news is that, in common with all other 18c Editions, it comes with Enterprise Manager Express – a purpose built Admin tool.
Acessing it should be fairly simple. You just open a web browser (Firefox being the default on CentOS) and point it at the address specified in the output from our configuration run earlier. In our case this is :
https://localhost:5500/em
However, you may be ever so slightly disappointed…
Fortunately for us, someone was good enough to document the solution for this particular problem.
In short, we need to follow the link to the Adobe download site and select the .tar.gz option for the Flash Download :
After this, we should now have the following :
ls -l *.tar.gz -rwxrwx---. 1 mike mike 9045426 Dec 22 15:02 flash_player_npapi_linux.x86_64.tar.gz
Next, we extract the libflashplayer.so file from the archive…
tar -xf flash_player_npapi_linux.x86_64.tar.gz *libflashplayer.so ls -l libflashplayer.so -rw-rw-r--. 1 mike mike 16607528 Nov 29 23:06 libflashplayer.so
…and copy it to the location that Firefox expects it to be…
sudo cp libflashplayer.so /usr/lib64/mozilla/plugins/.
…before finally setting the file ownership and permissions…
cd /usr/lib64/mozilla/plugins sudo chmod 755 libflashplayer.so sudo chgrp root libflashplayer.so sudo chown root libflashplayer.so
Our file should now look like this :
libflashplayer.so -rwxr-xr-x. 1 root root 16607528 Dec 31 17:05 libflashplayer.so
If we go to the EM page now :
Activate the plugin and login as sys (as sysdba) :
Eventually, you should see the Enterprise Manager Home Page :
I’ll leave you to explore for a bit.
Installing the HR demo application
Unlike it’s predecessor, 18cXE does not come with the HR demo application pre-installed. However, it does include the scripts that enable us to perform this installation ourselves.
As this is an application as opposed to a system-wide utility, we’re going to install it in the PDB rather than the main CDB.
We’ll need to switch to the oracle OS user so that we have permissions to write to the log file that we’re going to specify. Then we connect to the database…
sudo su oracle sqlplus system
Once connected :
alter session set container = XEPDB1;
select sys_context('userenv', 'con_name') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
XEPDB1
Now we’ve confirmed that we’re in the PDB, simply run :
@$ORACLE_HOME/demo/schema/human_resources/hr_main.sql
This script will prompt for :
- the password for the HR user – enter an appropriate password and remember it as you will need it to access the new HR schema
- the default tablespace to use for the HR user – enter USERS
- the temporary tablespace to use for the HR user – enter TEMP
- the path of the log file written by this installation script – enter $ORACLE_HOME/demo/schema/log
NOTE – the script does not obfuscate the password you enter but echos it to the screen. In any case, you may consider that changing it shortly after installation is a wise move.
The output will look something like this :
specify password for HR as parameter 1: Enter value for 1: mysupersecretpassword specify default tablespeace for HR as parameter 2: Enter value for 2: USERS specify temporary tablespace for HR as parameter 3: Enter value for 3: TEMP specify log path as parameter 4: Enter value for 4: $ORACLE_HOME/demo/schema/log PL/SQL procedure successfully completed. User created. User altered. User altered. Grant succeeded. Grant succeeded. Session altered. ...snip... Comment created. Comment created. Comment created. Commit complete. PL/SQL procedure successfully completed.
We should now see that we have a “local” user called HR :
select account_status, default_tablespace, temporary_tablespace, common from dba_users where username = 'HR'; ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE COM -------------------------------- ------------------------------ ------------------------------ --- OPEN USERS TEMP NO
As the account is not locked, we can connect to it from SQL*Plus. Note that we’ll have to use the connect string for the PDB (as specified in the installation feedback earlier) as the schema does not exist in the CDB :
sqlplus hr/mysupersecretpassword@frea.virtualbox:1522/xepdb1
Alternatively we could use a method which doesn’t record the password in the bash history…
sqlplus /nolog SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jan 2 16:55:31 2019 Version 18.4.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. SQL> conn hr/mysupersecretpassword@frea.virtualbox:1522/xepdb1 Connected. SQL>
If you want to see the difference, just try both connection methods in the same Terminal session and then run :
history |grep sqlplus
It’s probably worth remembering this if you are running on a shared environment.
Anyway, we can now see that the HR schema has the following objects :
Acessing the database from remote machines
Up to this point we’ve been working on the database server itself. This is fine if you’re running your Red-Hat based system as your desktop ( although in that case it’s more likely to be Fedora than CentOS), but if you want to be able to access it remotely, you’ll need to configure the firewall to allow remote access to specific ports.
Our objectives here are :
- to allow access to the database from a client machine via TNS
- to allow access to the Enterprise Manager Express site
For CentOS 7 the default firewall is firewalld :
systemctl status firewalld
firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
Active: <font color="#8AE234"><b>active (running)</b></font> since Tue 2019-01-01 14:53:08 GMT; 4min 30s ago
Docs: man:firewalld(1)
Main PID: 2842 (firewalld)
Tasks: 2
CGroup: /system.slice/firewalld.service
└─2842 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid
Jan 01 14:53:07 frea.virtualbox systemd[1]: Starting firewalld - dynamic fir....
Jan 01 14:53:08 frea.virtualbox systemd[1]: Started firewalld - dynamic fire....
Hint: Some lines were ellipsized, use -l to show in full.
On my client machine, I’ve added the following entries to the $ORACLE_HOME/network/admin/tnsnames.ora file :
XE18 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = frea.virtualbox)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
xepdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = frea.virtualbox)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xepdb1)
)
)
XE18 will allow me to connect to the CDB and xepdb1 will let me connect to the PDB.
At the moment, when we try to connect to the datbase from a client machine we hit…
sqlplus system@XE18 SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 1 15:10:34 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: ERROR: ORA-12543: TNS:destination host unreachable
Back on the server, we can remedy this by issuing the following command to open the port that the TNS Listener is listening on ( in my case 1522) :
sudo firewall-cmd --permanent --add-port=1522/tcp success
…and verify with :
sudo firewall-cmd --list-ports 1522/tcp
This then allows the remote connection :
sqlplus system@XE18 SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 1 15:12:44 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Last Successful login time: Mon Dec 31 2018 23:22:40 +00:00 Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production SQL>
As for EM Express, we need to do the same for the port it’s running on (5500 in this case) :
sudo firewall-cmd --permanent --add-port=5500/tcp
However, we also need to connect to the database as SYSTEM and allow remote access by running:
exec dbms_xdb_config.SetListenerLocalAccess(false);
Once this is done we should now be able to access the EM Express home page remotely …
Next Steps
If you want to explore the possibilities offerred by the prospect of multiple Pluggable Database in Express Edition then this guide may be useful.
Also, if you’re curious about Apex and ORDS, an installation guide is available here.
References
As ever, I’ve found a number articles that have proved useful in writing this post. I’d like to make a point of saying thank-you to the people who have essentially provided free consultancy to me (and you, if you’ve followed this guide). So thanks to…
- Bobby Curtis for your article on how to know whether you’re on a PDB or a CDB
- Kellyn Pot’Vin-Gorman for saving me from putting a fortune in the swear-jar with her article on EM Express and Adobe FlashPlayer
- Justin Ellingwood for an article which allowed me to pretend I knew the first thing about Firewalls
- Rob who did likewise for systemd commands in CentOS
- The nameless documentation elf at Oracle who produced the installation guide
Finally, thanks to Gerald Venzl who has replace Marko Arnautovic as my favourite Austrian. For a West Ham fan, that’s saying something !
Share this:
The Anti-Kyte 
Man, the work you done here is ABSOLUTELY unbelievable , TRULY beyond belief . HUGE THANK YOU, you basically covered everything from A to Z. Outstanding work 🙂 !
LikeLike
Thanks for the very interesting and detailed guide!
“Whilst 11gXE came with a database home page which consisted of an Apex application to administer XE, 18c does not come with Apex.”
I didn’t know that! How do I install apex in version 18c?
Thank you very much.
LikeLike
Usor,
I’ve documented the steps for installing Apex on 18cXE. You can find them here.
HTH,
Mike
LikeLike
I have never seen any article about anything this thorough. Simply Unbelievable. Thanks a million times and more.
LikeLike
Thanks.
Glad you found it useful,
Mike
LikeLike
Works perfekt. Thanks a ton!!
That saved me hours. As I installed XE in GCP, I wanted to ommit the download to my laptop and then the upload to the cloud VM again.
I used curl -L …. –output filename directly in the VM, which worked well for me.
Thanks again. Great writing . Very helpful and just working.
LikeLike
We also provide Oracle XE as a service here: https://www.yellowit.eu/oracle-xe-as-a-service/
LikeLike
Excellent write-up; thanks a ton
These messages did not come
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.vooservers.com
* extras: mirror.sov.uk.goscomb.net
* updates: mirrors.vooservers.com
I had to do all the dependency load manually as advised inhttps://community.oracle.com/tech/developers/discussion/4303249/problems-installing-18c-on-centos8
yum install -y gcc-c++ make
yum install -y ksh
yum install -y sysstat
yum install -y xorg-x11-utils
yum install java-11-openjdk-devel
yum install -y libnsl
wget http://mirror.centos.org/centos/8/BaseOS/x86_64/os/Packages/libaio-devel-0.3.112-1.el8.x86_64.rpm
rpm -ivh libaio-devel-0.3.112-1.el8.x86_64.rpm
wget http://mirror.centos.org/centos/8/BaseOS/x86_64/os/Packages/compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
yum install compat-libstdc++-33
wget http://mirror.centos.org/centos/7/os/x86_64/Packages/compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
rpm -ivh compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
wget http://mirror.centos.org/centos/7/os/x86_64/Packages/compat-libcap1-1.10-7.el7.x86_64.rpm
rpm -ivh compat-libcap1-1.10-7.el7.x86_64.rpm
yum localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
yum localinstall oracle-database-xe-18c-1.0-1.x86_64.rpm
15 extra steps before the last two we really need
Any idea why this was so smooth in your case but such a pain in mine
Regards
Jayanta@Kolkata
LikeLike
Jayanta,
glad you got it working and thank you for posting your solution here. I’m sure others stopping by here will find it useful.
As to why this may have happened, the only thing that jumps out at me is that, in this post, I was installing on CentOS 7 and the forum post you referred to is specific to CentOS8.
Other than that, I can’t see anything obvious.
Thanks again for posting your solution.
Mike
LikeLike
Thanks a lot for the good work. Installed with ease
LikeLike
Thank you very much for taking the time to create the tutorial! It helped me a lot! all information in one place THANK YOU
LikeLike