Skip to main content

Oracle REST Data Services

Download and Install ORDS​

Download ORDS version 24.4.0.345.1601 (December 12, 2024)

sudo -s

wget https://download.oracle.com/otn_software/java/ords/ords-24.4.0.345.1601.zip

sha1sum ords-24.4.0.345.1601.zip
0aa09441b826f48d65c0ec9bbbd49024be1291db

Download Java JRE version 21 LTS from https://adoptium.net/

https://adoptium.net/en-GB/temurin/releases/?os=linux&arch=x64&package=jre

sha256sum OpenJDK21U-jre_x64_linux_hotspot_21.0.5_11.tar.gz
553dda64b3b1c3c16f8afe402377ffebe64fb4a1721a46ed426a91fd18185e62

Create the ORDS Home directory​

su - oracle

mkdir ${ORDS_HOME}

Unzip ORDS and JAVA package in ${ORDS_HOME}​

Warning

Check if both paths of the JAVA and ORDS packages are correct in the .icats_profile configuration file.

Check line 5 and 10 of .icats_profile
.icats_profile configuration file

cd.ords.home    # Change into ${ORDS_HOME}

unzip -d ords-24.4.0.345.1601 /usr/local/src/ords-24.4.0.345.1601.zip

tar xvfz /usr/local/src/OpenJDK21U-jre_x64_linux_hotspot_21.0.5_11.tar.gz

ls -l ${ORDS_HOME}

drwxr-xr-x 6 oracle oinstall  8 Oct 15 22:35 jdk-21.0.5+11-jre
drwxr-xr-x 9 oracle oinstall 13 Jan 20 13:36 ords-24.4.0.345.1601

Configure Oracle ORDS​

ords --config ${ORDS_CONFIG} install

Oracle REST Data Services - Interactive Install

Created folder /opt/oracle/ords/conf
  Enter a number to select the TNS net service name to use from /opt/oracle/product/21c/dbhomeXE/network/admin/tnsnames.ora or specify the database connection
    [1] PDBICATSTST  SERVICE_NAME=PDBICATSTST
    [2] XE           SERVICE_NAME=XE
    [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:

Retrieving information.
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=PDBICATSTST TNS_FOLDER=/opt/oracle/product/21c/dbhomeXE/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]: 3
  Enter your choice for the runtime user password (ORDS_PUBLIC_USER)
    [S] Specify your own password
    [G] Generate password
    [C] Cancel - No Changes
  Choose [G]: S
  Enter the database password for ORDS_PUBLIC_USER:
  Confirm password:

  Choose [A]: 5
  Enter a number to select additional feature(s) to enable:
    [1] Database Actions  (Enables all features)
    [2] REST Enabled SQL and Database API
    [3] REST Enabled SQL
    [4] Database API
    [5] None
  Choose [1]: 1

  Choose [A]: 6
  Enter a number to update the value or select option A to Accept and Continue

  Choose [A]: A
The setting named: db.connectionType was set to: tns in configuration: default
The setting named: db.tnsAliasName was set to: PDBICATSTST in configuration: default
The setting named: db.tnsDirectory was set to: /opt/oracle/product/21c/dbhomeXE/network/admin in configuration: default
The setting named: plsql.gateway.mode was set to: proxied in configuration: default
The setting named: db.username was set to: ORDS_PUBLIC_USER in configuration: default
The setting named: db.password was set to: ****** in configuration: default
The setting named: feature.sdw was set to: true in configuration: default
The global setting named: database.api.enabled was set to: true
The setting named: restEnabledSql.active was set to: true in configuration: default
The setting named: security.requestValidationFunction was set to: ords_util.authorize_plsql_gateway in configuration: default
2025-01-22T13:27:09.881Z INFO        Created folder /opt/ords/logs
2025-01-22T13:27:09.882Z INFO        The log file is defaulted to the current working directory located at /opt/ords/logs
2025-01-22T13:27:10.060Z INFO        Installing Oracle REST Data Services version 24.4.0.r3451601 in PDBICATSTST
2025-01-22T13:27:11.747Z INFO        ... Verified database prerequisites
2025-01-22T13:27:12.194Z INFO        ... Created Oracle REST Data Services proxy user
2025-01-22T13:27:12.840Z INFO        ... Created Oracle REST Data Services schema
2025-01-22T13:27:14.347Z INFO        ... Granted privileges to Oracle REST Data Services
2025-01-22T13:27:27.399Z INFO        ... Created Oracle REST Data Services database objects
2025-01-22T13:27:49.040Z INFO        Completed installation for Oracle REST Data Services version 24.4.0.r3451601. Elapsed time: 00:00:38.943

2025-01-22T13:27:49.131Z INFO        Completed configuring PL/SQL gateway user for Oracle REST Data Services version 24.4.0.r3451601. Elapsed time: 00:00:00.90

2025-01-22T13:27:49.133Z INFO        Log file written to /opt/ords/logs/ords_install_2025-01-22_132709_88240.log

Configure the default ORDS connection pool​

# Set limits for default connection pool
ords --config ${ORDS_CONFIG} config set jdbc.InitialLimit 10
ords --config ${ORDS_CONFIG} config set jdbc.MinLimit 10
ords --config ${ORDS_CONFIG} config set jdbc.MaxLimit 100

# Must be set to proxied
ords --config ${ORDS_CONFIG} config set plsql.gateway.mode proxied

# ....
ords --config ${ORDS_CONFIG} config set restEnabledSql.active true

# ....
ords --config ${ORDS_CONFIG} config set security.verifySSL false

# 
ords --config ${ORDS_CONFIG} config set misc.pagination.maxRows 1000

# Folder of the Oracle APEX images
ords --config ${ORDS_CONFIG} config set standalone.static.path ${APEX_IMAGES}

Advanced ORDS database pool configuration​

Info

Create an own connection pool for each iCATs environment

ords --config ${ORDS_CONFIG} install --db-pool pdbicatstst
# ords --config ${ORDS_CONFIG} install --config-only --db-pool pdbicatstst

# Set limits for a specific pool -> pdbicatstst
ords --config ${ORDS_CONFIG} config --db-pool pdbicatstst set jdbc.InitialLimit 10
ords --config ${ORDS_CONFIG} config --db-pool pdbicatstst set jdbc.MinLimit 10
ords --config ${ORDS_CONFIG} config --db-pool pdbicatstst set jdbc.MaxLimit 100

# Must be set to proxied
ords --config ${ORDS_CONFIG} config --db-pool pdbicatstst set plsql.gateway.mode proxied

ords --config ${ORDS_CONFIG} config --db-pool pdbicatstst set restEnabledSql.active true
ords --config ${ORDS_CONFIG} config --db-pool pdbicatstst set security.verifySSL false
ords --config ${ORDS_CONFIG} config --db-pool pdbicatstst set misc.pagination.maxRows 1000
ords --config ${ORDS_CONFIG} config --db-pool pdbicatstst set misc.defaultPage f?p=20010911:LOGIN_DESKTOP

ords --config ${ORDS_CONFIG} config --db-pool pdbicatstst set standalone.static.path ${APEX_IMAGES}

ords --config ${ORDS_CONFIG} serve --apex-images ${APEX_IMAGES}
Info

The installation of this ORDS startscript /opt/oracle/ords.sh is optional.
It's recommended to use the Systemd Service /etc/systemd/system/ords.service to automatically starting the ORDS service.

/opt/oracle/ords/ords.sh
/opt/oracle/ords/ords.sh
cat > /opt/oracle/ords/ords.sh <<EOF
Start() {
    if [ \${PSCOUNT} -eq 2 ]; then
        echo "ORDS is already running"
        exit 1
    fi
    nohup ords --config \${ORDS_CONFIG} serve --apex-images \${APEX_IMAGES} >> \${ORDSLOG} 2>&1 &
}

Stop() {
    pkill -u oracle -f "ords" >> \${ORDSLOG} 2>&1
}

Status() {
    #ps -fu oracle | grep ords | grep -v grep
    if [ \${PSCOUNT} -eq 2 ]; then
        echo "ORDS is running"
    else
        echo "ORDS is stopped"
    fi
}

. /home/oracle/.icats_profile
ORDSLOG=\${ORDS_LOGS}/ords.log
PSCOUNT=\$(ps -fu oracle | grep -v grep | egrep "/opt/oracle/ords/ords.*/bin/ords|/opt/oracle/ords/.*/bin/java" | wc -l)

case "\$1" in
    start)   Start ;;
    stop)    Stop ;;
    status)  Status ;;
    restart) Stop; Start ;;
    *) echo "usage: $0 start|stop|restart" >&2
       exit 1
       ;;
esac
EOF
Warning

Check if both paths of the JAVA and ORDS packages are corresponding with ords.service and .icats_profile configuration file.

Check line 5 and 10 of .icats_profile
.icats_profile configuration file

Check line 14 and 17 of ords.service

/etc/systemd/system/ords.service
/etc/systemd/system/ords.service
cat > /etc/systemd/system/ords.service <<EOF
[Unit]
Description=Oracle ORDS Data Services
After=network.target oracle.service

[Service]
User=oracle
Group=oinstall

Type=simple
KillMode=mixed
WorkingDirectory=/opt/oracle/ords

Environment="JAVA_HOME=/opt/oracle/ords/jdk-21.0.5+11-jre"
Environment="ORDS_CONFIG=/opt/oracle/ords/conf"
Environment="APEX_IMAGES=/opt/oracle/apex/images"
ExecStart=/opt/oracle/ords/ords-24.4.0.345.1601/bin/ords --config \${ORDS_CONFIG} serve --apex-images \${APEX_IMAGES}
StandardOutput=file:/opt/oracle/ords/logs/ords.log
StandardError=file:/opt/oracle/ords/logs/ords.log

[Install]
WantedBy=multi-user.target
EOF

Set permission and enable the Systemd service

chmod 644 /etc/systemd/system/ords.service

systemctl daemon-reload
systemctl enable ords
systemctl status ords
systemctl start ords