Run Oracle Database with Docker

This tutorial will show you how to setup Oracle database with Docker.

Oracle database is one of the most successful commercial database and also critical part of many businesses today .

While Oracle database is commercial product, it also provides database called Oracle-XE with limitation on resources which is free to use.

In general we can install Oracle-XE on our systems by downloading corresponding installation files from official website.

Instead of installing as native application you can also run Oracle-XE database with Docker inside container. By running Oracle-XE as container you can easily experiment with different versions of database.

Oracle provides Oracle-XE container images in Oracle Container Registry . In this blog post first I am going to use oracle container images maintained in docker hub by user named gvenzl

There are 2 reasons for choosing images from docker hub

  1. Docker hub provides options for slim images which have much smaller size ( Full image size is more than 3GB)
  2. Has options for additional environment variables which simplifies the creation of new database and users

Images from Docker Hub comes in 3 flavors

FlavorExtensionDescriptionUse cases
Slim-slimAn image focussed on smallest possible image size instead of additional functionality.Wherever small images sizes are important but advanced functionality of Oracle Database is not needed.
Regular[None]A well-balanced image between image size and functionality. Recommended for most use cases.Recommended for most use cases.
Full-fullAn image containing all functionality as provided by the Oracle Database installation.Best for extensions and/or customizations.

Required Software

Now Let’s look at the process to run Oracle-XE as docker container

Starting Database with Docker Command

You can start Oracle-XE database with following docker command

docker run -d -p 1521:1521 -e ORACLE_PASSWORD=root gvenzl/oracle-xe:21-slim
Code language: plaintext (plaintext)

ORACLE_PASSWORD environment variable sets password for Database administrator.

When container starts it create database named XEPDB1 by default.

Once database container started we can access database using DBeaver with following properties

  • Host Name : localhost or 127.0.0.1
  • Port : 1521
  • Service Name : XEPDB1
  • User Name : SYSTEM or SYS ( ROLE : SYSDBA)
  • Password : root

Starting Database with docker-compose file

Step 1) create a new directory for the project and navigate into it

mkdir oracle && cd oracle
Code language: DOS .bat (dos)

Step 2) Create a docker compose file (docker-compose.yml) with following configuration

version: '3.3' services: oracle: image: gvenzl/oracle-xe:21-slim container_name: oracledatabase ports: - 1521:1521 environment: ORACLE_PASSWORD: root
Code language: Java (java)

Step 3) Open command prompt and navigate to oracle folder run following command

docker-compose up
Code language: Shell Session (shell)

If you want to run in detached mode, you can run in the following command

docker-compose up -d
Code language: Shell Session (shell)

Above command will download the image ( for the first time) and start the database in container.

The database is started on localhost at 1521 port.

Note

i) I am using oracle database v 21,If you want to use different version change the tag against image name. You can get all available image names from docker hub.

ii) When you start the container for the first time, database will be initialized . Later if you change some settings and restarting the containers , the changes will not take effect. In those scenarios you can force reinitialization by stopping containers with following command. Please note that, following command will remove any saved data from database.

docker compose down --volume

Customizing the Container

Oracle database image supports various environment variables which allows us to customize the container.

ORACLE_PASSWORD

This variable is mandatory for the first container startup and specifies the password for the Oracle Database SYS and SYSTEM users.

ORACLE_RANDOM_PASSWORD

This is an optional variable. If you set this variable to non-empty value, it will generate a random initial password for the SYS and SYSTEM users. The generated password will be printed to stdout.

Every time you start the container new password is generated when use this environment variable.

version: '3.3' services: oracle: image: gvenzl/oracle-xe:21-slim container_name: oracledatabase ports: - 1521:1521 environment: ORACLE_RANDOM_PASSWORD : root
Code language: Java (java)

Note

ORACLE_PASSWORD and ORACLE_RANDOM_PASSWORD environment variables are mutually exclusive. So only use one of the variable in your configuration.

ORACLE_DATABASE (for 18c and onwards)

This is an optional variable. Set this variable to a non-empty string to create a new pluggable database with the name specified in this variable.

version: '3.3' services: oracle: image: gvenzl/oracle-xe:21-slim container_name: oracledatabase ports: - 1521:1521 environment: ORACLE_PASSWORD : root ORACLE_DATABASE : eis
Code language: Java (java)

The docker-compose file creates another database named EIS along with default database XEPDB1

APP_USER

This is an optional variable. Set this variable to a non-empty string to create a new database schema user with the name specified in this variable. For 18c and onwards, the user will be created in the default XEPDB1 pluggable database. If ORACLE_DATABASE has been specified, the user will also be created in that pluggable database. This variable requires APP_USER_PASSWORD or APP_USER_PASSWORD_FILE to be specified as well.

APP_USER_PASSWORD

This is an optional variable. Set this variable to a non-empty string to define a password for the database schema user specified by APP_USER. This variable requires APP_USER to be specified as well.

version: '3.3' services: oracle: image: gvenzl/oracle-xe:21-slim container_name: oracledatabase ports: - 1521:1521 environment: ORACLE_PASSWORD : root ORACLE_DATABASE : eis APP_USER: dbuser APP_USER_PASSWORD: dbuser
Code language: Java (java)

The docker-compose file creates another database named EIS along with default database XEPDB1 and you can access the EID database with user name dbuser and password dbuser. It also creates schema named DBUSER in the database.

Persisting the Data

If you are looking for temp database above configuration is fine but if you want data to persist so that it is available when ever we start the container then we need to create a volume and store the data in it.

docker command with volume mapping.

docker run -p 1521:1521 -e ORACLE_PASSWORD=root -v oracle-volume:/opt/oracle/oradata gvenzl/oracle-xe:21-slim
Code language: Shell Session (shell)

docker-compose file with volume mapping ( create directory named oracle-volume where docker-compose is created)

version: '3.3' services: oracle: image: gvenzl/oracle-xe:21-slim container_name: oracledatabase volumes: - ./oracle-volume:/opt/oracle/oradata ports: - 1521:1521 environment: ORACLE_PASSWORD : root ORACLE_DATABASE : eis APP_USER: dbuser APP_USER_PASSWORD: dbuser volumes: oracle-volume:
Code language: Java (java)

Initialization scripts

If you want to run additional scripts during initialization of database in container , you can add one or more *.sql*.sql.gz*.sql.zip or *.sh files under /container-entrypoint-initdb.d.

After the database setup is completed, these files will be executed automatically in alphabetical order.

To run scripts on initialization, create folder map it to /container-entrypoint-initdb.d under volume.

I have created folder named initscripts where docker-compose file is created and create following sql script file in it.

connect dbuser/dbuser@xepdb1; create sequence hibernate_sequence start with 1 increment by 1; create table department (id integer not null, name varchar(255), primary key (id)); create table employee (id integer not null, birth_date date, email varchar(255), first_name varchar(255), gender varchar(255), hire_date date, last_name varchar(255), salary numeric(19,2), department_id integer, primary key (id)); COMMIT;
Code language: Java (java)

In docker-compose file map the folder containing the script

version: '3.3' services: oracle: image: gvenzl/oracle-xe:21-slim container_name: oracledatabase volumes: - ./oracle-volume:/opt/oracle/oradata - ./initscripts:/container-entrypoint-initdb.d ports: - 1521:1521 environment: ORACLE_PASSWORD : root APP_USER: dbuser APP_USER_PASSWORD: dbuser volumes: oracle-volume: initscripts:
Code language: Java (java)

Note

1.  scripts in /container-entrypoint-initdb.d are only run the first time the database is initialized; any pre-existing database will be left untouched on container startup

2. By default  *.sql*.sql.gz and *.sql.zip files will be executed in Sql*Plus as the SYS user connected to the Oracle instance (XE). If you want to run the script as different user you need add necessary commands in script

Startup Scripts

If you want to run additional scripts after the database running in a container has been started , you can add one or more *.sql*.sql.gz*.sql.zip or *.sh files under /container-entrypoint-startdb.d

After the database is up and ready for requests, these files will be executed automatically in alphabetical order.

To run scripts on database startup, create folder map it to /container-entrypoint-startdb.d under volume.

I have created folder named startupscripts where docker-compose file is created and createfollowing sql script file in it.

connect dbuser/[email protected]; create sequence hibernate_sequence start with 1 increment by 1; create table department (id integer not null, name varchar(255), primary key (id)); create table employee (id integer not null, birth_date date, email varchar(255), first_name varchar(255), gender varchar(255), hire_date date, last_name varchar(255), salary numeric(19,2), department_id integer, primary key (id)); COMMIT;
Code language: SQL (Structured Query Language) (sql)

version: '3.3' services: oracle: image: gvenzl/oracle-xe:21-slim container_name: oracledatabase volumes: - ./oracle-volume:/opt/oracle/oradata - ./startupscripts:/container-entrypoint-startdb.d ports: - 1521:1521 environment: ORACLE_PASSWORD : root APP_USER: dbuser APP_USER_PASSWORD: dbuser volumes: oracle-volume: startupscripts:
Code language: Java (java)

Note

1.   files placed in /container-entrypoint-startdb.d are always executed after the database in a container is started, including pre-created databases. Use this mechanism only if you wish to perform a certain task always after the database has been (re)started by the container.

2.if the database inside the container is initialized (started for the first time), startup scripts are executed after the setup scripts

Running Oracle-XE with Official Image

If you want to use the official oracle image to start oracle-xe database inside container use following docker-compose file

version: '3.3' services: oracle: image: container-registry.oracle.com/database/express:21.3.0-xe container_name: oracledatabase ports: - 1521:1521 environment: ORACLE_PWD: root
Code language: YAML (yaml)

References

https://hub.docker.com/r/gvenzl/oracle-xe

https://github.com/gvenzl/oci-oracle-xe

Similar Posts