Running Oracle Database Server as a Docker container

At work, I had to create an Oracle Enterprise database locally so that I could code scripts in different languages to show how to connect to Oracle using, for example .Net, NodeJS, Python or PHP.

So the idea was immediately to check whether there was an official image for this. And there is one but, damned, it's not as simple as for PostgreSQL, MySQL or MS SQL Server.

In this article, which is a clean-up of my notes, we're going to install Oracle Enterprise locally in a container, create a database with example data and access the database in several ways.

It's a step-by-step guide to make life easier for anyone who needs to do the same thing - me, for example, in a few months' time when I've forgotten everything.

It takes a while (Oracle is slow to initialize), but here's where we're headed: a running Oracle container, ready to query.

Let's build it, starting with the image itself.

Download a Docker image for OracleDB​

So, yes, there is a Docker image called container-registry.oracle.com/database/enterprise:latest but before being able to download it, we'll need to do a few things:

If you miss one step and try to download the image immediately, you'll get this error:

$ docker pull container-registry.oracle.com/database/enterprise:latest
Error response from daemon: Head "https://container-registry.oracle.com/v2/database/enterprise/manifests/21.3.0.0": unauthorized: Auth failed.▋

Creation of a free account on oracle.com​

Please surf on https://container-registry.oracle.com/ords/ocr/ba/database/enterprise and create a user by clicking on the Sign In button / top right link.

https://container-registry.oracle.com/ords/ocr/ba/database/enterprise

https://container-registry.oracle.com/ords/ocr/ba/database/enterprise

Generate a token​

Once your user has been validated (after reception of an email); go back to the https://container-registry.oracle.com/ords/ocr/ba/database/enterprise website; log in and click on your profile then choose Auth Token.

https://container-registry.oracle.com/ords/ocr/ba/database/enterprise

Click on the Generate Secret Key button and copy your SSO Username and your Secret Key somewhere (in your password vault application f.i.).

Accept License terms​

Please go to https://container-registry.oracle.com/ords/ocr/ba/database and search for enterprise in the Repository column.

https://container-registry.oracle.com/ords/ocr/ba/database

Being logged in, please click on the Continue button that appears on the right.

https://container-registry.oracle.com/ords/ocr/ba/database

A popup window will be displayed and, at the bottom, you'll need to click on Accept.

Optional, by going back to https://container-registry.oracle.com/ords/ocr/ba/database you'll see that Oracle has recorded the fact you've accepted their terms.

https://container-registry.oracle.com/ords/ocr/ba/database

Configure your local Docker environment​

Last part, teach Docker you can download from the Oracle registry.

Go to the command line and type docker login container-registry.oracle.com.

Fill in your SSO Username and your Secret Key (as given by the Auth Token page on oracle.com).

Docker will display Login Succeeded.

{
    "auths": {
        "container-registry.oracle.com": {},?
        "https://index.docker.io/v1/": {}?
    }
}

Finally, you can pull the image​

If all previous steps have been correctly done, now, you should be able to run docker pull container-registry.oracle.com/database/enterprise:latest without any error and download the 3.5GB Docker image.

Important Concepts to Consider When Working With Oracle v12 and After (skip this if you just want to run it)​

Container database (CDB) versus Pluggable database (PDB)​

In Oracle's multi-tenant architecture (introduced since Oracle 12c), the concepts of Container Database (CDB) and Pluggable Database (PDB) are fundamental.

In comparison with other database engines, a PDB is a database while the CDB is the underlying container infrastructure that manages one or more PDBs, sharing common background processes and memory.

Create an Oracle database container​

Once the image has been pulled, we can create our container i.e. our Oracle database service.

The list of supported command line flags is here: https://container-registry.oracle.com/ords/ocr/ba/database/enterprise#custom-configurations

If we wish, we can run a docker run command right now but, before, let's make some configuration.

Why? Because the container generation is terribly slow and can take up to ten minutes.

In order to be as efficient as possible, we'll first create a set of files on our disk so while creating the database, Docker will also automatically create some tables and put records in them.

Creation of some configuration files​

Please create a new folder on your disk, jump in it, then create the folder where we'll put our startup files:

$ mkdir -p /tmp/oracle && cd $_
$ mkdir -p scripts/startup

Let's create a Human Resources sample database​

Oracle provides some sample databases on the Oracle Database Sample Schemas page. You can download from there the db-sample-schemas-23.3.zip archive, open it and go to the human_resources folder. You'll found there a file called hr_create.sql which is a sample of an HR database. There is a second file called hr_populate.sql to feed the tables with some data.

Please download and copy these two files in the scripts/startup/sql/ folder you've previously created.

The second thing to do is to create the file scripts/startup/populate_db.sh with the content below so we'll automate the creation of our tables and put some records in our database. As said in a previous chapter, it's important to make sure to connect to the PDB so tables are created in a database; not in the container.

#!/usr/bin/env bash

sqlplus -S sys/admin@localhost:1521/ORCLCDB AS SYSDBA <<EOF?
ALTER SESSION SET CONTAINER = ORCLPDB1;?
CONNECT system/admin@orclpdb1
@/docker-entrypoint-initdb.d/startup/sql/hr_create.sql?
@/docker-entrypoint-initdb.d/startup/sql/hr_populate.sql?
COMMIT;
EXIT;
EOF

Now, make the script executable: chmod +x scripts/startup/populate_db.sh.

If you need the data model, here it is:

Create the OracleDBData volume​

Because running the container is a very slow process, we'll create a Docker volume to keep the database persistent on the disk.

The volume will keep the database on our disk, in a Docker self-managed volume. Like this, we can stop the Oracle container, start it again and we'll not lose our database. The DB will be persistent into a self-manager Docker volume.

Please run this command in the console to create a Docker self-managed volume:

$ docker volume create OracleDBData

Create the oracle network​

And lastly, we'll create a specific network for our Oracle container:

$ docker network create oracle

Ready to create the container​

We're ready to create our container by running this command:

$ docker run -d \
    --name oracle-db \
    --network oracle \
    -p 1521:1521 \
    -p 5500:5500 \
    -e ORACLE_SID=ORCLCDB \
    -e ORACLE_PDB=ORCLPDB1 \
    -e ORACLE_PWD=admin \
    -v OracleDBData:/opt/oracle/oradata \
    -v ./scripts/startup/:/docker-entrypoint-initdb.d/startup \
    container-registry.oracle.com/database/enterprise:latest
▋

The docker run was terribly complex; here is a breakdown:

• --name oracle-db: Our running container will be named oracle-db (so we can access to it later on by running docker exec -it oracle-db [...]),
• --network oracle: We will create our container on the oracle network,
• -p 1521:1521: Oracle use a port number 1521 (Oracle Listener) and we'll expose that port on our machine,
• -p 5500:5500: Optional. Oracle use a second port number 5500 (OEM Express) and we'll expose that port too so we'll be able to access to http://localhost:5500,
• -e ORACLE_SID=ORCLCDB: The Oracle Database SID (SID stands for System identifier) is a very important setting. We'll keep the default value which is ORCLCDB (remember; CDB stands for Container database)
• -e ORACLE_PDB=ORCLPDB1: The Oracle Database PDB name. Here too, we'll keep the default value which is ORCLPDB1. (remember; PDB stands for pluggable database).
• -e ORACLE_PWD=admin: The Oracle database SYS, SYSTEM and PDBADMIN password. There is no default value since the password is auto generated and should be retrieved from the logs (docker log). This parameter modifies the password for the SYS, SYSTEM and PDBADMIN users.
• -v OracleDBData:/opt/oracle/oradata: We'll persist the database on our disk but we don't care where, we'll let Docker manage it. We've previously created a Docker volume named OracleDBData.
• -v ./scripts/startup/:/docker-entrypoint-initdb.d/startup: This will allow us to create files locally in the ./scripts/startup folder (like our SQL files) and run them inside the container once the main Oracle database is ready. And, indeed, we've previously created the script populate_db.sh, sql/hr_create.sql and sql/hr_populate.sql files in our scripts/startup folder.

Important constants to remember​

From now on, our Oracle database server is running as a Docker container. We'll use some constants a lot.

Here are the constants to remember:

• 1521 is the port number to use when connecting to the Oracle DB service,
• oracle-db is the name of our container,
• ORCLCDB is the name of our CDB,
• ORCLPDB1 is the name of our pluggable database,
• admin is the password to use and
• system is the user used when we've created the database

If you look back at the populate_db.sh script, we had:

# highlight-next-line
sqlplus -S sys/admin@localhost:1521/ORCLCDB AS SYSDBA <<EOF?
# highlight-next-line
ALTER SESSION SET CONTAINER = ORCLPDB1;?
# highlight-next-line
CONNECT system/admin@orclpdb1?
@/docker-entrypoint-initdb.d/startup/sql/hr_create.sql?
@/docker-entrypoint-initdb.d/startup/sql/hr_populate.sql?
COMMIT;
EXIT;
EOF

And now, you understand why connection strings were:

• sys/admin@localhost:1521/ORCLCDB: sys is a system user in Oracle, admin the associated password, 1521 our exposed port and ORCLCDB is the name of our container database,
• ALTER SESSION SET CONTAINER = ORCLPDB1;: we need to create our tables and our records in our database (ORCLPDB1) and
• CONNECT system/admin@orclpdb1: system is also a system user in Oracle, it'll be the owner of our tables, admin is the same, associated password and orclpdb1 is the name of what Oracle call the service.

Running an interactive console in the DB container​

If you need to jump in the container, simply run docker exec -it oracle-db bash.

As a fictive example, we can go to the /docker-entrypoint-initdb.d/startup folder inside the container and run our ./populate_db.sh script again (again because was fired by Oracle during the creation of the container):

And of course, we'll get a lot of errors illustrating the fact that tables and records were already created/inserted.

Type exit to quit the console and go back to your host prompt.

Working with the Oracle DB container​

So the docker run command will take something like 10 minutes to run.

If you've Docker Desktop, switch to its interface, click on the Containers link and click on your oracle-db container to see the log. Wait until you see:

You'll get the same "ready to use" screen already teased at the top of this article.

You can also use the command line: docker logs oracle-db --follow and wait until we see the DATABASE IS READY TO USE! message. Press CTRL+C to quit the log.

Now, we can jump in the oracle-db container and run SQL*Plus by running docker exec -it oracle-db sqlplus sys/admin@ ORCLPDB1 as sysdba (remember: we'll connect to the PDB; not the CDB).

A word about schema​

Previously, we've created our tables using the scripts/startup/populate_db.sh script. Here was the script used:

#!/usr/bin/env bash

sqlplus -S sys/admin@localhost:1521/ORCLCDB AS SYSDBA <<EOF?
ALTER SESSION SET CONTAINER = ORCLPDB1;?
CONNECT system/admin@orclpdb1
@/docker-entrypoint-initdb.d/startup/sql/hr_create.sql?
@/docker-entrypoint-initdb.d/startup/sql/hr_populate.sql?
COMMIT;
EXIT;
EOF

The CONNECT statement will use the system Oracle user. Tables that will be created in the system schema.

If we run docker exec -it oracle-db sqlplus sys/admin@ ORCLPDB1 as sysdba and try to display records of the COUNTRIES table; it will not work unless we specify the schema:

This is because the sys user uses the sys schema by default (we can see this by running SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM dual;).

This is why, because our tables are in the system schema; we can access them by running SELECT * FROM SYSTEM.COUNTRIES;.

But, we can also change our user, no more using sys but system by running CONNECT system/admin@ orclpdb1.

Check our fake data​

This understood, here is how we can see our data:

• Run docker exec -it oracle-db sqlplus sys/admin@ ORCLPDB1 as sysdba,
• Then, in SQL*Plus, run CONNECT system/admin@ orclpdb1,
• For esthetic purposes, we'll run SET WRAP OFF and SET PAGESIZE 1000 and
• we can access our tables like this: SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES;

Accessing the Oracle Enterprise Manager Database Express​

Earlier, when running our docker run command, we've mapped the port 5500 from the container on our host. That port is the one used by Oracle Enterprise Manager Database Express (aka OEM Express).

This is a web application we can reach by surfing to https://localhost: 5500/em/.

Connect to it using system / admin and orclpdb1 as container name.

https://localhost:5500/em/

Accessing our database using Oracle tools​

Oracle SQL Developer​

You can download Oracle SQL Developer. It's a ZIP file and once unzipped; you can run the interface without the need to install the software, it's ready-to-use.

Once installed, run it and create a new connection to:

Start to type SELECT * FROM system. and press CTRL+SPACE to get the list of objects in that schema. By scrolling a few, you'll find employees.

Validate or simply type the full SQL SELECT * FROM system.employees. In the example below, I'll ask the list of regions and the list of employees. I will run the query by simply pressing F5 — the same result already shown at the top of this article.

Connect to Oracle SQL Developer using the correct user​

In the previous chapter, we've established a login using sys for the username and for that reason, if we click in the Tables (Filtered) menu in the left tree view, we'll get a bunch of tables but not the one of the system user like our employees table.

Now, by unfolding the list of tables, we can see ours:

Access our Oracle DB container using Oracle SQLPlus​

Another way would be to use the official sql*plus Docker image like this:

$ docker run --rm -it --network oracle oracletools/sqlplus:v19.18_lin SYS/admin@oracle-db:1521/orclpdb1 as sysdba▋

Once connected, for instance, we can get the list of countries like this:

Going further​

Now that the oracle-db container is running, two follow-up articles reuse it directly: Transform an Oracle DB as OpenData using Oracle REST Data Services and Accessing an Oracle database using .Net, NodeJS, PHP and Python.