![Johannes Michler PROMATIS Horus Oracle](/./us/wp-content/uploads/2022/06/Michler-Johannes-2.png?_t=1658731838)
ORDS 22.1 - Read the README
Jeff Smith provides a good overview of the major changes that came into place with ORDS 22.1. We consider these the most important points:
- New Format of Config Files - We basically ended up creating entirely new files.
- Deprecation of old java versions and the use of “java -jar ords.war” over a new “ords binary”.
- The ords.war no longer gets the config path “burned in” but that folder needs to be set e.g. over an environment variable.
In summary, what we did (as a one-time action) is:
mkdir $NE_BASE/xxords/conf
JAVA_HOME=/etc/alternatives/java_sdk_11 PATH=$JAVA_HOME/bin:$PATH ords --config $NE_BASE/xxords/conf install -i
[2] Create or update a database pool and install/upgrade ORDS in the database.
[1] Basic (host name, port, service name)
Enter the database host name [localhost]: xxxx
Enter the database listen port [1521]:
Enter the database service name [orcl]: DEV
Enter the administrator username: sys as sysdba
Enter a number to select additional feature(s) to enable:
Choose [1]: 5 (None)
Enter a number to configure and start ORDS in standalone mode
Choose [1]: 2 (Skip)
Finally, we changed our tomcat-based startup script to:
export _JAVA_OPTIONS=-Dconfig.url=$NE_BASE/xxords/conf
echo $_JAVA_OPTIONS
$XXORDS_TOP/bin/tomcat9/bin/startup.sh
Issue 1: Apex Access denied
After upgrading both ORDS and APEX, we encountered the following error:
ORDS-22001: The procedure named apex could not be accessed, it may not be declared, or the user executing this request may not have been granted execute privilege on the procedure, or a function specified by security.requestValidationFunction configuration property has prevented access. Check the spelling of the procedure, check that the execute privilege has been granted to the caller and check the configured security.requestValidationFunction function.
As a workaround, removing the following line from the ords/default/pool.xml helped:
However, from a security perspective, this obviously was not the optimal solution. According to an Oracle SR, this is Bug 34346908. For us
showed references to the old APEX Schema. By cleaning this with the following command, everything was working again:
ords_admin.clear_plsql_gateway_procedures(p_owner => 'APEX_210100');
end;
/
Issue 2: OCI Load Balancer with “ords standalone”
In the context of upgrading to the latest ORDS 22.1, we also made some of the applications visible externally. Todd Bottger gives a good overview on how to do this using the Oracle Autonomous Database.
A similar procedure can also be applied when running the “standalone ords” as the backend. However, when providing the standalone ords on “just http” and providing https externally (which I would highly recommend), you’ll run into issues on some APEX (built-in) with an error of “ORDS-13002 (Cross Origin Sharing requests)”.
The workaround for this is to modify your pool.xml to include the external URL(s) as a trusted origin:
However, I’m still in discussion with Oracle, since I consider this an inappropriate workaround, and since in my opinion “ords standalone” and the OCI Load Balancer should work together in a way so that this is not necessary; especially since this can be detected through the X-Forwarded-* headers passed from the Load Balancer to the ords standalone backend.
Issue 3: Standalone ords startup script on OEL8
Finally, while using “ords standalone” in a production setting for the first time on Oracle Linux 8, we realized that the systemctl scripts provided seemed to be broken. This is still under investigation by Oracle, however “our” workaround was to fix the systemctl startup script for ords standalone by adding the additions marked in yellow to /etc/init.d/ords:
exit_code=0
ords_base=$( printf "%q" "$ords_base" )
ords_log_file=$( printf "%q" "$ords_log_file" )
export ORDS_CONFIG=$( printf "%q" "$ORDS_CONFIG" )
ords_owner=oracle
# We need to be careful as the configuration directory could be
# relative
cd "\$ords_base"
# Start ORDS in background
#cd $ORDS_CONFIG #export APP_VM_OPTS=$app_vm_opts
nohup /usr/local/bin/ords serve $serve_extra_args &>> "\$ords_log_file" &
exit \$exit_code
EXECUTION_AS_THE_ORDS_OWNER
Summary
Ords and APEX 22.1 provide a bunch of new features and fixed bugs over the previous releases. However, I’d highly recommend an upgrade to those latest versions; by following the example shown above, this should be a smooth transition even though there are some major changes with ORDS 22.1.