In one of my recent blogs I’ve described how you can use Nginx as a reverse proxy to assign your custom domain name to the Oracle Apex on Autonomous database. But you still use the default ORDS managed by Oracle. What if you want to change some ORDS parameters? For example you might decide to change the “low” type of connection to “high” or “medium”. In this post I will describe how to install your custom ORDS to work with Autonomous databases in standalone mode, using only Tomcat and Apache http server with Tomcat as the frontend.
Before starting let me describe what was used as a platform for the frontend. I was using AMD flex based Oracle Linux 8 for my ORDS, Apache and Tomcat but the steps would be mostly the same for an Ampere box except ability to install the ORDS from the yum repo(it doesn’t exist for aarch64 platform).
Let’s start from the ORDS only configuration. Oracle has a chapter in documentation about setting up custom ORDS with Autonomous in two places. The first one is here with a link which leads to the old 20.4 ORDS documentation and you shouldn’t use that one. And here is the documentation for the 22.2 ORDS version. I used the latter as the main guideline with minor changes in the approach.
We need to configure your ADB first. It is recommended to create “ORDS_PUBLIC_USER2” and “ORDS_PLSQL_GATEWAY2” users and grant the necessary privileges to them.
CREATE USER "ORDS_PUBLIC_USER2" IDENTIFIED BY YourSecurePassword; GRANT CONNECT TO ORDS_PUBLIC_USER2; CREATE USER "ORDS_PLSQL_GATEWAY2" IDENTIFIED BY YourSecurePassword; GRANT CONNECT TO "ORDS_PLSQL_GATEWAY2"; ALTER USER ORDS_PLSQL_GATEWAY2 GRANT CONNECT THROUGH ORDS_PUBLIC_USER2; BEGIN ORDS_ADMIN.PROVISION_RUNTIME_ROLE( p_user => 'ORDS_PUBLIC_USER2', p_proxy_enabled_schemas => TRUE); END; BEGIN ORDS_ADMIN.CONFIG_PLSQL_GATEWAY( p_runtime_user => 'ORDS_PUBLIC_USER2', p_plsql_gateway_user => 'ORDS_PLSQL_GATEWAY2'); END; /
Having our database prepared (don’t forget to put your box IP to the allow list on the ADB) we move on to our box for the middleware and frontend. You need java and the ORDS itself. The java can be easily installed directly through the dnf utility.
[opc@gleb-ords-01 ~]$ sudo dnf install jdk-18 -y Last metadata expiration check: 1:28:05 ago on Thu 13 Oct 2022 07:22:36 PM GMT. … [opc@gleb-ords-01 ~]$ java -version java version "18.0.2.1" 2022-08-18 Java(TM) SE Runtime Environment (build 18.0.2.1+1-1) Java HotSpot(TM) 64-Bit Server VM (build 18.0.2.1+1-1, mixed mode, sharing) [opc@gleb-ords-01 ~]$ <pre> <!-- /wp:html --> <!-- wp:paragraph --> <p>The ORDS could be installed by two different ways - manually or through the same <em>dnf</em> utility. For manual installation you can either get it from the <a href="https://www.oracle.com/database/sqldeveloper/technologies/db-actions/download/">download page</a> or use curl or wget using this link like <a href="https://download.oracle.com/otn_software/java/ords/ords-latest.zip">this</a>:</p> <!-- /wp:paragraph --> <!-- wp:html --> <pre lang="bash"> [opc@gleb-tomcat-01 ~]$ curl -LO https://download.oracle.com/otn_software/java/ords/ords-latest.zip
Then unzip it to a prepared directory where you will keep the ORDS base.
For the yum or dnf installation you just add the yum repository with ORDS and set it up using the dnf utility. I preferred the latter way and installed through dnf.
[opc@gleb-ords-01 ~]$ sudo dnf list ords Last metadata expiration check: 1:31:33 ago on Thu 13 Oct 2022 07:22:36 PM GMT. Available Packages ords.noarch 22.3.0-7.el8 ol8_oci_included [opc@gleb-ords-01 ~]$ sudo dnf install ords -y Last metadata expiration check: 1:39:11 ago on Thu 13 Oct 2022 07:22:36 PM GMT. Dependencies resolved. ... INFO: Before starting ORDS service, run the below command as user oracle: ords --config /etc/ords/config install INFO: To enable the ORDS service during startup, run the below command: sudo systemctl enable ords Verifying : ords-22.3.0-7.el8.noarch 1/1 Installed: ords-22.3.0-7.el8.noarch Complete! [opc@gleb-ords-01 ~]$
The package creates the oracle user and puts the installation and configuration under the oracle ownership. So, it makes sense to run the ORDS configuration as the user oracle and perform the next configuration steps as this user.
For connection we need the wallet file for our ADB. I’ve used oci-cli to get the file but you can use the web console to get the file and then copy it to the box.
[oracle@gleb-ords-01 ~]$ oci db autonomous-database generate-wallet --autonomous-database-id ocid1.autonomousdatabase.oc1.ca-toronto-1.an2g6ljr2lpveayaa73klf9wi6qqrmi3lxm2wlt5u36k7hnoa26dmwfgbtpa --file wallet.zip --password welcome1 [opc@gleb-ords-01 ~]$ ll wallet.zip -rw-rw-r--. 1 opc opc 26400 Oct 13 20:28 wallet.zip [oracle@gleb-ords-01 ~]$
Also we need the Apex static files. To get them we have to download the apex and unpack it to the ORDS base directory.
[oracle@gleb-ords-01 ~]$ curl -OL https://download.oracle.com/otn_software/apex/apex-latest.zip [oracle@gleb-ords-01 ~]$ unzip -q apex-latest.zip -d /opt/oracle/ords/
The /opt/oracle/ords/apex/images directory is the local source for the static files used by Apex. We also have an option to use a CDN to get the images and later I will show that too.
We need to set the configuration parameters for our ORDS. The recommended place for the configuration is /etc/ords/conf and it has been created for me automatically when I was installing the ORDS. If you’ve done it manually you need to create it by hand. The configuration includes several necessary parameters to set up.
The patch for the Apex static files (don’t forget the slash in the end):
[oracle@gleb-ords-01 ~]$ ords --config /etc/ords/conf config set standalone.static.path /opt/oracle/ords/apex/images/
The path to the downloaded wallet file:
[oracle@gleb-ords-01 ~]$ ords --config /etc/ords/conf config set db.wallet.zip.path ~/wallet.zip
The database credentials:
[oracle@gleb-ords-01 ~]$ ords --config /etc/ords/conf config set db.username ORDS_PUBLIC_USER2 [oracle@gleb-ords-01 ~]$ ords --config /etc/ords/conf config secret db.password
The information about the TNS alias for the connection I want to use. I’ve decided to use the “_medium” instead of the “_low” used by the default ORDS.
[oracle@gleb-ords-01 ~]$ ords --config /etc/ords/conf config set db.wallet.zip.service glebaceatp01_medium
And defined that the ORDS is going to be used in proxy mode.
[oracle@gleb-ords-01 ~]$ ords --config /etc/ords/conf config set plsql.gateway.mode proxied
That would wrap up the configuration steps provided by the documentation. But you still need to perform some actions if you want to use the ORDS. You need to provide network access if you haven’t done that yet. Assuming you already have opened ports on your network layer you still need to open ports on the linux firewall. I’ve opened the ports 8080 and 8443 as default for http and https for standalone ORDS.
[opc@gleb-ords-01 ~]$ sudo firewall-cmd --permanent --add-port=8080/tcp success [opc@gleb-ords-01 ~]$ sudo firewall-cmd --permanent --add-port=8443/tcp success [opc@gleb-ords-01 ~]$ sudo firewall-cmd --reload success
Let’s start the ORDS in standalone mode and see if it works.
[oracle@gleb-ords-01 ~]$ ords --config /etc/ords/config serve ORDS: Release 22.3 Production on Fri Oct 14 14:46:36 2022 Copyright (c) 2010, 2022, Oracle. Configuration: /etc/ords/config/ 2022-10-14T14:46:37.217Z INFO HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080 ...
The ORDS is up and running, but if we try to connect we are getting an SSL error. By default it switches to SSL mode and, as result, we have to use our ORDS in secure mode too. So, we need to prepare our certificates. The best way is to use verified certificates provided by your company or by any 3d party like letsencrypt.org. But you can also create a self signed certificate. (keep in mind the self-signed certificates might not work on some browsers) Here is an example.
[oracle@gleb-ords-01 keystore]$ keytool -genkey -alias ords -keyalg RSA -keystore /etc/ords/config/keystore/ords.jks -dname "CN=gleb-ords-01.gleb.ca, OU=Gleb App, O=Gleb.ca, L=Ottawa, ST=Ontario, C=CA" -storepass YourStrongPassword -validity 3600 Generating 2,048 bit RSA key pair and self-signed certificate (SHA256withRSA) with a validity of 3,600 days for: CN=gleb-ords-01.gleb.ca, OU=Gleb App, O=Gleb.ca, L=Ottawa, ST=Ontario, C=CA [oracle@gleb-ords-01 keystore]$ keytool -importkeystore -srckeystore ords.jks -srcalias ords -srcstorepass YourStrongPassword -destkeystore ords.p12 -deststoretype PKCS12 -deststorepass YourStrongPassword -destkeypass YourStrongPassword Importing keystore ords.jks to ords.p12... [oracle@gleb-ords-01 keystore]$ openssl pkcs12 -in ords.p12 -nodes -nocerts -out gleb-ords-01-key.pem Enter Import Password: [oracle@gleb-ords-01 keystore]$ openssl pkcs12 -in ords.p12 -nodes -nokeys -out gleb-ords-01.pem Enter Import Password: [oracle@gleb-ords-01 keystore]$ openssl pkcs8 -topk8 -inform PEM -outform DER -in gleb-ords-01-key.pem -out gleb-ords-01-key.der -nocrypt [oracle@gleb-ords-01 keystore]$ openssl x509 -inform PEM -outform DER -in gleb-ords-01.pem -out gleb-ords-01.der [oracle@gleb-ords-01 keystore]$
The key and the certificates have been exported and converted to DER format to be used for our standalone ORDS in secure mode.
[oracle@gleb-ords-01 ~]$ ords --config /etc/ords/config serve --secure --port 8443 --certificate /etc/ords/config/keystore/gleb-ords-01.der --key /etc/ords/config/keystore/gleb-ords-01-key.der ORDS: Release 22.3 Production on Mon Oct 17 18:14:21 2022 Copyright (c) 2010, 2022, Oracle. Configuration: /etc/ords/config/ ...
Now we can test it again and try to connect using HTTPS and the port 8443 – for example https://gleb-ords-01.gleb.ca:8443/ . It is successfully connected now and we can see the login page.
Speaking about the Apex static files. Now Oracle provides an option to use CDN (Content Delivery Network) for Apex standard static files. If we look in the database it points to the internal directory by default.
SELECT apex_instance_admin.get_parameter('IMAGE_PREFIX') FROM dual; APEX_INSTANCE_ADMIN.GET_PARAMETER('IMAGE_PREFIX') ------------------------------------------------- /i/22.1.1/
But we can change it and point to the CDN endpoint like https://static.oracle.com/cdn/apex/22.1.0/ . To test it we can remove the option for static files in our ORDS.
[oracle@gleb-ords-01 ~]$ ords --config /etc/ords/config config delete standalone.static.path
Now if we restart the ORDS and try to connect we are going to see the warning about missing static files.
But if we change the path in the database using the APEX_INSTANCE_ADMIN package.
BEGIN apex_instance_admin.set_parameter( p_parameter => 'IMAGE_PREFIX', p_value => 'https://static.oracle.com/cdn/apex/22.1.0/' ); commit; END; /
Now it works again and gets the files from the Oracle CDN.
That concludes the standalone ORDS section. But what if we want to use a middleware like tomcat? Let’s stop our standalone ORDS and configure the Tomcat as our frontend.
First we need to install the Tomcat.
[opc@gleb-ords-01 ~]$ sudo dnf install tomcat apr-util-openssl --enablerepo=ol8_developer_EPEL -y
Then put the ords.war to the apps directory for tomcat. The apps destination /usr/share/tomcat/webapps is created automatically when you install the Tomcat.
[opc@gleb-ords-01 ~]$ sudo cp /opt/oracle/ords/ords.war /usr/share/tomcat/webapps/ [opc@gleb-ords-01 ~]$ sudo chown tomcat /usr/share/tomcat/webapps/ords.war
Copy and adjust the ORDS configuration for tomcat. We need it to be accessible by the tomcat user and have a proper destination for the wallet file.
[oracle@gleb-ords-01 ~]$ ords --config /etc/ords/tomcat config set db.wallet.zip.path /etc/ords/tomcat/wallet.zip ORDS: Release 22.3 Production on Fri Oct 14 16:52:53 2022 Copyright (c) 2010, 2022, Oracle. Configuration: /etc/ords/tomcat/ The setting named: db.wallet.zip.path was set to: /etc/ords/tomcat/wallet.zip in configuration: default [oracle@gleb-ords-01 ~]$ ords --config /etc/ords/tomcat config list ORDS: Release 22.3 Production on Fri Oct 14 16:53:01 2022 Copyright (c) 2010, 2022, Oracle. Configuration: /etc/ords/tomcat/ Database pool: default INFO: The configuration does not have any global settings. Setting Value Source --------------------- --------------------------- ----------- db.password ****** Pool Wallet db.username ORDS_PUBLIC_USER2 Pool db.wallet.zip.path /etc/ords/tomcat/wallet.zip Pool db.wallet.zip.service glebaceatp01_medium Pool plsql.gateway.mode proxied Pool [oracle@gleb-ords-01 ~]$
We remember that we need to use HTTPS for our proxy ORDS. So, we adjust the tomcat configuration to enable SSL and use the keystore we created for the standalone ORDS.
[opc@gleb-ords-01 ~]$ sudo vi /etc/tomcat/server.xml
Here is what we need to add to the /etc/tomcat/server.xml if we want to use direct connection to the default Tomcat 8443 port for SSL.
<Connector port="8443" protocol="org.apache.coyote.http11.Http11NioProtocol" maxThreads="150" SSLEnabled="true"> <SSLHostConfig> <Certificate certificateKeystoreFile="/etc/ords/tomcat/keystore/ords.jks" certificateKeystorePassword="YourStrongPassword" type="RSA" /> </SSLHostConfig>
To be able to use the ORDS with the custom configuration we need to add our ORDS configuration path to the /etc/tomcat/tomcat.conf parameter file. Here is what has been added in our case. The “-Dconfig.url=/etc/ords/tomcat” was added to the JAVA_OPTS in the file.
# Set default javax.sql.DataSource factory to apache commons one. See rhbz#1214381 JAVA_OPTS="-Djavax.sql.DataSource.Factory=org.apache.commons.dbcp.BasicDataSourceFactory -Dconfig.url=/etc/ords/tomcat"
The tomcat can be started.
[opc@gleb-ords-01 ~]$ sudo systemctl start tomcat
We can now access the Apex using the URL https://gleb-ords-01.gleb.ca:8443/ords .
So far we have configured two cases – standalone ORDS and Tomcat as frontend using the ORDS in proxy mode to connect to our Apex on the Autonomous. But what if we want to put a HTTP server like Apache in front of the running Tomcat. Let’s go through the basic configuration for that case too.
We assume that our Tomcat and ORDS are up and running. Now we need to install the httpd server and the mod_ssl for it:
[opc@gleb-ords-01 ~]$ sudo dnf install httpd mod_ssl -y
Make our certificates and keys available for the httpd server.
[opc@gleb-ords-01 ~]$ sudo mkdir /etc/httpd/keystore [opc@gleb-ords-01 ~]$ sudo cp /etc/ords/config/keystore/gleb-ords-01-key.pem /etc/httpd/keystore/ [opc@gleb-ords-01 ~]$ sudo cp /etc/ords/config/keystore/gleb-ords-01.pem /etc/httpd/keystore/
Create an apache httpd configuration file for the Tomcat-ORDS proxy connection.
[opc@gleb-ords-01 ~]$ sudo vi /etc/httpd/conf.d/ords.conf #Here is what inside the file <VirtualHost *:80> Alias "/i/" "/opt/oracle/ords/apex/images/" # uncomment the line below if you want # to redirect traffic to ORDS from root path # RedirectMatch permanent "^/$" "/ords" # proxy ORDS requests to tomcat ProxyRequests off <Location "/ords"> ProxyPass "https://localhost:8443/ords" ProxyPassReverse "https://localhost:8443/ords" </Location> </VirtualHost> <VirtualHost *:443> ServerName gleb-ords-01.gleb.ca Alias "/i/" "/opt/oracle/ords/apex/images/" SSLProxyEngine on SSLEngine on SSLProxyVerify none SSLProxyCheckPeerCN off SSLProxyCheckPeerName off <Location "/ords"> ProxyPass "https://localhost:8443/ords" ProxyPassReverse "https://localhost:8443/ords" </Location> #Path to your keys and sertificates SSLCertificateKeyFile /etc/httpd/keystore/gleb-ords-01-key.pem SSLCertificateFile /etc/httpd/keystore/gleb-ords-01.pem </VirtualHost>
Adjust SELinux configuration to allow httpd service to redirect the requests.
[opc@gleb-ords-01 ~]$ sudo setsebool httpd_can_network_connect 1
Start the httpd service.
[opc@gleb-ords-01 ~]$ sudo systemctl start httpd
That’s it. You can go to the URL https://gleb-ords-01.gleb.ca/ords and login as the Apex admin.
That is be enough if we want to use https connection between our Apache Httpd server and the Tomcat, but if we want to use AJP then we need to enable the AJP on our Tomcat first by adding the connector section to the /etc/tomcat/server.xml file:
<Connector protocol="AJP/1.3" address="::1" maxConnections="256" keepAliveTimeout="30000" port="8009" secret="Pass123" redirectPort="8443" />
You probably noticed the secret added to the AJP connector configuration. Since Tomcat version 9 the secret for AJP is enabled by default. Of course, you can disable it by replacing the secret line by secretRequired=”false”. It wouldn’t be ideal but probably acceptable if you are absolutely sure of security between httpd and middleware layers.
And in the /etc/httpd/conf.d/ords.conf we are replacing the https proxy by ajp:
# HTTPD -> Tomcat -> ORDS requests <VirtualHost *:80> Alias "/i/" "/opt/oracle/ords/apex/images/" # uncomment the line below if you want # to redirect traffic to ORDS from root path # RedirectMatch permanent "^/$" "/ords" # proxy ORDS requests to tomcat ProxyRequests off <Location "/ords"> ProxyPass "ajp://localhost:8009/ords" secret=Pass123 ProxyPassReverse "ajp://localhost:8009/ords" </Location> </VirtualHost> <VirtualHost *:443> ServerName gleb-ords-01.gleb.ca Alias "/i/" "/opt/oracle/ords/apex/images/" SSLEngine on <Location "/ords"> ProxyPass "ajp://localhost:8009/ords" secret=Pass123 ProxyPassReverse "ajp://localhost:8009/ords" </Location> #Path to your keys and sertificates SSLCertificateKeyFile /etc/httpd/keystore/gleb-ords-01-key.pem SSLCertificateFile /etc/httpd/keystore/gleb-ords-01.pem </VirtualHost>
After restarting the tomcat and httpd services we can test our connection again using the same URL as provided earlier.
In conclusion I would like to add that the configuration works not only for a public facing Apex but also for ADB with private only access. For Apex development service you will need to use a load balancer. Speaking about a proper configuration for a production I strongly recommend to put the middleware and http servers in a private network and set up a load balancer in front of them. It will be much more secure.
I hope that little how-to might help somebody with the first steps in configuration.