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 PathOraEE213 /opt/install_files /opt/install_filesTomCat9r0 /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:
- Windows Setup Files for Installation
- Ubuntu Setup Files for Installation
- Create a PDB
- Install APEX in the New PDB
- Patch APEX in the New PDB
- Install APEX Images on Tomcat
- 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
- Start the Ubuntu App
- 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
- Open Docker Desktop.
- Find the "OraEE213" container.
- Click the ">_" Icon (CLI) for that container to open a new window.
- Run "sqlplus / as sysdba" to startup SQL*Plus and connect to the database.
- 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.
- Open Docker Desktop.
- Find the "OraEE213" container.
- Click the ">_" Icon (CLI) for that container to open a new window.
- Run "cd /opt/install_files/apex212" to move into the apex folder.
- Run "sqlplus / as sysdba" to startup SQL*Plus and connect to the database.
- Run the commands below in SQL*Plus:
alter session set container = AP212PDB;
set serveroutput on size unlimited format wrapped
"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;
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.
- Open Docker Desktop.
- Find the "OraEE213" container.
- Click the ">_" Icon (CLI) for that container to open a new window.
- Run "cd /opt/install_files/apex212/p33420059" to move into the apex folder.
- Run "sqlplus / as sysdba" to startup SQL*Plus and connect to the database.
- 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
- Open Docker Desktop.
- Find the "TomCat9r0" container.
- Click the ">_" Icon (CLI) for that container to open a new window.
- Run "cd /opt/install_files/apex212_images" to move into the APEX Images folder.
- Run "cp -rv . "${CATALINA_HOME}/webapps/apex212_images""
- 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.- Open Docker Desktop.
- Find the "TomCat9r0" container.
- Click the ">_" Icon (CLI) for that container to open a new window.
- 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 <<EOFOraEE213#!Passw0rd!Passw0rd!EOF
ords config set security.verifySSL false
ords war ords222_ap212pdb.war
cp ords222_ap212pdb.war "${CATALINA_HOME}/webapps/"
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.
- Open Docker Desktop.
- Find the "TomCat9r0" container.
- Click the ">_" Icon (CLI) for that container to open a new window.
- 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
- Remove ORDS from Tomcat
- 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" 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/*
drop pluggable database "AP212PDB" keep datafiles;
zip -q ./AP212PDB_PDB.zip /opt/oracle/oradata/EE213CDB/AP212PDB/*
rm -rf /opt/oracle/oradata/EE213CDB/AP212PDB/*
