Install & Run Oracle APEX in a Docker Environment

Why This Repository

So far, with the manuals both on Oracle's website as well as the ones found on blogs etc. I never really succeeded in putting up the APEX stack on docker.

However, starting to try with some more (docker) experience after procrastinating APEX in the recent 3 years, and with new energy, I found some key hints to get it done.

Content

This docker project contains the following:

TL;DR

Here's the recipe to get this done:

Detailed Instructions

0 - Prerequisites

Image Sources

I wanted to use the following images:

Express DB:
- https://container-registry.oracle.com/ords/ocr/ba/database/express as opposed to
- https://container-registry.oracle.com/ords/ocr/ba/database/free)
ORDS:
- https://container-registry.oracle.com/ords/ocr/ba/database/ords as opposed to
- https://container-registry.oracle.com/ords/ocr/ba/database/ords-developer

The ords-developer image does not contain and install APEX, but there's a solution.

Naming Conventions

A few remarks about the names of objects: I generally try to name docker containers, networks etc. by using prefixes so I can identify them easier in, e.g., lists (docker ls ...) or when searching for their repositories (directories).

In this context, rad is short for Rapid Application Development, and, since there are several platforms, oracle-apex is the second prefix. expressand ords are then the specifying parts of the name.

In the case of docker service names, I use only express and ords because those service names are referred to only within the compose file.

1 - Prepare Directories For Persistent Data & Define Parameters ✔️

Start in the docker project's direcctory. According to Oracle, the following rights have to be applied to the directory ./express/oradata:

-v /opt/oracle/oradata
                  The data volume to use for the database.
                  Has to be writable by the Unix "oracle" (uid: 54321) user inside the container.
                  If omitted the database will not be persisted over container recreation.

Folders for Express & ORDS ✔️✔️

sudo bash -c '
  mkdir -p ./express/{oradata,cfgtoollogs,scripts/startup,scripts/setup} && 
  chown -R 54321:54321 ./express/{oradata,cfgtoollogs} &&
  mkdir -p ./ORDS/{variables,config} &&
  chown -R 54321:54321 ./ORDS/{config,variables} &&
  chmod -R 777 ./ORDS/config
'

The cfgtoollogs-diretory is for analysis in case of database creation failure (./cfgtoollogs/dbca/XE/XE.log).

.env File

Create .env file containing a ORACLE_PWD variable and a password (do not use special characters, only numbers, small and caps for compatibility reasons; Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts):

ORACLE_PWD=<password without quotes of any kind>, e.g., 1230321abcABC.

Script: ✔️✔️

#!/bin/bash

# Prompt user for ORACLE_PWD
read -p "Enter a value for ORACLE_PWD: " ORACLE_PWD

# Write the variable to .env file
echo "ORACLE_PWD=$ORACLE_PWD" > ./.env

echo "Password has been written to ./.env"

2 - Download & Extract APEX Files ✔️✔️

Download and extract the latest APEX files to the project directory; the APEX ZIP file contains the apex directory as root, so no extra dir has to be created.

If you have unzip: ✔️✔️

curl -o apex.zip https://download.oracle.com/otn_software/apex/apex-latest.zip && \
unzip -o apex.zip

If you have 7z (e.g., Synology NAS): ✔️✔️

curl -o apex.zip https://download.oracle.com/otn_software/apex/apex-latest.zip && \
7z x apex.zip

The files should now reside in ./apex.

3 - Pull Docker Images ✔️✔️

Option 1 - loop ✔️✔️

for img in express ords; do
  docker pull "container-registry.oracle.com/database/$img:latest"
done

Option 2 - xargs

echo "container-registry.oracle.com/database/"{express,ords}":latest" \
  | xargs -n1 docker pull

4 - Create & Run Temporary Express Container to Setup Persistent DB ✔️✔️

Run the following command to ✔️✔️

create the network rad-oracle-apex-temp
create and run the container rad-oracle-apex-express-temp
set up a persistent database (stored in ./express/oradata)

docker network create rad-oracle-apex-temp & \
docker run \
    -d \
    --name rad-oracle-apex-express-temp \
    --network rad-oracle-apex-temp \
    --hostname express \
    --env-file ./.env \
    -p 1521:1521 \
    -v "$(pwd)/express/oradata/:/opt/oracle/oradata" \
    -v "$(pwd)/express/cfgtoollogs/:/opt/oracle/cfgtoollogs" \
    -v "$(pwd)/apex/:/opt/oracle/oradata/apex" \
    container-registry.oracle.com/database/express:latest && \
docker logs -f rad-oracle-apex-express-temp

Note

Note that
- -e ORACLE_PWD=${ORACLE_PWD} as in the original documentation has been removed from the script above because the password is defined in the .env file
- running the container for the first time (initialization of persistent data) takes a long time - on my Synology DS918+, it took ~2.5hrs, on a laptop, however, it takes, e.g., under 10 minutes

Important

If the first time fails, a second run with the code above might solve it.

Keep the container running for the next steps of the installation (until you start the containers with docker-compose).

5 - Install APEX ✔️

Download APEX ✔️

Already done in the preparation steps above.

Install APEX in the Express DB ✔️

Create a shell in the express container:

docker exec -it rad-oracle-apex-express-temp bash

Change to the mounted apex directory:
```
cd /opt/oracle/oradata/apex
```
Connect to the DB XEPDB1:
- In separate steps:
  1. Start SQL:
```
sqlplus /nolog
```
    (note that unlike described in the documentation, steps 4 and 6, instead of sql, sqlplus is used)
  2. Connect to DB XEPDB1:
    - With extra PW prompt:
      1. CONNECT SYS@<express hostname>:1521/XEPDB1 as SYSDBA
      2. enter PW (defined in .env-file)
    - With PW in command:
      CONNECT SYS/<ORACLE_PWD>@<express hostname>:1521/XEPDB1 as SYSDBA
- In single step:
```
cd /opt/oracle/oradata/apex && sqlplus sys/${ORACLE_PWD}@<express hostname>:1521/XEPDB1 AS SYSDBA
```
  e.g.,
```
cd /opt/oracle/oradata/apex && sqlplus sys/${ORACLE_PWD}@express:1521/XEPDB1 AS SYSDBA
```
  (note that ${ORACLE_PWD} does not have to be replaced here since taken from the environment variable in this case; also, this will be used for the CONN_STRING file below, but there, ORACLE_PWD needs to be explicit)
Run install script: @apexins.sql SYSAUX SYSAUX TEMP /i/ After successful installation, leave SQL open for the next step

Final Preparations

Create the Instance Administration Account: In the same SQL prompt as before, enter
```
@apxchpwd.sql
```
to create the workspace admin account.
Unlock APEX_PUBLIC_USER account: In the same SQL prompt as before, enter
```
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
```

Change password (optional?):

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY <new_password>;

Unlimit account expiration: From a blog post:

Create unlimited expiration profile apex_public:

create profile apex_public limit
  password_life_time unlimited;

Assign profile to user:

alter user apex_public_user
  profile apex_public;

quit the SQL prompt
exit the container's bash

6 - Run Temporary ORDS-Developer Container to Setup the Connection to the Express DB ✔️✔️

Note

Things have changed since release of ORDS v25. A container can be started in 2 ways:

with an interactive CLI can be started in order to generate the configuration files in ./ORDS/config and establish the connection between ORDS and Express:

docker run \
  -it \
  --rm \
  --network rad-oracle-apex-temp \
  --name rad-oracle-apex-ords-temp \
  -v ./ORDS/config:/etc/ords/config \
  container-registry.oracle.com/database/ords:latest \
  install

automated setup (no interaction) - works for this tutorial:

docker run \
  -i \
  --rm \
  --network rad-oracle-apex-temp \
  --name rad-oracle-apex-ords-temp \
  -v ./ORDS/config:/etc/ords/config \
  -v ./apex/:/opt/oracle/apex \
  container-registry.oracle.com/database/ords:latest \
  install \
    --admin-user SYS \
    --db-hostname express \
    --db-port 1521 \
    --db-servicename XEPDB1 \
    --feature-sdw true \
    --password-stdin < <(grep '^ORACLE_PWD=' .env | cut -d= -f2-)

Double-check if the network name is correct (must be same as for the Express temporary container).

7 - Remove Temporary Containers ✔️✔️

docker rm -f rad-oracle-apex-{ords-temp,express-temp}

8 - Run APEX with Docker Compose ✔️✔️

services:
  express: # XE database
    image: container-registry.oracle.com/database/express:latest # 21.3.0-xe
    container_name: rad-oracle-apex-express
    # hostname: oracledev
    restart: unless-stopped
    environment:
      - ORACLE_PWD=${ORACLE_PWD} # make sure the declaration is in the .env file as ORACLE_PWD=<your password, non complex, min. 8 chars small, cap, & numbers>
    networks:
      - apex
    #ports:
    #  - 1521:1521
    #  - 5500:5500
    # depends_on:
    #   - oracle-ords
    volumes:
      - ./express/oradata:/opt/oracle/oradata
      - ./express/scripts/setup:/opt/oracle/scripts/setup
      - ./express/scripts/startup:/opt/oracle/scripts/startup
      - ./apex:/opt/oracle/apex
    healthcheck:
      #test command below is with grep because in my case, the output of checkDBstatus.sh is always "The Oracle base remains unchanged with value /opt/oracle" which seems to indicate the DB is fine.
      #test: /opt/oracle/checkDBStatus.sh | grep -q 'remains unchanged'
      test: [ "CMD", "/opt/oracle/checkDBStatus.sh"]
      interval: 30s
      timeout: 30s
      retries: 100
      start_period: 600s

  ords:
    #image: container-registry.oracle.com/database/ords-developer:latest
    image: container-registry.oracle.com/database/ords:latest
    container_name: rad-oracle-apex-ords
    restart: unless-stopped
    #depends_on:
    #  express:
    #    condition: service_healthy
    volumes:
      - ./ORDS/variables:/opt/oracle/variables
      - ./ORDS/config:/etc/ords/config
      - ./apex/:/opt/oracle/apex
    networks:
      - apex
    ports:
    #- 8181:8181 this is for the developer image
    - 8080:8080

networks:
  apex:
    name: rad-oracle-apex

In case you want to debug the healthcheck, use docker inspect --format "{{json .State.Health }}" rad-oracle-apex-express | jq:

Once the express-container has started up, the exit code changes from 3 over 2 to 0 (0=healthy).

9 - Log In

APEX Workspace

Go to your instance's APEX homepage, e.g., http://<docker-host>.
Select Oracle APEX (the middle pane)
Login:
- Workspace: internal
- User: ADMIN
- Password: Welcome_1

Warning

If you changed the password during log-in check from running the temporary ORDS-Developer container, use the updated password!

APEX Administration

Go to your instance's APEX homepage, e.g., http://<docker-host>.
Select Oracle APEX (the middle pane)
Go to the bottom of the page and select Administration in the Tasks column
Login:
- User: admin
- Password: The one you changed the default password Welcome_1 to

SQL Developer Web (SDW): Set Up & Log In

Well, that's a whole different story: The workspace/database schema needs to be enabled for SDW as follows:

Log into the express container's CLI:
- To get to the SQL prompt directly: docker exec -it oracle-apex-express sqlplus sys/<ORACLE_PWD>@//localhost:1521/XEPDB1 as sysdba
- Via shell: docker exec -it oracle-apex-express sh and then enter sqlplus sys/<ORACLE_PWD>@//localhost:1521/XEPDB1 as sysdba at the prompt

Now, the following must be entered:

BEGIN
 ords_admin.enable_schema(
  p_enabled => TRUE,
  p_schema => 'schema-name',
  p_url_mapping_type => 'BASE_PATH',
  p_url_mapping_pattern => 'schema-alias',
  p_auto_rest_auth => NULL
 );
 commit;
END;
/

Important

The value of p_schema (schema-name) has to be all upper case!
The value of p_url_mapping_pattern (schema-alias) has to be all lower case!
The / at the end of the statement is important in order to execute the statement.¹

E.g.,

BEGIN
 ords_admin.enable_schema(
  p_enabled => TRUE,
  p_schema => 'WORKSPACE1',
  p_url_mapping_type => 'BASE_PATH',
  p_url_mapping_pattern => 'workspace1',
  p_auto_rest_auth => NULL
 );
 commit;
END;
/

Optional: Verify if schema (= value used for p_schema) has been enabled with select username from all_users order by username
Change user password via SQL prompt: alter user <user> identified by <password>; (replace <user) - every workspace, in the database, is basically a user, hence this step
Go to http(s)://<domain name>/ords/sql-developer and log in with the credentials used above

Warning

There seems to be a bug in ORDS which prevents objects from loading in SDW's Data Modeler and SQL Navigator. The DB user (equals workspace name) therefore needs to be granted resource permissions as follows:

grant <privilege> to <user>

This solution might work, with me, it didn't. However, the ORDS update from Nov. 8, 2024, solved the issue.

10 - Access APEX from WAN with HTTPS / Reverse Proxy

Put the following 2 lines into ./ORDS/config/global/settings.xml, replacing <your apex domain, no trailing slash> with your domain's name:

<entry key="security.externalSessionTrustedOrigins">http://<your apex domain, no trailing slash>, https://<your apex domain, no trailing slash>:443</entry>
<entry key="security.forceHTTPS">true</entry>

The complete settings.xml might now look similar to:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Mon Oct 21 16:07:13 UTC 2024</comment>
<entry key="database.api.enabled">true</entry>
<entry key="db.invalidPoolTimeout">5s</entry>
<entry key="debug.printDebugToScreen">true</entry>
<entry key="standalone.static.path">/opt/oracle/apex/images</entry>
<entry key="security.externalSessionTrustedOrigins">http://<your apex domain, no trailing slash>, https://<your apex domain, no trailing slash>:443</entry>
<entry key="security.forceHTTPS">true</entry>
</properties>

11 - Update

APEX

Perform all steps described in Install APEX
Stay connected to DB in sqlplus
Delete old schema
- Enter select username from dba_users;
- Identify old schema (starts with APEX_ and the old version ID, e.g. APEX_240100) as opposed to new schma (same user name with a higher version ID)
- Enter drop user <user name> cascade;, e.g. drop user APEX_240100 cascade;
- If an error message is returned (e.g., ORA-28014: cannot drop administrative user or role), enter alter session set "_oracle_script"=TRUE;and repeat the previous command

Update ORDS

ORDS has been released in a v25. In order to configure the ORDS images (starting with v25), run a container and start the bash:

docker run -it --name ords_new -v ./ORDS/config:/etc/ords/config container-registry.oracle.com/database/ords:latest <command>

E.g.,

docker run -it --name ords_new -v ./ORDS/config:/etc/ords/config container-registry.oracle.com/database/ords:latest install

Docker Installation Sources

Sources used for new attempt

Oracle:
Schnell und einfach: Erstellen einer lokalen APEX-Umgebung mit Docker-Compose [https://blog.ordix.de/erstellen-einer-lokalen-apex-umgebung-docker-compose]
https://github.com/akridge/oracle-apex-docker-stack?tab=readme-ov-file#how-to-install
Docker Compose for Oracle APEX (https://tm-apex.hashnode.dev/docker-compose-for-oracle-apex)
Oracle 23c Free Docker, APEX & ORDS – all in one simple guide - Pretius (https://pretius.com/blog/oracle-apex-docker-ords/)
... and, finally, for SSL: How to Secure Oracle APEX Development Environment with Free SSL from Let's Encrypt?

Sources used years ago

... and a few more.

APEX Programming

Report Generation
- Responsive Classic Report in ORACLE APEX [https://blogs.ontoorsolutions.com/post/responsive-classic-report-in-oracle-apex/]
- How to control interative report columns [https://forums.oracle.com/ords/apexds/post/how-to-control-interactive-report-columns-width-9688]
Authentication
- How to log in with user credentials from database table [https://forums.oracle.com/ords/apexds/post/how-to-log-in-with-user-credentials-from-database-table-6626]
- Custom Authentication in Oracle APEX [https://o7planning.org/10443/custom-authentication-in-oracle-apex]

Integration With 3rd Party Tools

Keycloak

See also https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/slash.html ↩

Name		Name	Last commit message	Last commit date
Latest commit History 120 Commits
backup		backup
LICENSE		LICENSE
README.md		README.md
google03546b466acb9ee6.html		google03546b466acb9ee6.html
install-apex-ords.sh		install-apex-ords.sh

License

schnillerman/oracle-apex-docker

Folders and files

Latest commit

History

Repository files navigation