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/walletcd /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">
<properties>
<entry key="db.username">ORDS_PUBLIC_USER2</entry>
<entry key="db.password">!changeme</entry>
<entry key="db.wallet.zip.service">dbdimi_low</entry>
<entry key="db.wallet.zip"><![CDATA[$WALLET_BASE64]]></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>
<entry key="plsql.gateway.enabled">true</entry>
<entry key="jdbc.InitialLimit">10</entry>
<entry key="jdbc.MaxLimit">20</entry>
</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/;
}
location /ords/ {
proxy_pass http://localhost:8080/ords/;
proxy_redirect off;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-Proto $scheme;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
}
}
Next, you want to reload Nginx:
nginx -s reload
The first time I tried to access http://150.136.245.144/ords/ I got a bad gateway error.
Looking at the logs I saw what was going on:
tail -30 /var/log/nginx/error.log
To fix the issue I did:
cat /var/log/audit/audit.log | grep nginx | grep denied | audit2allow -M mynginx
semodule -i mynginx.pp
And there we go... it worked! Now we can access Oracle APEX with our own ORDS :)
Note: instead of using Nginx, you can also configure a Load Balancer in the Oracle Cloud.
Adrian did a nice blog post on how to do that.
There you go... now you can choose to have your preferred domain name (custom URL) with your own ORDS in the Oracle Cloud.