Monday, August 31, 2020

Free Oracle Cloud: Custom Domain Name (URL) with your own ORDS on the Webserver

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">
<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.

10 comments:

Garry Lawton said...

Hi,
you wrote "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."
Is this really true? I understood that the number of DB connections is limited regardless from where they come.

Regards, Garry

ERPisT.com said...

Hi,
i have a doubt that point "20 concurrent connections" suppose my apex application(not oracle db connection through sql) in production and i want to know is this limitation apply on "apex app end user concurrent ?"

Diego Fion said...

I have an issue with my configuration, Application builder works great but one of my applications is configured to Login using MS Azure portal and when the login process ends the callback URL is just completely wrong: https://www.myapp.com:80/ords/apex_authentication.callback

I'm not sure why it's adding the 80 port to the https protocol, and that is causing the following error:

An error occurred during a connection to www.myapp.com:80. SSL received a record that exceeded the maximum permissible length.

Error code: SSL_ERROR_RX_RECORD_TOO_LONG


Any ideas on what could be happening here?

zaks said...

what about "proxy_intercept_errors on;" to handle ords errors, to avoid exposing adb link "Access to this resource is protected. Please sign in to access this resource. "

Alexander Burbello said...

Hi Dimitri,

Great post!!
And I want to complement with another workaround when getting java OutOfMemoryError: Java heap space. Just run:
java -Xmx1024m -Xms1024m -jar ords.war install advanced

See ya.

Barry Samuel said...

Thanks Dimitri, however I too have a doubt about being able to exceed the 20 concurrent connections. I load tested the "built-in" ORDS and once it reached 18 ORDS_PUBLIC_USER connections I started seeing errors. Installing and configuring ORDS to allow a MaxLimit of 50 connections I re-ran the load test and this time once 19 ORDS_PUBLIC_USER2 connections were in action I started getting errors. I expected this to ramp up to 50. Do I have some sort of flawed understanding of what the separate ORDS installation is supposed to achieve?

Rafael Marteze said...

To resolve the "OutOfMemoryError: Java heap space" when starting the service, you need to edit the file "/etc/ords/ords.conf" and add the line "JAVA_OPTIONS = -Xmx800M" (set the amount of memory to your needness).

Unknown said...

I'm encountering the same issue as Diego Fion has (see above).
Do you (or anyone else) has any idea how to resolve this?

You're blogs have been really helpful.

Unknown said...

Hello,

I have followed your tutorial, but when I setup nginx and visited the url it gets redirected to jetty url:port and not ords.

upstream ords {
server 127.0.0.1:9090;
}

server {
listen 80 default_server;
listen [::]:80 default_server;
server_name atp.spacex.ae;
root /usr/share/nginx/html;

# Load configuration files for the default server block.
include /etc/nginx/default.d/*.conf;
try_files $uri /index.html;

location / {
proxy_pass http://ords;
proxy_set_header Host $host;
}

location /i/21.1.3/ {
alias /opt/oracle/apex/images/;
}
}

Siddharth Rajpoot said...

I am scratching my head past few days. When i execute below commands-

>su - oracle
>cd /opt/oracle/ords
>java -jar ords.war standalone

It ask me HTTP or HTTPs and then port and after that it simply output the below line :
/opt/oracle/ords/config/ords/ords
nothing happens then

Ords should run as per your screenshot.
Also if i do above commands using root then it just shows below output -

Oracle REST Data Services version : 21.4.0.r3481956
Oracle REST Data Services server info: jetty/9.4.44.v20210927

and again nothing happens. control doesn't return to next prompt.

Please guide me.