Installing APEX and ORDS on Oracle 18cXE on CentOS

It’s been rather a trying week.
Wales beat England in the Rugby on Saturday and every Welsh person alive has been observing the ancient tradition of rubbing English noses in it ever since.
My claim to Welsh heritage by marriage have been given short-shrift by Deb, whose accent has become rather more pronounced ever since the final whistle.

All in all, the onslaught of Welsh chauvinism has left me feeling rather like this :

Until things blow over, I’ve decided to spend more time in the shed. Fortunately, the Wifi signal is still pretty good so I’ve decided to use the free time by installing APEX 18.2 into an Oracle 18c RDBMS. As I’ve got time on my hands ( celebrations are unlikely to fizzle out for a couple of months yet), I’ve decided to follow Oracle’s recommendation and configure it to run on ORDS 18.4.
Specifically, what I’ll be covering here is :

• installing APEX 18c
• installing ORDS 18c
• configuring APEX to run on ORDS
• configuring ORDS to run on HTTPS with self-signed SSL certificates
• using systemd to start ORDS automatically on boot

That should keep me occupied for a while…

The Environment

The server is running CentOS 7, a Linux distro that is functionally identical to the corresponding RHEL (Red Hat Enterprise Linux) release.
The Oracle Database is 18c. In this context, the edition doesn’t really matter, but I’m using Express Edition.
The APEX (Application Express) version is 18.2.
The ORDS (Oracle Rest Data Services) version is 18.4.

Whilst using ORDS with APEX makes your application architecturally N-tier – the ORDS server is a separate piece of software form the RDBMS hosting APEX – you can physically run ORDS on the same server as the database itself and this is what I’m doing here.
Once again, this should make little (if any) difference to the steps required to complete the installation.

I’m assuming that the server you want to install ORDS on will be headless. Therefore, all of the server-side steps described here are performed on the command line.

Oracle Recommendations

There are several recommendations spread through the relevant Oracle documentation which I have followed :

• I’m running a multi-tenant database so APEX is installed in a PDB
• I’ve installed APEX before ORDS
• I’ve configured ORDS to run on HTTPS

I’ll link to the relevant documentation for each recommendation as and when we get to it.

Helpful Links

I was going to put these at the end but then I realised you might want to refer to them before you get thoroughly confused by my ramblings. So…

• I found this installation guide very useful, although it does take a slightly different approach to that described below
• This article on how to generate a Self-signed SSL certificate was rather helpful
• …as was this
• This guide to creating a service in systemd saved me rather a lot of typing

First up then…

Installing APEX

APEX itself sits entirely within the Oracle RDBMS.

Checking Requirements

According to the Installation Guide, the database requirements for APEX 18.2 are :

• Oracle Database 11.2.0.4 or higher
• a database MEMORY_TARGET of at least 300MB
• At least 220MB plus 60MB for each additional language in the “Apex” tablespace
• At least 100MB free in the SYSTEM tablespace
• If installing the development environment, Oracle XML DB

Note that “Apex” tablespace is in quotes because, by default, the APEX users tend to get installed into the SYSAUX tablespace.

Let’s have a quick check to make sure that all these requirements are met on our system before we go any further.

For the Database version, we can run the following query :

select banner
from v$version
/

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production             

The MEMORY_TARGET setting is a bit more convoluted as it’s set to 0 by default :

select display_value, isdefault, description
from v$parameter
where name = 'memory_target'
/

DISPLAY_VALUE        ISDEFAULT  DESCRIPTION                             
-------------------- ---------- ----------------------------------------
0                    TRUE       Target size of Oracle SGA and PGA memory

The description of the MEMORY_TARGET in this query provides a clue as to how you can make sure that this is the case.

The sga_target parameter holds the target size of the sga
the pga_aggregate_target parameter holds the “Target size for the aggregate PGA memory consumed by the instance”

So…

select sum(value)/1024/1024 as "Total Size (MB)"
from v$parameter
where name in ('sga_target', 'pga_aggregate_target')
/

Total Size (MB)
---------------
            378

Alternatively, if you’re running 12c or later, you can simply use Enterprise Manager Express :

As for the tablespace space availability :

select tablespace_name, 
    round((sum(maxbytes) - sum(bytes))/1024/1024) as "MB Free"
from dba_data_files
where tablespace_name in ('SYSTEM', 'SYSAUX')
group by tablespace_name;

TABLESPACE_NAME                   MB Free
------------------------------ ----------
SYSTEM                              32398
SYSAUX                              32148

Finally, we can check that Oracle XML DB is present with :

select comp_name, version_full
from dba_registry
where upper(comp_name) like 'ORACLE XML%'
/

COMP_NAME            VERSION_FULL                  
-------------------- ------------------------------
Oracle XML Database  18.4.0.0.0                    

Now that’s all done, we can go and get the software.

Downloading APEX

Head over to the APEX Download Page and pick up the latest version ( 18.2.0.0.12 at the time of writing). Note that there’s no OS specific options because APEX sits entirely within the RDBMS.

You can choose between the “All languages” version (705MB uncompressed) or “English language only” (310MB uncompressed). I’ve gone for the latter and therefore ended up with this file :

-rw-rw-r-- 1 mike mike 94421975 Feb 20 11:51 apex_18.2_en.zip

First we need to change the ownership of the file to the oracle user as that’s the os user we’ll be running the install as :

sudo chown oracle:oinstall apex_18.2_en.zip

Now we can switch to the oracle user and unzip the file to what is usually the $ORACLE_BASE directory (/opt/oracle) :

sudo su oracle
unzip -d /opt/oracle apex_18.2_en.zip

echo $ORACLE_BASE
/opt/oracle
cd $ORACLE_BASE/apex

Before we connect to the database, it’s worth noting that the Installation Guide has this to say about APEX on a Multitenant Database :

“Oracle recommends removing Oracle Application Express from the root container database for the majority of use cases, except for hosting companies or installations where all pluggable databases (PDBs) utilize Oracle Application Express and they all need to run the exact same release and patch set of Oracle Application Express. ”

In my case I’m installing into an 18cXE database which does not have APEX pre-installed. Either way, I want to install into a PDB rather than the CDB.
It’s also worth noting that you’ll be prompted for the following when you run the installation script :

• The tablespace for the APEX application user (usually SYSAUX)
• The tablespace for the APEX files user (SYSAUX)
• The temporary tablespace (TEMP)
• The virtual images directory (“/i/”)

So, still as the oracle user, from /opt/oracle/apex :

sqlplus /nolog
conn / as sysdba
alter session set container = xepdb1;

If you want to make sure that you are where you should be :

select sys_context('userenv', 'session_user') as session_user,
    sys_context('userenv', 'con_name') as container
from dual
/

SESSION_USER		       CONTAINER
------------------------------ ------------------------------
SYS			       XEPDB1

Next, we need to check that the Default Profile’s password complexity function is disabled :

select limit
from dba_profiles
where profile = 'DEFAULT'
and resource_type = 'PASSWORD'
and resource_name = 'PASSWORD_VERIFY_FUNCTION'
/

LIMIT
----------------------------------------
NULL

If there is a password complexity function assigned, you’ll need to disable it.
Remember to make a note of it’s name first so that you can put it back once the installation is complete.
To unset it :

alter profile default password_verify_function null;

Finally, we can start the installation. We want the full development environment so…

@apexins.sql SYSAUX SYSAUX TEMP /i/

This causes screens of messages and can run for some considerable time.
Eventually though, you should end up with :

Thank you for installing Oracle Application Express 18.2.0.00.12

Oracle Application Express is installed in the APEX_180200 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex/apex_admin     (Oracle REST Data Services)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex     (Oracle REST Data Services)

timing for: Phase 3 (Switch)
Elapsed: 00:00:06.44
timing for: Complete Installation
Elapsed: 00:05:51.38

PL/SQL procedure successfully completed.





1 row selected.

...null1.sql
SYS> 

According to the Installation Guide, we should now have 3 new users, howver, it seems that four are actually created…

select username, 
    default_tablespace as default_ts, 
    temporary_tablespace as temp_ts
from cdb_users
where trunc(created) = trunc(sysdate)
/

USERNAME                  DEFAULT_TS                     TEMP_TS
------------------------- ------------------------------ ------------------------------
APEX_PUBLIC_USER          USERS                          TEMP
FLOWS_FILES               SYSAUX                         TEMP
APEX_180200               SYSAUX                         TEMP
APEX_INSTANCE_ADMIN_USER  USERS                          TEMP

4 rows selected.

APEX_INSTANCE_ADMIN_USER is not mentioned in the documentation but seems to have been created in addition to the three expected accounts.

Setting up the APEX Admin User

The apxchpwd.sql script we run for this purpose will prompt for a password. The script enforces the following password complexity rules :

• Password must contain at least 6 characters
• New password must differ from old password by at least 2 characters
• Password must contain at least one numeric character (0123456789)
• Password must contain at least one punctuation character (!”#$%&()“*+,-/:;?_)
• Password must contain at least one upper-case alphabetic character

So…

Setting up the APEX_PUBLIC_USER database account

As we saw, the APEX_PUBLIC_USER account has been created as part of the installation.
At this point, it has been created with a randomly generated password, which we’ll need to change to something we know.
Additionally, you may feel it prudent to make sure that the password, once reset, won’t expire as, if it does, your application will stop working until you change it again.
Note that this is something you need to consider carefully – does the convenience of not having to worry about password expiration for this account outweigh the security risks raised by never changing it ? In my case I think it’s fine because I’m messing about with a VM on my laptop. If your in a more formal environment, you may have a somewhat different risk appetite.

If you’re horrified by the flagrant disregard for password security that I’m about to demonstrate, look away now…

First, we need to create a profile where the password does not expire :

create profile apex_pu limit password_life_time unlimited;

Note that all of the other profile properties will have default values :

select resource_name, limit
from dba_profiles
where profile = 'APEX_PU'
order by resource_type, resource_name
/

RESOURCE_NAME		       LIMIT
------------------------------ --------------------
COMPOSITE_LIMIT 	       DEFAULT
CONNECT_TIME		       DEFAULT
CPU_PER_CALL		       DEFAULT
CPU_PER_SESSION 	       DEFAULT
IDLE_TIME		       DEFAULT
LOGICAL_READS_PER_CALL	       DEFAULT
LOGICAL_READS_PER_SESSION      DEFAULT
PRIVATE_SGA		       DEFAULT
SESSIONS_PER_USER	       DEFAULT
FAILED_LOGIN_ATTEMPTS	       DEFAULT
INACTIVE_ACCOUNT_TIME	       DEFAULT
PASSWORD_GRACE_TIME	       DEFAULT
PASSWORD_LIFE_TIME	       UNLIMITED
PASSWORD_LOCK_TIME	       DEFAULT
PASSWORD_REUSE_MAX	       DEFAULT
PASSWORD_REUSE_TIME	       DEFAULT
PASSWORD_VERIFY_FUNCTION       DEFAULT

Next, we assign this profile to APEX_PUBLIC_USER :

alter user apex_public_user profile apex_pu;

To confirm that the profile has been assigned :

select profile
from dba_users
where username = 'APEX_PUBLIC_USER';

PROFILE
------------------------------
APEX_PU

Security conscientious objectors can look again now

To change the password :

alter user apex_public_user identified by Land0fmyfath3rs;

…replacing Land0fmyfath3rs with your own choice of non-rugby referencing password.

Finally, if you’ve unset the password verify function before starting, you now need to put it back :

alter profile default password_verify_function myfunc;

…where myfunc was the original password verify function.

At this point, the APEX installation is pretty much done.

Until now, I’ve been content to use the PL/SQL Gateway to serve my APEX pages. This involves using the Web Listener that is embedded in the database.
If you want go down this route, the installation steps can be found in Appendix A of the Installation Guide.

However, the Installation Guide has this to say on the subject of choosing a web listener :

“Oracle HTTP Server and Embedded PL/SQL gateway are considered legacy web listeners and their configuration instructions are in the appendix.”

This time, I’m going to go with Oracle’s recommendation and use ORDS.

Installing ORDS

ORDS – or Oracle Rest Data Services to give it it’s full name – is a Java EE based Web Listener. For this installation, we’ll be running it standalone using it’s built-in Jetty Web Server.
The official Installation and Configuration Guide can be found here.

Pre-Requisites

ORDS 18.4 requires an Oracle Database running release 11.1 or later. As the APEX install we’ve just done requires a slightly later minimum database version, we should be fine.

If you really feel the need, you can confirm that we’re good to go with the following query :

select banner
from v$version
/

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production             

The other requirement for ORDS is the presence of a JDK for version 8 or later.
This can be ascertained from the server command line by running :

java -version

Downloading ORDS

Back to Oracle we go, this time to the ORDS Download Page.
Unsurprisingly given that we’re downloading a Java application, the download is not OS specific. The current version is 18.4.

A short time later, you should now be the proud owner of…

-rwxrwx---. 1 mike mike 59777214 Feb 20 12:01 ords-18.4.0.354.1002.zip

As with the APEX install, we’ll want to transfer ownership of the file to oracle…

sudo chown oracle:oinstall ords-18.4.0.354.1002.zip

…as this is the account we’ll be using for the installation…

sudo su oracle

echo $ORACLE_BASE
/opt/oracle

mkdir $ORACLE_BASE/ords

Now we extract the file into the new directory…

unzip -d /opt/oracle/ords ords-18.4.0.354.1002.zip

…which produces screens of output ending with…

...
inflating: /opt/oracle/ords/examples/soda/getting-started/indexSpec1.json  
  inflating: /opt/oracle/ords/examples/db_auth/index.html  
  inflating: /opt/oracle/ords/examples/pre_hook/sql/install.sql  
  inflating: /opt/oracle/ords/examples/pre_hook/sql/uninstall.sql  
  inflating: /opt/oracle/ords/examples/plugins/plugin-echo-cmd/src/EchoMessages.properties  
  inflating: /opt/oracle/ords/examples/plugins/plugin-echo-cmd/src/EchoCommand.java  
  inflating: /opt/oracle/ords/examples/plugins/plugin-demo/build.xml  
  inflating: /opt/oracle/ords/examples/plugins/plugin-demo/.classpath  
  inflating: /opt/oracle/ords/examples/plugins/plugin-demo/.project  
  inflating: /opt/oracle/ords/examples/plugins/lib/javax.inject-1.jar  
  inflating: /opt/oracle/ords/examples/soda/getting-started/QBE.2.json  
  inflating: /opt/oracle/ords/examples/db_auth/sql/install.sql  
  inflating: /opt/oracle/ords/examples/pre_hook/sql/custom_auth_api.pls  
  inflating: /opt/oracle/ords/examples/soda/getting-started/poUpdated.json  
  inflating: /opt/oracle/ords/examples/soda/getting-started/QBE.3.json  
  inflating: /opt/oracle/ords/examples/soda/getting-started/QBE.5.json  
  inflating: /opt/oracle/ords/examples/soda/getting-started/poPatchSpec.json  
  inflating: /opt/oracle/ords/examples/soda/getting-started/QBE.1.json  
  inflating: /opt/oracle/ords/examples/soda/getting-started/QBE.4.json  
  inflating: /opt/oracle/ords/examples/soda/getting-started/po.json  
  inflating: /opt/oracle/ords/examples/pre_hook/README.md  
  inflating: /opt/oracle/ords/examples/soda/getting-started/qbePatch.json  
  inflating: /opt/oracle/ords/examples/soda/getting-started/POList.json  
  inflating: /opt/oracle/ords/examples/pre_hook/index.html  

Make sure the PL/SQL Gateway is disabled

You can check whether the PL/SQL Gateway is currently enabled by running…

select dbms_xdb.gethttpport
from dual;


GETHTTPPORT
-----------
          0

If this query returns something other than zero then you can disable the PL/SQL Gateway as follows :

exec dbms_xdb.sethttpport(0)

Copy the APEX images

Before we configure ORDS, we need to copy the APEX images somewhere that is visible to the ORDS server so…

cd /opt/oracle/ords
mkdir apex

…then…

cd apex
pwd 
/opt/oracle/ords/apex
cp -r $ORACLE_BASE/apex/images .
ls -l
total 44
drwxr-xr-x. 33 oracle oinstall 28672 Feb 20 16:57 images

Initial ORDS Installation

To start with, we’re going to install ORDS and configure it to run on HTTP. This is simply so that we can sanity check that ORDS and APEX are working together as expected.
Note that I’m accepting the default location for the default tablespace of the two new users that will be created as part of the installation. If you’re planning to do the same then you should make sure that you have a USERS tablespace available in your PDB.

Finally, we can now run the installation. Still connected as oracle :

cd $ORACLE_BASE/ords
java -jar ords.war install advanced

At this point, we now have the option to start ORDS…

…which causes a lot of feedback…

It’s perhaps unsurprising that we hit the ORA-28000 error at this stage…

alter session set container = xepdb1;

select username, account_status
from dba_users
where username like 'ORDS%'
or username like 'APEX%'
/

USERNAME                       ACCOUNT_STATUS                
------------------------------ ------------------------------
APEX_180200                    LOCKED                        
APEX_INSTANCE_ADMIN_USER       OPEN                          
APEX_PUBLIC_USER               LOCKED                        
ORDSYS                         EXPIRED & LOCKED              
ORDS_METADATA                  EXPIRED & LOCKED              
ORDS_PUBLIC_USER               OPEN                          

6 rows selected. 

We’ll sort that out in a bit. For now though, let’s just check that ORDS’ Jetty server is accessible.
As ORDS is running in the foreground, we’ll need to leave it running and start a separate session.
Then we can test it with :

curl -ISs http://frea.virtualbox:8080

…which should return the HTTP header :

Now we’re happy that ORDS itself is running, we can stop it by pressing [CTRL]+C in the Terminal session it’s running in.

Next, we need to run ords with the validate option :

cd $ORACLE_BASE/ords
java -jar ords.war validate 

The output looks innocuous enough :

However, if we look at the log file that has been written, we can see that there’s been a fair bit of activity…

[*** script: ords_alter_session_script.sql] 

PL/SQL procedure successfully completed.

[*** script: ords_schema_mapping.sql] 
INFO: Configuring ORDS_PUBLIC_USER to map APEX Workspaces and ORDS schemas

Session altered.

Configuring APEX and ORDS schemas for url mapping
Made APEX_PUBLIC_USER proxiable from ORDS_PUBLIC_USER
APEX_REST_PUBLIC_USER does not exist
APEX_LISTENER.POOL_CONFIG synonym does not exist, stubbing out
ORDS_METADATA.APEX_POOL_CONFIG


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Grant succeeded.

INFO: Completed configuring ORDS_PUBLIC_USER to map APEX Workspaces and ORDS Schemas

Session altered.

[*** script: ords_repair_proxy_connect.sql] 
INFO: Checking ords enabled schemas and its proxy user

Session altered.


PL/SQL procedure successfully completed.

[*** script: ords_migrate_grant_priv.sql] 

Session altered.

INFO: Verify if Application Express exists to setup the migration privileges for
ORDS.
INFO: Completed setting up the APEX REST migration privileges for ORDS.


PL/SQL procedure successfully completed.

[*** script: ords_validate_objects.sql] 

Session altered.

INFO: 15:25:18 Validating objects for Oracle REST Data Services.
VALIDATION: 15:25:18 Starting validation for schema: ORDS_METADATA
VALIDATION: 15:25:18 Validating objects
VALIDATION: 15:25:19 Validating ORDS Public Synonyms
VALIDATION: 15:25:20 Total objects: 262, invalid objects: 0
VALIDATION: 15:25:20     72  INDEX
VALIDATION: 15:25:20      1  JOB
VALIDATION: 15:25:20     12  PACKAGE
VALIDATION: 15:25:20     12  PACKAGE BODY
VALIDATION: 15:25:20     44  PUBLIC SYNONYM
VALIDATION: 15:25:20      1  SEQUENCE
VALIDATION: 15:25:20     14  SYNONYM
VALIDATION: 15:25:20     27  TABLE
VALIDATION: 15:25:20     26  TRIGGER
VALIDATION: 15:25:20     20  TYPE
VALIDATION: 15:25:20      6  TYPE BODY
VALIDATION: 15:25:20     27  VIEW
VALIDATION: 15:25:20 Validation completed.
INFO: 15:25:20 Completed validating objects for Oracle REST Data Services.


PL/SQL procedure successfully completed.


Session altered.


Commit complete.

[*** script: ords_alter_session_script.sql] 

PL/SQL procedure successfully completed.

In case you’re wondering, the scripts referenced in this log file are located in ords.war itself.

Now we’re ready to…

Configure APEX to run on ORDS

As oracle…

cd $ORACLE_BASE/apex
sqlplus / as sysdba

Once connected to the database…

alter session set container = xepdb1;

@apex_rest_config.sql

This will create two new user accounts :

• APEX_LISTENER
• APEX_PUBLIC_USER

You will be prompted for a password for each of them.

Once the script is completed, you should be able to confirm that two further accounts have been created :

select username, account_status
from dba_users
where username in ('APEX_LISTENER', 'APEX_PUBLIC_USER')
order by 1
/

USERNAME                       ACCOUNT_STATUS                
------------------------------ ------------------------------
APEX_LISTENER                  OPEN                          
APEX_PUBLIC_USER               LOCKED                        

Granting access to the APEX owner via ORDS

Once again, connect to the database as sysdba :

alter session set container = xepdb1;

begin
    dbms_network_acl_admin.append_host_ace(
        host => 'localhost',
        ace => xs$ace_type(
            privilege_list => xs$name_list('connect'),
            principal_name => 'APEX_180200',
            principal_type => xs_acl.ptype_db));
end;
/

alter user apex_public_user account unlock
/

Now if we re-start ORDS…

java -jar ords.war standalone

…and in a separate session we should be able to get a sensible header from the apex_admin URL :

curl -ISs http://frea.virtualbox:8080/ords/apex_admin

We could just call it a day at this point. However, if you like your applications to be a little more secure than an England three-quarter under a Dan Bigger garryowen, you’ll want to follow Oracle’s recommendation :
“If you want to use RESTful services that require secure access, you should use HTTPS.”

In order to do this, we’re going to have to do some messing about with SSL certificates.

Generating a self-signed SSL Certificate

We need to connect as root and create directory to hold the key :

sudo -s
mkdir -p /etc/ssl/private/frea.virtualbox
cd /etc/ssl
chmod -R 700 private/frea.virtualbox

Now we can generate the key and the certificate :

cd /etc/ssl/private/frea.virtualbox
openssl req -newkey rsa:2048 -nodes -keyout frea.virtualbox.key -x509 -days 3650 -out frea.virtualbox.crt

Note that the days value I’m using will create a certificate that does not expire for 10 years. Whilst this does mean I won’t have to worry about the certificate expiring and stopping my application from working at an unexpected moment, it’s probably not strictly in line with security best practices. If you find yourself doing this in a production environment, you may want to consider a rather shorter lifetime for your certificate.

Anyhow, we will be prompted to supply some values. The ones I’m using are :

• Country Name : UK
• State or Province Name : England
• Organization Name : The Anti-Kyte
• Organizational Unit Name : Mike
• Common Name : frea.virtualbox

…all of which looks like this :

You should now have two new files :

ls -l
total 8
-rw-r--r--. 1 root root 1363 Feb 22 11:45 frea.virtualbox.crt
-rw-r--r--. 1 root root 1708 Feb 22 11:45 frea.virtualbox.key

OK, we can stop being root now.

Incidentally, if you want to verify the expiry date of our your new certificate :

sudo openssl x509 -text -noout -in /etc/ssl/private/frea.virtualbox/frea.virtualbox.crt |grep 'Not After'
 Not After : Feb 19 11:45:07 2029 GMT

The easiest way to reconfigure ORDS to use the certificate – and HTTPS – is to stop any running instances of ORDS, connect as oracle and then start it again, using the appropriate command line parameters :

cd $ORACLE_BASE/ords

java -jar ords.war standalone --secure-port 8443 --secure-host frea.virtualbox --secure-cert-path /etc/ssl/private/frea.virtualbox/frea.virtualbox.crt --secure-cert-key-path /etc/ssl/private/frea.virtualbox/frea.virtualbox.key

If we test using curl…

curl -ISs https://frea.virtualbox:8443/ords/apex_admin

The presence of a self-signed certificate will cause comment :

…so we’ll have to use a bit of TLC…

curl -kISs https://frea.virtualbox:8443/ords/apex_admin

This does mean that your web browser is also likely to object to the cert the first time we point it at this site. We’ll come onto that in a bit.

For now though, we can see that the ssh settings have been added to the properties file in standalone sub-directory :

cd $ORACLE_BASE/ords/ords/standalone
cat standalone.properties 

The file now looks like this :

#Fri Feb 22 11:48:35 GMT 2019
jetty.secure.port=8443
ssl.cert=/etc/ssl/private/frea.virtualbox/frea.virtualbox.crt
ssl.cert.key=/etc/ssl/private/frea.virtualbox/frea.virtualbox.key
ssl.host=frea.virtualbox
standalone.context.path=/ords
standalone.doc.root=/opt/oracle/ords/ords/standalone/doc_root
standalone.scheme.do.not.prompt=true
standalone.static.context.path=/i
standalone.static.path=/opt/oracle/ords/apex/images

From now on, when ORDS starts, it will use these properties.

Now ORDS is installed and configured, we need to get it to start when the server boots…

Creating a Systemd service for ORDS

The documentation mentions the fact that there is a limit on the size of POST data when running standalone and suggests increasing this limit this by starting ORDS like this :

java -Dorg.eclipse.jetty.server.Request.maxFormContentSize=3000000 -jar ords.war

We will implement this suggestion in our service.

So, as root :

cd /etc/systemd/system
nano ords.service

The file should look something like this :

[Unit]

Description=Oracle Rest Data Services (ORDS) Embedded Jetty WEB Server for APEX
After=network.target

[Service]
User=oracle
TimeoutStartSec=0

Type=simple
KillMode=process
ExecStart=/usr/bin/java -Dorg.eclipse.jetty.server.Request.maxFormContentSize=3000000 -jar /opt/oracle/ords/ords.war standalone
Restart=always
RestartSec=2
LimitNOFILE=5555

[Install]

WantedBy=multi-user.target

We now need to make the file executable :

chmod a+x ords.service

Finally, we need to add the ORDS service to systemctl :

systemctl daemon-reload
systemctl enable ords
systemctl start ords

Now we can check that ORDS is up :

systemctl status ords

It’s alive !!!

We can test once again with…

curl -kISs https://frea.virtualbox:8443/ords/apex_admin

…which should return something like :

HTTP/1.1 302 Found
Date: Thu, 28 Feb 2019 22:27:34 GMT
Content-Type: text/html;charset=utf-8
X-Content-Type-Options: nosniff
X-Xss-Protection: 1; mode=block
Cache-Control: no-store
Pragma: no-cache
Expires: Sun, 27 Jul 1997 13:00:00 GMT
Set-Cookie: ORA_WWV_USER_250198699356158=ORA_WWV-R7rbCSQ886zYN9Q6CXIOpnb2; path=/ords; secure; HttpOnly
Location: https://frea.virtualbox:8443/ords/f?p=4550:10:14143531583026:::::
Transfer-Encoding: chunked

Making APEX available to remote machines

Now we’ve got everything configured, we simply need to update the server firewall to allow traffic to the HTTPS port :

sudo firewall-cmd --zone=public --permanent --add-port=8443/tcp
sudo firewall-cmd --reload

We can now confirm that the port is available :

sudo firewall-cmd --list-ports
1522/tcp 5500/tcp 8443/tcp

Finally, we can now access APEX from a remote machine.

When we first hit the APEX URL, Firefox is understandably skeptical of the my self-signed certificate…

…so I need to convince it that I’m trustworthy (or just add an exception)…

…before I can finally see APEX in the browser :

That’s it, I can now leave my sanctuary safe in the knowledge that APEX and ORDS are now configured and that the Welsh Nationalist fervour has abated…except that it’s now St. David’s Day. On the plus side, it looks like I’m having Cheese and Leek Pie for tea rather than the Humble Pie I’ve been eating all week.