APEX 19.2 installation and configure ORDS on tomcat

Dear Folks,

Here i am giving you overview of 19.2 APEX installation and configure REST DATA SERVICES and installation of TOMCAT.

What is APEX? What is it's use?

********Oracle APEX (Oracle Application Express) is enterprise low-code development platform in which developers use to develop and deploy their application that solve real problems and provide immediate value. It is no-cost feature of Oracle database.

What is ORDS?

*******Oracle REST Data Services (ORDS)  is a data service which is used to replace  Oracle HTTP server and mod_plsql. It bases on Java EE that provides RESTful service and increases security capability. it can deploy on WebLogic, Tomcat, Glassfish  or independently. Here we install on Tomcat.

Now we will see step by step installation of  APEX 19.2  on linux platform where oracle database 12.1.0.2 running along with ORDS installation.

Apex Link to download - https://www.oracle.com/tools/downloads/apex-192-downloads.html  ( apex_19.2_en.zip)

ORDS Link to Download - REST Data services 19.2 download - https://www.oracle.com/database/technologies/appdev/rest-data-services-v192-downloads.html  (ords-19.2.0.199.1647.zip) 

There are multiple ways we can install and configure ORDS.

Scenario : 1 - Apex with  Oracle XML DB HTTP listener with Embedded PL/SQL Gateway

Scenario : 2 :: Apex with Oracle HTTP server or  mod_plsql.

Scenario 3 :: Install APEX and configure ORDS. It replace completely  Oracle HTTP Server which shown in scenario 2 , therefore you can use  RESTful on APEX and new URL to work with  Oracle APEX.

                             Apex installation along with ORDS on tomcat ( Scenario:3) on database 12C

Download APEX software on database server  and unzip /u01/app/****/apex

apex_19.2_en.zip

Create separate tablespace for APEX to store APEX schemas. Our case created APEX tablespace.

cd /u01/app/***/apex

SQL> @apexins.sql APEX APEX TEMP /i/

Thank you for installing Oracle Application Express 19.2.0.00.18

Oracle Application Express is installed in the APEX_190200 schema.

                                         Change Application Express instance administrator password.

         SQL> @apxchpwd.sql

Check for  users APEX_PUBLIC_USER, APEX_INSTANCE_ADMIN_USER, APEX_190200. They should be open.

                                                                 Configure REST services

SQL> @apex_rest_config.sql

Enter a password for the APEX_LISTENER user              []

Enter a password for the APEX_REST_PUBLIC_USER user              []

...create APEX_LISTENER and APEX_REST_PUBLIC_USER users

PL/SQL procedure successfully completed.

APEX_LISTENER - The account used to query RESTful Services definitions stored in Oracle Application Express.

APEX_REST_PUBLIC_USER - The account used when calling RESTful Services definitions stored in Oracle Application Express.

                                     Change the image prefix for the whole APEX instance

cd /u01/app/*****/apex/utilities

SQL> @reset_image_prefix.sql

Enter the Application Express image prefix [/i/] /i/

...Changing Application Express image prefix

NEW

---

/i/

                                   Installation of ORDS and deploy on TOMCAT on Mid-Tier

Oracle REST Data Services supports the following Java EE application servers

                         Oracle Weblogic server :: 12g release 2 ( 12.2.1.3) and above
                         Tomcat - 8.5 and later

unzip ORDS software ords-19.2.0.199.1647.zip)  to /u01/app/****/ords

set ords_params.properties as below 

db.hostname=kchladrerbs01.ard.tks.com

db.password=*******

db.port=1522

db.sid=SIDNAME

db.username=APEX_PUBLIC_USER

migrate.apex.rest=false

plsql.gateway.add=true

rest.services.apex.add=true

rest.services.ords.add=true

restEnabledSql.active=true

schema.tablespace.default=APEX

schema.tablespace.temp=TEMP

standalone.mode=false

user.apex.listener.password=******

user.apex.restpublic.password=*********

user.public.password=*********

user.tablespace.default=APEX

user.tablespace.temp=TEMP

ORDS installation recommends Java JDK 8 or later. Download jdk1.8.0_241 install at 

/u02/app/****/ords/jdk/jdk1.8.0_241

export JAVA_HOME=/u02/app/****/ords/jdk/jdk1.8.0_241

export PATH=$PATH:$JAVA_HOME/bin

which java

/u02/app/****/ords/jdk/jdk1.8.0_241/bin/java

cd /u02/app/****/ords

/u02/app/*****/ords/jdk/jdk1.8.0_241/bin/java -jar ords.war    - It will install ORDS.

-

- Completed installation for Oracle REST Data Services version 19.2.0.r1991647.

                                                                                 

                                                              Tomcat installation

Download following software

OpenJDK11Downloads ( openjdk-11.0.1_linux-x64_bin.tar.gz)

Apache Tomcat ( used apache-tomcat-9.0.14)

Unzip JDK at  /u01/app/****/ords/java

 drwxrwxr-x 8      4096 Feb 17 23:22 jdk-11.0.1

create soft link  so we can Always use the same path for the JAVA_HOME environment variable >>> ln -s jdk-11.0.1 latest

lrwxrwxrwx 1      10 Feb 17 23:22 latest -> jdk-11.0.1

Unzip Tomcat at /u01/app/****/ords/tomcat and create soft link  so we can Always use the same path for the JAVA_HOME environment variable

drwxrwxr-x 9      4096 Feb 17 23:14 apache-tomcat-9.0.14

ln -s apache-tomcat-9.0.14 latest

lrwxrwxrwx 1     20 Feb 17 23:14 latest -> apache-tomcat-9.0.14

we want to separate config from binaries to make future upgrades easier, so we will create a new directory to act as the CATALINA_BASE location.

/u01/app/****/ords/tomcat

mkdir -p config/instance1

cp -r latest/conf  config/instance1/

cp -r latest/logs config/instance1/

cp -r latest/temp config/instance1/

cp -r latest/webapps config/instance1/

cp -r latest/work config/instance1/

apex.env should like below

export JAVA_HOME=/u01/app/**/ords/jdk/jdk1.8.0_241

export PATH=$PATH:$JAVA_HOME/bin

export JAVA_HOME=/u01/app/***/ords/java/latest

export CATALINA_HOME=/u01/app/***/ords/tomcat/latest

export CATALINA_BASE=/u01/app/***/ords/tomcat/config/instance1

copy APEX images from /u02/app/***/apex/images/* to webapps directory $ CATALINA_HOME /webapps/i

cd $ CATALINA_BASE/webapps

cp -Rp $CATALINA_HOME/webapps/i .

cp ords.war $CATALINA_HOME/webapps/

cp ords.war $CATALINA_BASE/webapps/

Start and stop of ORDS under TOMCAT

$CATALINA_HOME/bin/startup.sh

$CATALINA_HOME/bin/shutdown.sh

Validate ORDS

/u01/app/***/ords/jdk/jdk1.8.0_241/bin/java -jar ords.war validate

Create ACLS

BEGIN

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

host => '*',

ace => xs$ace_type(privilege_list => xs$name_list('connect'),

principal_name => 'APEX_190200',

principal_type => xs_acl.ptype_db));

END;

/

BEGIN

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

host => 'kchladrerbs02.brg.tks.com',

ace => xs$ace_type(privilege_list => xs$name_list('connect'),

principal_name => 'APEX_190200',

principal_type => xs_acl.ptype_db));

END;

/

Now you should be able to get TOMCAT and APEX URLS

Tomcat URL: http://kchladrerbs02.brg.tks.com:8090/

Apex URL: http:// kchladrerbs02.brg.tks.com:8090/ords/

Useful EBS Queries

Concurrent programs run between particular time
++++++++++++++++++++++++++++++++++++

select
r.REQUEST_ID,p.user_concurrent_program_name Program_name,user_name usr,r.PHASE_CODE,r.STATUS_CODE,r.RESPONSIBILITY_ID,r.ACTUAL_START_DATE,r.ACTUAL_COMPLETION_DATE,r.ARGUMENT_TEXT
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.fnd_concurrent_programs_vl p,
apps.fnd_user usr
where
p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and usr.user_id = r.requested_by
and c.concurrent_process_id = r.controlling_manager
and q.concurrent_queue_id = c.concurrent_queue_id
and actual_start_date between to_date('01-APR-2019 00:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('03-FEB-2020: 14:00:00','DD-MON-YYYY HH24:MI:SS')
and r.status_code = 'C'
and r.phase_code in ('C')
order by to_char(r.actual_start_date,'DD-MON-YYYY HH24:MI:SS') asc;



Check for Custom PROFILE
++++++++++++++++++++++

SELECT e.profile_option_name Profile,
decode(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue,
nvl(a.profile_option_value,'Is Null') Value
FROM fnd_profile_option_values a, fnd_responsibility_tl b,
fnd_application c, fnd_user d, fnd_profile_options e, fnd_profile_options_vl f
WHERE f.user_profile_option_name like '%<>%'
AND e.profile_option_id = a.profile_option_id
AND e.profile_option_id = f.profile_option_id
AND a.level_value = b.responsibility_id (+)
AND a.level_value = c.application_id (+)
AND a.level_value = d.user_id (+)
ORDER BY 1,2;

LIST OF System Administrator Resp Assigned IN PAST 30 DAYS'
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT fu.user_name "User Login", fu.start_date "Login Start Date", fu.end_date "Login End Date",
fu.employee_id "Employee Id",
hou.NAME "Business Group",
fr.responsibility_name "Responsibility Associated",
fur.start_date "Association Start Date",
fur.end_date "Association End Date",
fur.LAST_UPDATED_BY,
fur.START_DATE,
fur.END_DATE,
fur.CREATED_BY,
fur.LAST_UPDATED_BY,
fur.LAST_UPDATE_DATE
FROM apps.fnd_user fu,
apps.per_all_people_f ppf,
apps.hr_all_organization_units hou,
apps.fnd_user_resp_groups_all fur,
apps.fnd_responsibility_tl fr
WHERE ppf.person_id = fu.employee_id
AND hou.organization_id = ppf.business_group_id
AND fu.user_id = fur.user_id
AND fur.START_DATE  BETWEEN SYSDATE-30 AND SYSDATE
AND fr.responsibility_name LIKE '%System%Administrator%'
AND fur.responsibility_id = fr.responsibility_id
AND fr.LANGUAGE = 'US'
ORDER BY fu.user_name, fr.responsibility_name;

'LIST OF SYSADMIN USERS LOGONS WITH RESPONSIBILITIES IN PAST 7 Days'
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select d.user_name,c.user_id,a.*, b.responsibility_name from APPLSYS.FND_LOGIN_RESPONSIBILITIES a,
APPLSYS.FND_RESPONSIBILITY_TL b,  APPLSYS.fnd_logins c, APPLSYS.fnd_user d
where a.login_id  = c.login_id
and c.user_id in (0) and trunc (c.start_time) BETWEEN SYSDATE-8 AND SYSDATE
and A.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
and d.user_id = c.user_id order by  trunc(c.start_time) asc;

USERS WITH System Administrator RESP ACCESSED IN LAST 7 DAYS'
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select fu.user_name, max(flr.start_time), flr.responsibility_id ,
flt.responsibility_name
from apps.fnd_logins fl, apps.fnd_login_Responsibilities flr, apps.fnd_user fu ,
apps.fnd_responsibility_tl flt
where flr.login_id = fl.login_id
and flt.responsibility_id = flr.responsibility_id
and fu.user_id = fl.user_id
and flr.RESPONSIBILITY_ID='20420'
and fl.start_time BETWEEN SYSDATE-7 AND SYSDATE
group by fl.user_id, flr.RESPONSIBILITY_ID ,fu.user_name,
flt.responsibility_name
order by fl.user_id;

Pending requests in OPP

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

select REQUEST_ID,PHASE_CODE,STATUS_CODE from fnd_concurrent_requests

where request_id in (

select concurrent_request_id

from fnd_conc_pp_actions

where action_type >= 6

and processor_id is null)

and PHASE_CODE!='C'

Recent changed profiles for USER SYSADMIN.

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

column profile format a40

column value format a20

column level format a15

column app format a20

column resp format a20

column user format a20

select substr(fpo.user_profile_option_name,1,60) profile,

substr(fpov.profile_option_value,1,75) value,

fpov.profile_option_id,

decode(fpov.level_id,10001,

'SITE',10002,

'APPLICATION',10003,

'RESPONSIBILITY',

10004,'USER',10005,'SERVER')"level",

fa.application_short_name app,

substr(fr.responsibility_name,1,20)"responsibility",

substr(fu.user_name,1,10)"user"

from fnd_profile_option_values fpov,

fnd_profile_options_vl fpo,

fnd_application fa,

fnd_responsibility_vl fr,

fnd_user fu,

fnd_logins fl

where fpo.profile_option_id=fpov.profile_option_id

and fa.application_id(+)=fpov.level_value

and fr.application_id(+)=fpov.level_value_application_id

and fr.responsibility_id(+)=fpov.level_value

and fu.user_id(+)=fpov.level_value

and fl.login_id(+) = fpov.LAST_UPDATE_LOGIN

and fpo.user_profile_option_name in('Client Timezone') and fu.USER_NAME='SYSADMIN';

Profiles changed in last 2 days

+++++++++++++++++++++

column profile format a40

column value format a20

column level format a15

column app format a20

column resp format a20

column user format a20

select p.profile_option_name SHORT_NAME, n.user_profile_option_name "PROFILE NAME",

decode(v.level_id, 10001, 'Site', 10002, 'Application',

10003, 'Responsibility', 10004, 'User', 10005, 'Server',

10007, 'SERVRESP', 'UnDef') LEVEL_SET,

decode(to_char(v.level_id), '10001', '',

'10002', app.application_short_name, '10003', rsp.responsibility_key,

'10005', svr.node_name, '10006', org.name, '10004', usr.user_name,

'10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE, v.LAST_UPDATE_DATE 

from fnd_profile_options p,

fnd_profile_option_values v,

fnd_profile_options_tl n,

fnd_user usr,

fnd_application app,

fnd_responsibility rsp,

fnd_nodes svr,

hr_operating_units org

where p.profile_option_id = v.profile_option_id (+)

and p.profile_option_name = n.profile_option_name

--and upper(n.user_profile_option_name) like upper('BNE%')

--and trunc(v.LAST_UPDATE_DATE) > trunc(sysdate-1)

and usr.user_id (+) = v.level_value

and rsp.application_id (+) = v.level_value_application_id

and rsp.responsibility_id (+) = v.level_value

and app.application_id (+) = v.level_value

and svr.node_id (+) = v.level_value

and org.organization_id (+) = v.level_value

and v.LAST_UPDATE_DATE is not null

order by last_update_date desc, short_name, level_set;

Prepare phase failed with Error calling runPendingConfigClone subroutine

Dear Folks,

Recently, we have faced issue  with adop prepare phase which is failed with below error.

Prepare phase is completed in internal node,but failed in DMZ( external node).

From adop log

========

        [PROCEDURE] Calling: /u01/app/*******/KRHRY/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl

        [EVENT]     Log: /u01/app/*******/KRHRY/fs_ne/EBSapps/log/adop/19/20200502_045746/prepare/biaerp2

        [UNEXPECTED]Error occurred running "perl /u01/app/*******/KRHRY/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl -contextfile=/u01/app/*******/KRHRY/fs2/inst/apps/KRHRY_biaerp2/appl/admin/KRHRY_biaerp2.xml -patchcontextfile=/u01/app/*******/KRHRY/fs1/inst/apps/KRHRY_biaerp2/appl/admin/KRHRY_biaerp2.xml -promptmsg=hide -console=off -mode=migrate -sync_mode=copy -sessionid=19 -timestamp=20200502_045746 -outdir=/u01/app/*******/KRHRY/fs_ne/EBSapps/log/adop/19/20200502_045746/prepare/biaerp2"

        [UNEXPECTED]occurred during CONFIG_CLONE Patch File System from Run File System, running command: "perl /u01/app/*******/KRHRY/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl -contextfile=/u01/app/*******/KRHRY/fs2/inst/apps/KRHRY_biaerp2/appl/admin/KRHRY_biaerp2.xml -patchcontextfile=/u01/app/*******/KRHRY/fs1/inst/apps/KRHRY_biaerp2/appl/admin/KRHRY_biaerp2.xml -promptmsg=hide -console=off -mode=migrate -sync_mode=copy -sessionid=19 -timestamp=20200502_045746 -outdir=/u01/app/*******/KRHRY/fs_ne/EBSapps/log/adop/19/20200502_045746/prepare/biaerp2".

        [PROCEDURE] [START 2020/05/02 07:11:35] Updating status of patches

        [STATEMENT] SQL statement : "  update ad_adop_session_patches

  set status='F'

  where applied_file_system_base = '/u01/app/*******/KRHRY/fs2' and

  patch_file_system_base = '/u01/app/*******/KRHRY/fs1' and bug_number='CONFIG_CLONE' and status <> 'Y'

  and appltop_id=2127 and node_name='biaerp2'"

        [PROCEDURE] [END   2020/05/02 07:11:36] Updating status of patches

        [UNEXPECTED]fs_clone has failed.

        [UNEXPECTED]Error calling runPendingConfigClone subroutine.

        [ERROR]     Prepare phase has failed.

Further drill down the issue and found it was failed while OHS config.

-/u01/app/*******/KRHRY/fs_ne/EBSapps/log/adop/19/20200502_045746/prepare/biaerp2/TXK_SYNC_migrate/ohsConfig_apply

START: Instantiating the OHS Config.

Running /u01/app/*******/KRHRY/fs1/FMW_Home/webtier/perl/bin/perl  -I/u01/app/*******/KRHRY/fs1/FMW_Home/webtier/perl/lib/5.10.0 -I/u01/app/*******/KRHRY/fs1/FMW_Home/webtier/perl/lib/site_perl/5.10.0 -I/u01/app/*******/KRHRY/fs1/EBSapps/appl/au/12.0.0/perl -I/u01/app/*******/KRHRY/fs1/FMW_Home/webtier/ohs/mod_perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /u01/app/*******/KRHRY/fs1/EBSapps/appl/fnd/12.0.0/patch/115/bin/txkSetOHSConfig.pl cfgCloneInstance -ctxfile=/u01/app/*******/KRHRY/fs1/inst/apps/KRHRY_biaerp2/appl/admin/KRHRY_biaerp2.xml -archvloc=/u01/app/*******/KRHRY/fs2/EBSapps/comn/adopclone_biaerp2/FMW/OHS/ohsarchive.jar -mvpln=/u01/app/*******/KRHRY/fs2/EBSapps/comn/adopclone_biaerp2/FMW/OHS/moveplan.xml -t2plogloc=/u01/app/*******/KRHRY/fs2/inst/apps/KRHRY_biaerp2/admin/log/clone/ohsT2PApply -logdir=/u01/app/*******/KRHRY/fs2/inst/apps/KRHRY_biaerp2/admin/log/clone -console=off -promptmsg=hide

Script Executed in 39566 milliseconds, returning status 1

ERROR: Script failed, exit code 1

#########

Hence we decided to run FS_CLONE, because we see prepare phase failed during CONFIG_CLONE Patch File System from Run File System,

Below are the steps to follow.

Fix

==

1) Abort the cycle adop phase=abort

2) Clean up the cycle, we can use cleanup_mode=FULL (optional)  adop phase=cleanup

3) adop phase=fs_clone

Then run the prepare phase again. It will be completed.

adop phase=prepare

Thanks.