Install Oracle Apex 5.1.x on ORDS 3,17.4, 18.1

Oracle Apex is a modern framework to quickly develop and deploy web apps sitting on top of Oracle Database. Apex can be run directly from the database engine itself using a component called Mod PL/SQL. It is one of the easiest way to run Apex, but it is not a production ready setup. In a production scenario your database server should not be internet facing. The ideal design is a reverse proxy server facing the internet and a webserver sitting behind it and the DB server behind a firewall isolated from any public network.

Oracle has a modern gateway to do this, it is called ORDS( previously called Apex Listener). It stands for Oracle Rest Data Services. Not only it can help you host your apex instance in a Java web container like Tomcat, Glassfish, Weblogic but also it gives your developer access to build enterprise scale data access API’s. It can help you build your front-end in the modern JavaScript instead of the traditional programming languages. You can read about it more here : https://blogs.oracle.com/newgendbaccess/why-use-rest-and-ords-to-transform-your-oracle-database-into-a-restful-api-service

architecture big

Now to setup ORDS to host your Apex instance first we need to make sure you have 3 components

1. Oracle APEX itself, though from ORDS 17.1 it is not compulsory to have Apex installed, but i always prefer having it. Download the latest version from here : http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

2. A Servlet web container like Tomcat, Glassfish or Weblogic. I prefer to use Tomcat since it is the easiest to setup, is open source and is quite lightweight. Apache tomcat can be downloaded from here  : https://tomcat.apache.org/download-90.cgi

3. ORDS, this is the most important component in this setup. Oracle has recently changed it’s numbering scheme. So it jumped from ORDS 3 to ORDS 17.4 and ORDS 18.1. It is now done using YY.MM naming convention. It can be downloaded from here : http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html

Let’s  Start with Installing Apache tomcat first on you server

                             1.  INSTALL APACHE TOMCAT

1. Create a user tomcat on the linux server which will be your webserver

useradd tomcat

2. Untar the file in the home directory or create a separate directory. To have a well defined structure i always prefer to have a directory structure like /u01/tomcat

tar xvf apache-tomcat-9.0.8.tar.gz

This will create the directory ‘apache-tomcat-9.0.8’ inside

go to the .bash_profile of tomcat user and add below for the home directory

PATH=$PATH:$HOME/bin:$CATALINA_HOME/bin
CATALINA_HOME=/u01/tomcat/apache-tomcat-9.0.8
export CATALINA_HOME
export PATH

. .bash_profile

3. Start Stop Apache Tomcat 9 snd enable Gui Access

sh $CATALINA_HOME/bin/shutdown.sh

sh $CATALINA_HOME/bin/startup.sh

You can now access the console of tomcat using http://localhost:8080/

To enable access to manager app and manager app gui. Add below to lines to file tomcat-users.xml

vi $CATALINA_HOME/conf/tomcat-users.xml

<role rolename=”manager-gui”/>
<user username=”tomcat” password=”Abc1234$#” roles=”manager-gui”/>
<role rolename=”admin-gui”/>
<user username=”tomcat” password=”Abc1234$#” roles=”admin-gui”/>

Save and restart Tomcat

                        2.   INSTALL ORACLE APEX 5.1.x

This installation has to be done on the DB Server. Copy your Apex zip file to Oracle Home Directory. Remove the existing Apex directory and unzip the fil

1.Install Apex Binary and Create Tablespace and Schema

cd $ORACLE_HOME

rm -rf apex/

unzip apex_5_0_1_en.zip

cd $ORACLE_HOME/apex

sqlplus “/as sysdba”

SQL> CREATE TABLESPACE APEX  DATAFILE ‘/u01/oradata/orcl/datafiles/apex01.dbf’ SIZE 1G AUTOEXTEND ON NEXT 10M;

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

APEX – Tablespace to hold Apex Schema and its associated files

TEMP –  Temporary Tablespace

/i/ – It is  your image directory

Change your Admin password

SQL> @apxchpwd.sql

Create APEX_LISTENER and  APEX_REST_PUBLIC_USER

SQL> @apex_rest_config.sql

If you want to run the PL/SQL gateway continue to the next step else you can skip this and go to Step 3. directly

SQL> @apex_epg_config.sql /u01/app/oracle/product/12.2.0/dbhome_1

‘/u01/app/oracle/product/12.2.0/dbhome_1’ is the home directory of Oracle Database

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

Check the HTTP Port,

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

If this is 0 and you need to to install Apex with ORDS then goto Step 3. else continue and get the port

SQL> EXEC DBMS_XDB.sethttpport(8080);

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

8080

The URL for Apex application now should be accessible from Web Browser http://localhost:8080/apex/apex_admin

              3. ORACLE REST DATA SERVICES (ORDS) CONFIGURATION & INSTALLATION

After downloading the ORDS to the server, go to the server where you installed Tomcat earlier

1.Unzip the ords zip file

cd /u01

mkdir -p /u01/ords

mkdir -p /u01/ords/conf

unzip ords.18.1.1.95.1251.zip

2. Login to DB Server SQL*PLUS and unlock the Apex  listener usernames and password to ensure ORDS installation goes smoothly

SQL> ALTER USER SYS IDENTIFIED BY abc123 ACCOUNT UNLOCK;

SQL> ALTER USER APEX_LISTENER IDENTIFIED BY abc123 ACCOUNT UNLOCK;
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY abc123 ACCOUNT UNLOCK;
SQL> ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY abc123 ACCOUNT UNLOCK;

SQL>ALTER USER ORDS_PUBLIC_USER IDENTIFIED BY abc123 ACCOUNT UNLOCK;

If last SQL command fails, it is because there is no ORDS schema yet.

3. Configure ORDS for Database connectivity. Go back to WebServer and there will be a ords.war file in /u01/ords

Edit the /u01/ords/params/ords_params.properties and set the parameters of your DB server and instance. Make sure there is network connectivity between DB Server and Web Server

cd /u01/ords

java -jar ords.war configdir /u01/ords/conf

java -jar ords.war

Don’t select Standalone option in the last, just press 2 and exit. Standlone server is a quick Jetty webserver build in ORDS. But its not the best production setup, hosting on Apache Tomcat is better.

mkdir $CATALINA_HOME/webapps/i/

Copy the image directory from the Apex installation in DB Server to Tomcat image directory.

cp -R $ORACLE_HOME/apex/images/* $CATALINA_HOME/webapps/i/

Copy the ords.war file from /u01/ords to Webapps directory of tomcat

cd /u01/ords
cp ords.war $CATALINA_HOME/webapps/

ORDS should now be accessible on URL http://localhost:8080/ords

Remember to remove the port 8080 configuration from Embedded PL/SQL gateway, if you did Step 2. and are moving to ORDS from PL/SQL Gateway

SQL> EXEC DBMS_XDB.sethttpport(0);

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

0

And try URL again : http://localhost:8080/ords

Error 1 :

Oracle Rest Data Services 404 Not Found When Running APEX – PL/SQL Gateway Configured

Check APEX_PUBLIC_USER account and unlock it, stop and start Apache tomcat and try again. Or maybe an incorrect APEX_PUBLIC_USER password was entered during ORDS configuration. reconfigure the ords.war using below command:

java -jar ords.war setup

And copy new war file to webapps directory again and access URL again

http://localhost:8080/ords

Reference : Oracle Rest Data Services 404 Not Found When Running APEX – PL/SQL Gateway Configured (Doc ID 2048493.1)

Error 2:

Server isn’t redirecting oracle apex ords on Firefox

This page isn’t working
<hostname> redirected you too many times.

Try clearing the cookies and try again. Most likely close the browser windows and open and again and the error will go away

http://localhost:8080/ords

Reference: Problem Accessing APEX Application Using ORDS 3.0.9.348.07.16 (Doc ID 2280694.1)

Summary :

1.Steps to do when installing only PL/SQL gateway

1 > 2

2. Steps when upgrading from PL/SQL Gateway to ORDS

1 > 3

3. Steps when doing fresh new install of ORDS

1 > 2 > 3