Introduction

In this post, I will describe a production ready architecture (with some caveats) where you can run Oracle APEX and ORDS for free on your own custom domain. The entire architecture runs on the Oracle Cloud Infrastructure (OCI) Free Tier and costs $0/month to run. I should add that this post is not a How To guide, it is intended to make you aware of what is possible as well as the pros and cons of running this architecture.

Architecture Overview

I think it would be useful to discuss what happens when a user tries to access your custom domain (example.com):

The DNS provider routes requests to example.com to the IP address of the OCI VM.
NGINX on the VM (acting as a reverse proxy) holds SSL certificates (provided by Let's Encrypt) and routes the request to ORDS (localhost/ords)
ORDS routes the request to the appropriate DB server.
ORDS connects to the DB server using a wallet zip file (the same one used to connect to an ATP instance from SQL Developer).
APEX on the ATP instance generates the HTML response and it is returned to the browser.

Virtual Machine

The VM running on OCI is a ‘VM.Standard.E2.1.Micro’ shape running Oracle Linux 7.9. This provides 1 GB of RAM, 1 OCPU and 0.48 gbps of network bandwidth. This may not seem like a lot but it easily handles 50 or so active users. You can provision up to two VMs on the Free Tier so I provisioned the second one as a backup/stand by ORDS server.

In previous projects I have used Apache as a reverse proxy. Having seen the growth in popularity of NGINX I wanted to try it out and used NGINX for this architecture. It is light weight, easy to configure and also seems to be quite fast. I had noticed on the forums that people were having difficulty setting up NGINX to work with ORDS, APEX and Social Sign-on. I was able to get passed this issue so feel free to reach out to me if you want a copy of my NGINX configuration file. Having NGINX sit in-front of ORDS, provides some additional protection for ORDS (and your database). For example you can configure NGINX to restrict requests from the same IP address which helps with denial of service attacks.

Let's Encrypt is a nonprofit Certificate Authority that provides free TLS certificates. If you own your own domain, you can have a secure https URL using Let's Encrypt. When you generate your certificate with Let's Encrypt, it will automatically update your NGINX configuration file to reference the generated certificates. Using the certbot client, you can even have your certificates renew automatically which is a timesaver.

Why not use an OCI Load Balancer? You may be wondering why I don't use an OCI Load Balancer instead of NGINX? When you launch an OCI Load Balancer it requires that you upload your own SSL certificate. Given that a wildcard SSL certificate costs $400 or more per year our setup would no longer be free if we went this route.

By default an Oracle Cloud Free Tier Autonomous Transaction Processing (ATP) instance comes with a shared Oracle REST Data Services (ORDS) server and a long gnarly URL to access APEX. If you have your own VM, you can instead install your own Customer Managed ORDS. Being able to do this is the key to being able to direct traffic from your domain name to the specific IP address of your VM. The customer managed ORDS install is slightly different to other ORDS installs that I have done in that:

It uses a wallet zip file (the same one used to connect to an ATP instance from SQL Developer) to connect to your ATP database.
The install steps have you create a new proxy database user ORDS_PUBLIC_USER2 which is the gateway between ORDS and the rest of your ATP database. This means you don't have to manage connection pools for four traditional database users used by APEX and ORDS (APEX_PUBLIC_USER, ORDS_PUBLIC_USER, APEX_LISTENER and APEX_REST_PUBLIC_USER).

An ATP instance provides five pre-defined service names to connect to your ATP database (low, medium, high, tp and tpurgent). These provide five different levels of prioritized access when connecting to the database. The out of the box shared ORDS connection for ATP uses the 'low' service to connect. Having a Customer Managed ORDS implementation, you can pick a different service. I am using the ‘tp’ service.

One side effect of managing your own ORDS install is that you need to keep the version of ORDS running on your VM in sync with the version installed on the ATP database. You can check the version of ORDS running on the VM using the following from the command line java -jar ords.war version. You can get the version installed in the database by running the following SQL select ords.installed_version from dual;

As you have no doubt noticed, running your own VM for ORDS is by no means configuration or maintenance free. It takes half a day to do the initial setup and I spend about an hour a month patching and updating the OS and the software that runs on it.

ATP Database

The Autonomous Transaction Processing Database (ATPDB) that runs on the Free Tier comes with 1 OCPU and 20GB of storage. About 5GB of the 20GB is used by the database and APEX, leaving you with about 15GB. While this may not seem like a lot you can use advanced compression to get to around 25 GB. If you keep attachments etc. in the Free Tier Object storage this gives you over 35GB of storage which can handle many production use cases.

The Free Tier ATPDB limits the maximum number of simultaneous connections to twenty. While a number of people have expressed concern over this limit, I actually think it is sufficient for workloads of 50 or more active users. APEX uses ORDS to connect to the database and ORDS manages a pool of connections for APEX to use. When a user navigates to an APEX page, the connection is returned to the connection pool immediately after the page is rendered and is available for APEX to use for another user. If most of your page views are sub-second then you should be able to support 50 or more active users. This should be enough for many smaller production systems and it also encourages you to keep your page views snappy and improve user experience!

Backup & Restore

Of course I can't discuss a production ready architecture if I don't touch on backup and restore. Although OCI does perform incremental backup on a Free Tier ATPDB instance, you are not allowed to restore from these backups. I assume you could restore if you first upgraded to a paid instance. You also cannot create a manual backup using the Free Tier. In view of this, the responsibility of backing up and restoring your environment is completely yours. This is probably the biggest draw back of using the Free Tier to run a production workload. While certainly not ideal, this is not necessarily a show stopper. You can do the following to backup your data and your APEX applications and if the worst should happen, you can restore everything to your second Free Tier ATP environment.

Backing up Data and DDL

Schedule APEX Automations to initiate Data Pump schema exports to the DB file system (DATA_PUMP_DIR) every night.
Schedule a second automation to copy the Data Pump files to OCI Object Storage. This is made much easier by using OCI Credentials (DBMS_CLOUD.create_credential) and DBMS_CLOUD.PUT_OBJECT to copy the dump files.

Backing up APEX Applications

Use an APEX Automation to call the PL/SQL API apex_export.get_application for each of your APEX applications.
In the same automation call use DBMS_CLOUD.PUT_OBJECT to copy the exported APEX applications to Object Store.

Whilst restore is by no means instant, I can get my 2nd ATP Free Tier instance up and running with data from the previous evening within an hour or two. This SLA would not work for most production systems but is fine for many.

Oracle APEX

The Free Tier ATP databases comes installed with APEX. Oracle regularly (and automatically) applies PSE patches that contain bug fixes. This is a big improvement over other services like Amazon RDS which does not allow you to apply PSE patches after the initial install. Oracle also controls when APEX is upgraded after the release of a new version. Their stated goal is to upgrade after six weeks of a new release (again much quicker than Amazon). You can check this site for news from Oracle on updates to APEX running on ATP.

Object Storage

The OCI Free Tier comes with 10GB of Standard Access, 10GB of Infrequent Access and 10GB of Archive object storage. This gives you quite a bit of storage for file attachments, backup files etc.

The APEX development team have provided an out of the box OCI Web Credential for APEX. This allows you to store credentials in your APEX workspace that can be used to easily authenticate into OCI Object Storage from your APEX applications. This makes adding, fetching and deleting files from your APEX applications much easier. You can even utilize Pre-Authentication requests to generate pre-authorized URLs to post/get files in Object Storage from your APEX Apps. This integration is a great help in keeping BLOB files out of your database and into object storage, where they belong.

Email Delivery

Technically the OCI Free Tier does not include free email delivery. Having said that, it only costs $0.085 per 1,000 emails for the paid service. After nearly a year of using this service I have yet to be charged so I suspect they do not charge at all for less than 1,000 emails in a month.

Note: When you do configure email delivery, don't forget to do the DKIM setups in the OCI console. This will ensure your emails do not get stuck in spam filters.

Conclusion

I hope this post has described that it is possible to run small production workloads on the Oracle Cloud Free Tier. While there is manual setup and maintenance involved and limitations on the various components imposed by the Free Tier, this architecture is viable if your budget does not stretch to the APEX Cloud Service. It is also a great way of experimenting with OCI in a way you would not get to do with the paid offering.

The following links helped me in setting up the architecture described in this post:

Note: The technique used in this guide will not work on Free Tier APEX Service as this service does not allow SQL*Net connections.

🔗 Read More

#️⃣ ORDS Posts
🩳 APEX Shorts
#️⃣ APEX Posts

Running APEX & ORDS on your own Domain with Oracle OCI Free Tier

Table of contents