Run Your Oracle APEX Apps on Autonomous Database with Your Own Domain Name

As of today 14th Sep 2021, Oracle has introduced Vanity URLs for APEX and ORDS on Oracle Autonomous, which allows us to run APEX/ORDS applications with our own domain name on Oracle Autonomous database without managing ORDS servers separately. I hope the article would help those who still want to maintain their ORDS servers for reasons.

Oracle Autonomous Database has been a unique offering from Oracle Cloud Infrastructure (OCI) for running OLTP, data warehouse or mixed workloads. Its self-driving, self-securing and self-repairing?attributes have driven enormous innovation in terms of data management and data driven analytics across different industries. One of the inherited features offered by Oracle Autonomous Database (ADB) is the out of box Oracle Application Express (APEX) which is a low-code rapid application development (RAD) platform to build scalable, secure enterprise apps with world-class features that can be deployed anywhere.

Without the burden of mundane maintenance tasks of traditional data management solutions, data architects, developers, DBAs have much more time to focus on business innovations by quickly developing and deploying compelling apps to solve real world problems and bring immediate value. During this global COVID-19 pandemic, Oracle APEX has played a unique role helping medical communities with its in low-code app dev capability, such as Therapeutic Learning System, PPE Supply and Exchange and Vaccine Research and so on.

The out of box APEX on ADB, however, is based on an Oracle managed ORDS (Oracle REST Data Services) deployment with load balancer on the front end.

Hence, the URLs to APEX on ADB have unique domain names provided by Oracle for each individual ADB instance. They're not memorable as they start with 15 random alpha-numeric characters, which people may or may not like it. I hope this article will help you if you've ever wanted to use your own domain name for Oracle APEX running on ADB.

For those who would prefer to use their own URL for their APEX applications and even their own SSL certificates, the good news is that they can now set up a customer managed ORDS server connecting to the ADB backend. You can deploy your custom domain name either on the ORDS server, e.g. a Tomcat server or using a load balancer as shown in the diagram below.

By the way, you have probably noticed the ADB in the above diagram was deployed in a private subnet with private endpoint, which is what lots of businesses wanted, extra security. However, this is just one of the four deployment options you can choose for Oracle ADB today:

• within OCI Service Network on shared Exadata infrastructure (ADB-S), which can be accessed from anywhere
• within OCI Virtual Cloud Network on shared Exadata infrastructure (ADB-S), which can only be accessed via its Private Endpoint (even if it's in a public subnet)
• within OCI Virtual Cloud Network on dedicated Exadata infrastructure (ADB-D)
• within your own data centre on Exadata Cloud @ Customer infrastructure (ECC)

In terms of how to install/configure customer managed ORDS for ADB, you can visit the Oracle online documentation. This article was intended to give your an example and hopefully to make life a bit easier. Before we get started, here's what we need, which we can get from the Oracle Always Free so you can to try it out for FREE.

• a server (e.g. a Linux virtual machine on OCI Compute) to install/configure ORDS
• an Autonomous Database
• download the ADB client wallet to the ORDS server
• download the APEX19.2 to the ORDS server
• an OCI load balancer instance

As we use the same APEX as installed in ADB, we really just need the Images directory from the APEX19.2 installation package. DON'T TRY TO INSTALL APEX ON ADB. You might not be able to use ADB in VCN if it's an always free instance though but you certainly can use the Always Free ADB instance to practice the following steps.

Step 1: Download ADB Client Wallet and Verify Connection to ADB

ADB client wallet is a zip file you can download from the ADB console. You can also generate wallet via OCI CLI. You should download and upload this wallet zip file onto the ORDS server.

Once the wallet zip file is saved on the ORDS server, you want to convert the wallet zip file into a text format using base64. The content of the file wallet_privadwp.zip.b64 will be used for connecting to ADB in ORDS. We will also decompress the wallet zip file for the next step using SQL*Plus to create a new ORDS Public User.

[opc@kli-syd-ords-02 ~]$ sudo su - oracle

Last login: Thu Jun 25 06:36:44 GMT 2020 on pts/0

[oracle@kli-syd-ords-02 ~]$?

[oracle@kli-syd-ords-02 ~]$ mkdir -p network/admin
?
[oracle@kli-syd-ords-02 ~]$ base64 -w 0 /tmp/Wallet_privadwp.zip > Wallet_privadwp.zip.b64

[oracle@kli-syd-ords-02 ~]$ ls

network? Wallet_privadwp.zip.b64

[oracle@kli-syd-ords-02 ~]$ unzip /tmp/Wallet_privadwp.zip -d network/admin

Archive:? /tmp/Wallet_privadwp.zip

? inflating: network/admin/README ? ?

? inflating: network/admin/cwallet.sso ?

? inflating: network/admin/tnsnames.ora ?

? inflating: network/admin/truststore.jks ?

? inflating: network/admin/ojdbc.properties ?

? inflating: network/admin/sqlnet.ora ?

? inflating: network/admin/ewallet.p12 ?

? inflating: network/admin/keystore.jks 

[oracle@kli-syd-ords-02 ~]$
        

If you chose Oracle Cloud Developer Image when you deployed the compute instance like I did, you would have got SQL*Plus installed already. Alternatively, you may want to download and install Oracle InstantClient with SQL*Plus on to this ORDS server to validate the SQL*Net connectivity between the ORDS server and ADB. Make sure you have got TNS_ADMIN and sql*net.ora set properly and you should use your own TNS alias defined in tnsnames.ora to connect to your ADB instance.

[oracle@kli-syd-ords-02 ~]$?export TNS_ADMIN=/home/oracle/network/admin
[oracle@kli-syd-ords-02 ~]$ cd $TNS_ADMIN
[oracle@kli-syd-ords-02 admin]$ pwd

/home/oracle/network/admin
[oracle@kli-syd-ords-02 admin]$ ls

cwallet.sso? ewallet.p12? keystore.jks? ojdbc.properties? README? sqlnet.ora? tnsnames.ora? truststore.jks

[oracle@kli-syd-ords-02 admin]$ cat sqlnet.ora?

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/oracle/network/admin")))

SSL_SERVER_DN_MATCH=yes

[oracle@kli-syd-ords-02 ~]$sqlplus admin@privadwp_low

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 23 09:27:00 2020

Version 19.6.0.0.0


Copyright (c) 1982, 2019, Oracle.? All rights reserved.


Enter password:?

Last Successful login time: Tue Jun 23 2020 09:25:13 +00:00


Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0


SQL>         

Step 2: Create Customer Managed ORDS User and Role

As ADB comes with in-built ORDS public user ORDS_PUBLIC_USER, we need to create a new user and grant it relevant privileges so we don't break any existing ODRS setup.

SQL> select username from dba_users where username like 'ORDS%';



USERNAME

----------------------------------------------------------------------

ORDS_METADATA

ORDS_PUBLIC_USER


SQL> CREATE USER ORDS_PUBLIC_USER2 IDENTIFIED BY Your_Complex_PW_123;


User created.


SQL> GRANT CONNECT TO ORDS_PUBLIC_USER2;


Grant succeeded.



SQL> BEGIN

?? ? ORDS_ADMIN.PROVISION_RUNTIME_ROLE(

?? ? ? ? p_user => 'ORDS_PUBLIC_USER2',

?? ? ? ? p_proxy_enabled_schemas => TRUE);

END;
/


PL/SQL procedure successfully completed.



SQL>?exit


Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0


[oracle@kli-syd-ords-02 ~]$?        

Step 3: Install/Configure ORDS 19.4.6 on the Customer Managed ORDS Server

You can install ORDS 19.4.6 on the Oracle Linux 7 using YUM. Alternatively, you can download it from OTN. Keep in mind ORDS 19.4.6 or higher is required in this case according to the documentation.

[opc@kli-syd-ords-02 ~]$ sudo yum install ords

Loaded plugins: langpacks, priorities, ulninfo, versionlock

9 packages excluded due to repository priority protections

Excluding 2 updates due to versionlock (use "yum versionlock status" to show them)

Resolving Dependencies

--> Running transaction check

---> Package ords.noarch 0:19.4.6-1.el7 will be installed

--> Finished Dependency Resolution




Dependencies Resolved




==================================================================================================================================================

?Package? ? ? ? ? ? ? ? ? ? ? Arch ? ? ? ? ? ? ? ? ? ? ? ? ? Version ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Repository? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Size

==================================================================================================================================================

Installing:

?ords ? ? ? ? ? ? ? ? ? ? ? ? noarch ? ? ? ? ? ? ? ? ? ? ? ? 19.4.6-1.el7? ? ? ? ? ? ? ? ? ? ? ? ? ol7_oci_included? ? ? ? ? ? ? ? ? ? ? ? ? 65 M




Transaction Summary

==================================================================================================================================================

Install? 1 Package


Total download size: 65 M

Installed size: 69 M

Is this ok [y/d/N]: y

Downloading packages:

ords-19.4.6.rpm? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |? 65 MB? 00:00:00? ? ?

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

? Installing : ords-19.4.6-1.el7.noarch ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 1/1?

INFO: Before start ords, run below command as oracle id to configure it:

INFO:? ? /usr/local/bin/ords install simple?

INFO: In order to add the ords to systemd service run below command:

INFO:? ? sudo systemd enable ords

? Verifying? : ords-19.4.6-1.el7.noarch ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 1/1?


Installed:

? ords.noarch 0:19.4.6-1.el7 ? ? ? ? ? ? ? ? ? 


Complete!

[opc@kli-syd-ords-02 ~]$?        

By default, Yum will install ORDS to /opt/oracle/ords and we can use oracle user to configure it.

[opc@kli-syd-ords-02 ~]$ sudo su - oracle

Last login: Thu Jun 25 06:45:04 GMT 2020 on pts/0

[oracle@kli-syd-ords-02 ~]$ cd /opt/oracle/ords/
[oracle@kli-syd-ords-02 ords]$ ls

docs? examples? index.html? installer? ords.war? params? scripts
[oracle@kli-syd-ords-02 ords]$?

[oracle@kli-syd-ords-02 ords]$ mkdir -p config/ords/conf

[oracle@kli-syd-ords-02 ords]$?

[oracle@kli-syd-ords-02 ords]$ java -jar ords.war configdir ./config

2020-06-25T07:27:47.027Z INFO ? Set config.dir to ./config in: /opt/oracle/ords/ords.war

[oracle@kli-syd-ords-02 ords]$?        

As you can see there was no config directory under /opt/oracle/ords/ initially. I manually created it and a couple of subdirectories. We will then edit the apex_pu.xml file under ORDS_HOME/config/ords/conf using vi. Note that the content of db.wallet.zip (anything between <![CDATA[ and ]]></entry>) comes from the text file Wallet_privadwp.zip.b64 we created in Step 1.

[oracle@kli-syd-ords-02 ords]$ vi config/ords/conf/apex_pu.xml
[oracle@kli-syd-ords-02 ords]$ more config/ords/conf/apex_pu.xml?

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

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

<properties>

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

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

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

<entry key="plsql.gateway.enabled">true</entry>

<entry key="db.wallet.zip"><![CDATA[UEsDBBQACAgIACxlllAAAAAAAAAAAAAAAAAGAAAAUkVBRE1FbY/BTsMwEETv/Yr5ABIlbiiQK3CoxK1FnE28VVcy3mjtJOTvcdJWQog9z7x5+2G9p4TX7551xotNtCn+v83xzBHTJT/ZCCdT8GIdOUiAqUxVVE1hDGrTNnVrmvJpV+P9+FzmKuFweENHmvjEXZ6J6FVGXtockH6z2XvQIkRX8P0KrhfwtmnNrnw0DxfwPkDUkSIJ7CjsYMOMSDpyRxmcSHXoE0vIvgOtsXCFu79Kd5hlwNcQE5SK23vZjW5qn3SSbLXautwofwBQSwcIwEEOO8gAAAA/

AQAAUEsDBBQACAgIACxlllAAAAAAAAAAAAAAAAALAAAAY3dhbGxldC5zc28BTRqy5aH4TjYAAAAGAAAAIQbQS0rr9ulYJqfnNe4rqjWl8ytYTBXUy+yRXRJI2a42mzCCGhwCAQMwghniBgkqhkiG9w0BBwGgghnTBIIZzzCCGcswghnHBgkqhkiG9w0BBwagghm4MIIZtAIBADCCGa0GCSqGSIb3DQEHATAcBgoqhkiG9w0BDAEDMA4ECAJAMDxPkzm/AgInEICCGYCFp54pUNTsSlUUluFlfhpfVK1fYBKOw1fQNd5kPVpDd4UZ3G4l4N+Is60Psjg3Zhfonbr9pTRhFTO6Dj71sf1Q7SzWRkULczTw

rrLpmY1rbHrLsnUO84WNRMQf0Wf7lblE7609Z+LQtcAUdN0t6K9DsKJUAOuDlyysMypnXK4LGtAWkbeiEPTrHOnr4e8hW0j7Ohg0q/fhJg03SVchpxLINCmS6lbWrhGWnKd0JSTKWUmpGYB2Jmz9v1JRtYu1xwjvN96j0knzqi+XJ1g0gARcQGxnCjyQ5jvx5Cvq61bQpb4lDGv3ZeWg5/xi5sCLlx9GxIho+M7zIH2Tkdt7DoSpRcG7adQNrcXUS6+PcRQep7eKDirMI36FLxY+z3VM8bj//goWQJr62KMyCIZslrzkpg3tma/2p3th0082nUYWs4BDSpXOO9rikL6fWhcCf6AhcPXnRlIwi+IFY7FE

xY5ouyQhh5u1T/o29fU68vBLhZfxoL/BzuXWAtQI7SPOodJ8GSptWou4p4BOQFc4JGFMcDbYR+mfLXNjWSceHkohFUlhePYyoNprJqh2BGh55CXxK/V8V4sdl7/cXLcduLor2SPzPHgy+zK3eTceJqBTwmX+v/Me+v2CHz1Q3b+VEGQXxxiBf4hsnmKE4NWW5uFFk1aE+L33G5D9BbBCKWqZ8IJiulHUXWoW2E2I8+oGgE2f7YV2o/v8HfOv051FvQFKj+4QwaN1AVq1Od/7AMPBPAixs4EHCIAfg1sWHAAMq7rvyUc2mgznnUVH5rbohp3KH3PtbkPRi5054HVPk0rDQYo/JgxKpsYYdi3dLLjPWyho

FEiKtlJwzlvh0i3N+5QX5trgokCN1z01gO4DzKOR3/GAkS3DKoYM1SfZ/4XxvHDlhqmoR0uikR7Pc3d12m9O11YJGGFOMu7Csf3b5SQqW0W8JS38JFKERa9X55OGtfqBsz1cCxRu2i5yWl9ddjyjJa2zZucmaC5bYzWNzFqp6fwK9hnN1dROmBCPqXZVInrHLmDb5wJCCwSiCfFtHnSSGV1xm5jbMLIz/Za3z34bKnIVMDU3OlWUrRylj+c67EyorQaCZ9scltaTzXDJgsCRhSRjGafx456yRLJG6JcbBFHhpDKuuKZEcVVMOfqK3ZVbIP53ByE2xGM6cLOdsl1ttBDtW++OodXZioDTkUW3VPaxkbOw

...
...
ADEoAABzcWxuZXQub3JhUEsBAhQAFAAICAgALGWWUNhzGDYlGgAAIBoAAAsAAAAAAAAAAAAAAAAA0SgAAGV3YWxsZXQucDEyUEsBAhQAFAAICAgALGWWUFc8kmn6CgAAzAwAAAwAAAAAAAAAAAAAAAAAL0MAAGtleXN0b3JlLmprc1BLBQYAAAAACAAIAMwBAABjTgAAAAA=]]></entry>

</properties>

[oracle@kli-syd-ords-02 ords]$ ?
        

We're almost there. Now we want to decompress the downloaded APEX19.2 so we can get the path to the APEX images directory.

[oracle@kli-syd-ords-02 ~]$ pwd

/home/oracle
[oracle@kli-syd-ords-02 ~]$ mkdir apex192
[oracle@kli-syd-ords-02 ~]$ unzip /tmp/apex_19.2.zip -d apex192
...
...


[oracle@kli-syd-ords-02 ~]$ cd /opt/oracle/ords/

[oracle@kli-syd-ords-02 ords]$ java -Duser.timezone=Australia/Sydney -jar ords.war standalone --apex-images /home/oracle/apex192/apex/images --port 8080

2020-06-25 18:01:10.387:INFO::main: Logging initialized @2711ms to org.eclipse.jetty.util.log.StdErrLog

2020-06-25 18:01:10.478:INFO:oeju.TypeUtil:main: JVM Runtime does not support Modules

2020-06-25T08:01:10.580Z INFO ? HTTP and HTTP/2 cleartext listening on host: localhost port: 8080

2020-06-25T08:01:10.624Z INFO ? Disabling document root because the specified folder does not exist: /opt/oracle/ords/config/ords/standalone/doc_root

2020-06-25 18:01:11.271:INFO:oejs.Server:main: jetty-9.4.24.v20191120; built: 2019-11-22T11:09:44.612Z; git: 8b8c80157294e38f81ef8ea2358a0c49bf5db918; jvm 1.8.0_231-b11

2020-06-25 18:01:11.460:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0

2020-06-25 18:01:11.461:INFO:oejs.session:main: No SessionScavenger set, using defaults

2020-06-25 18:01:11.463:INFO:oejs.session:main: node0 Scavenging every 660000ms

2020-06-25T08:01:12.477Z INFO ? Obfustcated passwords in: /opt/oracle/ords/config/ords/conf/apex_pu.xml

2020-06-25T08:01:12.494Z WARNING The pool named: |apex|pu| is missing and will be ignored: The connection pool named: apex_pu does not exist

2020-06-25T08:01:12.739Z INFO ? Oracle REST Data Services initialized

Oracle REST Data Services version : 19.4.6.r1421859

Oracle REST Data Services server info: jetty/9.4.24.v20191120        

Instead of manually compile the standalone.properties file or run java -jar ords.war setup, I chose to run it straightaway in standalone mode with a couple of inline parameters, e.g. --apex-images, --port. In this way, ORDS will generate the default standalone.properties file with those parameters for me.

For security purpose we might not be able to access the APEX from your client browser just yet. We should check the security list defined in OCI console plus the iptables rule on the ORDS server to allow the traffic into ORDS server via the selected TCP port 8080.

root@kli-syd-ords-02 opc]# iptables -I INPUT 7 -p tcp --dport 8080 -j ACCEPT

[root@kli-syd-ords-02 opc]# iptables-save
        

It is also important to edit the following two config files to make it better:

• add key="security.httpsHeaderCheck" entry into /opt/oracle/ords/config/ords/default.xml if you want to terminate https on the load balancer.

[oracle@kli-syd-ords-02 ~]$ cd /opt/oracle/ords/config/ords/

[oracle@kli-syd-ords-02 ords]$ cat defaults.xml?

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

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

<properties>


<comment>Saved on Thu Jun 25 18:01:12 AEST 2020</comment>
...
...


<entry key="security.httpsHeaderCheck">X-Forwarded-Proto: https</entry>


</properties>


[oracle@kli-syd-ords-02 ords]$        

This will help you avoid error like this when ORDS is behind a reverse proxy like load balancer.

• point static.path to your APEX192 images directory in standalone.properties as mentioned in docs and static.context.path to /i/19.2.0.00.18/.

[oracle@kli-syd-ords-02 ~]$ cd /opt/oracle/ords/config/ords/

[oracle@kli-syd-ords-02 ords]$ cat defaults.xml?

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

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

<properties>

<comment>Saved on Thu Jun 25 18:01:12 AEST 2020</comment>
...
...

<entry key="security.httpsHeaderCheck">X-Forwarded-Proto: https</entry>

</properties>

[oracle@kli-syd-ords-02 ords]$ cd standalone/

[oracle@kli-syd-ords-02 standalone]$ pwd

/opt/oracle/ords/config/ords/standalone

[oracle@kli-syd-ords-02 standalone]$ cat standalone.properties?

#Thu Jun 25 18:11:43 AEST 2020

jetty.port=8080

standalone.context.path=/ords

standalone.doc.root=/opt/oracle/ords/config/ords/standalone/doc_root

standalone.scheme.do.not.prompt=true

standalone.static.context.path=/i/19.2.0.00.18/

standalone.static.path=/home/oracle/apex192/apex/images

[oracle@kli-syd-ords-02 standalone]$?        

This will help you avoid the following error message.

You can try the following to validate if the APEX page is up.

wget curl https://your_ip_address:8080/ords -O ords.html        

Now, we're finally ready to set up your custom domain and add your own SSL certificate, which can be done through the OCI Load Balancer Service.

Step 4: Configure Load Balancer

First of all, we want to create a load balancer instance on OCI.

Configure the backend set called ords or anything you like to point to the ORDS server we created above with TCP port 8080. For backend server, we don't have to use SSL. You can have multiple backends with the same configuration if you like so the load balancer can truly balance the workloads across multiple ORDS servers.

Of course we want to add virtual hostname (you should have your DNS record with the same name pointing to the public or private IP of your Load Balancer) and upload the SSL certificate you wanted to use for HTTPS for your custom URL.

Lastly, we define the load balancer listeners to listen on the ports you want for your virtual hostname. You can have both HTTPS and HTTP on the default ports or any ports of your choice. If the listener listens on HTTPS/443, remember to associate the SSL certificate you uploaded earlier and tick the "USE SSL" checkbox when adding/editing your listener.

After that, we should be able to access the APEX/ORDS connected to the backend ADB via your own domain name with HTTPS.

Bottom line, Oracle Autonomous Database really drives the innovation and productivity for businesses as it simplifies data management drastically. Oracle has delivered its promise to not only make it available in the public cloud but also enable you to deploy it securely on-premise behind your firewall. Coupled with the most popular low code app dev tool APEX, Oracle Autonomous Database really helps organisations unlock the endless possibilities.

Disclaimer: The views expressed on this article are my own and do not necessarily reflect the views of Oracle.?If you have any questions or comments on this article, please feel free to ping me on LinkedIn.