Quickstart APEX on the OCI

Light trails on the street.

It's here! Earlier this week, Chris Hoina from the Oracle REST Data Services (ORDS) team, shared that Oracle APEX, ORDS, and SQLcl are now readily available as RPM packages in Oracle's YUM repositories.

First, a huge "THANK YOU" to the team that's behind this release. It goes a long way to help anyone on the fence to jump right into APEX right away! And if it helps, here's some of the notes and scripts that used while attempting to understand what they are, and how to use these RPMs effectively.

Required OCI Resources

For this exercise, all I needed were the following OCI resources:

A Compute with 1 OCPU and 1 GB memory. I chose the latest version of Oracle Linux 8.
An OCI DB system with 1 OCPU and 8 GB memory running Oracle Database Enterprise Edition. Yes, this is possible with the Oracle's recent support for Ampere chips. Watch the announcement below for more information.
A Virtual Cloud Network (VCN) with at least two private subnets, the necessary routes and Network Security Groups to provide access for SSH, HTTP, and database traffic between their respective source and destinations.

Note

For the Compute instance, I wanted to provision and run ORDS on an Ampere compute, but unfortunately, these RPMs are not available yet on the YUM repositories for ARM (I heard that it's coming very soon 😉). Do know that ORDS can run with no issues on ARM. I had demonstrated that previously in this post.

Update 14-AUG-2023:

Kris Rice pointed out that the packages are noarch. If you wanted to deploy on an ARM Compute, for now, you will need to add the repository for the x86_64 architecture before running any of the steps in this article.
REGION=$(oci-metadata | grep "Canonical Region Name" | sed 's/^.*:\s*//') && \
  sudo dnf config-manager --add-repo https://yum.${REGION}.oci.oraclecloud.com/repo/OracleLinux/OL8/oci/included/x86_64/

Task 1 - Set Environment Variables

I like automating my builds with scripts, and a huge part of it involves writing Bash commands that do not embed parameters and secrets so they are reusable, and more importantly, secure. Here are a few environment variables that I defined:

export SYS_PASSWORD=mysecretpassword
export DB_HOST=mydb.myprivatesubnet.myvcn.oraclevcn.com
export DB_PORT=1521
export SERVICE_NAME=mypdb.myprivatesubnet.myvcn.oraclevcn.com
export APEX_PUB_USER_PASSWORD=mysecretpassword
export APEX_LISTENER_PASSWORD=mysecretpassword
export APEX_REST_PUB_USER_PASSWORD=mysecretpassword
export ORDS_PROXY_USER_PASSWORD=mysecretpassword
export INTERNAL_ADMIN_PASSWORD=mysecretpassword
export APEX_VERSION=23.1
export APEX_PATCHSET_NUMBER=3
export ENABLE_DATABASE_ACTIONS=Y
export TLS_TERMINATED_AT_LB=Y
export JDBC_MAX_LIMIT=50
export JDBC_INITIALLIMIT=10
export APEX_TABLESPACE=SYSAUX
export APEX_FILES_TABLESPACE=SYSAUX
export APEX_TEMP_TABLESPACE=TEMP
export ORDS_PROXY_USER_TABLESPACE=SYSAUX
export ORDS_PROXY_USER_TEMP_TABLESPACE=TEMP
export ORDS_METADATA_TABLESPACE=SYSAUX
export ORDS_METADATA_TEMP_TABLESPACE=TEMP
export ORDS_CONFIG_PATH=/etc/ords/config

### Do not modify the variables below ###
export APEX_BASE_VERSION=$APEX_VERSION.0
export APEX_IMAGES_PATH=/i/$APEX_BASE_VERSION/
export APEX_PATCH_VERSION=$APEX_VERSION.$APEX_PATCHSET_NUMBER

The key variables that you will want to pay attention to are:

Environment Variable	Notes
SYS_PASSWORD	The password for the SYS database account.
DB_HOST	The DB system's scan DNS name or IP address of its node.
DB_PORT	The DB system's port that is by default: `1521`.
SERVICE_NAME	The service name of the PDB that APEX will be installed in.¹
APEX_PUB_USER_PASSWORD	Set a suitable password and store it safely.
APEX_LISTENER_PASSWORD	Set a suitable password and store it safely.
APEX_REST_PUB_USER_PASSWORD	Set a suitable password and store it safely.
ORDS_PROXY_USER_PASSWORD	Set a suitable password and store it safely.
INTERNAL_ADMIN_PASSWORD	The password for the ADMIN user that allows you to administer the APEX instance.
APEX_VERSION	The desired major release version.²
APEX_PATCHSET_NUMBER	The desired patch set bundle number that is available through the YUM repository.³

This is usually the combination of the PDB's name and the DB system's host domain name. You may also retrieve the service name from the PDB's connect strings available through the OCI Console.

Only version 22.2 and 23.1 are available at the time of writing.

Only patch set bundle 3 for APEX version 23.1 is available at the time of writing. Set it to 0 if there are no patches to be installed.

The installation process will require running Bash commands as the opc and oracle users. The latter is created when the RPMs are installed. To make the environment variables available to all users, I placed the export statements in the file /tmp/setenv.sh. Before executing the tasks below, I would load the environment variables with this command:

. /tmp/setenv.sh

Task 2 - Install the Packages

Let's get the Oracle APEX, Oracle APEX static resources, and ORDS packages installed. We will do this as the opc user that is provisioned with every OCI Compute instance. It also has sudo privileges to run scripts as root and oracle. The latter is created when either the ORDS or APEX RPMs are installed.

. /tmp/setenv.sh

sudo dnf install -y --skip-broken \
  oracle-apex${APEX_VERSION} \
  oracle-apex${APEX_VERSION}-images \
  oracle-apex${APEX_PATCH_VERSION}-patch \
  ords \
  jdk-17

Note

ORDS requires the Java Development Kit (JDK) to be installed. Versions 11 and 17 are supported, and I chose the latter.

Task 3 - Install Oracle APEX

Run this task as oracle user. You may switch to the user using the command:
```
sudo su - oracle
```
Load the environment variables.
```
. /tmp/setenv.sh
```

Change the working directory.

cd /opt/oracle/apex/${APEX_BASE_VERSION}/

Run the APEX installation script.

sql sys/"$SYS_PASSWORD"@$DB_HOST:$DB_PORT/$SERVICE_NAME as SYSDBA <<EOF
  whenever sqlerror exit 17

  -- 3.1 Check that the desired version of APEX hasn't already been installed.
  --     Note that the installer script already checks this, so this is just to
  --     save a little time.
  declare
    l_status dba_registry.status%type;
  begin
    begin
      select status into l_status
      from dba_registry
      where comp_id = 'APEX' and version like '${APEX_VERSION}%';

      raise_application_error(-20901, 'APEX version ${APEX_VERSION} already installed.');
    exception
      when no_data_found then
        null;
    end;
  end;
  /

  -- 3.2 Install APEX using the silent installer script.
  @apxsilentins.sql $APEX_TABLESPACE $APEX_FILES_TABLESPACE $APEX_TEMP_TABLESPACE $APEX_IMAGES_PATH $APEX_PUB_USER_PASSWORD $APEX_LISTENER_PASSWORD $APEX_REST_PUB_USER_PASSWORD $INTERNAL_ADMIN_PASSWORD
EOF

Task 4 - Install and Configure ORDS

If you have not already done so, run this as oracle user:

sudo su - oracle

And load the environment variables.

. /tmp/setenv.sh

Install and configure ORDS.

ords --config $ORDS_CONFIG_PATH install \
  --admin-user sys \
  --proxy-user \
  --db-hostname $DB_HOST \
  --db-port $DB_PORT \
  --db-servicename $SERVICE_NAME \
  --schema-tablespace $ORDS_METADATA_TABLESPACE \
  --schema-temp-tablespace $ORDS_METADATA_TEMP_TABLESPACE \
  --proxy-user-tablespace $ORDS_PROXY_USER_TABLESPACE \
  --proxy-user-temp-tablespace $ORDS_PROXY_USER_TEMP_TABLESPACE \
  --log-folder \$HOME/install-ords-$(date +"%Y%m%d%H%M%S") \
  --feature-sdw $([[ "$ENABLE_DATABASE_ACTIONS" =~ (Y|y) ]] && echo 'true' || echo 'false') \
  --password-stdin <<EOF
$SYS_PASSWORD
$ORDS_PROXY_USER_PASSWORD
EOF

ords --config $ORDS_CONFIG_PATH config set jdbc.InitialLimit ${JDBC_INITIAL_LIMIT:-10}
ords --config $ORDS_CONFIG_PATH config set jdbc.MaxLimit ${JDBC_MAX_LIMIT:-3}

if [[ "${TLS_TERMINATED_AT_LB:-N}" =~ (Y|y) ]]; then
  ords --config $ORDS_CONFIG_PATH \
    config set security.httpsHeaderCheck "X-Forwarded-Proto: https"
fi

Task 5 - Deploy APEX Patch Set Bundle

IMPORTANT Perform this task only if the desired patch set bundle is available.

If your Oracle Support customer support identifier (CSI) is only for your OCI tenancy, then you should be aware that your CSI does not permit you to download patches, including Oracle APEX patch set bundles. To get them, you would have to submit a service request (SR) to have Oracle Support prepare a SFTP download for you, and it can be a pain... sometimes. It appears, starting with APEX 23.1.3, the patches are easily available through a simple RPM install.

Run this task as oracle user. You may switch to the user using the command:
```
sudo su - oracle
```
Load the environment variables.
```
. /tmp/setenv.sh
```

Install the patch and update the instance's IMAGE_PREFIX.

rpm -q oracle-apex${APEX_PATCH_VERSION}-patch

if [[ $? = 0 ]]; then
  cd /opt/oracle/apex/${APEX_PATCH_VERSION}/

  sql sys/"$SYS_PASSWORD"@$DB_HOST:$DB_PORT/$SERVICE_NAME as SYSDBA <<EOF
    whenever sqlerror exit 18

    declare
      l_status dba_registry.status%type;
    begin
      begin
        select status into l_status
        from dba_registry
        where comp_id = 'APEX' and version = '${APEX_PATCH_VERSION}';

        raise_application_error(-20901, 'APEX version ${APEX_PATCH_VERSION} already installed.');
      exception
        when no_data_found then
          null;
      end;
    end;
   /

   @catpatch.sql
EOF

  if [[ $? = 0 && -d "/opt/oracle/apex/$APEX_PATCH_VERSION/images/" ]]; then
    sql sys/"$SYS_PASSWORD"@$DB_HOST:$DB_PORT/$SERVICE_NAME as SYSDBA <<EOF
      exec apex_instance_admin.set_parameter('IMAGE_PREFIX', '/i/$APEX_PATCH_VERSION/');
      commit;
EOF
  fi
fi

Task 6 - Configure the ORDS Service

Enable the ORDS service to start and stop automatically at boot and shutdown respectively.
```
sudo systemctl enable ords
```
Either reboot the server, or start up the service for the first time.
```
sudo systemctl start ords
```

If necessary, configure the operating system's firewall to allow traffic to port 8080.

sudo sh -s <<EOF
firewall-cmd --zone=public --add-port 8080/tcp --permanent
firewall-cmd --reload
EOF

Note About Oracle APEX Static Resources

When either the APEX "images" or patch is installed, a symbolic link is created in /etc/ords/config/global/doc_root/i. For version 23.1 and patch version 23.1.3, you should expect to see this:

[root@ords01 i]# ls -ltr
total 0
lrwxrwxrwx. 1 root root 30 Aug 13 01:30 23.1.0 -> /opt/oracle/apex/23.1.0/images
lrwxrwxrwx. 1 root root 30 Aug 13 01:30 23.1.3 -> /opt/oracle/apex/23.1.3/images

For 22.2, unfortunately, this is not created during the RPM installation. The files are also deployed to the path /opt/oracle/apex/images/22.2.0. To quickly resolve the issue, create the link using the script below.

sudo sh -s <<EOF
[[ ! -d "$ORDS_CONFIG_PATH/global/doc_root/i" ]] && mkdir -p $ORDS_CONFIG_PATH/global/doc_root/i || echo "Directory exists"
ln -s /opt/oracle/apex/images/$APEX_PATCH_VERSION $ORDS_CONFIG_PATH/global/doc_root/i/$APEX_PATCH_VERSION
EOF

info

Image by PublicDomainPictures from Pixabay

Required OCI Resources​

Task 1 - Set Environment Variables​

Task 2 - Install the Packages​

Task 3 - Install Oracle APEX​

Task 4 - Install and Configure ORDS​

Task 5 - Deploy APEX Patch Set Bundle​

Task 6 - Configure the ORDS Service​

Note About Oracle APEX Static Resources​