How to capture HTTP Header information from the client browser

Steps to generate the HAR file for Chrome (Version 112.0.5615.140). It is part of Oracle Doc ID 815734.1 (How to Gather HTTP Header Information From a Client PC)

1.    Open Google Chrome and go to the page (such as EBS login page) where the issue is occurring.

2.    Bring the Chrome menu by clicking on the upper right > More tools > Developer Tools.

3.    From the panel opened at the bottom of your screen, select the Network tab.

4.    Look for a round Record button (Record button) in the upper left corner of the Network tab, and make sure it is red. If it is grey, click it once to start recording.

5.    Check the box next to Preserve log .

6.    Click the Clear button ( Clear button ) to clear out any existing logs from the Network tab.

7.    Now try to reproduce the issue that you were experiencing before, while the network requests are being recorded.

8.    Once you have reproduced the issue, right click anywhere on the grid of network requests, select Save as HAR with Content, and save the file to your computer.

9.    Upload your HAR file to your ticket or attach it to your email so that we may analyze it.

Somehow, during the capture, I do see some menu items are missing from System Administrator responsibility.  

 

Which EBS server did a user session log into?

Oracle Doc ID 364439.1 (Tips and Queries for Troubleshooting Advanced Topologies) provides a few useful queries. One of them is to tell what server a user logged into.

A query to show and monitor all users who logged to the system within the last hour.

col server_name format a12

col user_name format a30

select

  to_char(first_connect, 'HH24:MI:SS') "TIME",

  user_name,

  decode

   (a.node_id,

   a.node_id,

   (select node_name  

   from fnd_nodes n  

   where a.node_id=n.node_id),a.node_id) server_name

from

  icx_sessions a,

  fnd_user b,

  fnd_nodes svr

where

  first_connect > (sysdate-1/24)

  and (a.user_id=b.user_id

       and a.node_id=svr.node_id)

  and disabled_flag='N'

order by first_connect;

TIME      USER_NAME         SERVER_NAME

----------- --------------------------- ------------

20:11:21 SYSADMIN          HOSTBE

20:17:53 USER                   HOSTINT

20:19:16 OPERATIONS     HOSTEMT

20:39:21 GUEST                HOSTPC8  (did not login, hit "forgot password")

Upgrade AD and TXK from Delta 12 to Delta 14 RUPs in R12.2

AD.C.Delta.14 (patch 33600809) and TXK.C.Delta.14 (patch 33602997) are pre-requisite for January 2023 CPU patches (see Doc ID 2916871.1). We have to apply them before that CPU patch set can be applied to R12.2 instance.

Oracle document ID 1617461.1 (Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2) gives details on how to apply the latest Oracle Applications DBA (AD) and Oracle E-Business Suite Technology Stack (TXK) release update packs (RUPs) to EBS R12.2. I performed below steps to upgrade them to delta 14 from delta 12 in my instances where existing package levels are:

R12.AD.C.Delta.12

R12.TXK.C.Delta.12

R12.ATG_PF.C.delta.9 (30399994)

1. DBA runs the latest version of ETCC (via Patch 17537119), and ensure all required database objects by the Delta 14 RUP installation process will be found. One of recent database PSU patches will help to achieve this.

The following optimizer parameter should always be set to a value of TRUE:

_disable_actualization_for_grant=true

2. Pre-step: download all .zip files and unzip them to $PATCH_TOP (or an alternation folder) of all nodes. Then, DBA runs $PATCH_TOP/34669333/admin/adgrants.sql as SYSDBA

AD critical patch 34669333 has the latest adgrants.sql, as of now. Please read the instruction in the file before run it.

$ grep Header $PATCH_TOP/34669333/admin/adgrants.sql

REM $Header: adgrants.sql 120.67.12020000.123 2022/09/30 21:13:17 jwsmith ship $

3. Verify new db account ebs_system. 

adgrants.sql creates new database account ebs_system. Unlock it and change its password to be the same as SYSTEM's password (if it has not been done yet). 

$ sqlplus system/systemPWD

SQL> alter user ebs_system account unlock;

SQL> alter user ebs_system identified by systemPWD;

SQL> conn ebs_system/systemPWD

4. Run the validation script

$ perl $PATCH_TOP/33600809/ad/bin/adValidateEbssystemSchema.pl

Enter the APPS password:

Enter the SYSTEM password:

Validating APPS credentials...validated successfully

Validating SYSTEM credentials...validated successfully

Validating EBS_SYSTEM user

ebssys entity is not registered

All required validations completed successfully

5. Optional: Validate adop, and stop all services (if use downtime mode to apply the patch)

$ adop -validate

$ adop -status

$ cd $ADMIN_SCRIPTS_HOME

$ ./adadminsrvctl.sh stop

$ ./adnodemgrctl.sh stop

$ ps -ef | grep $LOGNAME

6. Apply AD.C.Delta.14 patch, plus 3 critical patches, by running below line on primary mode. I used downtime mode to apply them.

$ adop phase=apply apply_mode=downtime patches=33600809,34668508,34669333,34681299 merge=yes patchtop=$PATCH_TOP

Enter the APPS password:

Enter the SYSTEM password:

Enter the WLSADMIN password:

If patching gets error, most likely there is a problem with something else. Confirm 4 patches were applied to all nodes:

SQL> SELECT adb.bug_number, aas.name appl_top_name, adb.creation_date, adb.language,

decode(ad_patch.is_patch_applied('R12',aas.appl_top_id,adb.bug_number,adb.language),'EXPLICIT','APPLIED','NOT_APPLIED','NOT APPLIED') status

FROM ad_bugs adb,

(select distinct appltop_id appl_top_id, node_name name from ad_adop_sessions 

           where node_name in (select node_name from ADOP_VALID_NODES) ) aas

where adb.bug_number in (

'33600809',

'34668508',

'34669333',

'34681299'

) order by creation_date desc, adb.bug_number,aas.name,adb.language;

UPDATE in July 2023: patch 35280947 was added as a AD critical patch in the document. When they were applied to an instance with one single node, the adop log is

$ adop phase=apply apply_mode=downtime patches=33600809,34668508,34669333,34681299,35280947 merge=yes patchtop=$PATCH_TOP

... ...

Copying files...

5% complete. Copied 26 files of 519...

10% complete. Copied 52 files of 519...

15% complete. Copied 78 files of 519...

20% complete. Copied 104 files of 519...

25% complete. Copied 130 files of 519...

30% complete. Copied 156 files of 519...

35% complete. Copied 182 files of 519...

40% complete. Copied 208 files of 519...

45% complete. Copied 234 files of 519...

50% complete. Copied 260 files of 519...

55% complete. Copied 286 files of 519...

60% complete. Copied 312 files of 519...

65% complete. Copied 338 files of 519...

70% complete. Copied 364 files of 519...

75% complete. Copied 390 files of 519...

80% complete. Copied 416 files of 519...

85% complete. Copied 442 files of 519...

90% complete. Copied 468 files of 519...

95% complete. Copied 494 files of 519...

100% complete. Copied 519 files of 519...

Character-set converting files...

  5 unified drivers merged.

Patch merge completed successfully

Please check the log file at $ADOP_LOG_HOME/8/20230804_163029/apply/$EBS_HOSTNAME/admrgpch.log.

Applying patch ADOP_MRG_20230804_1691181079:u_ad_3366500462.drv.

    Log: $ADOP_LOG_HOME/8/20230804_163029/apply/$EBS_HOSTNAME/ADOP_MRG_20230804_1691181079/log/u_ad_3366500462.log

Running finalize actions for the patches being applied.

    Log: @ADZDSHOWLOG.sql "2023/08/04 16:36:47"

Running cutover actions for the patches being applied.

    Creating workers to process cutover DDL in parallel

    Log: $ADOP_LOG_HOME/8/20230804_163029/apply/$EBS_HOSTNAME/log/cutover.log

    Performing database cutover in Quick mode

Generating post apply reports.

Generating log report.

    Output: $ADOP_LOG_HOME/8/20230804_163029/apply/$EBS_HOSTNAME/adzdshowlog.out

The apply phase completed successfully.

adop exiting with status = 0 (Success)

7. Apply TXK.C.Delta.14 patch, plus 2 critical patches, by below line on primary mode. Note it now asks for EBS_SYSTEM password.

$ adop phase=apply apply_mode=downtime patches=33602997,34708635,34654260 merge=yes patchtop=$PATCH_TOP

Enter the APPS password:

Enter the EBS_SYSTEM password:

Enter the WLSADMIN password:

Confirm 3 patches were applied successfully:

SQL> SELECT adb.bug_number, aas.name appl_top_name, adb.creation_date, adb.language,

decode(ad_patch.is_patch_applied('R12',aas.appl_top_id,adb.bug_number,adb.language),'EXPLICIT','APPLIED','NOT_APPLIED','NOT APPLIED') status

FROM ad_bugs adb,

(select distinct appltop_id appl_top_id, node_name name from ad_adop_sessions 

           where node_name in (select node_name from ADOP_VALID_NODES) ) aas

where adb.bug_number in (

'33602997',

'34708635',

'34654260'

) order by creation_date desc, adb.bug_number,aas.name,adb.language;

8. Post steps

$ perl $AD_TOP/bin/admkappsutil.pl

$ cp -p $INST_TOP/admin/out/appsutil.zip $APPLPTMP

$ Autoconfig on db node

$ Autoconfig on all apps nodes

9. Start Apps services

$ ./adstrtal.sh apps/aapsPWD -mode=allnodes

10. After the instance was verified and checked out, run FS_CLONE to sync the file systems.

NOTES: adgrants.sql from patch 34669333 makes important changes in database. During above patching, it is copied to $APPL_TOP/admin as the current one. Below is its log by SYSDBA: 

Current user is SYS

------------------------------------------------------------

--- adgrants.sql started at 2023-04-20 11:15:11 ---

------------------------------------------------------------

Removing logs from prior runs of adgrants.sql

-

Start granting from SYS to EBS_SYSTEM

-

End granting from SYS to EBS_SYSTEM

Completed granting and checking privileges

Generating list of ERRORS and WARNINGS to print out

PRINT_ERROR

---------------------

:current_user

CURRENT_USER

-----------------------------------------------------------------------------------------

SYS user ERRORS and WARNINGS will report at end of script

to print out

CURRENT_USER

-----------------------------------------------------------------------------------------

SYS user ERRORS and WARNINGS will report at end of script

Creating PL/SQL profiler objects.

---------------------------------------------------------------

--- profload.sql started at 2023-04-25 11:15:14 ---

In 12.2c and beyond, the Oracle-supplied profload.sql script is a verification script, not an installation script.

Testing for correct installation

SYS.DBMS_PROFILER successfully loaded.

-----------------------------------------------------

--- profload.sql completed at 2023-04-25 11:15:16 ---

--------------------------------------------------

--- proftab.sql started at 2023-04-25 11:15:16 ---

-----------------------------------------------------

--- profltab.sql completed at 2023-04-25 11:15:18 ---

Installing Hierarchical Profiler.

-

Loading Stylesheets if missing

Begin creating the AD_ZD_SYS package

End creating the AD_ZD_SYS package

Executing PURGE DBA_RECYCLEBIN.

The following ERRORS and WARNINGS have been encountered during this adgrants session:

Grants given by this script have been written to the ad_zd_logs table.

You can run $AD_TOP/sql/ADZDSHOWLOG.sql to produce a report showing these grants.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.18.0.0.0

Started at 2023-04-25 11:15:14 ---

In 12.2c and beyond, the Oracle-supplied profload.sql script is a verification script, not an installation script.

Testing for correct installation

SYS.DBMS_PROFILER successfully loaded.

-----------------------------------------------------

--- profload.sql completed at 2023-04-25 11:15:16 ---

--------------------------------------------------

--- proftab.sql started at 2023-04-25 11:15:16 ---

-----------------------------------------------------

--- profltab.sql completed at 2023-04-25 11:15:18 ---

Installing Hierarchical Profiler.

HPTAB_SCRIPT

---------------------------

?/rdbms/admin/nothing.sql

-

Loading Stylesheets if missing

PACKAGE_NAME

------------------------

AD_ZD_SYS

Begin creating the AD_ZD_SYS package

End creating the AD_ZD_SYS package

Executing PURGE DBA_RECYCLEBIN.

The following ERRORS and WARNINGS have been encountered during this adgrants session:

Grants given by this script have been written to the ad_zd_logs table.

You can run $AD_TOP/sql/ADZDSHOWLOG.sql to produce a report showing these grants.

How to setup a custom env variable in R12.2

Where is the custom env file in R12.2? adsrtall.sh defines ENVFILE parameter to call file $APPL_TOP/APPS$CONTEXT_NAME.env. The main env file EBSapps.env also calls it. It resides in the same folder with $CONTEXT_FILE. File APPS$CONTEXT_NAME.env tells the location of custom$CONTEXT_NAME.env. Usually, it is $INST_TOP/appl/admin/custom$CONTEXT_NAME.env.

How to write the custom env file? It is a shell script. For example, use two lines in custom$CONTEXT_NAME.env to define a custom variable for the instance:

$ cd $INST_TOP/appl/admin

$ more custom$CONTEXT_NAME.env

FORMS_RECORD_GROUP_MAX=30000

export FORMS_RECORD_GROUP_MAX

$ echo $FORMS_RECORD_GROUP_MAX

30000

Another way to set custom env variables is to use the "custom" folder under $AD_TOP/admin/template. Oracle Doc ID 745711.1 gives steps on defining a Forms env variable:

a) Go to the autoconfig Template folder:

$ cd $AD_TOP/admin/template

b) Create new directory named (custom)

$ mkdir custom

c) Make sure that new directory has same file permissions as ($AD_TOP/admin/template)

d) Copy the following autoconfig template to the new custom directory:

$ cp $AD_TOP/admin/template/APPLSYS_ux.env $AD_TOP/admin/template/custom/APPLSYS_ux.env

e) Edit the file copied file under custom directory and add the following 2 lines at the end of section:

####################################

# Oracle Forms environment variables

####################################

FORMS_RECORD_GROUP_MAX=30000

export FORMS_RECORD_GROUP_MAX

f) Save and exit from the file.

g) Next time autoconfig run, it will read the custom directory and check for any customizations there.

After AutoConfig, above two lines will be copied to file $APPL_TOP/$CONTEXT_NAME.env (and other AD files?).

Notes: "FORMS_RECORD_GROUP_MAX=30000" can be equivalently added to Forms file $INST_TOP/ora/10.1.2/forms/server/default.env. But that file will be overwritten by AutoConfig. See Oracle Doc ID 745711.1.

EBS session status in the database & on OS

When an OS process uses high CPU or other OS resources which may lead to failure in launching EBS Forms or login page, use below line to find its start time:

$  ps -eo pid,lstart,cmd | grep 21846

74063 Tue Dec 19 12:29:31 2022 grep --color=auto 21486

21486 Tue Dec 19 07:39:01 2022 frmweb server webfile=HTTP-0,0,1,default

If a forms process (frmweb) exists on OS level but does not have a responding session in the database, it is a run-away and it can be terminated.  

Below SQL statement will tell and monitor what an EBS session is doing in the database after its process ID is identified (e.g. 21846) on OS level of EBS apps server. 

SQL> SELECT to_char(S.logon_time, 'DD-MON-RRRR HH24:MI:SS') "logon_time", to_char(S.PREV_EXEC_START, 'DD-MON-RRRR HH24:MI:SS') "last op started", 

    client_identifier, module, action, status, machine, round(last_call_et/60,1) "Minutes in current status",

    sql_id, blocking_session, process, sid, serial#, state,

    ROUND((S.WAIT_TIME_MICRO/1000000)/60/60,1) "total wait hrs",

    DECODE(S.TIME_REMAINING_MICRO,'-1', 'indefinite', '0','Wait timed out',NULL,'session not waiting') "remaining wait time", 

    DECODE(S.TIME_SINCE_LAST_WAIT_MICRO, '0','Still waiting') "current wait status",

    S.time_since_last_wait_micro

   FROM v$session s

WHERE process = '21846' 

-- order by client_identifier

;

Meaning of some v$session columns:

  client_identifier - Tell who runs the Forms session.

  BLOCKING_SESSION - Tell if it is blocked by another session.

  STATE - The state of the wait event.

  WAIT_TIME_MICRO - The wait time of the wait event in microseconds.

  TIME_REMAINING_MICRO - The time remaining in microseconds before the wait event times out.

  TIME_SINCE_LAST_WAIT_MICRO - The amount of time in microseconds elapsed from the last wait to the current wait.

For a concurrent job, a SQL query can find who runs the session. Normally, a running concurrent request will also show the SQL_ID of v$session.

An oacore process may have many sessions in the database. To find which process ID is for oacore, try "$ ps -ef | grep oacore " on OS level of apps server, or run below line in the database (if XX is part of a CUSTOM top name).

SQL> select distinct process, machine from v$session 

where action like '%XX%' order by machine;

Notes on OACORE (maybe useful or may not):

When an oacore process taking high CPU, you can take some actions:

(1) Log onto WebLogic console to see which oacore is not in Running status, and you may recycle it from the console. By that, some users' session will be terminated.

(2) If WebLogic console is not accessible, you may recycle WebLogic Admin services by adadminsrvctl.sh from primary node (which will not affect users' sessions). Then, you may have to recycle that frozen oacore by command such as "admanagedsrvctl.sh stop oacore_server2".

(3) Run following commands (But it may not help):

1) ps -eLo pid,tid,class,rtprio,ni,pri,psr,pcpu,stat,wchan:14,comm | grep PID

==> which thread is taking CPU.  Most of them should show 0.0 (which is the CPU column).

I did see an OACORE process (e.g. 13030) supports 125 JVM threads in one of my instances. So, it is NOT good to simply kill an oacore OS process.

$ ps -eLo pid,tid,class,rtprio,ni,pri,psr,pcpu,stat,wchan:14,comm | grep 13030 | wc -l

125

2) jmap -histo <jvm_process_id> > /tmp/ <jvm_process_id>.histo

==> will dump heap usage details.  

3) jmap -dump:format=b,file= <jvm_process_id>.jmap <jvm_process_id>

==> will generate an actual heap dump, binary format

4) kill -3 <jvm_process_id>

==> output goes to 12.2 oacore_server1.out. It will generate stack trace, run twice a minute apart.

kill -3 <jvm_process_id>

5) lsof -p <jvm_process_id> > <jvm_process_id>.log

==> List of open ports/files

6) SELECT audsid, module, last_call_et, action from gv$session where process = '&jvm_process_id';

==> corresponding DB sessions

7) log / incident files from that oacore :

$EBS_DOMAIN_HOME/servers/oa*/logs/*

$EBS_DOMAIN_HOME/servers/oa*/adr/diag/ofm/EBS_domain*/oacore*/incident/incdir*/*