{"id":1268,"date":"2022-07-13T11:33:00","date_gmt":"2022-07-13T09:33:00","guid":{"rendered":"https:\/\/promatis.com\/ch\/lessons-learned-while-upgrading-apex-and-ords-to-22-1\/"},"modified":"2023-03-27T13:00:52","modified_gmt":"2023-03-27T11:00:52","slug":"lessons-learned-while-upgrading-apex-and-ords-to-22-1","status":"publish","type":"post","link":"https:\/\/promatis-test.de\/ch\/lessons-learned-while-upgrading-apex-and-ords-to-22-1\/","title":{"rendered":"Lessons learned while upgrading APEX and ORDS to 22.1"},"content":{"rendered":"<p>[et_pb_section fb_built=\"1\" custom_padding_last_edited=\"on|tablet\" disabled_on=\"off|off|off\" admin_label=\"Sektion\" _builder_version=\"4.17.6\" _module_preset=\"default\" custom_padding=\"5vh||5vh||true|false\" custom_padding_tablet=\"5vh||5vh||true|false\" custom_padding_phone=\"5vh||5vh||true|false\" global_module=\"23\" global_colors_info=\"%91%93\" theme_builder_area=\"post_content\"][et_pb_row column_structure=\"1_4,3_4\" _builder_version=\"4.17.6\" _module_preset=\"default\" custom_margin=\"||0px||false|false\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_column type=\"1_4\" _builder_version=\"4.17.6\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_image src=\"\/.\/wp-content\/uploads\/2022\/06\/Michler-Johannes-2.png?_t=1658731838\" alt=\"Johannes Michler PROMATIS Horus Oracle\" _builder_version=\"4.17.6\" _module_preset=\"default\" width=\"90%\" custom_margin=\"0vh||0vh||true|false\" border_radii=\"on|516px|516px|516px|516px\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][\/et_pb_image][\/et_pb_column][et_pb_column type=\"3_4\" _builder_version=\"4.17.6\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_text ul_type=\"square\" _builder_version=\"4.19.5\" _module_preset=\"default\" text_font=\"Open Sans||||||||\" text_font_size=\"17px\" text_line_height=\"1.6em\" link_font=\"Open Sans||||on|||RGBA(255,255,255,0)|\" link_text_color=\"gcid-0becd5ff-19fc-4653-a221-c8c75771a987\" link_font_size=\"22px\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_font=\"|600||on|||||\" header_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_font_size=\"47px\" header_line_height=\"1.2em\" header_2_font=\"|600||on|||||\" header_2_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_line_height=\"1.6em\" header_3_font=\"|600|||||||\" header_3_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_3_font_size=\"24px\" header_3_line_height=\"1.4em\" header_4_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_4_line_height=\"1.4em\" header_5_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_5_line_height=\"1.4em\" header_6_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_font_size=\"16px\" header_6_line_height=\"1.4em\" custom_margin=\"2vh||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22link_text_color%22%93}\" theme_builder_area=\"post_content\"]<\/p>\n<div><span itemprop=\"name\"><a href=\"\/.\/author\/jmichler\/\" rel=\"author\" itemprop=\"url\">Johannes Michler<\/a><\/span><\/div>\n<p>[\/et_pb_text][et_pb_text ul_type=\"square\" _builder_version=\"4.19.5\" _module_preset=\"default\" text_font=\"Open Sans||||||||\" text_font_size=\"17px\" text_line_height=\"1.6em\" link_font=\"Open Sans||||on||||\" link_text_color=\"#00A9A0\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_font=\"|600||on|||||\" header_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_font_size=\"47px\" header_line_height=\"1.2em\" header_2_font=\"|600||on|||||\" header_2_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_line_height=\"1.6em\" header_3_font=\"|600|||||||\" header_3_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_3_font_size=\"24px\" header_3_line_height=\"1.4em\" header_4_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_4_line_height=\"1.4em\" header_5_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_5_line_height=\"1.4em\" header_6_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_font_size=\"16px\" header_6_line_height=\"1.4em\" custom_margin=\"1vh||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22%93}\" theme_builder_area=\"post_content\"]<\/p>\n<p><img decoding=\"async\" src=\"\/.\/wp-content\/uploads\/2023\/02\/ACE-Director-Logo_175x51.jpg\" \/><br \/><strong>Executive Vice President<\/strong> \u2013\u00a0Head of Platforms\u00a0&\u00a0Development<\/p>\n<p>[\/et_pb_text][et_pb_text ul_type=\"square\" _builder_version=\"4.17.6\" _module_preset=\"default\" text_font=\"Open Sans||||||||\" text_text_color=\"gcid-0becd5ff-19fc-4653-a221-c8c75771a987\" text_font_size=\"22px\" text_line_height=\"1.6em\" link_font=\"Open Sans||||on||||\" link_text_color=\"#00A9A0\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_font=\"|600||on|||||\" header_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_font_size=\"47px\" header_line_height=\"1.2em\" header_2_font=\"|600||on|||||\" header_2_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_line_height=\"1.6em\" header_3_font=\"|600|||||||\" header_3_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_3_font_size=\"24px\" header_3_line_height=\"1.4em\" header_4_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_4_line_height=\"1.4em\" header_5_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_5_line_height=\"1.4em\" header_6_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_font_size=\"16px\" header_6_line_height=\"1.4em\" custom_margin=\"5px||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22text_text_color%22%93}\" theme_builder_area=\"post_content\"]<a target=\"_blank\" href=\"https:\/\/www.linkedin.com\/in\/johannes-michler-099892ab?originalSubdomain=de\" rel=\"noopener\"><i class=\"fab fa-linkedin\"><\/i><\/a><a target=\"_blank\" href=\"https:\/\/www.xing.com\/profile\/Johannes_Michler3\" rel=\"noopener\"><i class=\"fab fa-xing-square\"><\/i><\/a><a target=\"_blank\" href=\"mailto:johannes.michler@promatis.de\" rel=\"noopener\"><i class=\"fas fa-envelope\"><\/i><\/a>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section][et_pb_section fb_built=\"1\" _builder_version=\"4.16\" _module_preset=\"default\" custom_padding=\"0vh||10vh||false|false\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_row use_custom_gutter=\"on\" _builder_version=\"4.17.3\" _module_preset=\"default\" custom_padding=\"0px||0px||true|false\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_column type=\"4_4\" _builder_version=\"4.16\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_text _builder_version=\"4.18.0\" _module_preset=\"default\" background_enable_color=\"off\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22background_color%22%93}\" theme_builder_area=\"post_content\"]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.<br \/>\n<\/p>\n<h3>ORDS 22.1 - Read the README<\/h3>\n<p>Jeff Smith provides a good <a href=\"https:\/\/www.thatjeffsmith.com\/archive\/2022\/04\/oracle-rest-data-services-version-22-1-read-the-readme\/\" target=\"_blank\" rel=\"noopener noreferrer\">overview of the major changes that came into place with ORDS 22.1<\/a>. We consider these the most important points:<\/p>\n<ul>\n<li>New Format of Config Files - We basically ended up creating entirely new files.<\/li>\n<li>Deprecation of old java versions and the use of \u201cjava -jar ords.war\u201d over a new \u201cords binary\u201d.<\/li>\n<li>The ords.war no longer gets the config path \u201cburned in\u201d but that folder needs to be set e.g. over an environment variable.<\/li>\n<\/ul>\n<p>In summary, what we did (as a one-time action) is:<\/p>\n<div style=\"font-family: courier;\"><small>mv $NE_BASE\/xxords\/conf $NE_BASE\/xxords\/conf.old<br \/>\nmkdir $NE_BASE\/xxords\/conf<br \/>\nJAVA_HOME=\/etc\/alternatives\/java_sdk_11 PATH=$JAVA_HOME\/bin:$PATH ords --config $NE_BASE\/xxords\/conf install -i<\/small><\/div>\n<p>&nbsp;<\/p>\n<p>[2] Create or update a database pool and install\/upgrade ORDS in the database.<\/p>\n<p>[1] Basic (host name, port, service name)<\/p>\n<p>Enter the database host name [localhost]: xxxx<\/p>\n<p>Enter the database listen port [1521]:<\/p>\n<p>Enter the database service name [orcl]: DEV<\/p>\n<p style=\"margin-left: 20px;\">Enter the administrator username: sys as sysdba<\/p>\n<p>Enter a number to select additional feature(s) to enable:<\/p>\n<p>Choose [1]: 5 (None)<\/p>\n<p>Enter a number to configure and start ORDS in standalone mode<\/p>\n<p>Choose [1]: 2 (Skip)<\/p>\n<p>Finally, we changed our tomcat-based startup script to:<\/p>\n<div style=\"font-family: courier;\"><small>export JAVA_HOME=\/etc\/alternatives\/java_sdk_11\/<br \/>\nexport _JAVA_OPTIONS=-Dconfig.url=$NE_BASE\/xxords\/conf<br \/>\necho $_JAVA_OPTIONS<br \/>\n$XXORDS_TOP\/bin\/tomcat9\/bin\/startup.sh<\/small><\/div>\n<p><\/p>\n<h3>Issue 1: Apex Access denied<\/h3>\n<p>After upgrading both ORDS and APEX, we encountered the following error:<\/p>\n<p>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.<\/p>\n<p>As a workaround, removing the following line from the ords\/default\/pool.xml helped:<\/p>\n<div style=\"font-family: courier;\"><small>wwv_flow_epg_include_modules.authorize<\/small><\/div>\n<div><\/div>\n<p>However, from a security perspective, this obviously was not the optimal solution. According to an Oracle SR, this is Bug 34346908. For us<\/p>\n<div style=\"font-family: courier;\"><small>select * from ords_metadata.plsql_gateway_allow_list<\/small><\/div>\n<div><\/div>\n<p>showed references to the old APEX Schema. By cleaning this with the following command, everything was working again:<\/p>\n<div style=\"font-family: courier;\"><small>begin<br \/>\nords_admin.clear_plsql_gateway_procedures(p_owner =&gt; 'APEX_210100');<br \/>\nend;<br \/>\n\/<\/small><\/div>\n<p><\/p>\n<h3>Issue 2: OCI Load Balancer with \u201cords standalone\u201d<\/h3>\n<p>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 <a href=\"https:\/\/blogs.oracle.com\/apex\/post\/introducing-vanity-urls-on-adb\" target=\"_blank\" rel=\"noopener noreferrer\">overview on how to do this using the Oracle Autonomous Database.<\/a><\/p>\n<p>A similar procedure can also be applied when running the \u201cstandalone ords\u201d as the backend. However, when providing the standalone ords on \u201cjust http\u201d and providing https externally (which I would highly recommend), you\u2019ll run into issues on some APEX (built-in) with an error of \u201cORDS-13002 (Cross Origin Sharing requests)\u201d.<\/p>\n<p>The workaround for this is to modify your pool.xml to include the external URL(s) as a trusted origin:<\/p>\n<div style=\"font-family: courier;\"><small>https:\/\/dev.xxx-portal-test.com, https:\/\/dev.portal.xxx.de<\/small><\/div>\n<div><\/div>\n<p>However, I\u2019m still in discussion with Oracle, since I consider this an inappropriate workaround, and since in my opinion \u201cords standalone\u201d 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.<br \/>\n<\/p>\n<h3>Issue 3: Standalone ords startup script on OEL8<\/h3>\n<p>Finally, while using \u201cords standalone\u201d 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 \u201cour\u201d workaround was to fix the systemctl startup script for ords standalone by adding the additions marked in yellow to \/etc\/init.d\/ords:<\/p>\n<div style=\"font-family: courier;\"><small>script=\"$( cat &lt;&lt;-EXECUTION_AS_THE_ORDS_OWNER<br \/>\nexit_code=0<br \/>\nords_base=$( printf \"%q\" \"$ords_base\" )<br \/>\nords_log_file=$( printf \"%q\" \"$ords_log_file\" )<br \/>\nexport ORDS_CONFIG=$( printf \"%q\" \"$ORDS_CONFIG\" )<br \/>\nords_owner=oracle<br \/>\n# We need to be careful as the configuration directory could be<br \/>\n# relative<br \/>\ncd \"\\$ords_base\"<br \/>\n# Start ORDS in background<br \/>\n#cd $ORDS_CONFIG #export APP_VM_OPTS=$app_vm_opts<br \/>\nnohup \/usr\/local\/bin\/ords serve $serve_extra_args &amp;&gt;&gt; \"\\$ords_log_file\" &amp;<br \/>\nexit \\$exit_code<br \/>\nEXECUTION_AS_THE_ORDS_OWNER<\/small><\/div>\n<p><\/p>\n<h3>Summary<\/h3>\n<p>Ords and APEX 22.1 provide a bunch of new features and fixed bugs over the previous releases. However, I\u2019d 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.[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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.<\/p>\n","protected":false},"author":2,"featured_media":1243,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"2880","footnotes":""},"categories":[23],"tags":[],"dipi_cpt_category":[],"class_list":["post-1268","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-techblog"],"_links":{"self":[{"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/posts\/1268"}],"collection":[{"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/comments?post=1268"}],"version-history":[{"count":0,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/posts\/1268\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/media\/1243"}],"wp:attachment":[{"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/media?parent=1268"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/categories?post=1268"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/tags?post=1268"},{"taxonomy":"dipi_cpt_category","embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/dipi_cpt_category?post=1268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}