Part 3: Configure APEX with Oracle Rest Data Service (ORDS) in the new EC2 Container

In my previous blog posts, I have described how to create the customer Oracle Database in the RDS with APEX Component:

In this post, I will explain the configuration of Middleware components needed to present apex content in web. We will build the separate VM (EC2 Container) in AWS and configure following components: ORDS, Apache Tomcat und Nginx Server.

Installing and Configuring the VM for the APEX Listener

Create EC2 Container for the APEX:

  • AMI: Amazon Linux 2 AMI (HVM), SSD Volume Type – ami-09def150731bdbcc2
  • Shape: t2.micro

 

I downloaded the SSH Key Pair in the directory on my laptop: /drives/d/Documents/Job/OC/KnowHow/Cloud/AWS/Netzwerke

Connect to the already created ec2 container:

I used the SSH Client MobaXterm. Navigate to the directory with saved private SSH key and connect to the VM in AWS:

[my laptop] cd /drives/d/Documents/Job/OC/KnowHow/Cloud/AWS/Netzwerke

[my laptop] ssh -i ./bnekeypair.pem ec2-user@<ec2-container>

Change to root and start the OS update:

[ec2-user@<ec2-container>]$ sudo -i

[root@<ec2-container>]# yum update

Create a nonprivileged OS user to own the APEX Listener installation. The following command creates a new user named apexuser.

[root@<ec2-container>]# useradd -d /home/apexuser apexuser

[root@<ec2-container>]# passwd apexuser <password>

Configure and check the connectivity from the EC2 Container to the Database

I used the new Tool SQLCLI for the connect to the database. The tool need the Java runtime.

Install Open JDK:

[root@<ec2-container>]# yum install java

Check:

[root@<ec2-container>]# java -version

Output:

openjdk version „1.8.0_201“

OpenJDK Runtime Environment (build 1.8.0_201-b09)

OpenJDK 64-Bit Server VM (build 25.201-b09, mixed mode)

Then I installed the Tool Oracle SQL Developer Command Line (SQLcl): is a free command line interface for Oracle Database. I copied the SQLcl installation zip file on my Laptop and copied it to the EC2 Container

[my laptop] scp -i ./bnekeypair.pem sqlcl-19.1.0.094.1619.zip ec2-user@<ec2-container>:/home/ec2-user

And installed the Tool under apexuser:

[root@<ec2-container>]# cp /home/ec2-user/sqlcl-19.1.0.094.1619.zip /home/apexuser/

[root@<ec2-container>]# chmod 777 /home/apexuser/sqlcl-19.1.0.094.1619.zip

[root@<ec2-container>]# su – apexuser

[apexuser@<ec2-container>]# unzip sqlcl-19.1.0.094.1619.zip

[apexuser@<ec2-container>]$ export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.201.b09-0.amzn2.x86_64/jre

[apexuser@<ec2-container>]$ alias sql=“/home/apexuser/sqlcl/bin/sql“

[apexuser@<ec2-container>]$ export PATH=$JAVA_HOME/bin:$PATH

Check the connection to the database:

[apexuser@<ec2-container>]$ sql /nolog

SQL> conn bne/“<password“@//bnedborcl.cevqhlcfstlv.eu-central-1.rds.amazonaws.com:1521/orcl

Connected.

Add to /home/apexuser/.bash_profile following lines:

export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.201.b09-0.amzn2.x86_64/jre

export PATH=$JAVA_HOME/bin:$PATH

alias sql=“/home/apexuser/sqlcl/bin/sql“

export TNS_ADMIN=/home/apexuser/sqlcl

Create tnsnames.ora in Dir. /home/apexuser/sqlcl:

myservice=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bnedborcl.xxxxxxx.eu-central-1.rds.amazonaws.com) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

Connect to the database via TNS Names:

[apexuser@xxxxx sqlcl]$ sql bne@myservice

Connected to:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 – 64bit Production

SQL>

It works!!!

Configuring RESTful Services for Oracle APEX

To configure RESTful services in APEX, use SQL*Plus to connect to your DB instance as the master user, and then run the rdsadmin.rdsadmin_run_apex_rest_config stored procedure. When you run the stored procedure, you provide passwords for the following users:

  • APEX_LISTENER
  • APEX_REST_PUBLIC_USER

Execute in the SQLPlus:

  • APEX_LISTENER
  • APEX_REST_PUBLIC_USER

Execute in the SQLPlus:

SQL> exec rdsadmin.rdsadmin_run_apex_rest_config(‚apex_listener_password‘,’apex_rest_public_user_password‘);

Unlock APEX User:

SQL> alter user APEX_PUBLIC_USER identified by <set the password>;
SQL> alter user APEX_PUBLIC_USER account unlock;

Download and Install APEX

Download APEX from http://download.oracle.com/otn/java/appexpress/apex_19.1.zip

Copy APEX from the local PC to the EC2-Container:

[my laptop] cd /drives/d/Documents/Job/OC/KnowHow/Cloud/AWS/Netzwerke

[my laptop] scp -i ./bnekeypair.pem apex_18.1.zip ec2-user@<ec2-container>:/home/ec2-user

Access the EC2 Container:

[my laptop] ssh -i ./bnekeypair.pem ec2-user@<ec2-container>

Change to root and install APEX in the EC2 Container:

[ec2-user@<ec2-container>]$ sudo -i

[root@<ec2-container>]# cp /home/ec2-user/apex_18.1.zip /home/apexuser/

[root@<ec2-container>]# chmod 777 /home/apexuser/apex_18.1_en.zip

[root@<ec2-container>]# su – apexuser

[apexuser@<ec2-container>]$ cd /home/apexuser

[apexuser@<ec2-container>]$ unzip apex_18.1.zip

APEX is in the EC2 Container now installed

Download and install ORDS

Now download the Oracle REST Data Services: http://download.oracle.com/otn/java/ords/ords-18.4.0.354.1002.zip

And copy to ec2 /home/apexuser:

[my laptop] cd /drives/d/Documents/Job/OC/KnowHow/Cloud/AWS/Netzwerke

[my laptop] scp -i ./bnekeypair.pem ords-18.4.0.354.1002.zip ec2-user@<ec2-container>:/home/ec2-user

Connect to the EC2 Container and install the ORDS:

[my laptop] ssh -i ./bnekeypair.pem ec2-user@<ec2-container>:/home/ec2-user

In the EC2 Container change to OS user root and install ORDS:

[ec2-user@<ec2-container>]$ sudo -i

[root@<ec2-container>]# cp /home/ec2-user/ords-18.4.0.354.1002.zip /home/apexuser/

[root@<ec2-container>]# chmod 777 /home/apexuser/ords-18.4.0.354.1002.zip

[root@<ec2-container>]# su – apexuser

apexuser@<ec2-container>]$ mkdir /home/apexuser/ORDS

[apexuser@<ec2-container>]$ cd /home/apexuser/ORDS

[apexuser@<ec2-container>]$ unzip ../ords-18.4.0.354.1002.zip

Setup ORDS as apexuser

Before you start the ORDS configuration get following data:

  • Database Hostname: Database Endpoint: refer to Chapter „Test the DB Connection“ in the <Link1>
  • Database SID and Listener Port: refer to Chapter „Test the DB Connection“ in Part 2: Create the Oracle APEX Database in Amazon Relation Data Service (RDS)
  • Password for the Database User APEX_PUBLIC_USER: refer to Chapter „Configuring RESTful Services for Oracle APEX“ in this Blogpost
  • Password for the Database User APEX_LISTENER: refer to Chapter „Configuring RESTful Services for Oracle APEX“ in this Blogpost
  • Password for the Database APEX_REST_LISTENER: refer to Chapter „Configuring RESTful Services for Oracle APEX“ in this Blogpost

[apexuser@<ec2-container>]$ cd /home/apexuser/ORDS

[apexuser@<ec2-container>]$ java -jar ords.war setup

Enter the location to store configuration data:/home/apexuser/ORDS

Enter the name of the database server [localhost]: <Database Endpoint Hostname>

Enter the database listen port [1521]: <RETURN>

Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2

Enter the database SID [xe]: orcl

Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:2

Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step: 2

If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]: <RETURN>

Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]: <RETURN>

Enter the database password for APEX_PUBLIC_USER: <Password for the APEX_PUBLIC_USER>

Confirm password: <Password for the APEX_PUBLIC_USER>

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: <Password for the APEX_LISTENER>

Confirm password: <Password for the APEX_LISTENER>

Enter the database password for APEX_REST_PUBLIC_USER: <Password for the APEX_REST_LISTENER>

Confirm password: <Password for the APEX_REST_LISTENER>

Apr 30, 2019 4:30:44 PM

INFO: reloaded pools: []

Set a password for the APEX admin user (for the APEX GUI)

[ec2-user@<ec2-container>]$ sudo -i

[root@<ec2-container>]# su – apexuser

[apexuser@<ec2-container>]$ sql bne/'<DB Password>’@myservice

SQL> @/home/apexuser/apex/apxchpwd.sql

Output:

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

This script can be used to change the password of an Application Express

instance administrator. If the user does not yet exist, a user record will be created.

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

Enter the administrator’s username [ADMIN] <RETURN> (User for the APEX GUI)

Enter ADMIN’s password [] (Define password for the APEX GUI User ADMIN)

Enter value for EMAIL: <RETURN>

Changed password of instance administrator ADMIN.

Start ORDS (as apexuser)

Bei the first start of the ORDS some settings will be set:

[apexuser@<ec2-container>]$ cd /home/apexuser/ORDS

[apexuser@@<ec2-container>S]$ java -jar ords.war

Apr 30, 2019 4:35:30 PM oracle.dbtools.rt.config.setup.MigrateV2 migrate

INFO: Migrating Oracle REST Data Services configuration files from 2.0.x to current version.

Migrate Database Configuration apex with connection host: bnedborcl.xxxxxxxxx.eu-central-1.rds.amazonaws.com port: 1521 sid: orcl

Apr 30, 2019 4:35:30 PM

INFO: reloaded pools: [|apex||, |apex|al|, |apex|rt|]

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1

Enter the APEX static resources location:/home/apexuser/apex/images

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

2019-04-30 16:37:40.454:INFO::main: Logging initialized @133213ms to org.eclipse.jetty.util.log.StdErrLog

Apr 30, 2019 4:37:40 PM

INFO: HTTP and HTTP/2 cleartext listening on port: 8080

Apr 30, 2019 4:37:40 PM

INFO: Disabling document root because the specified folder does not exist: /home/apexuser/ORDS/ords/standalone/doc_root

2019-04-30 16:37:42.116:INFO:oejs.Server:main: jetty-9.4.z-SNAPSHOT; built: 2018-05-03T15:56:21.710Z; git: daa59876e6f384329b122929e70a80934569428c; jvm 1.8.0_201-b09

2019-04-30 16:37:42.180:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0

2019-04-30 16:37:42.181:INFO:oejs.session:main: No SessionScavenger set, using defaults

2019-04-30 16:37:42.182:INFO:oejs.session:main: node0 Scavenging every 600000ms

Apr 30, 2019 4:37:44 PM

WARNING: The pool named: |apex|| is invalid and will be ignored: The username or password for the connection pool named |apex||, are invalid, expired, or the account is locked

Apr 30, 2019 4:37:45 PM

WARNING: The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named |apex|al|, are invalid, expired, or the account is locked

Apr 30, 2019 4:37:46 PM

WARNING: The pool named: |apex|rt| is invalid and will be ignored: The username or password for the connection pool named |apex|rt|, are invalid, expired, or the account is locked

Apr 30, 2019 4:37:46 PM

INFO: Oracle REST Data Services initialized

Oracle REST Data Services version : 18.4.0.r3541002

Oracle REST Data Services server info: jetty/9.4.z-SNAPSHOT

2019-04-30 16:37:46.617:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@52a86356{/ords,null,AVAILABLE}

2019-04-30 16:37:46.618:INFO:oejsh.ContextHandler:main: Started o.e.j.s.h.ContextHandler@5ce81285{/i,null,AVAILABLE}

2019-04-30 16:37:46.638:INFO:oejs.AbstractConnector:main: Started ServerConnector@a0e705e{HTTP/1.1,[http/1.1, h2c]}{0.0.0.0:8080}

2019-04-30 16:37:46.638:INFO:oejs.Server:main: Started @139397ms

I received the following warnings:

WARNING: The pool named: |apex|| is invalid and will be ignored: The username or password for the connection pool named |apex||, are invalid, expired, or the account is locked

WARNING: The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named |apex|al|, are invalid, expired, or the account is locked

WARNING: The pool named: |apex|rt| is invalid and will be ignored: The username or password for the connection pool named |apex|rt|, are invalid, expired, or the account is locked

To solve the problem, I performed the following step:

I have unlocked users and reset the password:

SQL> alter user ORDS_PUBLIC_USER account unlock;

SQL> alter user APEX_PUBLIC_USER account unlock;

SQL> alter user APEX_LISTENER account unlock;

SQL> alter user ORDS_PUBLIC_USER identified by <db passwort>;

SQL> alter user APEX_PUBLIC_USER identified by <db passwort>;

SQL> alter user APEX_LISTENER identified by <db passwort>;

Then I added the database passwords into the ORDS configuration files:

vi /home/apexuser/ORDS/ords/conf/apex_rt.xml

<?xml version=“1.0″ encoding=“UTF-8″ standalone=“no“?>

<!DOCTYPE properties SYSTEM „http://java.sun.com/dtd/properties.dtd“>

<properties>

<comment>Saved on Thu May 02 07:36:22 UTC 2019</comment>

<entry key=“db.password“>“<Password for the APEX_REST_PUBLIC_USER>„</entry>

<entry key=“db.username“>APEX_REST_PUBLIC_USER</entry>

</properties>

vi /home/apexuser/ORDS/ords/conf/apex.xml

<?xml version=“1.0″ encoding=“UTF-8″ standalone=“no“?>

<!DOCTYPE properties SYSTEM „http://java.sun.com/dtd/properties.dtd“>

<properties>

<comment>Saved on Thu May 02 09:21:57 UTC 2019</comment>

<entry key=“db.password“>“<Password for the APEX_PUBLIC_USER>„</entry>

<entry key=“db.username“>APEX_PUBLIC_USER</entry>

</properties>

vi /home/apexuser/ORDS/ords/conf/apex_al.xml

<?xml version=“1.0″ encoding=“UTF-8″ standalone=“no“?>

<!DOCTYPE properties SYSTEM „http://java.sun.com/dtd/properties.dtd“>

<properties>

<comment>Saved on Thu May 02 09:21:57 UTC 2019</comment>

<entry key=“db.password“>“<Password for the APEX_LISTENER>„</entry>

<entry key=“db.username“>APEX_LISTENER</entry>

</properties>

Then restart the ORDS:

[apexuser@<ec2-container>]$ java -jar ords.war

Add new Rule to the Securty Group

We must create the new Rule to allow the Access to APEX via the Port 8080:

Now I was able to connect to my APEX Application:

http://xxxxxxxxxxxxx.eu-central-1.compute.amazonaws.com:8080/ords/

Previously Blog posts in this series:

Next Blog post in this series:

Kommentar verfassen

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.

%d Bloggern gefällt das: