The aim of that blog is to look into Oracle REST Data Services (ORDS) 23.x error raised when setting up for APEX 23.1.
This is following some experience at a customer who wanted to move to latest version of ORDS with APEX 23.1, in late 2023, and ran into an issue.
If you have encountered similar error as below, when trying to run ORDS 23.x standalone for APEX 23.1, you will find a resolution and more details in this blog:
Context
– APEX 23.1 is installed first on Oracle DB 19.16 at PDB level without error
– ORDS 23.3 binaries are installed on Oracle Linux 8 from the yum package provided by Oracle ($ORDS_CONFIG=/etc/ords/config and $ORDS_BASE=/usr/local/bin)
ORDS was installed with following command line:$/usr/local/bin/ords --config $ORDS_CONFIG install
ORDS: Release 23.3 Production on Fri Oct 27 09:38:20 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
/etc/ords/config/The configuration folder /etc/ords/config does not contain any configuration files.
Oracle REST Data Services - Interactive Install
Enter a number to select the type of installation
[1] Install or upgrade ORDS in the database only
[2] Create or update a database pool and install/upgrade ORDS in the database
[3] Create or update a database pool only
Choose [2]: 2
Enter a number to select the database connection type to use
[1] Basic (host name, port, service name)
[2] TNS (TNS alias, TNS directory)
[3] Custom database URL
Choose [1]: 2
Enter the TNS location: /u01/app/oracle/product/network/admin
Enter a number to select the TNS Network alias to use
[1] PDB1 …SERVICE_NAME=pdb1)))
[2] PDB2 …SERVICE_NAME=pdb2)))
Choose [1]: 1
Provide database user name with administrator privileges.
Enter the administrator username: ORDS_INSTALL_ADMIN
Enter the database password for ORDS_INSTALL_ADMIN:
Connecting to database user: ORDS_INSTALL_ADMIN url:
jdbc:oracle:thin:@PDB1?TNS_ADMIN=/u01/app/oracle/product/network/admin
Retrieving information.
Connecting to database user: ORDS_PUBLIC_USER url:
jdbc:oracle:thin:@PDB1?TNS_ADMIN=/u01/app/oracle/product/network/admin
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
Enter a number to configure and start ORDS in standalone mode
[1] Configure and start ORDS in standalone mode
[2] Skip
Choose [1]: 1
Enter a number to select the protocol
[1] HTTP
[2] HTTPS
Choose [1]: 1
Enter the HTTP port [8080]: 8080
Enter the APEX static resources location: /u01/app/oracle/product/apex/images
As per standard installation ORDS was installed after APEX and all Database Actions have been deployed.
Issue
Unfortunately ORDS is providing a 404 error when trying to access APEX from the URL:http://mywebappserver:8080/ords/apex
Also when accessing the ORDS landing page with following URL, APEX is unavailable:http://mywebappserver:8080/ords/_landing
Investigation
ORDS is returning 404 for different errors as no handler is defined by default. Also looking into the Jetty log files is not showing any error details. The only option left, in order to have more information about the error behind the scene, is to print debug information to screen asking ORDS to return details. This is enabled by updating the ORDS configuration file pool.xml
for the database connection to APEX in folder $ORDS_CONFIG/databases/default
adding entry debug.printDebugToScreen
as following:
<?xml version="1.0" encoding="UTF-8"?>
<!--?xml version="1.0" encoding="UTF-8"?-->
<properties>
<comment>Saved on Fri Oct 27 09:41:18 UTC 2023</comment>
<entry key="db.connectionType">tns</entry>
<entry key="db.tnsAliasName">PDB1</entry>
<entry key="db.tnsDirectory">/u01/app/oracle/product/network/admin</entry>
<entry key="db.username">ORDS_PUBLIC_USER</entry>
<entry key="feature.sdw">true</entry>
<entry key="restEnabledSql.active">true</entry>
<entry key="debug.printDebugToScreen">true</entry>
<entry key="security.requestValidationFunction">ords_util.authorize_plsql_gateway</entry>
</properties>
This requires a restart of ORDS and provides further details when accessing the apex URL in the web browser:
The Java Stack Trace provided is showing some DispatcherNotFoundException which may not be very specific or helpful at a first glance. At least it gives some hint that ORDS is not finding a way to dispatch request towards the requested apex target.
Resolution
When checking the configuration file pool.xml
and comparing to previous installation (with ORDS 22.x), it shows there is one entry missing for plsql.gateway.mode
that needs to be set to value proxied and added as following:
<?xml version="1.0" encoding="UTF-8"?>
<!--?xml version="1.0" encoding="UTF-8"?-->
<properties>
<comment>Saved on Fri Oct 27 09:41:18 UTC 2023</comment>
<entry key="db.connectionType">tns</entry>
<entry key="db.tnsAliasName">PDB1</entry>
<entry key="db.tnsDirectory">/u01/app/oracle/product/network/admin</entry>
<entry key="db.username">ORDS_PUBLIC_USER</entry>
<entry key="feature.sdw">true</entry>
<entry key="restEnabledSql.active">true</entry>
<entry key="plsql.gateway.mode">proxied</entry>
<entry key="security.requestValidationFunction">ords_util.authorize_plsql_gateway</entry>
</properties>
This can also be achieved with following command:ords --config $ORDS_CONFIG config --db-pool default set plsql.gateway.mode proxied
The proxied mode is required as requests from ORDS will connect through the DB connection pool using ORDS_PUBLIC_USER who ever the end user connecting on APEX will be.
More information about proxy user can be found on AskTOM.
After restarting ORDS in standalone mode APEX is finally accessible.
This was tested with ORDS 23.2.3 with same result as well as with ORDS 23.1.
Conclusion
I hope this blog will help you to finalize your ORDS 23.x configuration in standalone for use as APEX 23.x gateway, since both are linked according to the documentation.
It would be good to know if there is a specific reason for not setting that parameter as in previous version. If so, documentation should also be updated accordingly. JeffSmith feel free to provide some feedback.
Enjoy APEX and ORDS!
george
02.01.2024Hi,
Excellent post...
i am 'testing' ords and apex and your post has solved my issue !
thanks,
George
Alain Lacour
02.01.2024Hi George,
glad to see that it helped.
Alain