Oracle Apex Tomcat

 

Install and Configure Oracle APEX with ORDS and Apache Tomcat

From this location connect as sys user and create a Tablespace in database for storing Apex MetaData. SQL CREATE TABLESPACE APEX DATAFILE ‘/u01/app/oracle/oradata/DEV/apex01.dbf’ SIZE 1G; Tablespace created. Start installation of Apex 19.2. Run the apexins.sql to install the Apex files into created APEX. Oracle REST Data Services (formerly APEX Listener) - The Oracle REST Data Services (ORDS) is Java based, can be installed into any J2EE compliant Web Server, and is the preferred option for use with Oracle Application Express. It is a free tool that is fully supported against Oracle WebLogic Server, Oracle Glassfish Server, and Apache Tomcat. Normally, there are two ways to install Oracle APEX. Method 1: Configure the Embedded PL/SQL Gateway: This is the way to configure Oracle APEX which can run on Oracle XML DB HTTP Server, it is a HTTP that is available when you install Oracle 11g or higher. Method 2: Install Oracle APEX on a specific web server such as WebLogic, Tomcat.

Introduction:

In this post, we will discuss about the step by step procedure to install Oracle Application express 19.2 with ORDS and Apache Tomcat.

Customer who works with Apex deployed on PL/SQL gateway face challenges in case of performance (It consumes more load on the server). In order to increase the Application performance, Oracle recommends to use Apex with ORDS and Tomcat. Added the reference DOC id from Oracle Support at the end of the document. Let’s start with the Installation steps.

PART 1: Installation of Oracle Apex 19.2

  1. In our case the downloaded apex software (apex_19.2_en.zip) is present in the below location.

/home/oracle/devdoyensys

  1. Unzip apex software from the above location.

After the Unzip, a new folder will be created named as ‘apex‘. This folder contains all the sql scripts regarding apex operations.

[[email protected]]$ unzip apex_19.2_en.zip[[email protected]]$ ls -lrth

total 153M

drwxr-xr-x. 6 oracle oinstall 4.0K Nov 7 2019 apex

-rwxr-xr-x. 1 oracle oinstall 95M Oct 15 17:21 apex_19.2_en.zip

  1. From this location connect as sys user and create a Tablespace in database for storing Apex MetaData.

SQL> CREATE TABLESPACE APEX DATAFILE ‘/u01/app/oracle/oradata/DEV/apex01.dbf’ SIZE 1G;

Tablespace created.

  1. Start installation of Apex 19.2

Run the apexins.sql to install the Apex files into created APEX tablespace.

[[email protected] ~]$ cd devdoyensys/apex/[[email protected] apex]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Thu Oct 15 17:50:33 2020

Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.8.0.0.0

SQL>@apexins.sql APEX APEX TEMP /i/

Thank you for installing Oracle Application Express 19.2.0.00.18

Oracle Application Express is installed in the APEX_190200 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:09.92

timing for: Complete Installation

Elapsed: 00:10:49.83

PL/SQL procedure successfully completed.

1 row selected.

…null1.sql

  1. Configure database RESTful services.

Note: The passwords for the newly created users APEX_LISTENER and APEX_REST_PUBLIC_USER will be used later for the ORDS setup.

SQL>@apex_rest_config.sql

Enter a password for the APEX_LISTENER user [] Set your Password here

Enter a password for the APEX_REST_PUBLIC_USER user [] Set your Password here

  1. Login to DB assysdba to unlock the user account.

SQL> alter user APEX_LISTENER identified by Pwd#123 account unlock;

User altered.

SQL> alter user APEX_PUBLIC_USER identified by Pwd#123 account unlock;

User altered.

SQL> alter user APEX_REST_PUBLIC_USER identified by Pwd#123 account unlock;

User altered.

  1. Check whether http port is set as 0, if not setit is as mentioned below (the agenda is to launch the Apex using Tomcat Apache )

SQL> EXEC dbms_xdb.sethttpport(0);

PL/SQL procedure successfully completed.

PART 2: Installation of Tomcat & Java JDK

  1. In our case the downloaded Tomcat, Java jdk softwareis present in the below location.

cd /home/oracle/devdoyensys

  1. Extract the software setup as below from the above location.

After the extract, a new folder will be created in the name jdk-13.0.1and apache-tomcat-9.0.27

[[email protected]]$ tar -xvf jdk-13.0.1_linux-x64_bin.tar.gz[[email protected]]$ tar -xvf apache-tomcat-9.0.27.tar.gz[[email protected]]$ ls -lrth

total 343M

-rwxr-xr-x. 1 oracle oinstall 95M Oct 15 17:21 apex_19.2_en.zip

drwxr-xr-x. 6 oracle oinstall 4.0K Oct 15 17:52 apex

-rwxr-xr-x. 1 oracle oinstall 180M Oct 15 18:34 jdk-13.0.1_linux-x64_bin.tar.gz

-rwxr-xr-x. 1 oracle oinstall 11M Oct 15 18:35 apache-tomcat-9.0.27.tar.gz

drwxr-xr-x. 9 oracle oinstall 99 Oct 15 18:35 jdk-13.0.1

drwxr-xr-x. 9 oracle oinstall 4.0K Oct 15 18:36 apache-tomcat-9.0.27

  1. Make sure to set the following environment variable to start the tomcat application.

vitomcat.env

export JAVA_HOME=/home/oracle/devdoyensys/jdk-13.0.1

export CATALINA_HOME=/home/oracle/devdoyensys/apache-tomcat-9.0.27

export CATALINA_BASE=$CATALINA_HOME

Now source the env file

. tomcat.env

Check Startup and shutdown of the tomcat service from the following location

[[email protected] ~]$ cd $CATALINA_HOME[[email protected] apache-tomcat-9.0.27]$ cd bin/[[email protected] bin]$ sh startup.sh

Using CATALINA_BASE: /home/oracle/devdoyensys/apache-tomcat-9.0.27

Using CATALINA_HOME: /home/oracle/devdoyensys/apache-tomcat-9.0.27

Using CATALINA_TMPDIR: /home/oracle/devdoyensys/apache-tomcat-9.0.27/temp

Using JRE_HOME: /home/oracle/devdoyensys/jdk-13.0.1

Using CLASSPATH: /home/oracle/devdoyensys/apache-tomcat-9.0.27/bin/bootstrap.jar:/home/oracle/devdoyensys/apache-tomcat-9.0.27/bin/tomcat-juli.jar

Tomcat started.

If tomcat is installed correctly,you could be able to view this page in web browser after starting tomcat service.

[[email protected] bin]$ sh shutdown.sh

Using CATALINA_BASE: /home/oracle/devdoyensys/apache-tomcat-9.0.27

Using CATALINA_HOME: /home/oracle/devdoyensys/apache-tomcat-9.0.27

Using CATALINA_TMPDIR: /home/oracle/devdoyensys/apache-tomcat-9.0.27/temp

Using JRE_HOME: /home/oracle/devdoyensys/jdk-13.0.1

Using CLASSPATH: /home/oracle/devdoyensys/apache-tomcat-9.0.27/bin/bootstrap.jar:/home/oracle/devdoyensys/apache-tomcat-9.0.27/bin/tomcat-juli.jar

NOTE: Picked up JDK_JAVA_OPTIONS: –add-opens=java.base/java.lang=ALL-UNNAMED –add-opens=java.base/java.io=ALL-UNNAMED –add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED

Note: Check the catalina log ($CATALINA_HOME/logs/catalina.out) for any issue.

  1. Specify as which user you need to access this application in following xml file.

cd /home/oracle/devdoyensys/apache-tomcat-9.0.27/conf

vi tomcat-users.xml

add below details

Oracle

<role rolename=”manager-gui” />

<user username=”oracle” password=”oracle” roles=”manager-gui” />

PART 3: Install Oracle Rest Data service (ORDS)

  1. Create a separate directory for ORDS insllataion and then Unzip ORDS software as below in the following location.
[[email protected]]$ mkdir -p /home/oracle/devdoyensys/ords[[email protected]]$ unzip ords-19.1.0.092.1545.zip -d /home/oracle/devdoyensys/ords[[email protected]]$ cd ords/[[email protected]]$ ls -lrth

total 58M

drwxr-xr-x. 2 oracle oinstall 35 May 31 2018 params

drwxr-xr-x. 6 oracle oinstall 60 Sep 27 2018 examples

-rw-r–r–. 1 oracle oinstall 35K Apr 1 2019 index.html

-rw-r–r–. 1 oracle oinstall 58M Apr 2 2019 ords.war

drwxr-xr-x. 3 oracle oinstall 20 Apr 2 2019 docs

  1. Make a directory to hold the configuration and edit the database configuration template(params file) as below
[[email protected]]$ mkdir -p /home/oracle/devdoyensys/ords/conf[[email protected]]$ ls -lrth

total 58M

drwxr-xr-x. 2 oracle oinstall 35 May 31 2018 params

drwxr-xr-x. 6 oracle oinstall 60 Sep 27 2018 examples

-rw-r–r–. 1 oracle oinstall 35K Apr 1 2019 index.html

-rw-r–r–. 1 oracle oinstall 58M Apr 2 2019 ords.war

drwxr-xr-x. 3 oracle oinstall 20 Apr 2 2019 docs

drwxr-xr-x. 2 oracle oinstall 6 Oct 15 18:56 conf

vi /home/oracle/devdoyensys/ords/params/ords_params.properties

db.hostname=devdoyensys.com

db.port=1527

db.servicename=DEV

db.sid=DEV

db.username=APEX_PUBLIC_USER

migrate.apex.rest=false

rest.services.apex.add=true –set true

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

  1. Set Oracle REST Database Service ORDS configuration directory
[[email protected]]$ cp ords.war ords.war_bkpoct15[[email protected]]$ mv ords.warapex.war[[email protected]]$ ls -lrth

total 115M

drwxr-xr-x. 6 oracle oinstall 60 Sep 27 2018 examples

-rw-r–r–. 1 oracle oinstall 35K Apr 1 2019 index.html

-rw-r–r–. 1 oracle oinstall 58M Apr 2 2019 apex.war

Oracle Apex Tomcat Performance

drwxr-xr-x. 3 oracle oinstall 20 Apr 2 2019 docs

drwxr-xr-x. 2 oracle oinstall 6 Oct 15 18:56 conf

drwxr-xr-x. 2 oracle oinstall 73 Oct 15 19:12 params

-rw-r–r–. 1 oracle oinstall 58M Oct 15 19:21 ords.war_bkpoct15

[[email protected]]$ /home/oracle/devdoyensys/jdk-13.0.1/bin/java -jar apex.warconfigdir /home/oracle/devdoyensys/ords/confRequirements

Oct 15, 2020 7:26:04 PM

INFO: Set config.dir to /home/oracle/devdoyensys/ords/conf in: /home/oracle/devdoyensys/ords/apex.war

[[email protected]]$

Note:

If you need to restart the installation for any reason, remember to clear down the config directory

and check the contents of “/home/oracle/devdoyensys/params/ords_params.properties” file before you restart the installation.

Having password entries in this file from a failed installation can cause problems.

  1. Start the installation of ORDS
[[email protected]]$ /home/oracle/devdoyensys/jdk-13.0.1/bin/java -jar apex.war install advanced

Enter the name of the database server [testdoyensys.com]:

Enter the database listen port [1527]:

Enter 1 to specify the database service name, or 2 to specify the database SID [1]:

Enter the database service name [DEV]:

Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:

Enter the database password for ORDS_PUBLIC_USER:

Confirm password:

Requires to login with administrator privileges to verify Oracle REST Data Services schema.

Enter the administrator username:sys

Enter the database password for SYS AS SYSDBA:

Confirm password:

ORA-01017: invalid username/password; logon denied

Try again. Enter the administrator username:sys

Enter the database password for SYS AS SYSDBA:

Confirm password:

Retrieving information.

Enter the default tablespace for ORDS_METADATA [APEX]:

Enter the temporary tablespace for ORDS_METADATA [TEMP]:

Enter the default tablespace for ORDS_PUBLIC_USER [APEX]:

Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:

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 the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:

Enter the database password for APEX_PUBLIC_USER:

Confirm password:

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 the database password for APEX_LISTENER:

Confirm password:

Enter the database password for APEX_REST_PUBLIC_USER:

Confirm password:

Oct 15, 2020 7:38:47 PM

INFO: reloaded pools: []

Installing Oracle REST Data Services version 19.1.0.r0921545

… Log file written to /home/oracle/ords_install_core_2020-10-15_193847_00441.log

… Verified database prerequisites

… Created Oracle REST Data Services proxy user

… Created Oracle REST Data Services schema

… Granted privileges to Oracle REST Data Services

… Created Oracle REST Data Services database objects

… Log file written to /home/oracle/ords_install_datamodel_2020-10-15_193908_00250.log

… Log file written to /home/oracle/ords_install_apex_2020-10-15_193910_00295.log

Completed installation for Oracle REST Data Services version 19.1.0.r0921545. Elapsed time: 00:00:25.294

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

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

cd /home/oracle/devdoyensys/ords/conf/apex/conf

vi apex.xml

add below lines,

Oracle Apex 19 Tomcat

<entry key=”jdbc.InitialLimit”>15</entry>

<entry key=”jdbc.MinLimit”>15</entry>

<entry key=”jdbc.MaxLimit”>50</entry> <———————–You can specify here the No of application users as per requirement.

  1. Prepare the application server directory for the Oracle Application Express images
[[email protected]]$ mkdir -p $CATALINA_HOME/webapps/i/[[email protected]]$ cd apex/[[email protected] apex]$ cp -R /home/oracle/devdoyensys/apex/images/* $CATALINA_HOME/webapps/i/
  1. Copy the ORDS application apex.warfile to the Apache Tomcat
[[email protected]]$ cdords/[[email protected]]$ cpapex.war $CATALINA_HOME/webapps
  1. Startup the Apache tomcat application server

Oracle Apex Tomcat

[[email protected] ~]$ . tomcat.env[[email protected] ~]$ cd $CATALINA_HOME/bin[[email protected] bin]$ sh startup.sh

Using CATALINA_BASE: /home/oracle/devdoyensys/apache-tomcat-9.0.27

Using CATALINA_HOME: /home/oracle/devdoyensys/apache-tomcat-9.0.27

Using CATALINA_TMPDIR: /home/oracle/devdoyensys/apache-tomcat-9.0.27/temp

Using JRE_HOME: /home/oracle/devdoyensys/jdk-13.0.1

Using CLASSPATH: /home/oracle/devdoyensys/apache-tomcat-9.0.27/bin/bootstrap.jar:/home/oracle/devdoyensys/apache-tomcat-9.0.27/bin/tomcat-juli.jar

Tomcat started.

  1. Now navigate to apex login page http://devdoyensys.com:8080/apex

Oracle Apex 20.1 Tomcat

Reference: APEX Performance is Very Slow Using XDB HTTP Server / EPG (Doc ID 1409784.1)

Introduction

In a previous post Tuning GlassFish for APEX/ORDS in Production I described the steps to configure ORDS and GlassFish for production use. In this post, I will turn my attention to Tomcat and see what needs to be done to get Tomcat production ready for ORDS and APEX. I will focus purely on the Tomcat configurations as I already covered the ORDS side in the previous post.
Like GlassFish, out of the box, Tomcat comes configured for use in a development environment. You will see many of the same limitations as Glassfish when considering production use:
  • Tomcat will be running in development mode (which incurs an overhead in checking to see if code has changed)
  • If you have more than 1 CPU, you will not be taking advantage of it
  • Tomcat will only assign 512m of ram to the JVM (and ORDS), even if you have more available
  • Logging will be set to FINE and INFO, adding a logging burden to Tomcat
  • You are in danger of timing out on large file uploads

Setting the Scene

I will use the same example as I did previously. I am going to assume an actively used system with 250 users and an anticipated peak load of 50 sessions. We are assuming that only 50 of these 250 users could be clicking on an APEX button (or making and Ajax request) at exactly the same time requiring all 50 JDBC connections. We are assuming a normal load of 20 concurrently active users. I am going to assume we have a dedicated web server running ORDS and Tomcat with 4GB RAM and 1 Quadcore CPU. The configurations discussed here assume Tomcat version 8.5.6.

Tomcat Configurations

Follow your normal routine for downloading and installing the JDK and Tomcat. I will be calling out the name of the configuration file that needs to be created/changed as well as the setting within the file. I will assume $CATALINA_HOME refers to the root of your Tomcat install folder. So if you installed Tomcat at /opt/tomcat you would see sub-directories of bin, conf, logs etc. making $CATALINA_HOME = /opt/tomcat
Logging
Configuration File: $CATALINA_HOME/conf/logging.properties
Change all occurrences of FINE and INFO to SEVERE
JVM Options
To pass JVM options to Tomcat on startup, you should create a new file called $CATALINA_HOME/bin/setenv.sh
Add the following values:
export CATALINA_OPTS='$CATALINA_OPTS -Xms1536m'
export CATALINA_OPTS='$CATALINA_OPTS -Xmx1536m'
export CATALINA_OPTS='$CATALINA_OPTS -server'
Note: If you are running JDK7, you can also set PermSize and MaxPermSize. Note: PermSize and MaxPermSize not applicable for Java 8.
export CATALINA_OPTS='$CATALINA_OPTS -XX:PermSize=256m'
export CATALINA_OPTS='$CATALINA_OPTS -XX:MaxPermSize=256m'
Main Tomcat Server Configurations
We are going to be configuring Tomcat on Port 8080.
Configuration File: $CATALINA_HOME/conf/server.xml
Add the following under the Catalina Service:
Notes:
  • acceptorThreadCount - The number of threads to be used to accept connections. Increase this value on a multi CPU Core machine.
  • acceptCount - The maximum queue length for incoming connection requests when all possible request processing threads are in use. Any requests received when the queue is full will be refused. Set to 10 to protect server from getting overloaded.
  • maxConnections - The maximum number of connections that the server will accept at any given time. When this number has been reached, the server will accept, but not process, one further connection. Connections are passed on to available threads to perform the actual work.
  • maxThreads - The maximum number of request processing threads to be created by a connector, which therefore determines the maximum number of simultaneous requests that can be handled.
  • minSpareThreads - The minimum number of threads always kept running waiting for new requests.
  • connectionTimeout - The number of milliseconds a connector will wait, after accepting a connection, for the request URI line to be presented.
  • disableUploadTimeout - Set to false and use the value in connectionUploadTimeout as the upload timeout.
  • connectionUploadTimeout - Specifies the timeout, in milliseconds, to use while a data upload is in progress. This only takes effect ifdisableUploadTimeout is set to false.

Oracle Apex Tomcat Tutorial


Note: The threads described above do not impact JDBC connection pooling, which ORDS is handling for us. ORDS is protecting (or throttling) the JDBC connections Tomcat is organizing the web connections. If we assume the MaxLimit JDBC connection setting in ORDS is 50 then we have a healthy overhead of connections over to queue any users we have over 50.

Oracle Apex Tomcat Reverse Proxy

Conclusion

The above settings are a guideline for configuring Tomcat to run ORDS in a fictitious production environment. As with all things performance, you will still need to go through the exercise of adding load to your system and finding the right settings for your hardware, users, data etc. The good news is, you now know which levers to pull!