Homepage 5 Techblog 5 Lessons learned while upgrading APEX and ORDS to 22.1

Lessons learned while upgrading APEX and ORDS to 22.1

July 13, 2022

Johannes Michler PROMATIS Horus Oracle


Executive Vice President – Head of Platforms & Development

We recently upgraded a great amount of customer instances, both on the APEX and on the ORDS layer. The general procedure for Oracle APEX is more or less unchanged in comparison to previous versions. On the ORDS layer however, the upgrade to 22.1 contains many changes. Additionally, we encountered a couple of issues, which are (along with their solutions) described below.

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:

mv $NE_BASE/xxords/conf $NE_BASE/xxords/conf.old
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_HOME=/etc/alternatives/java_sdk_11/
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:

wwv_flow_epg_include_modules.authorize

However, from a security perspective, this obviously was not the optimal solution. According to an Oracle SR, this is Bug 34346908. For us

select * from ords_metadata.plsql_gateway_allow_list

showed references to the old APEX Schema. By cleaning this with the following command, everything was working again:

begin
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:

https://dev.xxx-portal-test.com, https://dev.portal.xxx.de

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:

script="$( cat <<-EXECUTION_AS_THE_ORDS_OWNER
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.