Running ORDS Against the Autonomous Database

The Oracle Autonomous Database (ADB) is Oracle's current flagship database product and rightly so! Each instance comes with self-tuning, self-patching and several Oracle Database Options that customer would otherwise have to purchase separate licenses for. Database developers will also be pleased to know that there are a suite of tools at your disposal including Oracle Application Express (APEX), Oracle REST Data Services (ORDS) and Oracle Machine Learning Notebooks.

The minimum instance you can create will have 1 CPU and 1 TB of storage and with so much features, doesn't come cheap. Here's the good news though, Oracle provides everyone two Always Free ADBs through the Oracle Cloud Free Tier offering. Yes, that's a great deal for anyone wanting to host a simple web application on the Internet. It is however, not without limits.

Unlike the paid Autonomous Database, a free instance is limited to 1 CPU with no autoscaling, 8 GB of RAM and 20 GB of storage. It will also be shutdown after 7 days of inactivity, and even terminated if left in that state for too long. Another important limitation is the 20 concurrent database session limit imposed on the instance. These are crucial considerations that you will need to take into account when running production applications on the free instance.

If you can run your applications successfully within these limits, then ADBs are a great choice for deploying APEX applications for several reasons. The most obvious benefit is that you do not have to be an expert database administrator (DBA) to install, tune and manage the database. These are all handled automatically by Oracle. Upgrading and patching the database and APEX are either performed incrementally in the background or in some cases, initiated by the user with a simple button click.

Typically, after installing APEX on the database, users will also have to install, configure and deploy ORDS. The software can either run in a standalone mode or deployed on a Java Enterprise Edition (JEE) container. For the ADB though, ORDS comes preinstalled on a separate, robust and scalable hardware infrastructure.

Not surprisingly, that convenience comes with some restrictions. When the ADB is provisioned, you are assigned a cryptic URL that Oracle assigns and till today (June 30, 2020), vanity URLs are still not supported. That means, you may not assign a domain name to your APEX instance, e.g. http://example.com/orcl/apex.

While you can use a web server and appropriate proxy rules, mask the APEX instance, it does have inherent security risks. I have also found it challenging to get Social Sign-In (OAuth2) working with APEX applications because of how URLs used in the authentication workflow are generated.

Access to the database and its tools are managed together. It is my understanding that it is not possible to assign different access rules. For example, perhaps we want users to access the APEX instance from the web, but only connect to the database using SQL Developer while on the corporate network. That's not possible at least for now.

Thankfully, with the release of ORDS version 19.4.6, it is now possible to install your own instance of ORDS alongside the ADB. You can find the feature description here.

BUG 30957867 - Implement feature to support alternative runtime user (ORDS_PUBLIC_USER)

With a customer-managed ORDS, we can create different entry points to the ADB. This ORDS instance can be placed in an Internet-facing subnet, while access to the ADB can be limited to the new ORDS instance and restricted networks by either using an Access Control List (ACL), or in the case of paid instances, assigned a private endpoint address.

You can find the full-length instructions from Oracle here, on how to set this up. I have however, summarised the steps here, as well as noted the issues I encountered and the solutions.

These instructions assume that you have the following provisioned:

1. Autonomous Database. Any workload type is fine, and it can be either paid or Always Free.
2. Always Free Micro-shape Compute Instance. The Compute may be of any shape, but the assumption is that it runs Oracle Linux 7.8.

You may attempt to use Oracle Autonomous Linux 7.8, however, many of the packages required are installed using YUM, and this prebuilt OS image has limited repositories available. While there's a workaround, you are advised in the documentation not to mess with the configuration. If you're interested to know what these are, please check with me privately.

Tasklist for ADB

User Accounts

Installing and configuring ORDS usually requires the following accounts:

• APEX_PUBLIC_USER
• APEX_LISTENER
• APEX_REST_PUBLIC_USER
• ORDS_PUBLIC_USER

The first three are created in a typical APEX installation. The last one is created when we install ORDS. However, in ADB, these accounts are already created and we do not have the passwords, so clearly, a different approach is needed.

In this new ORDS installation method, we will use a different database user. ORDS_PUBLIC_USER2 is the recommended name, but you don't have to name it so. You must however, as the admin user, assign the necessary privilege by executing the following commands in the database:

-- 1. Create the database user.
create user ords_public_user2 identified by "duntellanyone";

-- 2. Allow the user to connect to the database.
grant connect to ords_public_user2;

-- 3. Perform some magic.
begin
    ords_admin.provision_runtime_role(
        p_user => 'ORDS_PUBLIC_USER2'
        , p_proxy_enabled_schemas => true
    );
end;
/

Client Credentials

Download the wallet necessary for connecting to the ADB and then upload it securely, to the Compute instance's /tmp directory.

Tasklist for Compute

As an Oracle Cloud Infrastructure (OCI) customer, you will have access to software like Oracle Instant Client, Java runtimes and ORDS through a private YUM repository. Login to the server as the opc user using SSH and then enable the repository:

sudo yum-config-manager --enable ol7_oci_included

Install ORDS using YUM, which will automatically install its dependencies include a supported Java Runtime Engine (JRE):

sudo yum install -y ords

The package installation also creates and oracle OS user that will be used to configure and run the standalone server. Switch to that user:

sudo su - oracle

Set the ORDS_CONFIG_DIR variable to the base directory where ORDS configuration files will be stored, and then create the necessary directory structure:

ORDS_CONFIG_DIR=/opt/oracle/ords/config && mkdir -p $ORDS_CONFIG_DIR/ords/conf

Set the ORDS configuration directory using the command ords:

ords configdir $ORDS_CONFIG_DIR

If you are running this on an Always Free Compute instance, chances are, you may encounter this error:

Caused by: java.lang.OutOfMemoryError: Java heap space

Try setting the JAVA_OPTIONS variable with an appropriate maximum heap size before reattempting to set the ORDS configuration directory. Run the command:

export JAVA_OPTIONS=-Xmx512M

Next, set the following environment variables:

ORDS_USER=ORDS_PUBLIC_USER2
ORDS_PASSWORD=duntellanyone
SERVICE_NAME=myadb_low
WALLET_BASE64=`base64 -w 0 /tmp/wallet_MYADB.zip`

Then run the following script to create the required ORDS configuration files:

cat << EOF > $ORDS_CONFIG_DIR/ords/conf/apex_pu.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <entry key="db.username">$ORDS_USER</entry>
  <entry key="db.password">!$ORDS_PASSWORD</entry>
  <entry key="db.wallet.zip.service">$SERVICE_NAME</entry>
  <entry key="db.wallet.zip"><![CDATA[$WALLET_BASE64]]></entry>
</properties>
EOF

cat << EOF > $ORDS_CONFIG_DIR/ords/defaults.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <entry key="plsql.gateway.enabled">true</entry>
  <entry key="jdbc.InitialLimit">5</entry>
  <entry key="jdbc.MaxLimit">10</entry>
</properties>
EOF

Once the configuration is done, logout and return to the OS as the opc user. Start the ORDS service using the following command:

sudo systemctl start ords

Make sure you have and OCI Security List rule that has port 8080 opened and accessible to your client machine. On the Compute instance, you will also need to ensure that the firewall port is opened. Run the following command:

sudo firewall-cmd --zone=public --add-port 8080/tcp

Check that ORDS is accessible from your browser, e.g. http://example.com:8080/ords/apex. If so, save the OS firewall rule:

sudo firewall-cmd --zone=public --add-port 8080/tcp --permanent

Enable the ORDS service to start automatically on boot:

sudo systemctl enable ords

APEX Image Files

Login as the oracle user again to deploy the APEX image files.

Download the compatible APEX installer and extract the contents of the images directory to /opt/oracle/apex/images/$RELEASE, where the RELEASE must be set to the current release number in full.

Based on what's current, set the following values:

RELEASE=20.1.0.00.13
APEX_FILE=/tmp/apex_20.1.zip

Then execute the following:

mkdir -p /opt/oracle/apex/images/$RELEASE
unzip $APEX_FILE -d /tmp/
cp -R /tmp/apex/images/* /opt/oracle/apex/images/$RELEASE
rm -rf /tmp/apex

After starting ORDS the first time, the standalone properties file should be created. Edit the file /opt/oracle/ords/config/ords/standalone/standalone.properties and add the following two lines:

standalone.static.context.path=/i
standalone.static.path=/opt/oracle/apex/images

Finally reboot the server and make sure everything is running as expected.

That's it?

No it's not. Checkout my next post on how to setup an Always Free Load Balancer to front the ORDS server you have just created.