Sunday, April 23, 2023

Part 4: Inventory application setup (low code) using oracle apex

Objective: Inventory application setup (low code) using Oracle Apex.

In the previous blog we saw how to setup the PDB, APEX & ORDS for an inventory application.

In this blog, we will see how to configure the ORDS.


Reference url: https://docs.oracle.com/en/database/oracle/apex/22.2/htmig/configuring-ords.html#GUID-D688F4D6-FCAB-4C4D-BA6D-9A3506B3E05F

Prereqs: Ensure the listener is up, DB is open and PDB is in RW mode.

SQL> select name,open_mode from v$pdbs;
NAME                 OPEN_MODE
-------------------- ----------
INVENTORYPDB         READ WRITE  <<< our practise pdb
SQL>

Step 1) Copy the apex/image file directory to the ORDS setup

[oracle@vcentos79-oracle-sa1 ORDSBIN]$ cp -pr /u01/app/oracle/APEXBIN/apex/images/ .
[oracle@vcentos79-oracle-sa1 ORDSBIN]$ pwd
/u01/app/oracle/ORDSBIN
[oracle@vcentos79-oracle-sa1 ORDSBIN]$
[oracle@vcentos79-oracle-sa1 ORDSBIN]$ ls -altr
total 187048
drwxr-xr-x. 30 oracle oinstall    28672 Nov 11 19:38 images <<< images directory is copied

Step 2) Validate the ORDS install (recommended for versions 21.2.1 and lesser, but we will try as well - ours is 23).

java -jar ords.war validate --database INVENTORYPDB

[oracle@vcentos79-oracle-sa1 ORDSBIN]$ java -jar ords.war validate --database INVENTORYPDB
Warning: Support for executing: java -jar ords.war has been deprecated.
...

So no success. This probably is the reason, why oracle said it is allowed only for versions 21.2.1 or lesser; where it is still supported.

Step 3) Configure Static File Support

Connect to the PDB as sysdba and let us run the below command to allow developer's to add any static file to the apex application later. Oracle asks this to be done for every new installs of the APEX.

apex_rest_config.sql

File presence:

[oracle@vcentos79-oracle-sa1 apex]$ ls -altr apex_rest_config.sql
-rw-r--r--. 1 oracle oinstall 5054 Jan 19  2018 apex_rest_config.sql
[oracle@vcentos79-oracle-sa1 apex]$ pwd
/u01/app/oracle/APEXBIN/apex
[oracle@vcentos79-oracle-sa1 apex]$

Outcome below:
-------------------------
SQL> show user;
USER is "SYS"
SQL> show con_name;
CON_NAME
------------------------------
INVENTORYPDB
SQL> @apex_rest_config.sql
PL/SQL procedure successfully completed.

<few empty lines>

PL/SQL procedure successfully completed.

<few empty lines>

Enter a password for the APEX_LISTENER user              []
Enter a password for the APEX_REST_PUBLIC_USER user              []
...set_appun.sql
...setting session environment
PL/SQL procedure successfully completed.

Procedure created.
...
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@vcentos79-oracle-sa1 apex]$
-------------------------

What changes it brought to DB @ high level:

SQL> select username,account_status,created,default_tablespace from dba_users where trunc(created) > trunc(sysdate-2) order by created,username;
USERNAME                       ACCOUNT_STATUS  CREATED   DEFAULT_TABLESPACE
------------------------------ --------------- --------- ------------------------------
APEX_220200                    LOCKED          22-APR-23 TBSPC_APEX_APEXUSR
APEX_PUBLIC_USER               OPEN            22-APR-23 TBSPC_APEX_APEXUSR
FLOWS_FILES                    LOCKED          22-APR-23 TBSPC_APEX_FILEUSR
ORDS_PUBLIC_USER               OPEN            22-APR-23 TBSPC_ORDS_PUBLICUSR
ORDS_METADATA                  OPEN            22-APR-23 TBSPC_ORDS_METADATA
APEX_LISTENER                  OPEN            23-APR-23 TBSPC_APEX_APEXUSR << new user created
APEX_REST_PUBLIC_USER          OPEN            23-APR-23 TBSPC_APEX_APEXUSR << new user created
7 rows selected.
SQL>
SQL> select comp_id,comp_name,version,status,modified from dba_Registry order by comp_name;
COMP_ID COMP_NAME                                VERSION    STATUS     MODIFIED
------- ---------------------------------------- ---------- ---------- --------------------
JAVAVM  JServer JAVA Virtual Machine             19.0.0.0.0 VALID      06-APR-2023 19:59:27
APS     OLAP Analytic Workspace                  19.0.0.0.0 VALID      06-APR-2023 19:59:27
APEX    Oracle APEX                              22.2.0     VALID      22-APR-2023 17:09:25 <<< the APEX version, but no change to its last modified timestamp, withour last script.
CATALOG Oracle Database Catalog Views            19.0.0.0.0 VALID      06-APR-2023 19:59:23
CATJAVA Oracle Database Java Packages            19.0.0.0.0 VALID      06-APR-2023 19:59:27
CATPROC Oracle Database Packages and Types       19.0.0.0.0 VALID      06-APR-2023 19:59:23
DV      Oracle Database Vault                    19.0.0.0.0 VALID      06-APR-2023 19:59:28
OLS     Oracle Label Security                    19.0.0.0.0 VALID      06-APR-2023 19:59:28
ORDIM   Oracle Multimedia                        19.0.0.0.0 VALID      06-APR-2023 19:59:27
XOQ     Oracle OLAP API                          19.0.0.0.0 VALID      06-APR-2023 19:59:28
RAC     Oracle Real Application Clusters         19.0.0.0.0 OPTION OFF 17-APR-2019 02:03:52
CONTEXT Oracle Text                              19.0.0.0.0 VALID      06-APR-2023 19:59:27
OWM     Oracle Workspace Manager                 19.0.0.0.0 VALID      06-APR-2023 19:59:26
XML     Oracle XDK                               19.0.0.0.0 VALID      06-APR-2023 19:59:27
XDB     Oracle XML Database                      19.0.0.0.0 VALID      06-APR-2023 19:59:24
SDO     Spatial                                  19.0.0.0.0 VALID      06-APR-2023 19:59:28
16 rows selected.
SQL>

Step 4) Securing Oracle Rest Data Services by setting necessary authorization..

Oracle says "Set parameter security.requestValidationFunction to wwv_flow_epg_include_modules.authorize activates the white list of callable procedures which ships with Oracle APEX and prohibits calls to other procedures."

I found this attribute under the config location: /etc/ords/config/databases/default [where /etc/ords/config, is our config file location for ords]

<entry key="security.requestValidationFunction">ords_util.authorize_plsql_gateway</entry>

We will use the ords utility to set this property
Query ORDS to confirm if the value we picked was right, it seems its right.
We can use both get & info routine to verify the values.


[oracle@vcentos79-oracle-sa1 ORDSBIN]$ ords --config /etc/ords/config config get security.requestValidationFunction
ORDS: Release 23.1 Production on Sun Apr 23 09:11:01 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
  /etc/ords/config/
ords_util.authorize_plsql_gateway
[oracle@vcentos79-oracle-sa1 ORDSBIN]$

[oracle@vcentos79-oracle-sa1 ORDSBIN]$ ords --config /etc/ords/config config info security.requestValidationFunction
ORDS: Release 23.1 Production on Sun Apr 23 09:12:34 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
  /etc/ords/config/

Setting information from configuration /etc/ords/config/databases/default
    Setting: security.requestValidationFunction
Description: Specifies a validation function to determine if the requested procedure in the URL should be allowed or disallowed for processing. The function should return true if the procedure is allowed; otherwise, return false.
      Value: ords_util.authorize_plsql_gateway
    Default: None
  Sensitive: No
     Global: No
[oracle@vcentos79-oracle-sa1 ORDSBIN]$

Overall setting:
[oracle@vcentos79-oracle-sa1 ORDSBIN]$ ords --config /etc/ords/config config list
ORDS: Release 23.1 Production on Sun Apr 23 09:13:44 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
  /etc/ords/config/
Database pool: default
Setting                              Value                               Source
----------------------------------   ---------------------------------   -----------
database.api.enabled                 true                                Global
db.connectionType                    basic                               Pool
db.hostname                          vcentos79-oracle-sa1                Pool
db.password                          ******                              Pool Wallet
db.port                              1521                                Pool
db.servicename                       inventorypdb                        Pool
db.username                          ORDS_PUBLIC_USER                    Pool
feature.sdw                          true                                Pool
plsql.gateway.mode                   proxied                             Pool
restEnabledSql.active                true                                Pool
security.requestValidationFunction   ords_util.authorize_plsql_gateway   Pool
[oracle@vcentos79-oracle-sa1 ORDSBIN]$

Now let us move forward to set the property...
Backup the /etc/ords/config folder xml files.

ords --config /etc/ords/config config set security.requestValidationFunction "wwv_flow_epg_include_modules.authorize";

Output:

[oracle@vcentos79-oracle-sa1 ORDSBIN]$ ords --config /etc/ords/config config set security.requestValidationFunction "wwv_flow_epg_include_modules.authorize";
ORDS: Release 23.1 Production on Sun Apr 23 09:16:15 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
  /etc/ords/config/
The setting named: security.requestValidationFunction was set to: wwv_flow_epg_include_modules.authorize in configuration: default

Verify:

[oracle@vcentos79-oracle-sa1 ORDSBIN]$ ords --config /etc/ords/config config list
ORDS: Release 23.1 Production on Sun Apr 23 09:16:30 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
  /etc/ords/config/
Database pool: default
Setting                              Value                                    Source
----------------------------------   --------------------------------------   -----------
database.api.enabled                 true                                     Global
db.connectionType                    basic                                    Pool
db.hostname                          vcentos79-oracle-sa1                     Pool
db.password                          ******                                   Pool Wallet
db.port                              1521                                     Pool
db.servicename                       inventorypdb                             Pool
db.username                          ORDS_PUBLIC_USER                         Pool
feature.sdw                          true                                     Pool
plsql.gateway.mode                   proxied                                  Pool
restEnabledSql.active                true                                     Pool
security.requestValidationFunction   wwv_flow_epg_include_modules.authorize   Pool
[oracle@vcentos79-oracle-sa1 ORDSBIN]$

In the actual file:

[oracle@vcentos79-oracle-sa1 default]$ diff pool.xml pool.xml_bkp_23Apr23
4c4
< <comment>Saved on Sun Apr 23 09:16:17 UTC 2023</comment>
---
> <comment>Saved on Sat Apr 22 19:17:55 UTC 2023</comment>
13c13
< <entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>
---
> <entry key="security.requestValidationFunction">ords_util.authorize_plsql_gateway</entry>
[oracle@vcentos79-oracle-sa1 default]$ pwd
/etc/ords/config/databases/default
[oracle@vcentos79-oracle-sa1 default]$

So allset with configuration.

Step 5) Let us run ords in standalone mode, remember when we finished installing the ords, it suggested to use the below command to start the ords in standalone mode

Log Snippet:

2023-04-22T19:18:39.311Z INFO        To run in standalone mode, use the ords serve command:
2023-04-22T19:18:39.311Z INFO        ords --config /etc/ords/config serve
2023-04-22T19:18:39.311Z INFO        Visit the ORDS Documentation to access tutorials, developer guides and more to help you get started with the new ORDS Command Line Interface (http://oracle.com/rest).

Reference: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.1/ordig/deploying-and-monitoring-oracle-rest-data-services.html#GUID-8B65CC69-1D98-4F26-B0A7-389A1332A129

Command Template: ords --config <CONFIG_PATH> serve

Actual command: ords --config /etc/ords/config serve

Output:

[oracle@vcentos79-oracle-sa1 ~]$ ords --config /etc/ords/config serve
ORDS: Release 23.1 Production on Sun Apr 23 09:52:50 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
  /etc/ords/config/
2023-04-23T09:52:51.402Z INFO        HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080
2023-04-23T09:52:51.533Z INFO        Disabling document root because the specified folder does not exist: /etc/ords/config/global/doc_root
2023-04-23T09:53:08.920Z INFO        Configuration properties for: |default|lo|
db.servicename=inventorypdb
awt.toolkit=sun.awt.X11.XToolkit
java.specification.version=11
conf.use.wallet=true
sun.cpu.isalist=
sun.jnu.encoding=UTF-8
user.region=US
java.class.path=/u01/app/oracle/ORDSBIN/ords.war
java.vm.vendor=Oracle Corporation
sun.arch.data.model=64
nashorn.args=--no-deprecation-warning
java.vendor.url=https://openjdk.java.net/
resource.templates.enabled=false
user.timezone=UTC
db.port=1521
java.vm.specification.version=11
os.name=Linux
sun.java.launcher=SUN_STANDARD
user.country=US
sun.boot.library.path=/u01/app/oracle/JAVABIN/jdk-11.0.18/lib
sun.java.command=/u01/app/oracle/ORDSBIN/ords.war --config /etc/ords/config serve
jdk.debug=release
sun.cpu.endian=little
user.home=/home/oracle
oracle.dbtools.launcher.executable.jar.path=/u01/app/oracle/ORDSBIN/ords.war
user.language=en
java.specification.vendor=Oracle Corporation
java.version.date=2023-01-17
database.api.enabled=true
java.home=/u01/app/oracle/JAVABIN/jdk-11.0.18
db.username=ORDS_PUBLIC_USER
file.separator=/
java.vm.compressedOopsMode=32-bit
line.separator=
restEnabledSql.active=true
java.specification.name=Java Platform API Specification
java.vm.specification.vendor=Oracle Corporation
java.awt.graphicsenv=sun.awt.X11GraphicsEnvironment
feature.sdw=true
java.awt.headless=true
db.hostname=vcentos79-oracle-sa1
db.password=******
sun.management.compiler=HotSpot 64-Bit Tiered Compilers
security.requestValidationFunction=wwv_flow_epg_include_modules.authorize
java.runtime.version=11.0.18+9-LTS-195
user.name=oracle
path.separator=:
os.version=3.10.0-1160.88.1.el7.x86_64
java.runtime.name=Java(TM) SE Runtime Environment
file.encoding=UTF-8
plsql.gateway.mode=proxied
java.vm.name=Java HotSpot(TM) 64-Bit Server VM
java.vendor.version=18.9
java.vendor.url.bug=https://bugreport.java.com/bugreport/
java.io.tmpdir=/tmp
oracle.dbtools.cmdline.ShellCommand=ords
java.version=11.0.18
user.dir=/home/oracle
os.arch=amd64
java.vm.specification.name=Java Virtual Machine Specification
java.awt.printerjob=sun.print.PSPrinterJob
oracle.dbtools.cmdline.home=/u01/app/oracle/ORDSBIN
sun.os.patch.level=unknown
java.library.path=/usr/java/packages/lib:/usr/lib64:/lib64:/lib:/usr/lib
java.vendor=Oracle Corporation
java.vm.info=mixed mode
java.vm.version=11.0.18+9-LTS-195
sun.io.unicode.encoding=UnicodeLittle
db.connectionType=basic
java.class.version=55.0
2023-04-23T09:53:08.986Z WARNING     *** jdbc.MaxLimit in configuration |default|lo| is using a value of 20, this setting may not be sized adequately for a production environment ***
2023-04-23T09:53:08.989Z WARNING     *** jdbc.InitialLimit in configuration |default|lo| is using a value of 3, this setting may not be sized adequately for a production environment ***
2023-04-23T09:53:22.517Z INFO
Mapped local pools from /etc/ords/config/databases:
  /ords/                              => default                        => VALID

2023-04-23T09:53:23.008Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 23.1.1.r1091003
Oracle REST Data Services server info: jetty/10.0.12
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 11.0.18+9-LTS-195

>>>> If we notice, we are listening on 0.0.0.0 port 8080.

Step 6) Let us add the necessary port exemptions.
We need to add 1521 for oracle & 8080 for http as exemptions. Let us also add http as a service to be exempted from linux firewall.

[root@vcentos79-oracle-sa1 ~]# firewall-cmd --add-port=1521/tcp --permanent
success

[root@vcentos79-oracle-sa1 ~]# firewall-cmd --add-port=8080/tcp --permanent
success

[root@vcentos79-oracle-sa1 ~]#
[root@vcentos79-oracle-sa1 ~]# firewall-cmd --permanent --zone=public --add-service=http
success
[root@vcentos79-oracle-sa1 ~]#

[root@vcentos79-oracle-sa1 ~]# firewall-cmd --reload
success
[root@vcentos79-oracle-sa1 ~]#

Outcome:

[root@vcentos79-oracle-sa1 ~]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3 enp0s8
  sources:
  services: dhcpv6-client http ssh <<< so http is visiable here
  ports: 1521/tcp 8080/tcp <<< port 1521 & 8080 are visible here
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:
[root@vcentos79-oracle-sa1 ~]#

Step 7) The web page is still not accessible from host. The reason could be the ip 0.0.0.0 which the ords is listening on

java process:

oracle   18464 18418  0 10:52 pts/1    00:00:00 /bin/bash /u01/app/oracle/ORDSBIN/bin/ords --config /etc/ords/config serve
oracle   18490 18464 12 10:52 pts/1    00:00:30 java -Doracle.dbtools.cmdline.home=/u01/app/oracle/ORDSBIN -Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8 -Djava.awt.headless=true -Dnashorn.args=--no-deprecation-warning -Doracle.dbtools.cmdline.ShellCommand=ords -Duser.timezone=UTC -jar /u01/app/oracle/ORDSBIN/ords.war --config /etc/ords/config serve
[root@vcentos79-oracle-sa1 ~]# netstat -plantu|grep 18464
[root@vcentos79-oracle-sa1 ~]#

[root@vcentos79-oracle-sa1 ~]# netstat -plantu|grep 18490
tcp6       0      0 :::8080                 :::*                    LISTEN      18490/java

So let us fix this by binding this process to listen to the host ip; which is rechable from outside; which in our case is 192.168.194.5.
Or we have to use port forwarding, by making this process listen to 127.0.0.1.

Let us try 127.0.0.1 first...

Oracle says:
url: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.1/ordig/about-REST-configuration-files.html#GUID-006F916B-8594-4A78-B500-BB85F35C12A0

standalone.binds => string Specifies the comma separated list of host names or IP addresses to identify a specific network interface on which to listen. Default 0.0.0.0.
Parameter impact:  Global

Step 8) Change the standalone.binds as needed...

Collect existing info:

[oracle@vcentos79-oracle-sa1 ~]$ ords --config /etc/ords/config config get standalone.binds
ORDS: Release 23.1 Production on Sun Apr 23 10:17:23 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
  /etc/ords/config/
Cannot get setting standalone.binds because the setting is not found in database pool default located at /etc/ords/config/databases/default

Set new info:

[oracle@vcentos79-oracle-sa1 ~]$ ords --config /etc/ords/config config set standalone.binds 127.0.0.1
ORDS: Release 23.1 Production on Sun Apr 23 10:19:32 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
  /etc/ords/config/
The global setting named: standalone.binds was set to: 127.0.0.1

Verify the new value:

[oracle@vcentos79-oracle-sa1 ~]$ ords --config /etc/ords/config config get standalone.binds
ORDS: Release 23.1 Production on Sun Apr 23 10:19:42 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
  /etc/ords/config/
127.0.0.1

[oracle@vcentos79-oracle-sa1 ~]$ ls -altr /etc/ords/config/*/*
-rw-r--r--. 1 root   root     240 Apr 23 10:03 /etc/ords/config/global/settings.xml_bkp_23Apr23
-rw-r--r--. 1 oracle oinstall 288 Apr 23 11:19 /etc/ords/config/global/settings.xml
/etc/ords/config/databases/default:
total 8
drwxr-xr-x. 3 oracle oinstall  21 Apr 22 20:17 ..
drwxr-xr-x. 2 oracle oinstall  25 Apr 22 20:17 wallet
-rw-r--r--. 1 root   root     649 Apr 23 10:03 pool.xml_bkp_23Apr23
drwxr-xr-x. 3 oracle oinstall  64 Apr 23 10:03 .
-rw-r--r--. 1 oracle oinstall 654 Apr 23 10:16 pool.xml

[oracle@vcentos79-oracle-sa1 ~]$ date
Sun Apr 23 11:20:15 BST 2023

[oracle@vcentos79-oracle-sa1 ~]$ diff /etc/ords/config/global/settings.xml_bkp_23Apr23 /etc/ords/config/global/settings.xml
4c4
< <comment>Saved on Sat Apr 22 19:17:55 UTC 2023</comment>
---
> <comment>Saved on Sun Apr 23 10:19:32 UTC 2023</comment>
5a6
> <entry key="standalone.binds">127.0.0.1</entry>
[oracle@vcentos79-oracle-sa1 ~]$

Step 9) Launch the ORDS in standalone mode again..

[oracle@vcentos79-oracle-sa1 ~]$ ords --config /etc/ords/config serve
ORDS: Release 23.1 Production on Sun Apr 23 10:26:25 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
  /etc/ords/config/
2023-04-23T10:26:26.881Z INFO        HTTP and HTTP/2 cleartext listening on host: 127.0.0.1 port: 8080 <<<< the IP is changed from 0.0.0.0 to 127.0.0.1
2023-04-23T10:26:26.992Z INFO        Disabling document root because the specified folder does not exist: /etc/ords/config/global/doc_root
2023-04-23T10:26:45.519Z INFO        Configuration properties for: |default|lo|
db.servicename=inventorypdb
awt.toolkit=sun.awt.X11.XToolkit
java.specification.version=11
conf.use.wallet=true
sun.cpu.isalist=
sun.jnu.encoding=UTF-8
user.region=US
java.class.path=/u01/app/oracle/ORDSBIN/ords.war
java.vm.vendor=Oracle Corporation
sun.arch.data.model=64
nashorn.args=--no-deprecation-warning
java.vendor.url=https://openjdk.java.net/
resource.templates.enabled=false
user.timezone=UTC
db.port=1521
java.vm.specification.version=11
os.name=Linux
sun.java.launcher=SUN_STANDARD
user.country=US
sun.boot.library.path=/u01/app/oracle/JAVABIN/jdk-11.0.18/lib
sun.java.command=/u01/app/oracle/ORDSBIN/ords.war --config /etc/ords/config serve
jdk.debug=release
sun.cpu.endian=little
user.home=/home/oracle
oracle.dbtools.launcher.executable.jar.path=/u01/app/oracle/ORDSBIN/ords.war
user.language=en
java.specification.vendor=Oracle Corporation
java.version.date=2023-01-17
database.api.enabled=true
java.home=/u01/app/oracle/JAVABIN/jdk-11.0.18
db.username=ORDS_PUBLIC_USER
file.separator=/
java.vm.compressedOopsMode=32-bit
line.separator=
restEnabledSql.active=true
java.specification.name=Java Platform API Specification
java.vm.specification.vendor=Oracle Corporation
java.awt.graphicsenv=sun.awt.X11GraphicsEnvironment
feature.sdw=true
java.awt.headless=true
db.hostname=vcentos79-oracle-sa1
db.password=******
standalone.binds=127.0.0.1
sun.management.compiler=HotSpot 64-Bit Tiered Compilers
security.requestValidationFunction=wwv_flow_epg_include_modules.authorize
java.runtime.version=11.0.18+9-LTS-195
user.name=oracle
path.separator=:
os.version=3.10.0-1160.88.1.el7.x86_64
java.runtime.name=Java(TM) SE Runtime Environment
file.encoding=UTF-8
plsql.gateway.mode=proxied
java.vm.name=Java HotSpot(TM) 64-Bit Server VM
java.vendor.version=18.9
java.vendor.url.bug=https://bugreport.java.com/bugreport/
java.io.tmpdir=/tmp
oracle.dbtools.cmdline.ShellCommand=ords
java.version=11.0.18
user.dir=/home/oracle
os.arch=amd64
java.vm.specification.name=Java Virtual Machine Specification
java.awt.printerjob=sun.print.PSPrinterJob
oracle.dbtools.cmdline.home=/u01/app/oracle/ORDSBIN
sun.os.patch.level=unknown
java.library.path=/usr/java/packages/lib:/usr/lib64:/lib64:/lib:/usr/lib
java.vendor=Oracle Corporation
java.vm.info=mixed mode
java.vm.version=11.0.18+9-LTS-195
sun.io.unicode.encoding=UnicodeLittle
db.connectionType=basic
java.class.version=55.0
2023-04-23T10:26:45.525Z WARNING     *** jdbc.MaxLimit in configuration |default|lo| is using a value of 20, this setting may not be sized adequately for a production environment ***
2023-04-23T10:26:45.529Z WARNING     *** jdbc.InitialLimit in configuration |default|lo| is using a value of 3, this setting may not be sized adequately for a production environment ***
2023-04-23T10:26:50.120Z INFO
Mapped local pools from /etc/ords/config/databases:
  /ords/                              => default                        => VALID

2023-04-23T10:26:50.567Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 23.1.1.r1091003
Oracle REST Data Services server info: jetty/10.0.12
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 11.0.18+9-LTS-195

>>>>>>>>>>>>> the process is launched

oracle   20442 18418  0 11:26 pts/1    00:00:00 /bin/bash /u01/app/oracle/ORDSBIN/bin/ords --config /etc/ords/config serve
oracle   20468 20442 68 11:26 pts/1    00:00:29 java -Doracle.dbtools.cmdline.home=/u01/app/oracle/ORDSBIN -Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8 -Djava.awt.headless=true -Dnashorn.args=--no-deprecation-warning -Doracle.dbtools.cmdline.ShellCommand=ords -Duser.timezone=UTC -jar /u01/app/oracle/ORDSBIN/ords.war --config /etc/ords/config serve

[root@vcentos79-oracle-sa1 ~]# netstat -plantu|grep -E "20442|20468"
tcp6       0      0 127.0.0.1:8080          :::*                    LISTEN      20468/java <<< now we are on 127.0.0.1
tcp6       0      0 192.168.194.5:24214     192.168.194.5:1521      ESTABLISHED 20468/java
tcp6       0      0 192.168.194.5:24216     192.168.194.5:1521      ESTABLISHED 20468/java
tcp6       0      0 192.168.194.5:24212     192.168.194.5:1521      ESTABLISHED 20468/java

[root@vcentos79-oracle-sa1 ~]#

Step 10) The webpage isnt accessible yet!
These are our urls coming from apex install:

The structure of the link to the Oracle APEX administration services is as follows:
http://host:port/ords/apex_admin

The structure of the link to the Oracle APEX development interface is as follows:
http://host:port/ords

[root@vcentos79-oracle-sa1 ~]# curl -I http://127.0.0.1:8080/ords
HTTP/1.1 302 Found
Location: http://127.0.0.1:8080/ords/
Content-Length: 0
[root@vcentos79-oracle-sa1 ~]#

[root@vcentos79-oracle-sa1 ~]# curl -I http://127.0.0.1:8080
HTTP/1.1 302 Found
Location: http://127.0.0.1:8080/ords/
Content-Length: 0
[root@vcentos79-oracle-sa1 ~]#

Step 11) Change the bind address to 192.168.194.5; which is a host-only adapter.

[oracle@vcentos79-oracle-sa1 ~]$ ords --config /etc/ords/config config list
ORDS: Release 23.1 Production on Sun Apr 23 14:51:48 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
  /etc/ords/config/
Database pool: default
Setting                              Value                                    Source
----------------------------------   --------------------------------------   -----------
database.api.enabled                 true                                     Global
db.connectionType                    basic                                    Pool
db.hostname                          vcentos79-oracle-sa1                     Pool
db.password                          ******                                   Pool Wallet
db.port                              1521                                     Pool
db.servicename                       inventorypdb                             Pool
db.username                          ORDS_PUBLIC_USER                         Pool
feature.sdw                          true                                     Pool
plsql.gateway.mode                   proxied                                  Pool
restEnabledSql.active                true                                     Pool
security.requestValidationFunction   wwv_flow_epg_include_modules.authorize   Pool
standalone.binds                     192.168.194.5                            Global
[oracle@vcentos79-oracle-sa1 ~]$

Step 12) Troubleshoot the issue

Before we started the java process...

[root@vcentos79-oracle-sa1 ~]# curl -I http://192.168.194.5:8080/ords
curl: (7) Failed connect to 192.168.194.5:8080; Connection refused
[root@vcentos79-oracle-sa1 ~]#

After we started the java process...

2023-04-23T14:54:33.911Z INFO        HTTP and HTTP/2 cleartext listening on host: 192.168.194.5 port: 8080
2023-04-23T14:54:34.025Z INFO        Disabling document root because the specified folder does not exist: /etc/ords/config/
...
2023-04-23T14:54:57.432Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 23.1.1.r1091003
Oracle REST Data Services server info: jetty/10.0.12
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 11.0.18+9-LTS-195

This time we got the page open and accessible...

http://192.168.194.5:8080/ords/f?p=4550:1:17128284404794:::::

Just that the below message came:

There is a problem with your environment because the Oracle APEX files have not been loaded. Please verify that you have copied the images directory to your application server as instructed in the Installation Guide. In addition, please verify that your image prefix path is correct. Your current path is /i/ (it should contain both starting and ending forward slashes, such as the default /i/). Use the SQL script reset_image_prefix.sql if you need to change it.





So let us start the ORDS with image path set explicitly as below..

/u01/app/oracle/ORDSBIN/images/

Ex.: ords --config <CONFIG_PATH> serve --port 8777 --apex-images /path/to/apex/images

Actual: ords --config /etc/ords/config serve --apex-images /u01/app/oracle/ORDSBIN/images/

Now the screen came properly. Both the below urls loaded fine.
http://192.168.194.5:8080/ords/apex_admin




http://192.168.194.5:8080/ords




Summary of the last web page access issue:
The endpoint or bind has to be an ip not lo or 0.0.0.0.
The image directory path has to be called out explictly when java process is started.

>>> Let us close this blog here. The basic setup of APEX and ORDS is complete. Next series will be the workspace creation and then coding.
at
Labels: , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Troubleshooting the “Cannot Generate SSPI Context” Error After SQL Server Migration

  Introduction After a recent  SQL Server migration from 2012 to 2022 , our team encountered a classic authentication issue: The target prin...