Oracle 18 XE Ubuntu Quick Start Guide for Dummies

In this blog, Digia's expert Jani Hurskainen shares his views on the joys of Oracle database programming and hands out a quick start guide for developers to get Oracle database up and running locally.

Recently I had an opportunity to return to the joys of Oracle database programming with PL/SQL after a long break. I have always found the local database installation handy, so I was looking for the options for today to install the most current free Oracle Express Edition (aka Oracle XE) database management system in Ubuntu-based Linux distros.

Note  that this is not a detailed DBA guide but a quick start guide for developers to get Oracle database up and running locally with reasonable (IMO) defaults without any previous knowledge.

The easiest way to use Oracle XE in Ubuntu-based  Linux distros is the Docker container provided by Oracle. The container can be found in  Oracle's container registry:

https://container-registry.oracle.com/
> Database
> express

The page above also contains usage instructions but because I had not used Docker before I had to study the Docker basics a little bit  before I was able to record the transcript below.

Note that at the time of writing Oracle version 18.4.0.0 is the latest and that's what is referred to here by the latest Docker tag.

I used Lubuntu 20.04 LTS for the guide but the steps should apply to any Ubuntu derivative.

1. Docker on Ubuntu 

Install:

sudo apt install docker.io

Basic Docker commands I found useful:

# download image
sudo docker pull <IMAGE>

# list all images
sudo docker image ls

# create new (start/run) container from the image
sudo docker run --detached --name <NAME> <IMAGE>

# list all (both running and stopped) containers
sudo docker ps --all

# stop container
sudo docker stop <NAME>

# restart stopped container
sudo docker start <NAME>

# remove container
sudo docker rm <NAME>

# show container logs
sudo docker logs <NAME>

# list container port mappings
sudo docker port <NAME>

Note that in Ubuntu one must always use sudo with Docker.

2. Download the Docker Image

The image is huge so it might take some time with slow connections:

sudo docker pull container-registry.oracle.com/database/express:latest

3. Run Oracle with Default Docker Settings

With this method the database can't be accessed outside of the container:

sudo docker run \
--detached \
--name <NAME> \
container-registry.oracle.com/database/express:latest

The passwords for SYS, SYSTEM and PDBADMIN are created automatically during the container setup and can be found from the logs:

$ sudo docker logs <NAME> | head 1
ORACLE PASSWORD FOR SYS AND SYSTEM: <PASSWORD>

Now the database can be accessed by running sqlplus inside the container:

# connect to CDB$ROOT as sysdba
sudo docker exec --interactive --tty <NAME> sqlplus sys/<PASSWORD>@xe as sysdba

# connect to CDB$ROOT as non-sysdba
sudo docker exec --interactive --tty <NAME> sqlplus system/<PASSWORD>@xe

# connect to the XEPDB1 (pluggable) database
sudo docker exec --interactive --tty <NAME> sqlplus pdbadmin/<PASSWORD>@xepdb1

That's nice but even nicer would be to access the database from the outside of the container with your preferred tools!

4. Run Oracle with Reasonable Docker Settings

What I really want to do is to access the database  from the outside of the Docker container.

The Docker container  has several configuration points that you can tailor to  your needs. In the commands below:

• --publish 1521:1521 exposes Oracle Listener port.
• --publish 5500:5500 exposes Oracle EM that should be accessed https://localhost:5500/em/ but I just ran into the following error with the latest Firefox: "Secure Connection Failed - An error occurred during a connection to localhost:5500. PR_END_OF_FILE_ERROR". I simply gave up as I didn’t need Oracle EM 
• --env ORACLE_PWD=<PASSWORD> sets the password for SYS, SYSTEM and PDBADMIN
• --volume /var/lib/oracle/<NAME>:/opt/oracle/oradata bind mounts directory /var/lib/oracle/<NAME> on the host machine into a container /opt/oracle/oradata Note that the host directory must exist and be writable by the container's  Oracle user.

The command:

# create the host directory
sudo mkdir -p /var/lib/oracle/<NAME>

# make the directory writable by container's Oracle user
sudo chmod a+w /var/lib/oracle/<NAME>

# start the container
sudo docker run \
--detached \
--name <NAME> \
--publish 1521:1521 \
--publish 5500:5500 \
--env ORACLE_PWD=<PASSWORD> \
--volume /var/lib/oracle/<NAME>:/opt/oracle/oradata \
container-registry.oracle.com/database/express:latest

Other possible configurations are:

• Setting up ORACLE_CHARACTERSET but I found the default (AL32UTF8) fine.
• Configuring setup and startup scripts.

Now I can use Oracle SQLcl (or SQL Developer) to access the database:

# sqlcl below is just a handy alias for the actual Oracle SQLcl executable 

# connect to CDB$ROOT as sysdba
sqlcl -noupdates -LOGON sys/<PASSWORD>@//localhost:1521/XE as sysdba

# connect to CDB$ROOT as non-sysdba
sqlcl -noupdates -LOGON system/<PASSWORD>@//localhost:1521/XE

# connect to the XEPDB1 pluggable database
sqlcl -noupdates -LOGON pdbadmin/<PASSWORD>@//localhost:1521/XEPDB1

5. Using the Database

I'm not going into Oracle DBMS server details here but only show how to use the ready-made XEPDB1 pluggable database.

Connect to the database as sysdba

sqlcl -noupdates -LOGON sys/<PASSWORD>@//localhost:1521/XE as sysdba

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show pdbs



   CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 XEPDB1                         READ WRITE NO

SQL> alter session set container = xepdb1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
XEPDB1

SQL>

Create Database User FOO

SQL> create user foo identified by foo;

User FOO created.

SQL> grant create session to foo;

Grant succeeded.

SQL> alter user foo default tablespace users temporary tablespace temp;

User FOO altered.

SQL> grant unlimited tablespace to foo;

Grant succeeded.

SQL> grant create table to foo;

Grant succeeded.

Connect as User FOO

sqlcl -noupdates -LOGON foo/foo@//localhost:1521/XEPDB1

SQL> show con_name

CON_NAME
------------------------------
XEPDB1

SQL>

SQL> create table a(b number(1));

Table A created.

SQL> insert into a values(1);

1 row inserted.

SQL> commit;

Commit complete.

SQL> select * from a;

         B
----------
         1

SQL>

Now all you need to do is enjoy your local Oracle database instance!