How to install and configure Oracle APEX and ORDS server: Step-by-Step Guide -TTMatters

Before we begin, here are the assumptions we base this guide on

An oracle container database (CDB) is already created, and a pluggable database (PDB) is already created inside the container
We are using an architecture where there will a dedicated VM to act as the ORDS server, which will connect to the database. This way, we don't have to allow the end users to connect to the DB server directly
We are not going to deploy ORDS to any high availability systems (like a Weblogic cluster). Instead we are using a single standalone ORDS installation.

Here are the high level steps we need to follow:

Download and install Oracle APEX in the database
Configure REST in the installed APEX
Install and start ORDS in a dedicated VM, separate from the DB server

Install and configure APEX

We are going to install APEX at the PDB level. Before doing that, we need to uninstall APEX from the container, if it exists.
Connect to the container database as sysdba

sqlplus / as sysdba
--Check if APEX is installed
SQL> SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
--Unistall APEX from the container DB
SQL> @$ORACLE_HOME/apex/apxremov_con.sql
--Verify the status to make sure APEX is uninstalled
SQL> SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';

Install APEX in the PDB

Before the installation, make sure that the PDB is opened in READ WRITE mode, and you are connected to the PDB using sqlplus (either using TNS entry or using PDB_SID environment variable)

Confirm you are connected to the PDB

SQL> show con_id con_name

Check free space in the database

SQL> SELECT tablespace_name, SUM(bytes/1024/1024) AS "Free MB" FROM dba_free_space GROUP BY tablespace_name;

Check if the SYSTEM tablespace is autoextensible

SQL> SET linesize 500
SQL> SELECT TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE, MAXBYTES/1024/1024 AS "MAXSIZE(MB)" FROM DBA_DATA_FILES;

If not autoextensible, increase the SYSTEM tablespace to at least 500MB

SQL> ALTER DATABASE DATAFILE '.dbf' resize 500m;

Create APEX_DATA and APEX_FILES tablespaces

SQL> create tablespace APEX_DATA datafile size 1G autoextend on;
SQL> create tablespace APEX_FILES datafile size 1G autoextend on;

Disable default password complexity rules

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNTION NULL;

Start the APEX installation

SQL> @apexins.sql APEX_DATA APEX_FILES TEMP /i/

A successful installation will return something similar to the below with the respective versions and schema name

Thank you for installing Oracle Application Express 20.2.0.00.20

Oracle Application Express is installed in the APEX_200200 schema.

The structure of the link to the Application Express administration services is as follows:

http://<SERVERNAME>:<PORT>/ords/apex_admin

The structure of the link to the Application Express development interface is as follows:

http://<SERVERNAME>:<PORT>/ords

timing for: Phase 3 (Switch)

Elapsed: 00:03:52.35

timing for: Complete Installation

Elapsed: 00:18:47.84

PL/SQL procedure successfully completed.

Post-installation tasks

Check validity of installation

SQL> SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';

APEX schemas post installation

SQL> SET LINESIZE 500
SQL> COLUMN username FORMAT A25
SQL> SELECT * FROM ALL_USERS WHERE username LIKE '%APEX%' OR username LIKE '%FLOWS%';

USERNAME USER_ID CREATED COM O
------------------------- ---------- --------- --- -
APEX_PUBLIC_USER 107 20-APR-21 NO Y
APEX_INSTANCE_ADMIN_USER 110 20-APR-21 NO Y
APEX_200100 105 20-APR-21 NO Y
FLOWS_FILES 106 20-APR-21 NO Y

4 rows selected.

Unlock public user

SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK IDENTIFIED BY <passwd>;

Enable Network Services (change the value of principal_name according to your installation schema

BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*',
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'APEX_200200',
principal_type => xs_acl.ptype_db));
END;
/

Configure RESTful Services. In this step you will need to provide credentials for the users: APEX_LISTENER and APEX_REST_PUBLIC_USER

SQL> @apex_rest_config.sql

Check the DB parameter JOB_QUEUE_PROCESSES and set a value higher than 0

SQL> SHOW PARAMETER job_queue_processes
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;

Install ORDS (Oracle Rest Data Services)

Run the database privileges script

Connect to the Pluggable Database as SYS user and execute ORDS Installer Privileges Script to create an admin user. This script provides the assigned database user the privileges to install, upgrade, validate and uninstall ORDS in Oracle Pluggable Database or Oracle 11g.

cd $ORDS_SOFTWARELOC/installer
sqlplus sys/@PDB
SQL> @ords_installer_privileges.sql PDBADMIN

Install ORDS

export JAVA_HOME=<JAVA_HOME>
export PATH=$JAVA_HOME/bin:$PATH

cd $ORDS_SOFTWARELOC
java -jar ords.war setup advanced

The output will be similar to the one below:

This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data: <DIRECTORY_PATH>/ORDS/20.2/ords_<TNS_ALIAS>/myConfData
Specify the database connection type to use.
Enter number for [1] Basic [2] TNS [3] Custom URL [1]:
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:.vm.oracle.com
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:PDBADMIN
Enter the database password for PDBADMIN:
Confirm password:
Connecting to database user: PDBADMIN url: jdbc:oracle:thin:@//<SERVERNAME>:<PORT>/<TNS_ALIAS>.vm.oracle.com
Retrieving information.

Enter the default tablespace for ORDS_METADATA [SYSAUX]:
Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:
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:
Enter a number to select a feature to enable:
[1] SQL Developer Web (Enables all features)
[2] REST Enabled SQL
[3] Database API
[4] REST Enabled SQL and Database API
[5] None
Choose [1]:
<TIMESTAMP> INFO reloaded pools: []
Installing Oracle REST Data Services version 20.2.0.r1781804
... Log file written to /refresh/home/ords_install_core_.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 /refresh/home/ords_install_datamodel_.log
... Log file written to /refresh/home/ords_install_apex_.log
Completed installation for Oracle REST Data Services version 20.2.0.r1781804. Elapsed time: 00:00:37.646

Configure static files location

Copy the contents of APEX images directory from the DB server to the ORDS VM. All static files will be served by the ORDS directly from the VM. Once the images files are copied to the VM to <IMAGESLOC>, configure ORDS to use this location for static files

java -jar ords.war static <IMAGESLOC>

Output:

WAR Generation complete
WAR location : <ORDS_LOC>/i.war
Context path : /i
Static resources : <IMAGESLOC>
Ensure the static resources are available at path: <IMAGESLOC>
on the server where the WAR is deployed

Start ORDS standalone server

cd <ORDS_SOFTWARE_LOC>
java -jar ords.war standalone

Enter the APEX static resources location:<IMAGESLOC>

Enter 1 if using HTTP or 2 if using HTTPS [1]:

Enter the HTTP port [8080]:8202

<TIMESTAMP> INFO HTTP and HTTP/2 cleartext listening on host: localhost port: 8202

<TIMESTAMP> INFO Disabling document root because the specified folder does not exist: <ORDS_LOC>/myConfData/ords/standalone/doc_root

<TIMESTAMP> INFO Configuration properties for: |apex||

database.api.enabled=true

db.connectionType=basic

db.hostname=localhost

db.port=<LISTENER_PORT>

db.servicename=<TNS_ALIAS>.<DOMAIN>

feature.sdw=true

restEnabledSql.active=true

security.requestValidationFunction=wwv_flow_epg_include_modules.authorize

security.validationFunctionType=plsql

db.password=******

db.username=APEX_PUBLIC_USER

resource.templates.enabled=true

. . . . . .

The default port used by ORDS, 8080, is a common default port used by other software: the Oracle XDB HTTP server, Glassfish, Tomcat, etc. If port 8080 is in use, then ORDS will not start and report a port conflict. If this occurs, change the jetty.port entry in <ORDS_LOC>/standalone.properties, and restart ORDS

Create the document root directory on the VM

In order to run the ORDS server in background, use screen or nohup to run the standalone server command

This completes the ORDS setup for APEX. The apex admin console should be available at <VM>:<PORT>/ords/

If you want to configure ORDS web server using Apache Tomcat instead of running a standalone server, please use this guide

Use of Optional Cookies

How to install and configure Oracle APEX and ORDS server: Step-by-Step Guide