Installing ORDS 22.2 and Oracle APEX 21.2 on WSL-2/Docker

What are we installing?

• Oracle APEX (Application Express)
• ORDS (Oracle REST Data Services)
• On an Oracle Database and Tomcat Server (Using Installing Oracle Database and Tomcat on WLS-2/Docker)

Is this a best practice?

• It is much easier to avoid the APEX/ORDS installation by Getting Started with Oracle APEX Free Tier

• To continue with this APEX/ORDS installation, an active Oracle Support Identifier is required to download the latest patch for recent APEX/ORDS releases.

• There are several ways to obtain an active Oracle Support Identifier:
• Ask your employer/client to add you to their support contract.
• Become an Oracle Partner Member (Development Only).
• Purchase an Oracle Database Personal Edition (On-Premises) with Support (Development Only).
  

What do we need?

• Oracle Installation Files for APEX and ORDS
• APEX installation zip file (We will be using "apex_21.2_en.zip")
• Patch Set Bundle for Oracle APEX 21.2 (Requires Oracle Support Login.  The simplest way to get an Oracle Support Login is to purchase an Oracle Database Personal Edition License with support at Oracle Store. We renamed "p33420059_212_GENERIC.zip" to "apex_p33420059_212_GENERIC.zip")
• ORDS installation zip file (We will be using "ords-22.2.0.172.1758.zip")
• The Windows "W:" drive should be mapped to "\\wsl$\Ubuntu".
• The following requires an Oracle Database and Tomcat server setup and running. For this example, I am using a setup described at Installing Oracle Database and Tomcat on WSL-2/Docker. I have both of these are running in Docker:

• Note: ORDS 22.2 requires Oracle JDK version 11 or higher.  The "tomcat:9.0" docker image includes OpenJDK 11.0.13.  "Oracle JOK and OpenJDK builds are essentially identical from Java 11 onward"
• These Docker Volume mappings should also be in place, per the Blog Post:

Container Name   WSL2/Ubuntu Path         Docker Path

OraEE213         /opt/install_files       /opt/install_files

TomCat9r0        /opt/install_files       /opt/install_files

Note: There is an excellent reference at Oracle-Base that includes setup of ORDS 22.1 onward and a link to setup ORDS versions previous to 22.1.

Overview:

1. Windows Setup Files for Installation
2. Ubuntu Setup Files for Installation
3. Create a PDB
4. Install APEX in the New PDB
5. Patch APEX in the New PDB
6. Install APEX Images on Tomcat
7. Configure and Install ORDS for the PDB on Tomcat

1) Windows Setup Files for Installation

Move/Copy these files to C:\tmp

• apex_21.2_en.zip
• apex_p33420059_212_GENERIC.zip
• ords-22.2.0.172.1758.zip

2) Setup APEX/ORDS Files for Installation

1. Start the Ubuntu App
2. Run These Commands

sudo su -

apt install unzip

mkdir /opt/install_files

cd /opt/install_files

#

unzip /mnt/c/tmp/apex_21.2_en.zip

mv apex apex212

#

unzip /mnt/c/tmp/apex_p33420059_212_GENERIC.zip

mv 33420059 apex212/p33420059

#

mv apex212/images apex212_images

cp -rv apex212/p33420059/images/* apex212_images/

rm -rf apex212/p33420059/images

#

chown -Rv 54321:54321 apex212

#

mkdir ords222

cd ords222

unzip /mnt/c/tmp/ords-22.2.0.172.1758.zip

3) Create a PDB

1. Open Docker Desktop.
2. Find the "OraEE213" container.
3. Click the ">_" Icon (CLI) for that container to open a new window.
4. Run "sqlplus / as sysdba" to startup SQL*Plus and connect to the database.
5. Run the commands below in SQL*Plus:

create pluggable database "AP212PDB"

   admin user "PDB_ADMIN" identified by "PDB_ADMIN"

   default tablespace users

      datafile '/opt/oracle/oradata/EE213CDB/AP212PDB/users01.dbf'

               size 5M autoextend on

   FILE_NAME_CONVERT = ('pdbseed', 'AP212PDB')

   STORAGE UNLIMITED TEMPFILE REUSE;

alter pluggable database "AP212PDB" open;

*NOTE:* To remove the new PDB run `drop pluggable database "AP212PDB" including datafiles;`

4) Install APEX in the New PDB

Login to the Docker Container that is running the Oracle EE 21.3 Database.

1. Open Docker Desktop.
2. Find the "OraEE213" container.
3. Click the ">_" Icon (CLI) for that container to open a new window.
4. Run "cd /opt/install_files/apex212" to move into the apex folder.
5. Run "sqlplus / as sysdba" to startup SQL*Plus and connect to the database.
   
6. Run the commands below in SQL*Plus:

alter session set container = AP212PDB;

set serveroutput on size unlimited format wrapped

@apxsilentins.sql "SYSAUX" "SYSAUX" "TEMP" "/apex212_images/" \

  "Passw0rd!" "Passw0rd!" "Passw0rd!" "Passw0rd!"

select status, owner, count(*)
 from  dba_objects
 where owner in ('APEX_210200', 'FLOWS_FILES', 'APEX_LISTENER')
 group by status, owner
 order by status, owner;

alter user APEX_210200 identified by "Passw0rd!" account unlock;

NOTE: apxsilentins.sql values:

• SYSAUX - Default Tablespace for APEX application user
• SYSAUX - Default Tablespace for APEX file user
• TEMP - APEX Temporary Tablespace for Tablespace Group
• /apex212_images/ - Virtual Directory in Tomcat for APEX Images"
• Passw0rd! - APEX Public User Account
• Passw0rd! - APEX Listener Account
• Passw0rd! - APEX REST Public User Account
• Passw0rd! - APEX Internal Administrator User Account

5) Patch APEX in the New PDB

Login to the Docker Container that is running the Oracle EE 21.3 Database.

1. Open Docker Desktop.
2. Find the "OraEE213" container.
3. Click the ">_" Icon (CLI) for that container to open a new window.
4. Run "cd /opt/install_files/apex212/p33420059" to move into the apex folder.
5. Run "sqlplus / as sysdba" to startup SQL*Plus and connect to the database.
   
6. Run the commands below in SQL*Plus:

alter session set container = AP212PDB;

set serveroutput on size unlimited format wrapped

@catpatch.sql

6) Install APEX Image Files on Tomcat

1. Open Docker Desktop.
2. Find the "TomCat9r0" container.
3. Click the ">_" Icon (CLI) for that container to open a new window.
4. Run "cd /opt/install_files/apex212_images" to move into the APEX Images folder.
5. Run "cp -rv . "${CATALINA_HOME}/webapps/apex212_images""
6. Confirm images are working using http://127.0.0.1:8080/apex212_images/apex_ui/img/apex-logo.svg.  A red oval with the work APEX should appear in the browser.

7) Configure and Install ORDS for the PDB on Tomcat

After I published this BLOG, I re-ran the scripts.  I found errors and missing information.  I will update as soon as possible.

1. Open Docker Desktop.
2. Find the "TomCat9r0" container.
3. Click the ">_" Icon (CLI) for that container to open a new window.
4. Run the commands below:

export PATH="$PATH:/opt/install_files/ords222/bin"

exec /bin/bash

export ORDS_CONFIG=/opt/ords222_config

mkdir "${ORDS_CONFIG}"

cd "${ORDS_CONFIG}"

ords install \

  --log-folder      /opt/ords222_config/logs \

  --feature-db-api  true \

  --admin-user      SYS \

  --db-hostname     OraEE213 \

  --db-port         1521 \

  --db-servicename  AP212PDB \

  --db-user         ORDS_PUBLIC_USER \

  --feature-rest-enabled-sql true \

  --feature-sdw     true \

  --gateway-mode    proxied \

  --gateway-user    APEX_PUBLIC_USER \

  --proxy-user      \

  --password-stdin  <<EOF

OraEE213#!

Passw0rd!

Passw0rd!

EOF

ords config set security.verifySSL false

ords war ords222_ap212pdb.war

cp ords222_ap212pdb.war "${CATALINA_HOME}/webapps/"

• Test Oracle APEX URL:
• Paste "http://localhost:8080/ords222_ap212pdb" into a Browser
• Workspace: INTERNAL
• Username: ADMIN
• Password: Passw0rd!
• The APEX Administrator Page should appear
• Test Web Based SQL*Developer:
• Paste "http://localhost:8080/ords222_ap212pdb/sql-developer" into a Browser
• Enter a Database User Name when prompted.
• Enter a Database User Password when prompted.
• The "Database Actions | Launchpad" page should appear.
• REST Enable a Database Object:
• Login to APEX as an APEX Developer.
• Open the SQL Workshop.
• Select Object Browser.
• Select a table.
• Click on the REST tab
• "Rest Enable Object": YES
• "Authentication Required": NO
• Click on APPLY
• Copy the RESTful URL
• Past the URL into a Web Browser
• A JSON document should return

Removal

1. Remove ORDS from Tomcat
2. Unplug the PDB

1) Remove ORDS from Tomcat

WARNING: This will remove the ORDS installation.  It will undo the work done in Step 7.

rm -f "${CATALINA_HOME}/webapps/ords222_ap212pdb.war

rm -rf "${CATALINA_HOME}/webapps/ords222_ap212pdb

2) Unplug the new PDB

WARNING: This will remove the PDB from the Database.  It will undo the work done in Step 3.

alter pluggable database "AP212PDB" close immediate;

alter pluggable database "AP212PDB" unplug

  into '/opt/oracle/oradata/EE213CDB/AP212PDB/AP212PDB.XML';

drop pluggable database "AP212PDB" keep datafiles;

zip -q ./AP212PDB_PDB.zip /opt/oracle/oradata/EE213CDB/AP212PDB/*

rm -rf /opt/oracle/oradata/EE213CDB/AP212PDB/*