Installing APEX on Oracle Database Base Service

This is Part 3 of my  Oracle Database Developer OCI Environment series.

Download APEX

On the database machine, get the latest APEX release from https://www.oracle.com/tools/downloads/apex-downloads/

Download the ‘All Languages’ package or use wget, rename the apex folder to append the version number and create a symlink apex to that folder.

cd /u01
wget -P /tmp https://download.oracle.com/otn_software/apex/apex-latest.zip
unzip -d /tmp /tmp/apex-latest.zip
mv /tmp/apex apex-26-1
ln -sfn apex-26-1 apex

If ORDS is planned to run on a separate machine, repeat those steps on the ORDS machine, as the static files from the apex directory are also required on the ORDS machine.

Run APEX installer

From the apex directory on the database machine, run the APEX installer (change tablespaces according to your standards). The user SYS is mandatory, other users are not supported for running the APEX installation (https://apexapps.oracle.com/apex/r/apex_pm/ideas/details?idea=FR-3902&session=130781358621911).

cd /u01/apex

sql sys/<password>@orclpdb as sysdba @apexins sysaux sysaux temp /i/

or

export ORACLE_PDB_SID=orclpdb
sql / as sysdba @apexins sysaux sysaux temp /i/

Keep the session open. Check the APEX release

select * from apex_release;

Set the APEX Administrator password by running

@apxchpwd.sql

Unlock the APEX_PUBLIC_USER and change his password, which has been randomly set at installation.

alter user apex_public_user identified by oracle account unlock;

Still in the same session, run @apex_rest_config.sql and set both passwords for the APEX_LISTENER user and the APEX_REST_PUBLIC_USER user.

@apex_rest_config.sql

To use web services and for sending email, network services need to be enabled. Copy the following from the documentation and run it.  

BEGIN    
   DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
      host => '*',
      ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                         principal_name => 'APEX_230200',
                         principal_type => xs_acl.ptype_db));
END;
/

Install ORDS

First, we need to make some settings. Add the following to .bashrc and source it.

cat >> ~/.bashrc <<EOF
export _JAVA_OPTIONS="-Xms3000M -Xmx3000M"
export ORDS_CONFIG=/u01/ords-config
export ORDS_BASE=/u01/ords
export PATH=\$ORDS_BASE/bin:\$PATH
EOF
. ~/.bashrc    

Download the latest ORDS from https://download.oracle.com/otn_software/java/ords/ords-latest.zip and unzip the contents to your target directory (eg. /u01/ords-24-2 and reflect that in the $ORDS_BASE variable in .bashrc. Create the directories and optionally create a symlink:

cd /u01
mkdir ords-26-1
wget -P /tmp https://download.oracle.com/otn_software/java/ords/ords-latest.zip
unzip -d ords-26-1 /tmp/ords-latest.zip
ln -sfn ords-26-1 ords

Have your static document root folder at hand, or create a dummy document root and a log folder like

mkdir -p $ORDS_CONFIG/global/doc_root
mkdir -p $ORDS_CONFIG/log  

to avoid error messages because the document root folder does not exist.

ORDS requires at least JDK 17, but newer versions will also work. As opc user, install any JDK version 17 or higher eg.

sudo dnf in jdk-25-headless.x86_64

Optional: run ORDS installation without SYS

If you want to install ORDS without SYS AS SYSDBA, a user needs to be prepared for that task, see MOS note 2597045.1 or the ORDS Installation and Configuration Guide 1.4 ORDS Installer Privileges Script.

Create a user to install ORDS and run the script

grant connect, RESOURCE to ordsinstall identified by oracle;
@/u01/ords/scripts/installer/ords_installer_privileges.sql ordsinstall 

Mind the lines regarding APEX at the end of the script execution:

grant select on APEX_230200.WWV_FLOW_RT$MODULES to "ORDSINSTALL" with grant option;
grant select on APEX_230200.WWV_FLOW_POOL_CONFIG to "ORDSINSTALL" with grant option; 

As these explicit target the give APEX version, these need to be rerun with every new installed APEX version. But installation of a new APEX version requires SYSDBA rights anyways.

Run the ORDS installer

Either use the interactive installation or configure every parameter on the command line.

Interactive ORDS installation

run

ords install --interactive

The ORDS installation comes up with a menu

If ORDS should be installed without SYS, choose 1 and enter your given user from the previous chapter.

Select 3 and set a password for the ORDS_PUBLIC_USER .

Select 9 and set the APEX static resources location to something like /u01/apex/images/ .

Then continue by choosing A.

ORDS non-interactive silent installation

Alternatively, configure ORDS via command line parameters like shown below. Create a file with the passwords for the ordsinstall and ORDS_PUBLIC_USER passwords and pipe it into the command or if you do not want to use a password file, just enter the parameters when the installer starts. There will be no visible prompt, but it is there. Just type the password for SYS or ordsadmin, hit <Return>, enter the Password for apex_public_user, hit <Return> again and you are done. Some examples:

Basic connection with ORDS install user and interactive password entry

ords install --db-hostname localhost --db-port 1521 
    --db-servicename db26pdb.subxxxx.vcndb.oraclevcn.com --feature-sdw true 
    --log-folder $ORDS_CONFIG/log --admin-user ordsinstall --gateway-mode proxied 
    --    gateway-user APEX_PUBLIC_USER --proxy-user --password-stdin

Basic connection with SYS and password file

ords install --db-hostname localhost --db-port 1521 --db-servicename db26pdb.subxxxx.vcndb.oraevcn.com 
    --feature-sdw true --log-folder $ORDS_CONFIG/log --admin-user sys --gateway-mode proxied 
    --gateway-user APEX_PUBLIC_USER --proxy-user --password-stdin stdin < ~/password.txt

TNS connection with SYS and password file

ords --java-options "-DuseOracleHome=true" install --db-tns-dir $ORACLE_HOME/network/admin 
    --feature-sdw true --db-tns-alias orclpdb --admin-user sys --gateway-mode proxied 
    --gateway-user APEX_PUBLIC_USER --bequeath-connect --log-folder /u01/ords-config/log 
    --proxy-user --password-stdin < ~/password.txt

The non-interactive installation does not set the static resources location, so add this with

ords config set standalone.static.path /u01/apex/images/
ords config set standalone.static.context.path /i
ords config set jdbc.MaxLimit 30
ords config set jdbc.InitialLimit 20

If you plan to use the MongoDB-API, also run the following:

ords config set mongo.enabled true
ords config set mongo.access.log $ORDS_CONFIG/log
ords config set mongo.port 27017

Configuring ORDS via command line does not start ORDS afterward, so start ORDS with

ords serve

ORDS should come up without warnings.

Test ORDS connection

For a first try run the following on the same machine

curl -I http://localhost:8080/ords/

To test the connection from any other machine, we need to open the SQLNet port in the firewalls first.
As opc user run

sudo iptables -I INPUT 1 -p tcp --dport 8080 -j ACCEPT
sudo service iptables save
sudo iptables -S | grep 8080

Then we need to add the port to the Security List. As already done with port 1521 in the first chapter, navigate to your VCN and open the Default Security List. Under Ingress Rules, where you already have a rule for port 1521, create a similar rule for port 8080.

So the result should look as follows

Open http://<address>:8080 in a Browser, that should route to the ORDS landing page.

To test SQL Developer Web, we need at least one rest-enabled user. Connect to the orclpdb as SYS. For REST-enabling schemas, the executing user first needs an additional privilege, see MOS-Note 2863257.1

grant inherit privileges on user sys to ords_metadata;

Then run

BEGIN
    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'hr',
                       p_auto_rest_auth => FALSE);
    commit;
END;
/

Klick on the ‘Go’ button below SQL Developer Web and login with hr. That should open the SDW homepage.

Navigate back to the ORDS Landing page or go directly to http://hostname:8080/ords/apex . Login to workspace INTERNAL with user ADMIN and the password you provided at installation. 

That should lead you to the APEX welcome page.

So APEX is up and running.