Configuration of EBS Vision OCI VM Instance & APEX

Intro

This guide is windows-focus and sets up the following

• Sets up the EBS Vision OCI instance Image on Oracle Cloud (v. 12.2.10 - recommended)

• Gives access to Weblogic

• Configures Firefox to access EBS (NOTE: esr is too old for APEX builder, but APEX apps can still be run)

• Installs APEX (latest)

• Installs ORDS (latest) in Standalone with a script to run in the background. This is a "supported option" for EBS 12.2

• Configures supported schema for APEX to use alongside EBS

Important This guide switches between SU and oracle users. There is not a lot of opc login unless it's with sudo command. be careful who you are logged in as

Tools

SQLplus

At some points after installation you might need to run SQLPlus to do this you must run both these commands

sudo -i -u oracle
. /u01/install/APPS/19.0.0/ebscdb_apps.env
sqlplus / as sysdba

DB start up/shutdown

sudo -i -u oracle
sh /u01/install/APPS/scripts/stopdb.sh
sh /u01/install/APPS/scripts/startdb.sh

Step 1

Install EBS on OCI Cloud Compute by following this exact guide

Do not do step 3.10 Enable Oracle E-Business Suite Integrated SOA Gateway for REST Services (Optional)

Do not do step 4.6

Windows hosts should be

my.public.ip.goes.here change to exact.url.eg.vcnplcinternalv.oraclevcn.com 
my.public.ip.goes.here myapps.example.com
my.public.ip.goes.here apps.example.com

Step 2.

Download firefox "Firefox Setup 52.9.0esr.exe" and install it to a custom ESR folder (do not overwrite your existing folder). Do not immediately run after the install

Install Java "jdk-8u121-windows-i586.exe" <-- this will allow the install of the Firefox Add-Ons required for EBS

Step 3

Windows>Start>Configure Java

Click Security Tab and add the following to the Exception Site List

http://exact.url.eg.vcnplcinternalv.oraclevcn.com 
http://exact.url.eg.vcnplcinternalv.oraclevcn.com:8000
http://myapps.example.com
http://myapps.example.com:8000
http://apps.example.com
http://apps.example.com:8000

Add the following to windows hosts

127.0.0.1 www.mozilla.org
127.0.0.1 download.cdn.mozilla.net
127.0.0.1 aus5.mozilla.org
127.0.0.1 download.mozilla.org

Step 4

Run Firefox ESR

Block automatic updates in Firefox

Go to Add Ons > Enable all Java Addons

Check/Bookmark EBS in ESR

apps.example.com:8000/OA_HTML/AppsLocalLogin.jsp

Step 5

In OCI, follow the steps similar to Section 2 to add an Ingress 7001 port

Follow this guide to allow traffic to Web Logic. The config.xml file you need to edit is found in:

/u01/install/APPS/fs1/FMW_Home/user_projects/domains/EBS_domain/config

To edit it, set up WinSCP in this way.

Protocol > SCP
Hostname > Public IP of instance
User > OPC
Password > BLANK

Advanced > SSH > Authentication > Private Key File > your private key file
Advnaced > SCP/Shell > Shell > sudo su -

Once connected, edit the file linked above to change the deny to allow as per guide linked above.

Add port 7001 to the firewall - se below

sudo su - 
firewall-cmd --add-port=7001/tcp --permanent
systemctl restart firewalld

Restart apps

sudo -i -u oracle
cd /u01/install/APPS/scripts
sh stopapps.sh
sh startapps.sh

Check/Bookmark Weblogic in ESR

apps.example.com:7001/console

If the above doesn't work, try to find the URL using

ps aux | grep weblogic

User/pass is weblogic/welcome1 (unless you changed it in step 4.4.e)

Step 6

Ensure still logged in as oracle, otherwise...

sudo -i -u oracle

Download APEX

cd /home/oracle
wget https://download.oracle.com/otn_software/apex/apex-latest.zip

Then follow the APEX installation instructions here

In the end, it may error. Check files

/home/oracle/apex/apexins_cdb0.log
/home/oracle/apex/apexins_cdb1.log

... if it comes back with the error

Application Express installation requires the Oracle XML Database database component.

... then this can be ignored. But you can check if XDB is installed anyway by running the SQL below. I think it failed the reattemps by installing XDB in a 2nd installation phase (TODO: TBC)

  select comp_id,
         schema,
         version,
         status
    from sys.dba_registry
   where comp_id = 'XDB'
order by comp_id

It should be one row as above.

Finally, in sqlplus unlock the APEX_PUBLIC_USER account (click for more information)

alter user APEX_PUBLIC_USER account unlock;

Step 7

Prepare APEX images. In WinSCP...

1. Make a folder /u01/software/apex (this is "as suggested" by Oracle Base)

2. find the /home/oracle/apex/images folder and right-click->copy on the folder name

3. Go back to /u01/software/apex and right-click->paste so that the full folder is /u01/software/apex/images

Get Java installed by running

sudo su -
wget https://download.oracle.com/java/17/latest/jdk-17_linux-x64_bin.rpm
rpm -ivh jdk-17_linux-x64_bin.rpm
java -version

The last step (above) just verifies the installation, it should report Java 17

As opc user (log out of Oracle/Root by typing exit user if still in), run the following

sudo yum install ords

then configure it (going back in as SU)

sudo su -
ords --config /etc/ords/config install

Use the following configuration

Installation Type > Choose option [2] Enter
Connection Type > Choose option [1] Enter
host name > Enter
listen port > Enter
service name > ebsdb
administrator username > SYS
password > [enter SYS password]
default tablespace > Enter
temp tablespace > Enter
features > Enter
Start ORDS > [1] Enter <-- Standalone Mode
protocol > [1] < http
port > [1] <-- 8080
Static Resources > /u01/software/apex/images <-- from step above

ORDS will then install/configure. It will take 1-2 mines and finally load

2022-12-22T10:28:39.079Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 22.4.0.r3401044
Oracle REST Data Services server info: jetty/10.0.12
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 17.0.5+9-LTS-191

At any time, ORDS stops running (like an accidental ctrl+c), it can be restarted by

sudo su -
ords --config /etc/ords/config serve

In OCI, follow the steps similar to Section 2 to add an Ingress 8080 port. Then add port 8080 to the firewall.

sudo su - 
firewall-cmd --add-port=8080/tcp --permanent
systemctl restart firewalld

Then try it out

apps.example.com:8080/ords

Running standalone is not ideal, therefore configure ORDS as a service. See next step

Step 8

To configure ORDS as a service we are going to take the ideas from the Oracle Base blog on this subject.

1. Ctrl+C in putty to quit ORDS

2. In WinSCP, create a folder /home/oracle/scripts

3. Enter the folder and then Right-Click>New File

4. Create a file named start_ords.sh containing

export _JAVA_OPTIONS="-Xms1126M -Xmx1126M"
LOGFILE=/home/oracle/logs/ords-`date +"%Y""%m""%d"`.log
nohup ords --config /etc/ords/config serve >> $LOGFILE 2>&1 &
echo "View log file with : tail -f $LOGFILE"

NOTE: You have to do it this way (i.e through WinSCP) as creating/uploading from Windows notepad corrupts the file (TBC. It might work via VSCode + LF)

1. Next. Right-Click>New File, then Create a file named stop_ords containing

kill `ps -ef | grep [o]rds.war | awk '{print $2}'`

1. Upload the two files in WinSCP to /home/oracle/scripts

When you decide (you can run the start script now), log on as SU

sudo su -

then run them like this

sh /home/oracle/scripts/start_ords.sh
sh /home/oracle/scripts/stop_ords.sh

Step 9

Before logging in change the Internal Admin Workspace password

cd /home/oracle/apex

Then in surplus run the following from a local machine

EDIT: you have to run from your local machine (not terminal) - TODO explain why this works like this EDIT: The server logs in to a PDB which the local machine doesn't
Experimental: try

## This is experimental see note above
alter session set container ="ebsdb" from server

For this (i.e local machine approach), you need a TNS names

EBSVISION=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = apps.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ebsdb)
    )
  )

[End of Experimental Section]

Then connect as SYS and run (use either local or experimental approach)

@apxchpwd.sql

Then at the prompt

Administrators Username > Enter
Administrators Email > [ADMIN] Enter
Administrators Password > Type a Complex Password

Then try logging in at

apps.example.com:8080/ords

Workspace > INTERNAL
Username > ADMIN
Password > The Complex Password

If you cannot log in, then I think its PDB related... try the other approach i.e experimental/local approach

Step 10

This step prepares the workspace for supported EBS extension with APEX. We are going to follow the flowcharts in https://bit.ly/3jn2uen specifically the "Recommended Database Architecture" in the "Supported Schema Usage" section.

Click Create a Workspace

Workspace Name: XX_APEX
Existing Schema: No
Schema Name: XX_APEX
Password > A Complex Password

Then

1. Go to Manage Workspaces > Manage Developers and Users > Create / Edit User

2. Find the ADMIN / XX_APEX user and edit them

3. Set Require Change of Password on First Use to No

4. Click Apply Changes

5. Logout

6. Log Back in with ...

Workspace > XX_APEX
Username > ADMIN
Password > The Complex Password

Conclusion

You are all set up now for Oracle-supported development on EBS.

Info/Tools

Next Steps

Read https://bit.ly/3jn2uen about granting access from APPS to XX_APEX

Also, see Insums' Up and Running Package with EBS.