Docker Oracle and APEX

Update: I’ve moved my setup process to Dockerize Oracle and APEX to Github. It will be maintained with the latest steps I’m using it. The repository is docker-oracle-setup.

Last week I needed to test an Oracle 12.2 feature and got hooked on Docker. I wrote an article on how to setup an Oracle DB 12.2 Docker container. This solved my goal to test the 12.2 feature. Roel Hartman then wrote a followup article about how to setup an Oracle database in a Docker container but preserve the data on his laptop. I.e. if the container was deleted or needed to be rebuilt his database data wouldn’t be lost.

Roel’s article inspired me to setup an entire Dockerized Oracle and APEX environment, thus replacing my current local VM infrastructure. I took a lot of notes about the process. The result is this blog post which contains everything I did to create my containers and link them together. To get it all working I used the following articles and all my scripts are a result of a combination of the code found in the links.

Background

A few things to keep in mind that will help when reading the rest of this article:

  • All my scripts are Linux / MacOS focused. If you use a Windows machine you’ll need to translate
  • I specifically made reference to “your laptop” to emphasize what was run “on your machine” vs “in a docker container”

The following key configurations were used for the containers:

Port Mapping

Container Port Description
oracle 32712 TNS listener
ords 32713 ORDS

Passwords

Container Username Password Description
oracle sys Oradoc_db1
oracle admin oracle Workspace Internal for APEX admin

Download Files

Due to licensing restrictions I can’t host/provide these files in Github or elsewhere. As such you’ll need to download them manually. Download the following files and store them in your Downloads folder

Laptop Folder Structure

The following script will create a folder structure that looks like:

Path Description
~/docker root
~/docker/apex To host APEX installation and images for each version
~/docker/apex/5.1.3 APEX 5.1.3 installation files
~/docker/oracle Hold Oracle 12.2 data files
~/docker/ords ORDS Dockerfile (to build ORDS image)
~/docker/tmp Temp folder 
1
2
3
4
5
6
7
8
9
10
mkdir ~/docker

# The APEX folder structure allows for multiple versions of APEX to be hosted by different ORDS instances
mkdir ~/docker/apex
mkdir ~/docker/apex/5.1.3

# To store oracle data
mkdir ~/docker/oracle
#Oracle REST Data Services
mkdir ~/docker/ords

Move Downloaded Files

1
2
3
4
5
6
7
8
9
10
11
12
13
# ORDS (done in ORDS section)

# APEX
cd ~/docker/apex/
mv ~/Downloads/apex_5.1.3.zip .
unzip apex_5.1.3.zip
mv apex 5.1.3

# Oracle Docker files
mkdir ~/docker/tmp
cd ~/docker/tmp
mv ~/Downloads/docker-images-master.zip .
mv ~/Downloads/linuxx64_12201_database.zip .

Docker

Build Oracle Docker Image

This will be a different setup then I previously blogged about in that this docker container will separate the data from the app. I.e. we can remove the container and rebuild it while keeping all the data in place. This allows for us to

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
cd ~/docker/tmp
unzip docker-images-master.zip
mv linuxx64_12201_database.zip docker-images-master/OracleDatabase/dockerfiles/12.2.0.1
cd docker-images-master/OracleDatabase/dockerfiles
./buildDockerImage.sh -v 12.2.0.1 -e

# Once completed should see a message like:
# Successfully built ad2c10d804a7
# Successfully tagged oracle/database:12.2.0.1-ee
#
#   Oracle Database Docker Image for 'ee' version 12.2.0.1 is ready to be extended:
#
#     --> oracle/database:12.2.0.1-ee
#
#   Build completed in 678 seconds.

Setup Docker Network

In order for the containers to “talk” to each other we need to setup a Docker network and associate all the containers on this network. Containers can reference each other by their respective container names. When referencing another container on the same Docker network the port used is the container’s native port not the mapped port on your laptop.

1
2
3
4
5
6
7
8
9
10
docker network create oracle_network

# Other docker network commands (don't need to run them as part of install)

# Connect and existing container to a docker network
# docker network connect <network name> <container name>

# View a network and connected containers
# In this example "oracle_network" is the network we're interested in
docker network inspect oracle_network

Create Oracle Docker Container

The following command will create and run the Oracle Docker container. It’s TNS listener will be accesible via port 32712 on your laptop. The reference to the APEX installation files are necessary only whe installing APEX.

Adding the -e TZ will set the appropriate timezine for the OS and the database. A full list of timezones can be found here. If excluded it will default to UTC.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
docker run \
  --name oracle \
  --network=oracle_network \
  -e TZ=America/Edmonton \
  -p 32712:1521 \
  -v ~/docker/oracle:/opt/oracle/oradata \
  -v ~/docker/apex/5.1.3:/tmp/apex-install \
  oracle/database:12.2.0.1-ee

# Once the Docker image is working you'll see something like:
# #########################
# DATABASE IS READY TO USE!
# #########################
# It will then have a bunch of other information showing which you can ignore.

In another terminal tab, set the sys password to Oradoc_db1 and install APEX

1
docker exec oracle ./setPassword.sh Oradoc_db1

Install APEX

In a terminal tab on your laptop run:

1
2
3
4
5
# Install and configure APEX
docker exec -it oracle bash -c "source /home/oracle/.bashrc; bash"

cd /tmp/apex-install
sqlplus sys/[email protected]/orclpdb1 as sysdba

The above command will open a SQL prompt. Run the following scripts:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Install APEX
@apexins.sql SYSAUX SYSAUX TEMP /i/

-- APEX REST configuration
@apex_rest_config_core.sql oracle oracle

-- Required for ORDS install
alter user apex_public_user identified by oracle account unlock;

-- From Joel's blog: "Create a network ACE for APEX (this is used when consuming Web services or sending outbound mail):"
declare
    l_acl_path varchar2(4000);
    l_apex_schema varchar2(100);
begin
    for c1 in (select schema
                 from sys.dba_registry
                where comp_id = 'APEX') loop
        l_apex_schema := c1.schema;
    end loop;
    sys.dbms_network_acl_admin.append_host_ace(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
        principal_name => l_apex_schema,
        principal_type => xs_acl.ptype_db));
    commit;
end;
/

-- Setup APEX Admin password
begin
    apex_util.set_security_group_id( 10 );
    apex_util.create_user(
        p_user_name => 'ADMIN',
        p_email_address => '[email protected]',
        p_web_password => 'oracle',
        p_developer_privs => 'ADMIN' );
    apex_util.set_security_group_id( null );
    commit;
end;
/

-- Exit SQL
exit

Now exit bash:

1
2
# Exit bash
exit

Create ORDS Container

The following assumes that you’ve downloaded ORDS 3.0.12. Referencing the ORDS version so that can create ORDS images for each ORDS release.

The scripts below will first create the ORDS Docker image then create the containers.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# Uses https://github.com/martindsouza/docker-ords Dockerfile
cd ~/docker/ords
git clone [email protected]:martindsouza/docker-ords.git .
mv ~/Downloads/ords.3.0.12.263.15.32.zip ~/docker/ords
# Only need ords.war from install
unzip ~/docker/ords/ords.3.0.12.263.15.32.zip ords.war

# Build the Docker Image
docker build -t ords:3.0.12 .

# Run ORDS
# Note: Leave DB_PORT=1521 as this is NOT the port that maps to your laptop,
# rather the internal docker network that was created. I.e. container to container
docker run -t -i \
  --name ords \
  --network=oracle_network \
  -e DB_HOSTNAME=oracle \
  -e DB_PORT=1521 \
  -e DB_SERVICENAME=ORCLPDB1 \
  -e APEX_PUBLIC_USER_PASS=oracle \
  -e APEX_LISTENER_PASS=oracle \
  -e APEX_REST_PASS=oracle \
  -e ORDS_PASS=oracle \
  -e SYS_PASS=Oradoc_db1 \
  --volume /Users/giffy/docker/apex/5.1.3/images:/usr/local/tomcat/webapps/i \
  -p 32713:8080 \
  ords:3.0.12

You should now be able to go to localhost:32713/ords/ on your laptop to run APEX

Useful Commands

Docker Start/Stop Containers

1
2
3
4
5
6
7
8
docker stop -t 200 oracle
docker stop -t 200 ords

# Start containers
docker start oracle

# Wait a minute to start ORDS as Oracle will take a few minutes to boot
docker start ords

Connect to DB from laptop

sqlcl is my alias for SQLcl. More information on how to install SQLcl on MacOS here.

1
2
3
4
5
# CDB (not usually required)
sqlcl sys/[email protected]:32712:orclcdb as sysdba
# PDB
sqlcl sys/[email protected]:32712/orclpdb1 as sysdba
sqlcl martin/[email protected]:32712/orclpdb1

Create New DB User

1
2
3
4
define new_user = 'martin'
create user &new_user. identified by &new_user. container = current;
grant connect, resource, create any context to &new_user;
alter user &new_user quota unlimited on users;