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