Getting started with Oracle Database 18c Express Edition - Part 4

Part 4 of Getting Started with Oracle 18c XE

This post is the 4th and final post in the series of getting started with Oracle Database 18c Express Edition and Docker. We do recommend you review the previous posts before we continue with this post. The previous posts can be found here:

• Part 1
• Part 2
• Part 3

In the previous post we managed to create a Docker image called oracle-db:18cXE and to quickly recap, you can start a container from this image using the following Docker run command: 

docker run -it -p 1521:1521 -p 5500:5500 -h devXE --name devXE oracle-db:18cXE

And if you have the instant client installed locally you could connect to the XE database using the commands:

sqlplus /nolog

connect system/Kiwi123@localhost/XE

Now when you review and look at this XE database instance you will notice you have a container database running one PDB:

SQL> select con_id, dbid, open_mode, name from v$containers;

CON_ID        DBID OPEN_MODE     NAME

------- ----------- ------------ ---------------

      1  2892796011 READ WRITE     CDB$ROOT

      2  1983177395 READ ONLY     PDB$SEED

      3   697499808 READ WRITE     XEPDB1

Now you are allowed to create up to 3 Pluggable databases, but keep in mind you're still limited to 12GB user storage.

This does raise the question, do you want to use Pluggable databases, or do you just want a non-container XE environment?  

Well if you are using the default XE creation, you will end up with a Container database and one Pluggable Database. BUT if you do not create the default database, you can still use the DBCA and create your own XE database and even call it something different - well at least on Linux this is possible.

So how do you do this? Let's take a look at how we can achieve this by making a few small changes to our Dockerfile. 

First we need to make sure that we do not execute the following command - as this is the command that runs the default configuration and creates the default XE database:

printf Kiwi123\\nKiwi123\\n  | /etc/init.d/oracle-xe-18c configure

Then we need to execute the following as the “oracle” Unix account, not the “root” user.

Step 1: Creating Listener

We execute the network configuration assistant to create the listener - this can be done in silent mode, the same as what you can do with the “dbca” and installer. Here is the example:

netca /orahome $ORACLE_HOME \

         /instype typical  \

         /inscomp client,oraclenet,javavm,server,ano  \

         /insprtcl tcp  \

         /cfg local  \

         /authadp NO_VALUE  \

         /responseFile $ORACLE_HOME/network/install/netca_typ.rsp  \

         /silent \

         /listenerparameters DEFAULT_SERVICE=XE  \

         /lisport 1521

Step 2: Creating the Database

The next step is running the Database Configuration Assistant - “dbca” and this is one powerful easy utility with a large amount of options. Now to take it even one step further, make a few small adjustments to use these parameters:

1. Enable Archive Log Mode - Backups are still possible in XE and with Archivelog Mode enabled - you can create online backups and even create a standby database for your XE database using Standby™️!
2. Disable Sample schemas - In most cases, you do not need this, so just disable this.
3. Modify the memory parameters - SGA and PGA - in this example and for testing, reduce it to a nice small size.
4. Use Oracle Managed Files - OMF. This is something we highly recommend. Let Oracle manage the file naming.

The command used is as follows:

dbca -silent -createDatabase -gdbName XE \

-templateName XE_Database.dbc \

-createAsContainerDatabase false \

-sid XE \

-emConfiguration DBEXPRESS \

-emExpressPort 5500 \

-J-Doracle.assistants.dbca.validate.DBCredentials=false \

-sampleSchema false \

-initParams sga_target=600M,pga_aggregate_target=50M \

-storageType FS \

-datafileDestination /opt/oracle/oradata \

-useOMF true \

-enableArchive true \

-systemPassword Kiwi123 \

-sysPassword Kiwi123 \

-recoveryAreaDestination /opt/oracle/fast_recovery_area \

-recoveryAreaSize 5120 \

-redoLogFileSize 50

The end result is if you are using the above, that you end up with a small XE database that is not a container database with any pluggable databases.  

The updated Dockerfile will look like this:

##

First we select our base image we will use to build from, in this case we will use Oracle Linux 7.6

#

FROM oraclelinux:7.6

##

Then we provide some basic information - you can customise this to your need, but 
I just add my email here:

#

MAINTAINER anton.els@dbvisit.com

#

We are now switching to the root user as the next steps will require root permission:

USER root

First we add the “oracle”  Unix user and groups and assign a password to the user. Note - you will have to make this password more complex in production configurations:

RUN groupadd -g 501 oinstall \

  && groupadd -g 502 dba \

  && groupadd -g 503 oper \

  && useradd -m -g oinstall -G oinstall,dba,oper -u 501 oracle \

  && echo 'oracle:Kiwi123' | chpasswd

Now as a prerequisite for the Oracle 18c XE Install, a few extra packages are required - and as we use tools like tree, which and sysstat, we add them here as well. You can customise this, but “file” and “openssl” must be installed before you can install Oracle 18c XE. We also want to keep the image small, so to do this we cleanup after the install and remove all temporary files and install caches using during installation.

RUN rpm --rebuilddb \

&& yum -y install file openssl lsof sudo sysstat tree wget which  \

&& yum clean all \

&& rm -rf /var/cache/yum/*

Update sudoers to allow the oracle Unix user to run commands as the root user.  

In this example we keep it simple and allow the user to execute any command without a password but for production configurations you might want to look at locking this down more.

We also create a few directories and make sure the Oracle Unix user own them. First the directory /opt/oracle in which Oracle 18c XE will be installed into.

Second we create the /install folder which will be a temporary location from where we will install files. Third we create a bin directory in the Oracle user home folder location - where we can place scripts.

RUN echo "oracle ALL=(ALL) NOPASSWD:ALL" >> /etc/sudoers \

  && echo "Defaults !requiretty"  >> /etc/sudoers \

  && mkdir -p /opt/oracle \

  && mkdir -p /install \

  && mkdir -p /home/oracle/bin \

  && chown -R oracle:oinstall /opt/oracle \

  && chown -R oracle:oinstall /home/oracle/bin

Next, we copy the Oracle 18c XE software to the install directory followed by setting the ORACLE_DOCKER_INSTALL=true environment variable before we install the Oracle 18c XE software with “yum -y localinstall” command.  

The ORACLE_DOCKER_INSTALL variable is used to indicate we are installing into Docker and as part of the pre/post install scripts Oracle will use this to remove/unset settings which is not required when using Docker. On the last part we cleanup the install folder and any temporary files.

COPY software/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm /install

COPY software/oracle-database-xe-18c-1.0-1.x86_64.rpm /install/

ENV ORACLE_DOCKER_INSTALL=true

RUN cd /install \

&& yum -y localinstall oracle-database-*18c*  \

&& yum clean all \

&& rm -rf /var/cache/yum/* \

&& rm -rf /install/oracle*.rpm

Next we specify which ports we want to expose for Oracle when a container is created from this image. We pick the defaults for the listener and EM Express:

EXPOSE 1521 5500

Now in this example we will use the default configure option, and during testing we found that for Docker you have to remove memlock options from the /etc/security/limits.d/oracle-database-preinstall-18c.conf file.

Creating the XE database:

Now this is done by running the "/etc/init.d/oracle-xe-18c configure” command but this command expects input for user passwords, now two are required and the second is just confirmation of the first. This password is for the SYS, SYSTEM and PDBADMIN users - they will all use the same one. Printf is useful here to “pass” the password into the configure command so that no user interaction is required.

RUN sed -i -e "s/^.*memlock.*//g" /etc/security/limits.d/oracle-database-preinstall-18c.conf 

## NOTE: We do not need this line anymore as we will create the database manually

### printf Kiwi123\\nKiwi123\\n  | /etc/init.d/oracle-xe-18c configure

We now add an entrypoint script. There is a lot of information on Entrypoint and CMD options in the Docker documentation for more reference. But in this example we will copy a script called “manage-xe.sh” to the /home/oracle/bin folder which is executable by the Oracle Unix account and this will be used to start the Oracle 18c XE database on launch of the container.

COPY scripts/manage-xe.sh /home/oracle/bin

RUN chown oracle:oinstall /home/oracle/bin/manage-xe.sh \

  && chmod 775 /home/oracle/bin/manage-xe.sh

We now switch to the “oracle” user as up to this point all operations were performed as the root user. We also specify the work directory - which in this case is the default Oracle user home folder location.

USER oracle

WORKDIR /home/oracle

Something really useful is to set current environment for the Oracle user.

This is done using the ENV command. With this we specify the core parameters that will make things simpler for the “oracle” Unix account, example setting the PATH, ORACLE_HOME ORACLE_SID etc.

ENV ORACLE_DOCKER_INSTALL=true \

    ORACLE_BASE=/opt/oracle \

    ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE \

    PATH=/home/oracle/bin:/opt/oracle/product/18c/dbhomeXE/bin:$PATH \

    NLS_DATE_FORMAT="dd/mm/yyyy:hh24:mi:ss" \

    ORACLE_SID=XE

Instead of creating the Default CDB XE database with one Pluggable database, we now create our own custom XE database which is a nonCDB without any pluggable databases.  

This is a 2 step process, first we add the listener, then we execute the DBCA command to create the database:

RUN netca /orahome $ORACLE_HOME /instype typical /inscomp client,oraclenet,javavm,server,ano /insprtcl tcp /cfg local /authadp NO_VALUE /responseFile $ORACLE_HOME/network/install/netca_typ.rsp /silent /listenerparameters DEFAULT_SERVICE=XE  /lisport 1521 \

&& dbca -silent -createDatabase -gdbName XE \

-templateName XE_Database.dbc \

-createAsContainerDatabase false \

-sid XE \

-emConfiguration DBEXPRESS \

-emExpressPort 5500 \

-J-Doracle.assistants.dbca.validate.DBCredentials=false \

-sampleSchema false \

-initParams sga_target=600M,pga_aggregate_target=50M \

-storageType FS \

-datafileDestination /opt/oracle/oradata \

-useOMF true \

-enableArchive true \

-systemPassword Kiwi123 \

-sysPassword Kiwi123 \

-recoveryAreaDestination /opt/oracle/fast_recovery_area \

-recoveryAreaSize 5120 \

-redoLogFileSize 50

##

#  We clear bash history to keep things clean - this is optional

#

RUN history -c

The last instruction is to specify what will be executed when the container is created from the image. There are a few options, you could just start a bash shell and then manually start the Oracle 18c XE database, or, you could call the custom manage-xe.sh script with the “start” parameter to start the listener and XE database on container creation (run time):

#

# CMD ["/bin/bash”]

#

CMD ["/home/oracle/bin/manage-xe.sh", "-o", "start”]

Next we can run the Docker build command (note we are using the same manage-xe.sh script as mentioned in the previous post) - but this time we call the image - oracle-db:18cXE-noncdb. This clearly shows “noncdb” and is just for reference to easily identify the custom build image.

docker build -f dockerfiles/Dockerfile -t oracle-db:18cXE-noncdb .

Now you have a Docker image with the XE database that you can use for testing, example:

docker run -it -p 1521:1521 -p 5500:5500 -h devXE --name devXE oracle-db:18cXE-noncdb

Database in  - setting environment

devXE - 172.17.0.2

Starting Oracle Net Listener.

Oracle Net Listener started.

Starting Oracle Database instance XE.

Oracle Database instance XE started.

Database in  - setting environment

update settings to allow DBExpress Access

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jun 23 06:55:49 2019

Version 18.4.0.0.0

From another session, you can now connect to this database - same as before: 

sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 Production on Sun Jun 23 19:02:47 2019

Version 18.1.0.0.0

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

SQL> connect system/Kiwi123@localhost/XE

Connected.

SQL> select cdb, con_id, dbid, name from v$database;

CDB    CON_ID         DBID NAME

--- ---------- ---------- ---------

NO         0 2892802797 XE

SQL> select con_id, file#, status, name, bytes/1024/1024 sizeMB from v$datafile;

    CON_ID    FILE# STATUS  NAME                                   SIZEMB

---------- ---------- ------- ------------------------------------------------------------ ----------

     0        1 SYSTEM  /opt/oracle/oradata/XE/datafile/o1_mf_system_gjy82d2r_.dbf      830

     0        3 ONLINE  /opt/oracle/oradata/XE/datafile/o1_mf_sysaux_gjy83hb5_.dbf      470

     0        4 ONLINE  /opt/oracle/oradata/XE/datafile/o1_mf_undotbs1_gjy848kg_.dbf       70

     0        7 ONLINE  /opt/oracle/oradata/XE/datafile/o1_mf_users_gjy849rl_.dbf         5

SQL>

Now the other interesting saving is disk space here, the custom built container with a non-CDB database is 2GB smaller. You could still make this even smaller by using the Docker export and Docker import commands to squash the layers. This can save a bit of space if you do not care about the layers and give you a smaller image to use.

Below is a quick summary of the disk usage if connected to the container using “docker exec -it …” and start a bash shell. We will also show you that this database is not in archive log mode - perfect, ready for learning to use RMAN backups.

aelsnz@kiwi[/Users/aelsnz]: docker exec -it devXE bash

[oracle@devXE ~]$ cd /opt/

ORCLfmap/ oracle/

[oracle@devXE ~]$ cd /opt/oracle/oradata

[oracle@devXE oradata]$ ls

XE

[oracle@devXE oradata]$ du -sh

1.6G    .

[oracle@devXE oradata]$ cd ../fast_recovery_area/

[oracle@devXE fast_recovery_area]$ du -sh ./*

215M    ./XE

[oracle@devXE fast_recovery_area]$ . oraenv

ORACLE_SID = [XE] ?

The Oracle base remains unchanged with value /opt/oracle

[oracle@devXE fast_recovery_area]$ sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jun 23 07:05:26 2019

Version 18.4.0.0.0


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


SQL> connect / as sysdba

Connected.

SQL> archive log list;

Database log mode           Archive Mode

Automatic archival           Enabled

Archive destination           USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     3

Next log sequence to archive   5

Current log sequence           5

SQL>

SQL>

Summary 

In this blog series we showed you how you can get started with Oracle Database 18c Express Edition on Docker. There is so much more we can cover such as Docker volumes and Docker-compose and how you can even get a VNC lightweight graphical session in a Docker container… yes it is possible - a bit more work involved, but possible.  

Now keep in mind we did not use Docker Volumes but created the XE database inside the image. If you want more persistent storage, you will want to look at using Volumes for the /opt/oracle/oradata and /opt/oracle/fast_recovery locations.