Integration of APEX, ORDS, Tomcat, and Apache HTTP Server (APEXOTA) ‐ PART 1 - symbolthree/apex2ebs GitHub Wiki

For Oracle developers, APEX is definitely one of the most important platforms for web application development. Using PL/SQL as main programming language, Oracle developers, who usually do not have leading-edge knowledge of fast-changing web technologies, still able to create an attractive, user-friendly, data-driven web application in a fairly short time, with very limited resources.

The actual power of APEX is that it can integrate with other applications easily. For example, you have a PHP app, a Springboot app with Angular / React frontend, and some static web content. Now you want to add a new APEX app, or handling oracle database data through REST API. Rather than running each app in its silo, you can put all apps in one single system -- Apache HTTP server in the front, then Tomcat, serving data through ORDS and APEX, and Oracle database as backend. The stability, scalability, and versatility of each component can produce synergetic power for any company needs.

• The following instructions can be applied in Windows or Linux OS. For demonstration purpose this setup is applied to a Windows 11 environment.

• Create a folder to contains all components. It is recommended that the full path of this folder is short and does not contain space. In here I use C:\APEXOTA for this folder.

Get Started with Oracle Database 23ai

https://www.oracle.com/tools/downloads/apex-downloads.html

• Unzip the file to C:\APEXOTA. It will create the apex folder under it.

https://www.oracle.com/database/technologies/appdev/rest-data-services-downloads.html

• Unzip the file to C:\APEXOTA\ords

https://tomcat.apache.org/download-90.cgi

• Unzip the file to C:\APEXOTA. It will create folder apache-tomcat-9.0.xx under it. Rename this folder to Tomcat9.

https://www.apachelounge.com httpd-2.4.63-250207-win64-VS17.zip

• Unzip the file to C:\APEXOTA. It will create a folder Apache24 under it.

• Install Visual C++ Redistributable for Visual Studio 2015-2022 (runtime library for HTTP Server)

https://aka.ms/vs/17/release/VC_redist.x64.exe

Apache Tomcat connector 1.2.50 Win64 VS17 (mod_jk) NO LONGER WORKS AFTER ORDS 24.4

https://www.apachelounge.com/download/VS17/modules/mod_jk-1.2.50-win64-VS17.zip
- Extract the file `mod_jk.so` to `C:\APEXOTA\Apache24\modules`

https://adoptium.net/temurin/releases/?os=windows&arch=x64&package=jre&version=11

• OpenJDK11U-jre_x64_windows_hotspot_11.0.26_4.zip
• Unzip the file to C:\APEXOTA. Rename the folder jdk-11.0.26+4-jre to JRE11.

• 7 files are downloaded
  • Oracle database 23ai free edition
  • Oracle APEX 24.2
  • Oracle ORDS 24.4
  • Apache Tomcat 9
  • Apache HTTP Server 2.4
  • Microsoft VC17 runtime libraries
  • Java 17 JRE

Archives are unzipped to folder APEXOTA:

1. Install Oracle 23ai

   • Install it using all default options. Set the SYS / SYSTEM / PDBADMIN password to Welcome1.

2. Open System Properties (sysdm.cpl) -> Advanced -> Environment Variables

   • add JAVA_HOME=C:\APEXOTA\JRE17
   • Modify system path to contain

     C:\APEXOTA\JRE17\bin
     C:\app\Administrator\product\23ai\dbhomeFree\bin
     

3. Change password policy

• Start SQL*Plus

c:\APEXOTA\apex>sqlplus / as sysdba
    
SQL*Plus: Release 23.0.0.0.0 - Production on Mon Feb 24 16:01:47 2025
Version 23.7.0.25.01
    
Copyright (c) 1982, 2025, Oracle.  All rights reserved.
    
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.7.0.25.01
    
alter session set container=FREEPDB1;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

4. Install Latest APEX

(Still under FREEPDB1 from Step 2)

SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/    
...
...
...

Thank you for installing Oracle APEX 24.2.0

Oracle APEX is installed in the APEX_240200 schema.

The structure of the link to the Oracle APEX Administration Services is as follows:
http://host:port/ords/apex_admin

The structure of the link to the Oracle APEX development environment is as follows:
http://host:port/ords/apex


timing for: Phase 3 (Switch)
Elapsed:    0.25


timing for: Complete Installation
Elapsed:    7.90

SYS>

5. Unlock DB user accounts

       SQL> alter user APEX_PUBLIC_USER account unlock;
   
       SQL> alter user APEX_PUBLIC_USER identified by APEX_PUBLIC_USER;
   

   • reset APEX admin password:

       SQL> @apxchpwd.sql
       ...set_appun.sql
       ================================================================================
       This script can be used to change the password of an Application Express
       instance administrator. If the user does not yet exist, a user record will be
       created.
       ================================================================================
       Enter the administrator's username [ADMIN]
       User "ADMIN" exists.
       Enter ADMIN's email [ADMIN]
       Enter ADMIN's password [] Welcome!23
       Changed password of instance administrator ADMIN.
   

   • Config RESTful services

       SQL> @apex_rest_config.sql
   
       Enter a password for the APEX_LISTENER user           []  Welcome1
       Enter a password for the APEX_REST_PUBLIC_USER user   []  Welcome1
       ...set_appun.sql
       ...setting session environment
       ...create APEX_LISTENER and APEX_REST_PUBLIC_USER users
       ...grants for APEX_LISTENER and ORDS_METADATA user
       ...
       Updated ORDS views and synonyms successfully.
       SQL>
   

6. Setup ORDS config file in C:\APEXOTA\ords\bin (22.4):

C:\APEXOTA\ords\bin>ords.exe install

ORDS: Release 24.4 Production on Tue Feb 25 05:35:03 2025

Copyright (c) 2010, 2025, Oracle.

Configuration:
  C:\APEXOTA\ords\bin

The configuration folder C:\APEXOTA\ords\bin does not contain any configuration files.

Oracle REST Data Services - Interactive Install

  Enter a number to select the TNS net service name to use from C:\app\Administrator\product\23ai\dbhomeFree\network\admin\tnsnames.ora or specify the database connection
    [1] FREE         SERVICE_NAME=FREE
    [S] Specify the database connection
  Choose [1]: 1
  Provide database user name with administrator privileges.
    Enter the administrator username: SYS
  Enter the database password for SYS AS SYSDBA: Welcome1

Retrieving information...
Your database connection is to a CDB.  ORDS common user ORDS_PUBLIC_USER will be created in the CDB.  ORDS schema will be installed in the PDBs.
Root CDB$ROOT - create ORDS common user
PDB PDB$SEED - install ORDS 24.4.0.r3451601 (mode is READ ONLY, open for READ/WRITE)
PDB FREEPDB1 - install ORDS 24.4.0.r3451601
PDB FREEPDB1 - configure PL/SQL gateway user APEX_PUBLIC_USER in ORDS version 24.4.0.r3451601

  Install ORDS in the database
    [1] Yes
    [2] No
  Choose [1]: 1

ORDS is not installed in the database. ORDS installation is required.

  Enter a number to update the value or select option A to Accept and Continue
    [1] Connection Type: TNS
    [2] TNS Connection: TNS_NAME=FREE TNS_FOLDER=C:\app\Administrator\product\23ai\dbhomeFree\network\admin
           Administrator User: SYS AS SYSDBA
    [3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <generate>
    [4] ORDS runtime user and schema tablespaces:  Default: SYSAUX Temporary TEMP
    [5] Additional Feature: Database Actions
    [6] Configure and start ORDS in Standalone Mode: Yes
    [7]    Protocol: HTTP
    [8]       HTTP Port: 8080
    [9]   APEX static resources location:
    [A] Accept and Continue - Create configuration and Install ORDS in the database
    [Q] Quit - Do not proceed. No changes
  Choose [A]: A

.........
.........
Mapped local pools from C:\APEXOTA\ords\bin\databases:
  /ords/                              => default                        => VALID


2025-02-25T05:40:07.369Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 24.4.0.r3451601
Oracle REST Data Services server info: jetty/12.0.13
Oracle REST Data Services java info: OpenJDK 64-Bit Server VM Temurin-17.0.14+7 (build: 17.0.14+7 mixed mode, sharing)

^C (CTRL-C to exit)

C:\APEXOTA\ords\bin>ords.exe war C:\APEXOTA\tomcat11\webapps\apex.war

ORDS: Release 24.4 Production on Tue Feb 25 06:02:56 2025

Copyright (c) 2010, 2025, Oracle.

Configuration:
  C:\APEXOTA\ords\bin

C:\APEXOTA\tomcat11\webapps

C:\APEXOTA\ords\bin>