Oracle REST Data Service

In this article I will show you how you can install and configure Oracle Application Express (APEX) and the Oracle REST Data Service (ORDS), which is running on an Apache Tomcat application server. In previous installations I used the Oracle HTTP server and the extenstion mod_plsq. But this does not work anymore with the newest Oracle HTTP server version. In the Oracle Application Express Installation Guide is written:

mod_plsql is deprecated as of Oracle HTTP Server 12c (12.1.3). For more information, please see My Oracle Support Note 1576588.1. Oracle recommends using Oracle REST Data Services instead.

Oracle REST Data Service is the future – so let’s go to the future.

Installation Steps

1. Installation and Configuration Oracle Application Express 5.1.1
2. Installation and Configuration Apache Tomcat 8.5.14
3. Installation and Configuration Oracle Rest Data Service ORDS 3.0.9

My new architecture what I want to build looks like as described on this picture.  

Source: http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-deploy-installation-1878444.html

My Environment

• Red Hat Enterprise Linux Server Release 7.3 – server hostname is neuendorf.jurasuedfuss.coom
• Oracle Database 12c Standard Edition Release 12.2.0.1.0 – database service name is APEXORDS.jurasuedfuss.com
• OS Firewall is open for Port 8080

OS Users

I work with two OS users to separate RDBMS and application tasks:

• oracle – Oracle RDBMS / Listener / Oracle Application Express
• tomcat – Apache Tomcat / ORDS / Java Development Kit JDK 1.8

Directories

• Oracle Software: /u01/app/oracle
• Apache Tomcat / ORDS / JDK: /u01/app/tomcat

1. Installation and Configuration Oracle Application Express 5.1.1 – OS User: oracle

I have downloaded Oracle Application Express here: http://www.oracle.com/technetwork/developer-tools/apex/downloads/download-085147.html . The extracted software is located on the server in  the /tmp directory. For the APEX data I have created a new tablespace called APEX too.

Go to the software location:

[oracle@neuendorf ~]$ cd /tmp/apex

Login into the database as SYSDBA:

[oracle@neuendorf ~]$ . oraenv
ORACLE_SID = [oracle] ? APEXORDS
The Oracle base has been set to /u01/app/oracle

[oracle@neuendorf ~]$ sqlplus / as sysdba

Execute the installation script:

SQL> @apexins apex apex temp /i/

Set password for ADMIN user / Workspace INTERNAL:

SQL> @apxchpwd.sql

Configure database RESTful services – the passwords for the new created users APEX_LISTENER and APEX_REST_PUBLIC_USER will be used later for the ORDS setup:

SQL> @apex_rest_config.sql

Set password for the APEX_PUBLIC_USER and unlock the account:

SQL> ALTER USER apex_public_user IDENTIFIED BY  <MY_APEX_PUBLIC_USER_PASSWORD> ACCOUNT UNLOCK;

To avoid the password expiration for the APEX_PUBLIC_USER, I have created a  new profile especially for this user with unlimited password lifetime:

SQL> CREATE PROFILE upd_password_life_time_unlimited LIMIT PASSWORD_LIFE_TIME UNLIMITED;
SQL> ALTER USER apex_public_user PROFILE upd_password_life_time_unlimited;

Allow other hosts than the localhost to use the Oracle Application Express installation:

SQL> BEGIN
2     DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
3          host => '*',
4          ace => xs$ace_type(privilege_list => xs$name_list('connect'),
5          principal_name => 'apex_050100',
6          principal_type => xs_acl.ptype_db));
7 END;
8 /

2. Installation and Configuration Apache Tomcat 8.5.14 – OS User: tomcat

Create directory :

[tomcat@neuendorf ~]$ mkdir /u01/app/tomcat

Go to directory and extract software  form /tmp:

[tomcat@neuendorf ~]$ cd /u01/app/tomcat/
[tomcat@neuendorf ~]$ tar xvfz /tmp/jdk-8u131-linux-x64.tar.gz
[tomcat@neuendorf ~]$ tar xvfz /tmp/apache-tomcat-8.5.14.tar.gz

Two new directories for Apache Tomcat and JDK are created:

[tomcat@neuendorf tomcat]$ ll
total 8
drwxrwxr-x. 9 tomcat tomcat 4096 May 9 12:29 apache-tomcat-8.5.14
drwxr-xr-x. 8 tomcat tomcat 4096 Mar 15 09:35 jdk1.8.0_131

To simplify the management with the Apache Tomcat application server, I have added environment variables to the .bash_profile:

# Tomcat Environment Variables
JAVA_HOME=/u01/app/tomcat/jdk1.8.0_131
CATALINA_HOME=/u01/app/tomcat/apache-tomcat-8.5.14
CATALINA_BASE=$CATALINA_HOME

export JAVA_HOME
export CATALINA_HOME
export CATALINA_BASE

Startup Tomcat – after the re-login as OS user tomcat the application server can be started by using the environment variable $CATALINA_HOME.

[tomcat@neuendorf ~]$ $CATALINA_HOME/bin/startup.sh
Using CATALINA_BASE: /u01/app/tomcat/apache-tomcat-8.5.14
Using CATALINA_HOME: /u01/app/tomcat/apache-tomcat-8.5.14
Using CATALINA_TMPDIR: /u01/app/tomcat/apache-tomcat-8.5.14/temp
Using JRE_HOME: /u01/app/tomcat/jdk1.8.0_131
Using CLASSPATH: /u01/app/tomcat/apache-tomcat-8.5.14/bin/bootstrap.jar:/u01/app/tomcat/apache-tomcat-8.5.14/bin/tomcat-juli.jar
Tomcat started.

Verify on command line level if tomcat has started, for example with CURL – HTTP 200 means that the response is OK:

[tomcat@neuendorf tomcat]$ curl -I http://localhost:8080
HTTP/1.1 200
Content-Type: text/html;charset=UTF-8
Transfer-Encoding: chunked
Date: Tue, 09 May 2017 12:10:11 GMT

Browser Verification – http://neuendorf.jurasuedfuss.com:

 

Shutdown Tomcat:

[tomcat@neuendorf ~]$ $CATALINA_HOME/bin/shutdown.sh

Start- / Stop Runlevel Script:

To automate the start/stop – we use a 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@neuendorf ~]# vi /etc/init.d/tomcat

Content:

#!/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-8.5.14"

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

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

[root@neuendorf ~]# chmod 755 /etc/init.d/tomcat

[root@neuendorf ~]# cd /etc/rc3.d
[root@neuendorf ~]# ln -s /etc/init.d/tomcat S99tomcat

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

3. Installation and Configuration Oracle Rest Data Service ORDS 3.0.9 – OS User: tomcat

Create ORDS installation directory:

[tomcat@neuendorf tomcat]$ cd /u01/app/tomcat/
[tomcat@neuendorf tomcat]$ mkdir ords.3.0.9
[tomcat@neuendorf tomcat]$ cd ords.3.0.9

Extract ORDS from /tmp directory:

[tomcat@neuendorf tomcat]$ unzip /tmp/ords.3.0.9.348.07.16.zip

Verify directory content:

[tomcat@neuendorf tomcat]$ cd /u01/app/tomcat/ords.3.0.9
[tomcat@neuendorf ords.3.0.9]$ ll
total 48332
drwxrwxr-x. 3 tomcat tomcat 17 May 9 12:40 conf
drwxr-xr-x. 3 tomcat tomcat 20 Dec 13 07:16 docs
drwxr-xr-x. 6 tomcat tomcat 81 Dec 13 07:21 examples
drwxr-xr-x. 2 tomcat tomcat 4096 May 9 12:42 logs
-rw-r--r--. 1 tomcat tomcat 49442163 May 9 12:40 ords.war
drwxr-xr-x. 2 tomcat tomcat 35 May 9 12:40 params
-rw-r--r--. 1 tomcat tomcat 43605 Dec 13 07:21 readme.html

Create ORDS directory and edit the database configuration template – set hostname and database service name:

[tomcat@neuendorf tomcat]$ mkdir /u01/app/tomcat/ords.3.0.9/conf
[tomcat@neuendorf tomcat]$ vi /u01/app/tomcat/ords.3.0.9/params/ords_params.properties

Content of my ords_params.properties file:

db.hostname=neuendorf
db.port=1521
db.servicename=APEXORDS.jurasuedfuss.com
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
user.tablespace.default=USERS
user.tablespace.temp=TEMP

Set Oracle REST Database Service ORDS configuration directory:

[tomcat@neuendorf tomcat]$ cd /u01/app/tomcat/ords.3.0.9/
[tomcat@neuendorf ords.3.0.9]$ $JAVA_HOME/bin/java -jar ords.war configdir /u01/app/tomcat/ords.3.0.9/conf
May 09, 2017 12:33:54 PM
INFO: Set config.dir to /u01/app/tomcat/ords.3.0.9/conf in: /u01/app/tomcat/ords.3.0.9/ords.war

Install Oracle REST Database Service ORDS:

You will be asked for a new password for the database user ORDS_PUBLIC_USER (user will be created by this script), for the already existing users APEX_LISTENER and APEX_REST_PUBLIC_USER and for the SYS password. For ORDS metadata, I have created a new tablespace called ORDS.

The values for database server, hostname and the service name are taken from the configuration file ords_params.properties. Do not start ORDS in standalone mode at the end of the configuration.

[tomcat@neuendorf ords.3.0.9]$ $JAVA_HOME/bin/java -jar ords.war install advanced
Enter the name of the database server [neuendorf]: <ENTER>
Enter the database listen port [1521]: <ENTER>
Enter 1 to specify the database service name, or 2 to specify the database SID [1]: <ENTER>
Enter the database service name [ORDSAPEX.jurasuedfuss.com]: <ENTER>
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]: <ENTER>
Enter the database password for ORDS_PUBLIC_USER: <MY_ORDS_PUBLIC_USER_PASSWORD> <ENTER>
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.

Enter the username with SYSDBA privileges to verify the installation [SYS]: <ENTER>
Enter the database password for SYS: <MY_SYS_PASSWORD> <ENTER>
Confirm password:
Enter the default tablespace for ORDS_METADATA [APEX]: ORDS <ENTER>
Enter the temporary tablespace for ORDS_METADATA [TEMP]: <ENTER>
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:ORDS <ENTER>
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]: <ENTER>
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]: <ENTER>
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]: <ENTER>
Enter the database password for APEX_PUBLIC_USER: <MY_APEX_PUBLIC_USER_PASSWORD> <ENTER>
Confirm password: <MY_APEX_PUBLIC_USER_PASSWORD> <ENTER>
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]: <ENTER>
Enter the database password for APEX_LISTENER: <MY_APEX_LISTENER_PASSWORD> <ENTER>
Confirm password: <MY_APEX_LISTENER_PASSWORD> <ENTER>
Enter the database password for APEX_REST_PUBLIC_USER: <MY_APEX_REST_PUBLIC_USER_PASSWORD> <ENTER>
Confirm password: <MY_APEX_REST_PUBLIC_USER_PASSWORD> <ENTER>
May 09, 2017 11:18:27 AM
INFO: Updated configurations: defaults, apex, apex_pu, apex_al, apex_rt
Installing Oracle REST Data Services version 3.0.9.348.07.16
... Log file written to /u01/app/tomcat/ords.3.0.9/logs/ords_install_core_2017-05-09_111827_00102.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /u01/app/tomcat/ords.3.0.9/logs/ords_install_datamodel_2017-05-09_111843_00674.log
Completed installation for Oracle REST Data Services version 3.0.9.348.07.16. Elapsed time: 00:00:18.80

Enter 1 if you wish to start in standalone mode or 2 to exit [1]: <2> <ENTER>

You can see the encrypted passwords and the selected tablespaces in the configuration file ords_params.properties:

[tomcat@neuendorf tomcat]$ cat /u01/app/tomcat/ords.3.0.9/params/ords_params.properties

#Tue May 09 12:41:57 CEST 2017
db.hostname=neuendorf
db.password=@057AF00D1B3F13CCECCD71BCF1E0AD259AA183443E64B284CE
db.port=1521
db.servicename=APEXORDS.jurasuedfuss.com
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
plsql.gateway.add=true
rest.services.apex.add=true
rest.services.ords.add=true
schema.tablespace.default=ORDS
schema.tablespace.temp=TEMP
user.apex.listener.password=@05B9EEC26803C78359021659CB0EEFE0175C898A5D0404FD44
user.apex.restpublic.password=@05F57FA4BB374FE48E6452F1AD3E36FC5BD6B178B18952C791
user.public.password=@05B3277F27BC54402B72DE458C49A4188F33EBDCA7A021DFC3
user.tablespace.default=ORDS
user.tablespace.temp=TEMP

Optimize the database connection settings – add these lines to the apex.xml configuration file:

[tomcat@neuendorf tomcat]$ vi /u01/app/tomcat/ords.3.0.9/conf/ords/conf/apex.xml

<entry key="jdbc.InitialLimit">15</entry> 
<entry key="jdbc.MaxLimit">50</entry> 
<entry key="jdbc.MinLimit">15</entry>

Prepare the application server directory for the Oracle Application Express images

[tomcat@neuendorf tomcat]$ mkdir $CATALINA_HOME/webapps/i/
[tomcat@neuendorf tomcat]$cp -R /tmp/apex/images/* $CATALINA_HOME/webapps/i/

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

[tomcat@neuendorf tomcat]$ cd /u01/app/tomcat/ords.3.0.9
[tomcat@neuendorf tomcat]$ cp ords.war $CATALINA_HOME/webapps/

Startup the application server:

[tomcat@neuendorf ~]$ $CATALINA_HOME/bin/startup.sh
Using CATALINA_BASE: /u01/app/tomcat/apache-tomcat-8.5.14
Using CATALINA_HOME: /u01/app/tomcat/apache-tomcat-8.5.14
Using CATALINA_TMPDIR: /u01/app/tomcat/apache-tomcat-8.5.14/temp
Using JRE_HOME: /u01/app/tomcat/jdk1.8.0_131
Using CLASSPATH: /u01/app/tomcat/apache-tomcat-8.5.14/bin/bootstrap.jar:/u01/app/tomcat/apache-tomcat-8.5.14/bin/tomcat-juli.jar
Tomcat started.

Login into  Oracle Application Express – add /ords at the end of the application server URL like http://neuendorf.jurasuedfuss.com:8080/ords:

That’s it – have fun with the Oracle Application Express, Oracle REST Data Services and Tomcat :-). In the next post I will show you how you can secure your installation by change to https/SSL.

Manuals:

Installing Application Express and Configuring Oracle REST Data Services

Tomcat Web Application Deployment

Oracle Application Express Installation Overview