Close Menu

    Oracle Application Express for Amazon RDS for Oracle demystified

    Amazon Relational Database Service (Amazon RDS) for Oracle is a fully managed commercial database where Amazon RDS automation manages time-consuming, repetitive administration tasks like provisioning, backups, software patching, monitoring, and compute scaling, allowing you to focus on tasks and activities needed for application use.

    Oracle Application Express (APEX) allows you to quickly develop and deploy compelling applications that solve real problems and provide immediate value. APEX provides you with the tools you need to build applications in a single, extensible environment, which runs as a part of Oracle Database.

    Solution overview

    The APEX architecture consists of a web browser, web listener, and an Oracle Database containing APEX (also known as a repository).

    The repository stores the metadata for APEX applications and components. The repository consists of tables, indexes, and other objects that are installed in your database configured with APEX. The APEX repository is installed on Amazon RDS for Oracle.

    The web listener manages HTTP/HTTPS communications with APEX clients. The listener resides in a separate host such as an Amazon Elastic Compute Cloud (Amazon EC2) instance or your desktop computer. The listener accepts incoming connections from web browsers, forwards them to the RDS DB instance for processing, and sends results from the repository back to the browsers. Oracle recommends using Oracle REST Data Services (ORDS) as the web listener.

    In the following sections, we cover the steps for installing, configuring, and upgrading an APEX repository in Amazon RDS for Oracle and ORDS. We also show how to handle APEX when performing snapshot restore or point-in-time recovery (PITR).

    Prerequisites

    To follow the instructions detailed in this post, you need the following resources:

    • Amazon RDS for Oracle with an option group assigned to it
    • An EC2 instance with the Linux operating system

    Install an APEX repository on an RDS for Oracle instance

    Amazon RDS for Oracle supports APEX using the APEX and APEX-DEV options in an option group. When you install Oracle APEX, you can install two different environments:

    • If you want to deploy only the APEX runtime environment, add only the APEX option. In a runtime environment, users can run applications but can’t modify them. Oracle recommends selecting this option for production implementations.
    • To deploy the full development environment, add both the APEX and APEX-DEV options. In a full development environment, users can develop, modify, run, and delete APEX applications. This option provides complete access to the Oracle APEX environment.

    Adding the APEX or APEX_DEV option to an RDS for Oracle instance causes a brief downtime. You should plan for the installation of either option. Also, APEX consumes space in Amazon RDS for Oracle. Before installing Oracle APEX, make sure that you meet appropriate storage requirements.

    Complete the following steps to install the APEX repository on your RDS for Oracle instance:

    1. On the Amazon RDS console, navigate to the RDS instance details page.
    2. Choose the option group associated with the instance.
      If the instance is associated with the default option group, create a new option group or choose an existing option group that already has APEX enabled and associate that option group with the RDS for Oracle instance. If you have options other than APEX in your option group, it will also be configured in your RDS for Oracle instance.
    3. In the Options section of the option group details page, choose Add option.Add Apex option in Option group
    4. For Option name, choose APEX
    5. For Version, choose the version you want to install.
    6. For Apply immediately, select Yes.
    7. Choose Add option.Add Apex Option to Option group

    To install the full development environment, add the APEX_DEV option to the option group using the same steps.

    The instance will be in a Modifying state until the APEX installation is complete. During this time, you can’t make further changes to the instance until it returns to the Available state. An option group can be associated with more than one RDS for Oracle instance, and when an option is added to an option group, it gets reflected to the other database targets.

    After the APEX option is installed successfully, you can unlock the public user account on your DB instance and configure RESTful services for Oracle APEX.

    Choose a web listener

    The web listener functions as a communication broker between the web browser and the APEX objects in the Oracle database by mapping browser requests into database stored procedure calls.

    APEX supports the following web listeners: ORDS, Oracle HTTP Server with mod_plsql, and Embedded PL/SQL gateway. Refer to Comparing Web Listener Options for more information about web listener options for APEX.

    Beginning with APEX 20.2, the only supported web listener is ORDS. The deprecation of the embedded PL/SQL gateway and mod_plsql was announced in APEX 20.1 and will be de-supported in a future APEX release. Oracle recommends using ORDS as the web listener for APEX.

    Install ORDS for APEX on a separate host

    Complete the following steps to install ORDS on a separate EC2 host:

    1. Create a new EC2 instance with the Linux operating system and prepare your host to install ORDS.
    2. Update the security group assigned to your RDS for Oracle instance to allow connections to the database port from this EC2 instance.
    3. Refer to the steps to install and configure ORDS 21 or lower or ORDS 22 and higher. It is recommended to use the latest available ORDS version.For the minimum ORDS version requirements for the APEX version you have installed, refer to the Oracle APEX installation guide. Also review the Oracle Support document Doc ID 1344948.1 to understand more about the APEX and ORDS certification details.

      Throughout this post, the following variables point to different directory locations:

      • $ORDS_HOME – The installation directory for ORDS binaries. In this post, it’s located in /home/apexuser/ORDS.
      • $ORDS_CONFIG – The location for ORDS config files. In this post, it’s located in /home/apexuser/ords_config.
      • $APEX_HOME – The installation directory for APEX on Amazon EC2. In this post, it’s located in /home/apexuser/apex.

      If you’re using a different directory location for your installation, you should set them up accordingly.

    4. After ORDS is installed successfully, log in to your RDS for Oracle instance and verify the ORDS version: 
      --- SQL to check the Installed ORDS version

SQL> select ords.installed_version from dual;

INSTALLED_VERSION
--------------------------------------------------------------------------------
23.4.0.r3461619
    5. Start ORDS with the appropriate commands for your version:
      1. If you installed ORDS version 22.x or higher, use the following command: 
        -- To start ORDS version 22.x or higher
$ORDS_HOME/bin/ords --config $ORDS_CONFIG serve --port 8193 --apex-images $APEX_HOME/images
      2. If you installed ORDS version 21.x or lower, use the following command: 
        -- To start ORDS version 21.x or lower
cd $ORDS_HOME
java -jar ords.war

        The first time you start ORDS 21.x or lower, you’re prompted to provide the location of the APEX static resources. This images folder is located in the /apex/images directory in the installation directory for APEX ($APEX_HOME).

    6. After ORDS is started successfully, open the APEX Administrator URL using a supported browser from the ORDS host or another host that has proper network access to the HTTP port(8080) and 8193 port of your EC2 instance where ORDS is installed: https://<ordshost>:<port no>/ords/apex_admin.ORDS webpage
    7. When you’re prompted for credentials, enter the following information:
      1. User name – Enter admin
      2. Password – Enter the password you set using the apxchpwd.sql script.

    After you log in to APEX Administration services, you can create the workspaces needed. To login to the APEX workspaces, use the following URL: https://<ordshost>:<port no>/ords.

    Common issues with ORDS installation

    You might encounter the following error when starting ORDS, if your RDS for Oracle DB instance is a non-CDB instance:

    The procedure named apex_admin 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.

    By default, ORDS uses the ords_util.authorize_plsql_gateway procedure, which is supported only on CDB instances. For non-CDB instances, you need to configure ORDS to use the wwv_flow_epg_include_modules.authorize package. Complete the following steps:

    1. Stop ORDS and verify the current configuration for security.requestValidationFunction using the following command: 
      -- To verify the current configuration of ORDS
$ORDS_HOME/bin/ords --config $ORDS_CONFIG --verbose config --db-pool default list
    2. Update the value of security.requestValidationFunction accordingly:
      1. If your RDS for Oracle instance is a CDB instance, update security.requestValidationFunction to ords_util.authorize_plsql_gateway (default value): 
        $ORDS_HOME/bin/ords --config $ORDS_CONFIG --verbose config --db-pool default set security.requestValidationFunction ords_util.authorize_plsql_gateway
      2. If your RDS for Oracle instance is a non-CDB instance, run the following command to update security.requestValidationFunction to the wwv_flow_epg_include_modules.authorize package 
        $ORDS_HOME/bin/ords --config $ORDS_CONFIG --verbose config --db-pool default set security.requestValidationFunction wwv_flow_epg_include_modules.authorize
    3. Confirm that the value is updated properly by using the following command, and then start ORDS: 
      $ORDS_HOME/bin/ords --config $ORDS_CONFIG --verbose config --db-pool default list

    You might receive the following warning message when opening the APEX webpage. This warning message commonly happens when you can’t download the exact same APEX images version that is installed on the RDS for Oracle instance to your EC2 instance. A new patch version is released for every major APEX version frequently from Oracle Support, so you might not be able to use the exact same APEX version that is installed on your RDS for Oracle instance on your EC2 machine.

    Apex webpage warning

    To make sure that APEX images for the correct version is loaded, configure APEX to use the CDN URL for the entire APEX instance:

    1. Verify the APEX images version that is installed on the RDS for Oracle instance: 
      -- SQL to verify the APEX Patch version and Images Version in RDS
SQL> select patch_version,images_version from apex_patches;
PATCH_VERSION                  IMAGES_VERSION
------------------------------ ------------------------------
2                              23.1.1
    2. Verify the current setting for IMAGE_PREFIX: 
      -- SQL to verify the current setting for IMAGE_PREFIX
SQL> SELECT apex_instance_admin.get_parameter('IMAGE_PREFIX') FROM dual;
APEX_INSTANCE_ADMIN.GET_PARAMETER('IMAGE_PREFIX')
--------------------------------------------------------------------------------
/i/
    3. Find the CDN URL for the current image version from the APEX downloads page
    4. Use the apex_instance_admin.set_parameter procedure to update IMAGE_PREFIX with the correct CDN URL: 
      -- Procedure to update IMAGE_PREFIX to use CDN URL for APEX Images
SQL> begin
apex_instance_admin.set_parameter(
p_parameter => 'IMAGE_PREFIX',
p_value     => 'https://static.oracle.com/cdn/apex/23.1.1/');
commit;
end;
    5. Verify the new value for IMAGE_PREFIX 
      -- SQL to verify the current setting for IMAGE_PREFIX
SQL> SELECT apex_instance_admin.get_parameter('IMAGE_PREFIX') FROM dual;
APEX_INSTANCE_ADMIN.GET_PARAMETER('IMAGE_PREFIX')
--------------------------------------------------------------------------------
https://static.oracle.com/cdn/apex/23.1.1/
    6. Restart ORDS, and you should notice that the warning message related to images is fixed.

    If you need to revert the images directory to what you were using before, use the following code. Connect to your RDS for Oracle instance as the primary user and run the following PL/SQL to set the IMAGE_PREFIX parameter to the static resources hosted on your ORDS server:

    -- Procedure to update IMAGE_PREFIX parameter to use static resources hosted on ORDS server.

begin
apex_instance_admin.set_parameter(
p_parameter => 'IMAGE_PREFIX',
p_value => '/i/');
commit;
end;
/

    Handle APEX during snapshot restore or PITR

    During snapshot restore or PITR, associate the correct option group with APEX option. After the snapshot restore or PITR is complete, confirm whether both the APEX and ORDS installation are available on your newly restored RDS for Oracle instance. You can run the following queries to confirm the installation status of APEX and ORDS:

    -- To confirm the APEX installation
select comp_name,version,status from dba_registry where comp_name='Oracle APEX';

-- To confirm the ORDS installation
select ords.installed_version from dual;

    The following are possible scenarios with respect to snapshot restore or PITR:

    • You can delete or rename the existing RDS for Oracle instance and create a new RDS for Oracle instance with the same name when restoring the automated or manual snapshot. In this scenario, validate the APEX and ORDS installation and restart the ORDS before accessing the APEX installation. No additional configuration changes are needed on ORDS.
    • You can delete or rename the existing RDS for Oracle instance and create a new RDS for Oracle instance with a different name when restoring the snapshot. In this scenario, validate the APEX and ORDS installation and update the ORDS configuration settings to point to a new RDS for Oracle instance and start the ORDS.

    You can use the following command to get the current ORDS configuration settings:

    $ORDS_HOME/bin/ords config --db-pool <pool name> list

    To update the homename to a new RDS for Oracle instance, use the following command:

    $ORDS_HOME/bin/ords config --db-pool <pool name> set db.hostname <rds endpoint name>
    • The existing RDS for Oracle instance is operating normally and you created a new RDS for Oracle instance with a new name using PITR or when restoring automated or manual snapshot. In this scenario, validate the APEX and ORDS installation on the newly created RDS for Oracle installation, and you can either configure ORDS for multiple databases or install a new ORDS in standalone mode.

    Upgrade scenarios for Oracle APEX

    The following are scenarios related to upgrades and Oracle APEX in Amazon RDS for Oracle.

    Upgrade APEX version on Amazon RDS for Oracle

    Complete the following steps to upgrade the APEX version on your RDS for Oracle DB instance:

    1. Backup your RDS for Oracle DB instance before you upgrade APEX.
    2. Create a new option group and add the upgraded versions for the APEX and APEX-DEV options to the new option group.The following screenshot shows a new option group named apexupg was created and the options APEX and APEX_DEV for the latest APEX version 23.2.v1 were added to it.new option group for upgrade
    3. Modify your DB instance and specify the new option group you created.upgrade modify option groupThe instance will go into a Modifying state. When the instance is Available, you can confirm that latest APEX version is installed on your DB instance:
    -- SQL to verify the APEX schemas in RDS for Oracle
SQL> select username,account_status from dba_users where username like 'APEX%';
USERNAME ACCOUNT_STATUS
--------------------- --------------------------------
APEX_230100 OPEN <<< Old APEX
APEX_PUBLIC_USER OPEN
APEX_230200 LOCKED <<< new APEX
APEX_LISTENER OPEN
APEX_REST_PUBLIC_USER OPEN

    After you upgrade your version of APEX, the APEX schema for the previous version might still exist in your database. If you don’t need it anymore, you can drop the old APEX schema from your database after the upgrade, as shown in the following code. Before you drop the old APEX schema, make sure the new APEX version is installed properly and the application is working as expected.

    -- SQL to drop old APEX schema.
drop user <Old APEX schema name> cascade;

    Upgrade the ORDS version

    It isn’t mandatory to upgrade the ORDS version for every APEX version upgrade, but it’s recommended to use the latest ORDS version available. In this section, we discuss the steps for upgrading ORDS installed on an EC2 machine.

    The following are mandatory ORDS version requirements for available APEX versions in Amazon RDS for Oracle:

    • If you’re upgrading to APEX version 20.1.v1, 20.2.v1, 21.1.v1 or 21.2.v1, make sure the ORDS version is at least 19.x or higher. However, ORDS 20.x or higher is recommended.
    • If you’re upgrading to APEX version 22.1.v1 or higher, make sure the ORDS version is at least 20.x or higher.
    • APEX-based REST services were de-supported in APEX release 22.1; only ORDS is supported from 22.1.v1.

    Refer to the Oracle Support document Doc ID 1344948.1 to understand more about the APEX and ORDS certification details. To upgrade ORDS, complete the following steps:

    1. Verify the current version of ORDS installed on your RDS for Oracle instance: 
      --- SQL to check the Installed ORDS version
SQL> select ords.installed_version from dual;
INSTALLED_VERSION
--------------------------------------------------------------------------------
23.4.0.r3461619
    2. Go to the ORDS downloads page and examine the README for the ORDS version that you plan to upgrade. Make sure that you have the required version of Java installed.
    3. Create a new directory for your ORDS latest version. $ORDS_UPGRADE points to following directory: 
      mkdir /home/apexuser/ORDS_UPGRADE
cd /home/apexuser/ORDS_UPGRADE
    4. Download the file ords-latest.zip. The latest ORDS version available at the time of this writing is 24.2.
    5. Unzip the file into $ORDS_UPGRADE. In this post, it’s located in /home/apexuser/ORDS_UPGRADE.
    6. Stop the ORDS that is running from the current version.
    7. Run the appropriate upgrade commands based on your version:
      1. If you’re upgrading from ORDS 22.1.x or later to ORDS 22.2 or higher, run the following ORDS install command from the newly unzipped ORDS release distribution: 
        -- Command to upgrade ORDS from version 22.1.x or later to 22.2 or higher
$ORDS_UPGRADE/bin/ords --config $ORDS_CONFIG install --interactive --log-folder /home/apexuser/ords_logs

        Specify the configuration folder where the ORDS configuration files are stored. If you specify an existing ORDS configuration folder that contains the configuration files, ORDS attempts to connect to each database defined in the configuration folder and check the installed version.During the execution of the command, you are prompted for the user name and password (enter the details of the Amazon RDS primary user) to enable ORDS to apply the in-place upgrade.

      2. If you’re upgrading from ORDS 21.4 or earlier releases to 22.x or higher, use the following command. This command moves the config files to the new directory and performs the in-place upgrade of ORDS. Refer to Migrate Configuration and Upgrade from ORDS 21.4.x or Earlier Releases for details. 
        -- Command to upgrade ORDS from version 21.4 or earlier releases to 22.x or higher
$ORDS_UPGRADE/bin/ords --config <new config path> install --interactive --legacy-config <existing ORDS config path> --log-folder /home/apexuser/ords_logs
      3. If you’re upgrading to ORDS version 21.4 or earlier releases from older versions, go to the folder where you unzipped the new ORDS release distribution and run the following command to initiate the upgrade. Refer to the Oracle Support document Doc ID 2771381.1 for more details. 
        -- Command to upgrade ORDS version to 21.4 or earlier releases from any older versions
cd /home/apexuser/ORDS_UPGRADE
java -jar ords.war install advanced

      The in-place upgrade will modify the existing installation to add the updated schema objects and packages. The existing metadata stored in the ORDS schema will remain intact. Refer to the appropriate product documentation for more details about the upgrade.

      The following screenshot shows the snippet of the in-place upgrade of ORDS from 23.4.0 to 24.2.3.

      apex in-place upgrade

    8. When the ORDS upgrade is complete, you can verify the ORDS installed on your RDS for Oracle instance and confirm that it’s showing the latest version that you installed: 
      --- SQL to check the ORDS version after upgrade
SQL> select ords.installed_version from dual;
INSTALLED_VERSION
--------------------------------------------------------------------------------
24.2.3.r2011847
    9. Start ORDS from the new home $ORDS_UPGRADE: 
      $ORDS_UPGRADE/bin/ords --config $ORDS_CONFIG serve --port 8193 --apex-images $APEX_HOME/images

    Upgrade the database version

    A database upgrade can be either a minor or major version upgrade. APEX and ORDS upgrades aren’t normally required before minor version upgrades.

    Before a major version upgrade, make sure that the APEX version that is currently installed is supported on the new major version. For more details on compatibility between APEX versions and Oracle database versions, refer to APEX version requirements. If your APEX version isn’t supported with the Oracle database version you are planning to upgrade, you need to upgrade APEX before performing the Amazon RDS for Oracle major version upgrade.

    Convert an RDS for Oracle non-CDB to a CDB

    APEX and ORDS upgrades are not required when converting an RDS for Oracle non-CDB to a CDB. After the conversion is completed, you may receive the following error when accessing the APEX installation:

    The procedure named apex_admin 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.

    If you receive this error, follow the actions suggested earlier in this post regarding common issues with ORDS installation.

    Clean up

    To revert the changes made to the RDS for Oracle instance as part of this post and clean up your resources, complete the following steps:

    1. If the instance was originally associated with the default option group or an option group without the APEX option, reassign it back to the default option group or the original option group without the APEX option. Be aware that this requires downtime.
    2. Delete the option groups, RDS for Oracle instances and EC2 instance created for the ORDS configuration that are no longer required and were created solely for testing.

    Summary

    In this post, we discussed how to install and upgrade APEX in Amazon RDS for Oracle, how to handle APEX during snapshot restore or PITR, and how to successfully configure and upgrade ORDS in a separate EC2 host.

    Try out the solution for your own use case, and let us know your feedback and questions in the comments.

    About the Authors

    Manoj Ponnurangam works as a Cloud Database Engineer in Amazon Web Services. He is a Subject Matter Expert for Amazon RDS for Oracle, Amazon RDS for PostgreSQL and AWS DMS. Manoj has 15 years of experience working with relational databases. He works with our customers to provide guidance and technical assistance on various database and migration projects.

    Deepak Mani works as a Senior Cloud Database Engineer in Amazon Web Services. He is a Subject Matter Expert for Amazon RDS for Oracle and Amazon RDS infrastructure. Deepak has 15 years of experience working with relational databases. At AWS, he works primarily on process and service improvements for support engineering and works on internal escalations created for Amazon RDS for Oracle.

    Source: Read More

    Related Posts

    Leave A Reply