APEX:: 18.2 Setup on Windows with ORDS

I had a requirement to setup APEX 18.2 with ORDS on my local machine running on windows 10 OS. Here is the steps I followed to install and configure APEX 18.2.

Pre-Requisites: 
Oracle Database 11g/12c Installation

Environment:

Operating System: Windows 10
Database Version: Oracle Database 12c EE Release 12.2.0.1.0 - 64bit
APEX Version: 18.2
ORDS Version: 18.3.0.270.1456

Steps:

Step#1: Download Required Installation File

Download APEX installation file from 

https://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

File Name: apex_18.2.zip

Step#2: Extract Installation File

Extract apex_18.2.zip to a temporary folder. Once its extracted go to 'apex' folder.

cd C:\Soft\DB\TEMP
unzip apex_18.2.zip
cd apex

Step#3: Login to SQL Command Line

C:\Soft\DB\TEMP\apex>sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 27 12:52:07 2018

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Step#4: Create Tablespace for APEX

CREATE TABLESPACE apex DATAFILE SIZE 100M AUTOEXTEND ON NEXT 1M;

Note: If you want to specify data file path

CREATE TABLESPACE apex DATAFILE '/path/to/datafiles/apex01.dbf' SIZE 100M AUTOEXTEND ON NEXT 1M;

Step#5: Check, if the Oracle Application Express is already installed

SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

Step#6: Run APEX Installation Script

If APEX is not installed, run the "apexins.sql" script, specifying the relevant tablespace names and image URL.

-- @apexins.sql tablespace_apex tablespace_files tablespace_temp images

@apexins.sql APEX APEX TEMP /i/

Where:

APEX – name of the tablespace for the APEX user.

APEX – name of the tablespace for APEX files user.

TEMP – name of the temporary tablespace.

i – virtual directory for APEX images. Define the virtual image directory as /i/ for future updates.

Note: If you are upgrading, you've finished at this point. You only need to run subsequent steps for new installations. If you are using ORDS, it's a good idea to run an ORDS validate to make sure the APEX hasn't affected your ORDS installation.

The above script creates following schemas on Database, you can test by querying view DBA_USERS.

APEX_INSTANCE_ADMIN_USER

APEX_PUBLIC_USER

FLOWS_FILES

APEX_180200

Step#7: Verify APEX Version and Status

Check the VERSION and STATUS is installed

SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

Check APEX RELEASE VERSION:

SELECT * FROM apex_release;

Step#8: Change APEX Admin Password

Once complete, change the admin password by running the "apxchpwd.sql" scripts as the SYS user.

Note: Password must contain at least one punctuation character (!"#$%&()``*+,-/:;?_).

Web Listener Setup:

Option#1: Configuring Embedded PL/SQL Gateway

Step#9: Run the Embedded PL/SQL Gateway configuration (EPG)

This script will load the Application Express images into XDB,

@apex_epg_config.sql <parent of apex directory>

-- C:\Soft\DB\TEMP is home directory for apex installer, the base directory is C:\Soft\DB\TEMP\apex

-- do not include apex.

SQL> @apex_epg_config.sql C:\Soft\DB\TEMP

. Loading images directory: C:\Soft\DB\TEMP/apex/images
timing for: Load Images
Elapsed: 00:01:13.29

Step#10: Unlock Database Accounts

Make sure that the following accounts are unlocked:

ALTER USER anonymous ACCOUNT UNLOCK;
ALTER USER xdb ACCOUNT UNLOCK;
ALTER USER apex_public_user ACCOUNT UNLOCK;
ALTER USER flows_files ACCOUNT UNLOCK;

Step#11: APEX Database Parameters Configuration

Configure database parameters for APEX

Step#12: Enable XML DB Protocol Server

Check Whether Oracle XML DB Protocol Server enabled or not.

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

If the port number returns 0, the Oracle XML DB Protocol Server is disabled.

To enable Oracle XML DB Protocol Server:

EXEC DBMS_XDB.SETHTTPPORT(port);

--Change default port 8080, we will use for apache tomcat

EXEC DBMS_XDB.SETHTTPPORT(8181);

Note: You can also run @apxconf.sql to perform the final configuration steps for Oracle Application Express, including setting the XDB HTTP listener port and Application Express ADMIN password.

SQL> select dbms_xdb.gethttpport from dual;

GETHTTPPORT
———–
8181

Step#13: Check the listener status and HTTP port is enabled

Step#14: Administration Services Login Page

Login as a ADMIN user to http://localhost:8080/apex/apex_admin

Successfully logged into the Oracle APEX 18.2

Option#2: ORDS (Oracle REST Data Services) Listener:

Step#15: Create Required Users

Create the APEX_LISTENER and APEX_REST_PUBLIC_USER users by running the "apex_rest_config.sql" script.

SQL> @apex_rest_config.sql

Enter a password for the APEX_LISTENER user              []
Enter a password for the APEX_REST_PUBLIC_USER user              []
...set_appun.sql
...create APEX_LISTENER and APEX_REST_PUBLIC_USER users

PL/SQL procedure successfully completed.

Step#16: Disable the Oracle XML DB Protocol Server

sqlplus /nolog
CONNECT SYS as SYSDBA
EXEC DBMS_XDB.SETHTTPPORT(0);

Step#17: Unlock ORDS Database Accounts

Check the SYS user and common public users are unlocked and you know their passwords. Remember to lock the SYS user when the installation is complete.

CONN / AS SYSDBA
ALTER USER APEX_LISTENER IDENTIFIED BY password ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY password ACCOUNT UNLOCK;
ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY password ACCOUNT UNLOCK;

Step#18: Download ORDS Installation File

Download Oracle REST Data Services from 

https://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html

File Name: ords-18.3.0.270.1456.zip

Step#19: Extract Installation File and Create Config Folder

Extract ords-18.3.0.270.1456.zip to ords directory

cd C:\Soft\DB\TEMP
unzip -d ords ords-18.3.0.270.1456.zip
cd ords
mkdir config

Step#20: Configure Oracle REST Data Services (ORDS)

Run following command to configure ORDS and start in Standalone mode.

java -jar ords.war

C:\Soft\DB\TEMP\ords>java -jar ords.war
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data:C:\Soft\DB\TEMP\ords\config
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:1522
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
Enter the database SID [xe]:ORCL
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:

Requires SYS AS SYSDBA to verify Oracle REST Data Services schema.
Enter the database password for SYS AS SYSDBA:
Confirm password:

Retrieving information.

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]:1
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]:1

Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:

Nov 27, 2018 6:27:17 PM
INFO: reloaded pools: []
Installing Oracle REST Data Services version 18.3.0.r2701456
... Log file written to C:\Users\lkakarla\ords_install_core_2018-11-27_182717_00867.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to C:\Users\lkakarla\ords_install_datamodel_2018-11-27_182729_00527.log
... Log file written to C:\Users\lkakarla\ords_install_apex_2018-11-27_182732_00665.log
Completed installation for Oracle REST Data Services version 18.3.0.r2701456. Elapsed time: 00:00:19.656

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1
Enter the APEX static resources location:C:\Soft\DB\TEMP\apex\images
Enter 1 if using HTTP or 2 if using HTTPS [1]:1
Nov 27, 2018 6:30:56 PM
INFO: HTTP and HTTP/2 cleartext listening on port: 8080
INFO: Oracle REST Data Services initialized
Oracle REST Data Services version : 18.3.0.r2701456
Oracle REST Data Services server info: jetty/9.4.z-SNAPSHOT

2018-11-27 18:30:57.547:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@291ae{/ords,null,AVAILABLE}
2018-11-27 18:30:57.553:INFO:oejsh.ContextHandler:main: Started o.e.j.s.h.ContextHandler@61df66b6{/i,null,AVAILABLE}
2018-11-27 18:30:57.576:INFO:oejs.AbstractConnector:main: Started ServerConnector@28701274{HTTP/1.1,[http/1.1, h2c]}{0.0.0.0:8080}

Note: Standalone Mode is a way to run ORDS without needing an application server like Oracle WebLogic or Apache Tomcat. It’s a quick and handy way to get ORDS up and running using Jetty server, but it’s not supported for use in production, you’ll need to deploy ORDS to a supported Application Server when it comes time to go production.

Step#20: Test ORDS using Jetty

Access APEX environment using URL http://localhost:8080/ords

If it’s working fine, enter Ctrl + C to kill the process.

2018-11-27 18:32:10.991:INFO:oejs.AbstractConnector:Thread-4: Stopped ServerConnector@28701274{HTTP/1.1,[http/1.1, h2c]}{0.0.0.0:8080}
2018-11-27 18:32:10.992:INFO:oejs.session:Thread-4: node0 Stopped scavenging
2018-11-27 18:32:10.993:INFO:oejsh.ContextHandler:Thread-4: Stopped o.e.j.s.h.ContextHandler@61df66b6{/i,null,UNAVAILABLE}
2018-11-27 18:32:11.005:INFO:oejsh.ContextHandler:Thread-4: Stopped o.e.j.s.ServletContextHandler@291ae{/ords,null,UNAVAILABLE}

Step#21: Validate ORDS Installation

You can validate/fix the current ORDS installation using the validate option.

java -jar ords.war validate

-- In case you have error when accessing http://localhost:8080/ords, try to remove ords instalation, and install again

java -jar ords.war uninstall

C:\Soft\DB\TEMP\ords>java -jar ords.war validate
Enter the name of the database server [localhost]:
Enter the database listen port [1522]:
Enter the database SID [ORCL]:
Requires SYS AS SYSDBA to verify Oracle REST Data Services schema.

Enter the database password for SYS AS SYSDBA:
Confirm password:
Passwords do not match, try again
Enter the database password for SYS AS SYSDBA:
Confirm password:

Retrieving information.

Oracle REST Data Services will be validated.
Validating Oracle REST Data Services schema version 18.3.0.r2701456
... Log file written to C:\Users\lkakarla\ords_validate_core_2018-11-27_183320_00811.log
Completed validating Oracle REST Data Services version 18.3.0.r2701456.  Elapsed time: 00:00:04.485

Step#22: Install Tomcat 

Download Apache Tomcat from https://tomcat.apache.org/download-90.cgi

Extract apache-tomcat-9.0.13-windows-x64.zip to a folder.

Modify Windows Environment Variables to add JAVA_HOME and PATH via command line

setx JAVA_HOME "C:\Soft\JDK\jdk1.8.0_171"

Note: When you change an environment variable by using the Windows setx command, you must close and reopen the Command Prompt window for the change to take effect.

echo %JAVA_HOME%

Step#23: Tomcat Deployment

Create new directory named ‘i’ under Tomcat "webapps" directory.

C:\Soft\tomcat9\webapps>mkdir i

Copy the APEX images to ‘i’ directory the Tomcat "webapps" directory.

C:\Soft\tomcat9\webapps>xcopy C:\Soft\DB\TEMP\apex\images C:\Soft\tomcat9\webapps\i /s /e /h

Move the ords.war file into the webapps folder where Apache Tomcat is installed.

Note: Apache Tomcat determines the context root from the file name of a WAR archive. If you need to keep backward compatibility, so that URLs are of the form http://server/apex/... rather than http://server/ords/..., then you must rename ords.war to apex.war before moving it into to the webapps folder.

C:\Soft\tomcat9\webapps>copy C:\Soft\DB\TEMP\ords\ords.war C:\Soft\tomcat9\webapps\

Step#24: Test ORDS using Tomcat

Go to bin folder under Tomcat setup and start command line, and enter startup to start tomcat server.

$CATALINA_HOME/bin/startup.bat

ORDS can be accessible using URL http://localhost:8080/ords/

Enter shutdown on command line to shutdown tomcat server.

$CATALINA_HOME/bin/shtudown.bat

Additional Notes About Users:

APEX_180200:
The account that owns the Oracle Application Express schema and metadata.

FLOWS_FILES:
The account that owns the Oracle Application Express uploaded files.

APEX_PUBLIC_USER:
The minimally privileged account is used for Oracle Application Express configuration with Oracle REST Data Services or Oracle HTTP Server and mod_plsql. If Oracle REST Data Services are configured with Oracle Application Express, this is the database user used when invoking PL/SQL Gateway operations, for example, all Oracle Application Express operations.

APEX_REST_PUBLIC_USER:
The account used when invoking RESTful Services definitions stored in Oracle Application Express. It's created when RESTful Web services are configured.

APEX_LISTENER:
The account used to query RESTful Services definitions stored in Oracle Application Express. It's created when RESTful Web services are configured.

APEX_INSTANCE_ADMIN_USER:
APEX_INSTANCE_ADMIN_USER was added in 5.1 to To provide the REST administration interface. This schema is low-privileged. It only has the CREATE SESSION system privilege. The service modules for the REST administration interface are being stored in the APEX_INSTANCE_ADMIN_USER schema. Oracle does not require or recommend direct log ins into APEX_INSTANCE_PUBLIC_USER.

ORDS_PUBLIC_USER:
ORDS_PUBLIC_USER is the database user that’s used to create ORDS connection pool(s) and invoke RESTful Services in the Oracle REST Data Services-enabled schemas. The ORDS_PUBLIC_USER user is created during the first run of the ORDS installer. When RESTful Services published under ABC user on database XYZ, then ORDS resolves the call to the proper jdbc connection pool, then proxy connects from ORDS_PUBLIC_USER to user ABC, and runs the SQL or PL/SQL.