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
- Installation and Configuration Oracle Application Express 5.1.1
- Installation and Configuration Apache Tomcat 8.5.14
- 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