Install and Configure Oracle APEX with ORDS and Apache Tomcat

Install and Configure Oracle APEX with ORDS and Apache 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

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

/home/oracle/devdoyensys

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.

[oracle@devdevdoyensys]$ unzip apex_19.2_en.zip

[oracle@devdevdoyensys]$ 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

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 tablespace.

[oracle@dev ~]$ cd devdoyensys/apex/

[oracle@dev 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

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

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

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.

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

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

cd /home/oracle/devdoyensys

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

[oracle@devdevdoyensys]$ tar -xvf jdk-13.0.1_linux-x64_bin.tar.gz

[oracle@devdevdoyensys]$ tar -xvf apache-tomcat-9.0.27.tar.gz

[oracle@devdevdoyensys]$ 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

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

vi tomcat.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

[oracle@dev ~]$ cd $CATALINA_HOME

[oracle@dev apache-tomcat-9.0.27]$ cd bin/

[oracle@dev 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.

http://devdoyensys.com:8080

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

[oracle@dev 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.

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

=================

PART 3: Install Oracle Rest Data service (ORDS)

Create a separate directory for ORDS insllataion and then Unzip ORDS software as below in the following location.

[oracle@devdevdoyensys]$ mkdir -p /home/oracle/devdoyensys/ords

[oracle@devdevdoyensys]$ unzip ords-19.1.0.092.1545.zip -d /home/oracle/devdoyensys/ords

[oracle@devdevdoyensys]$ cd ords/

[oracle@devords]$ 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

Make a directory to hold the configuration and edit the database configuration template(params file) as below

[oracle@devords]$ mkdir -p /home/oracle/devdoyensys/ords/conf

[oracle@devords]$ 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

Set Oracle REST Database Service ORDS configuration directory

[oracle@devords]$ cp ords.war ords.war_bkpoct15

[oracle@devords]$ mv ords.warapex.war

[oracle@devords]$ 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

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

[oracle@devords]$ /home/oracle/devdoyensys/jdk-13.0.1/bin/java -jar apex.warconfigdir /home/oracle/devdoyensys/ords/conf

Oct 15, 2020 7:26:04 PM

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

[oracle@devords]$

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.

Start the installation of ORDS

[oracle@devords]$ /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

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,

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

Prepare the application server directory for the Oracle Application Express images

[oracle@devdevdoyensys]$ mkdir -p $CATALINA_HOME/webapps/i/

[oracle@devdevdoyensys]$ cd apex/

[oracle@dev apex]$ cp -R /home/oracle/devdoyensys/apex/images/* $CATALINA_HOME/webapps/i/

Copy the ORDS application apex.warfile to the Apache Tomcat

[oracle@devdevdoyensys]$ cdords/

[oracle@devords]$ cpapex.war $CATALINA_HOME/webapps

Startup the Apache tomcat application server

[oracle@dev ~]$ . tomcat.env

[oracle@dev ~]$ cd $CATALINA_HOME/bin

[oracle@dev 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.

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

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

Abishek Kathiresan