Installing APEX 18 on an 11g OCI DB Systems running in Oracle Cloud

The current generation of Oracle Cloud databases do not have APEX installed on them as the first generation of Database Classic had, so some of you might find it useful to follow this post on how I did my installation of APEX on Oracle Cloud Infrastructure.

First off, the dependencies we need to install are:

1. APEX 18
2. Oracle Rest Data Services -ORDS
3. Apache Tomcat

On this post I will be using a 11g Oracle Database Standard Edition as the back end, so in case you don’t have the database created already please follow my post HERE.

I will cover the installation on a 12c/18c multitenant database in a later post.

1. Downloading and installing APEX

You will need to first download the latest APEX version from HERE :

Oracle Application Express - Downloads

You need to download and move the software on your cloud database machine and not locally. So you have two options to do it, either you download the file to your laptop and then use a tool like WinSCP or FileZilla to move it to the database. Or download it directly on the database using a little trick :)

a) Go to the link from above on your laptop, agree to the Terms and Conditions and click on Download. If you don’t have an Oracle OTN account, you can create it now as it’s pretty fast and free. Once the file starts downloading you need to PAUSE it and then copy it’s unique URL link.

We do this because if we would just download using the normal link it wouldn’t work.

b)Now connect to the database machine and download it using wget (I switched to root user and moved in /tmp ) :

[opc@linux ~]$ sudo su -
[root@linux ~]# cd /tmp
[root@linux tmp]# wget https://download.oracle.com/otn/java/appexpress/apex_18.2_en.zip?AuthParam=1552035503_zxxxx

c) Unzip the file in a new folder as root user, change directory into it and then become user oracle:

[root@linux tmp]# unzip apex_18.2_en.zip?AuthParam=1552035503_zxxxx /u01/app/ -d /u01/app/
[root@linux apex]# cd /u01/app
[root@linux app]# chown -R oracle:oinstall apex/
[root@linux app]# cd apex
[root@linux app]# su oracle

d) Let’s connect to the database and create a tablespace for APEX:

[oracle@linux apex]$ sqlplus / as sysdba
SQL> CREATE TABLESPACE apex DATAFILE SIZE 100M AUTOEXTEND ON NEXT 1M;

e) Execute the installation script for APEX:

SQL> @apexins apex apex temp /i/

f) Change the admin password for the INTERNAL workspace with “@apxchpwd.sql”:

g) Create the APEX_LISTENER and APEX_REST_PUBLIC_USER users by running the "@apex_rest_config.sql" script.

h) Set password for the APEX_PUBLIC_USER and unlock the account. I also made a new profile so that the paswword for apex_public_user never expires.

SQL> ALTER USER apex_public_user IDENTIFIED BY “OraPassword1” ACCOUNT UNLOCK;
SQL> CREATE PROFILE password_unlimited LIMIT PASSWORD_LIFE_TIME UNLIMITED;
SQL> ALTER USER apex_public_user PROFILE password_unlimited;
SQL> exit

2. Downloading ans installing REST — ORDS

a) Download Oracle REST Data Services 18.4 software from HERE to the cloud database using the same trick as above, by pausing the download on your laptop, copying the unique URL and pasting it on the database as user oracle:

Oracle REST Data Services Download

[oracle@linux ~]$ cd /tmp
[oracle@linux tmp]$ wget https://download.oracle.com/otn/java/ords/ords-18.4.0.354.1002.zip?AuthParam=1552040043_cxxx

b) OPTIONAL : Check the SYS user and common APEX public users are unlocked and you know their passwords(we did kind of the same thing in the steps above)

[oracle@linux apex]$ sqlplus / as sysdba
SQL> ALTER USER SYS IDENTIFIED BY OraPassword1 ACCOUNT UNLOCK;
SQL> ALTER USER APEX_LISTENER IDENTIFIED BY OraPassword1 ACCOUNT UNLOCK;
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY OraPassword1 ACCOUNT UNLOCK;
SQL> ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY OraPassword1 ACCOUNT UNLOCK;
SQL> exit

c) Back in Linux, as user root let’s create the TOMCAT Linux user (we will need it later when we install Tomcat) , make a new directory for ORDS files and unzip it there:

[oracle@linux apex]$ exit
[root@linux apex]# adduser tomcat
[root@linux apex]# mkdir /u01/app/ords
[root@linux apex]# mv /tmp/ords-18.4.0.354.1002.zip?AuthParam=1551998522_ea19e89d6001xxxx /u01/app/ords
[root@linux apex]# chown -R tomcat:tomcat /u01/app/ords
[root@linux apex]# cd /u01/app/ords
[root@linux ords]# su tomcat
[tomcat@linux ords]# unzip ords-18.4.0.354.1002.zip?AuthParam=1551998522_ea19e89d6001xxxx

d) Make a directory to hold the configuration.

[tomcat@linux ords]# mkdir -p /u01/app/ords/conf

e) Edit the configuration file (still as user tomcat) and change the contents accordingly. If you are not used to the text editor tool in Linux called vi, it’s pretty easy to use it, you need to know 3 things: writing in any file can be done only after you press the INSERT key on you keyboard. To close and/or save the changes you need to press the ESC key and then type either :wq (to save the changes you made and exit) or :q! (to exit without saving the changes)

[tomcat@linux ords]# vi /u01/app/ords/params/ords_params.properties

db.hostname=linux
db.port=1521
db.servicename=ORCL_fra3md.sub12131435142.vcn.oraclevcn.com
db.sid=ORCL
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
rest.services.apex.add=
rest.services.ords.add=true
schema.tablespace.default=APEX
schema.tablespace.temp=TEMP
standalone.http.port=8080
standalone.static.images=
user.tablespace.default=APEX
user.tablespace.temp=TEMP

You can find your specific configurations using these:

• db.hostname : in Linux type “hostname”
• db.port : this is by default 1521 unless you manually changed it
• db.servicename : connect to the database with “sqlplus / as sysdba” and type “show parameter service_name”
• db.sid : still connected to the database type “select instance from v$thread”

You can leave the rest as I have in my file.

f) Use the “ords.war” file to specify the configuration directory using the following command. The file name “ords.war” will result in a URL containing “/ords/”. If you want a different URL, rename the WAR file accordingly. In this post I will use the original name.

As ROOT user (if you are tomcat type exit to become root again) type:

[root@linux ords]# /usr/bin/java -jar ords.war configdir /u01/app/ords/conf
Dec 17, 2017 8:50:19 AM
INFO: Set config.dir to /u01/app/ords/conf in: /u01/app/ords/ords.war
$

g) Install ORDS using the following command. This is the equivalent of specifying the “install simple” command line parameters. Provide the passwords for the users like we did in point 1.b) and press 1 to use PL/SQL Gateway

[root@linux ords]# /usr/bin/java -jar ords.war

3. Downloading and installing Tomcat

a) Download Tomcat 9 from HERE to the cloud database without using any trick this time :) in a new directory as user root, change it’s owner and unzip it :

[root@linux ords]# mkdir /u01/app/tomcat
[root@linux ords]# wget  http://mirrors.london-do.gethosted.online/apache/tomcat/tomcat-9/v9.0.16/bin/apache-tomcat-9.0.16.tar.gz -d /u01/app/tomcat/
[root@linux ords]# chown -R tomcat:tomcat /u01/app/tomcat/
[root@linux ords]# su tomcat
tomcat@linux ords]$ cd /u01/app/tomcat/
[tomcat@linux tomcat]$ tar zxvf apache-tomcat-9.0.16.tar.gz

b) If you want to make life a bit easier you can optionally add the Tomcat environment variables to your profile so they are configured each time you log in.

CATALINA_HOME=/u01/app/tomcat/apache-tomcat-9.0.16
CATALINA_BASE=$CATALINA_HOME

export CATALINA_HOME
export CATALINA_BASE

c) Let’s start up Tomcat web server:

[tomcat@linux tomcat]$ /u01/app/tomcat/apache-tomcat-9.0.16/bin/startup.sh

d) Now we need to open the web server port to allow incoming access, first in the Linux console and then on the Oracle Cloud. Switch to root user and add this port to iptables and save it:

[tomcat@linux tomcat]$ exit
[root@linux tomcat]# iptables -I INPUT -p tcp -m tcp --dport 8080 -j ACCEPT
[root@linux tomcat]# service iptables save
[root@linux tomcat]# service iptables reload

e) Go to the web console and open port 8080 on the Oracle Cloud firewall in the Security Lists of the VCN you are using for the database (you can find a quick guide on where to find it on this post HERE). It should like like this:

f) Test that Tomcat is working fine and the web page is loading. Type in your web browser the IP address of your cloud database followed by the 8080 port;

http://144.133.122.111:8080

g) Some automation is needed now so that Tomcat starts when our instance is up with runlevel script. The script has to be created as OS user root. In one of the first lines, I have set a sleep command to be sure that the database is available before the application server starts.

[root@linux tomcat]#   vi /etc/init.d/tomcat

Paste this inside the file:

#!/bin/bash
#
# tomcat
#
# chkconfig:
# description: Start up the Tomcat servlet engine.
# Source function library.
. /etc/init.d/functions
# Sleep 20 seconds until database is started sucessfully
sleep 20
RETVAL=$?
CATALINA_HOME="/u01/app/tomcat/apache-tomcat-9.0.16"
case "$1" in
start)
if [ -f $CATALINA_HOME/bin/startup.sh ];
then
echo $"Starting Tomcat"
/bin/su tomcat $CATALINA_HOME/bin/startup.sh
fi
;;
stop)
if [ -f $CATALINA_HOME/bin/shutdown.sh ];
then
echo $"Stopping Tomcat"
/bin/su tomcat $CATALINA_HOME/bin/shutdown.sh
fi
;;
*)
echo $"Usage: $0 {start|stop}"
exit 1
;;
esac
exit $RETVAL

h) Add the script to the runlevel environment level 3 and 5:

[root@linux tomcat]#   chmod 755 /etc/init.d/tomcat
[root@linux tomcat]#   cd /etc/rc3.d
[root@linux tomcat]#   ln -s /etc/init.d/tomcat S99tomcat

[root@linux tomcat]#   cd /etc/rc5.d
[root@linux tomcat]#   ln -s /etc/init.d/tomcat S99tomcat

4. Linking Tomcat with APEX

a) Switch back to the Tomcat OS user and copy the APEX images to the Tomcat “webapps” directory.

[root@linux tomcat]#   su tomcat
[tomcat@linux tomcat]$   mkdir /u01/app/tomcat/apache-tomcat-9.0.16/webapps/i/
[tomcat@linux tomcat]$   cp -R /u01/app/apex/images/* /u01/app/tomcat/apache-tomcat-9.0.16/webapps/i/

b) Copy the ORDS application ords.war to the Apache Tomcat:

[tomcat@linux tomcat]$   cp /u01/app/ords/ords.war /u01/app/tomcat/apache-tomcat-9.0.16/webapps/

c) Login into Oracle Application Express — add /ords at the end of the application server URL like http://130.120.110.100:8080/ords

You can fill in the INTERNAL workspace and use the Admin and the password we gave in first part of the post to log in.

That’s it!