The Oracle Autonomous Database (ADB) is Oracle’s currernt 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 Autonomouos 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 conncurent 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:
- Autonomous Database. Any workload type is fine, and it can be either paid or Always Free.
- 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.
Installing and configuring ORDS usually requires the following accounts:
The first three are created in a typical APEX installaation. 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.
Download the wallet necessary for connecting to the ADB and then upload it securely, to the Compute instance’s
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
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 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:
Next, set the following environment variables:
Then run the following script to create the required ORDS configuration files:
cat << EOF > $ORDS_CONFIG_DIR/ords/conf/apex_pu.xml
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
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:
Then execute the following:
mkdir -p /opt/oracle/apex/images/$RELEASE
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:
Finally reboot the server and make sure everything is running as expected.
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.