Dimitri Gielis Blog (Oracle Application Express

This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

One of the most popular posts in this series is how to Setup a web server on the Virtual Machine.

In that blog post we setup Nginx as our web server, and as a proxy in front of Oracle APEX running on the Autonomous Database Cloud, so we can serve our own domain name e.g. dgielis.com.

Since the release of ORDS 19.4.6 (and higher), Oracle supports that we run our own ORDS version in front of the Oracle Autonomous Database Cloud. This is really cool, for a couple of reasons:

We can tune ORDS. Oracle limits our Free ATP to 20 concurrent connections, having our own ORDS we can allow more simultaneous connections to our database by changing the config files.
We are able to create a reverse proxy on the same network as where ORDS is running, which will result in a more secure solution.
We can have multiple ORDS running against the same database.
We have more security options as we can choose where we run ORDS.

But before we go into the details on how to run ORDS on your own Compute VM and let it talk to the Autonomous Database Cloud, I also want to highlight a few consequences of doing this.

There are two main points of attention running your own ORDS in front of your APEX instance:

You are responsible to keep your own ORDS version the same as the on the Autonomous Database Cloud. Whenever Oracle upgrades ORDS, you have to upgrade your own ORDS too.
You have to update your images folder whenever a patch or upgrade is done for APEX.

Here's a small script I use to check the version of both Oracle APEX and ORDS:

select 'APEX' as product, version_no, api_compatibility, case when patch_applied = 'APPLIED' then (select listagg('Patch ' || to_char(patch_number) || ' (' || patch_version || ') installed on ' || installed_on, ', ') within group (order by installed_on) as patches from apex_patches) end as applied_patches from apex_release

union all

select 'ORDS' as product, ords.installed_version as version_no, null as api_compatibility, null as applied_patches from dual;

This results in:

You could automate the check, and in case something changed that it will email you for example.

Now, let's get started with the installation of ORDS on the same machine as our Nginx webserver.

# Log in as the opc user to the Compute VM and install Nginx and ORDS:

ssh 150.136.245.144 -l opc -i .ssh/oraclecloud

# connect as root

sudo su

# as a best practice, update all packages on Linux

yum update

# install Nginx webserver

yum install -y nginx

# load repo which holds ORDS

yum-config-manager --enable ol7_oci_included

# in case you get an error, add the repo first manually

/etc/yum.repos.d

vi oci-included-ol7.repo

[ol7_oci_included]

name=Oracle Software for OCI users on Oracle Linux $releasever ($basearch)

baseurl=https://yum$ociregion.oracle.com/repo/OracleLinux/OL7/oci/included/$basearch/

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

gpgcheck=1

enabled=1

yum-config-manager --enable ol7_oci_included

# install ORDS

yum install -y ords

Let's also make sure we have the latest versions of SQLcl and the Oracle software and wallet.

Download SQLcl here and check this blog post about the software and wallet and let's make sure we can still connect from our compute instance to our Autonomous Database.

# uninstall old Oracle Instant client

yum remove oracle-instantclient18.5-tools.x86_64

# install latest Oracle Instant client

yum install oracle-instantclient19.8-basic.x86_64

# connect as oracle

su - oracle

# set environment variables

export PATH=/usr/lib/oracle/19.8/client64/bin:$PATH

export LD_LIBRARY_PATH=/usr/lib/oracle/19.8/client64/lib

export TNS_ADMIN=/usr/lib/oracle/19.8/client64/lib/network/admin

# connect with SQLcl to see if we can connect from the VM to the DB

./sql admin@dbdimi_high

If for some reason something is not working any longer, follow the steps again in the blog post how to run sqlcl from the compute instance to ATP.

We first have to create an ORDS user we will connect at, and it won't be the normal ORDS_PUBLIC_USER as that is used by ATP itself. We will create an ORDS_PUBLIC_USER2 as specified in the Installing and Configuring Customer Managed ORDS on Autonomous Database documentation.

CREATE USER "ORDS_PUBLIC_USER2" IDENTIFIED BY "changeme";

GRANT "CONNECT" TO "ORDS_PUBLIC_USER2";

BEGIN

ORDS_ADMIN.PROVISION_RUNTIME_ROLE(

p_user => 'ORDS_PUBLIC_USER2',

p_proxy_enabled_schemas => TRUE);

END;

Now let's configure ORDS to connect to this user in the database:

# make a base64 string of the zip

mkdir /opt/oracle/ords/wallet

cd /opt/oracle/ords/wallet

cp /tmp/wallet_DBDIMI.zip .

base64 -w 0 wallet_DBDIMI.zip > wallet_DBDIMI.zip.b64

WALLET_BASE64=`cat wallet_DBDIMI.zip.b64`

# create the apex_pu.xml and defaults.xml configuration files for ORDS

cat << EOF > /opt/oracle/ords/config/ords/conf/apex_pu.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">

<entry key="db.username">ORDS_PUBLIC_USER2</entry>

<entry key="db.password">!changeme</entry>

<entry key="db.wallet.zip.service">dbdimi_low</entry>

</properties>

EOF

cat << EOF > /opt/oracle/ords/config/ords/defaults.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">

</properties>

EOF

Let's start ORDS manually first, so we can see if there are any errors:

cd /opt/oracle/ords

java -jar ords.war standalone

Note: In case you get java.lang.OutOfMemoryError: Java heap space, you can fix that by doing:

export JAVA_OPTIONS=-Xmx512M

In July I got this error, when I tried again last week I didn't anymore.

Copy Oracle APEX images folder.

Download Oracle APEX and upload the zip file with your favorite SFTP program to your compute instance. E.g. I use Transmit on OSX or you can use wget or something else.

# Unzip the file in the /opt/oracle folder

unzip /tmp/apex_20.1_en.zip -d /opt/oracle/

You need to make sure your APEX Images folder is in sync with the version of APEX running on ATP.

Typically Oracle will apply some patches, so download from Oracle Support, the patch for Oracle APEX and download it to your Compute Instance:

# Unzip the file in the /tmp

unzip p30990551_2010_Generic.zip

# Overwrite the files from the original Oracle APEX folder

cp -rf /tmp/30990551/images /opt/oracle/apex

# Edit the standalone.properties to add the reference to the images folder

vi /opt/oracle/ords/config/ords/standalone/standalone.properties

# Add

standalone.static.context.path=/i

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

Restart ORDS again, and once we are sure all is working, let's start ORDS as a service and enable it to autostart on reboot:

# make sure you are root user

exit

# run ORDS service and enable with startup

systemctl start ords

systemctl enable ords

Next, we want to configure Nginx as a reverse proxy in front of ORDS.

ORDS by default is running on port 8080. You may want to read my previous blog post on how to get started with Nginx. I didn't configure the domain yet, but I just wanted to access the IP.

The different bit is :

vi /etc/nginx/conf.d/dgielis.com.conf

Add the following:

server {

listen 80;

listen [::]:80;

server_name 150.136.245.144;

root /usr/share/nginx/html/dgielis.com;

index index.html;

try_files $uri /index.html;

location /i/20.1.0.00.13/ {

alias /opt/oracle/apex/images/;