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

In R12.2, custom env variables can be defined in file $INST_TOP/appl/admin/custom$CONTEXT_NAME.env (after it is created), which is called by $APPL_TOP/APPS$CONTEXT_NAME.env (and so, by the main env file EBSapps.env).  For example, use two lines in custom$CONTEXT_NAME.env to define a custom variable:

$ 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 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

;

  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.

Normally, a session for a running concurrent request will show the SQL_ID.

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;

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*/*

SQL statement to find blocking session

Use SQL to identify and monitor the database session that blocks other session:

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') status, sid sess_ID, id1, id2, lmode, request, type

FROM    V$LOCK

WHERE (id1, id2, type) IN

(SELECT id1, id2, type FROM V$LOCK WHERE request > 0)

ORDER BY id1, request;

Use below to confirm the blocking status in the database:

SQL> SELECT blocking_session block_holder,

  sid, serial#, process, machine, client_identifier, wait_class, seconds_in_wait

FROM v$session

WHERE blocking_session IS NOT NULL

ORDER BY blocking_session; 

Purge R12.2 log files in Linux OS

Folder structure in R12.2 file system is more complicated than in R12.1, because RUN and PATCH may switch their location. After an adop cutover, the path to log files will be changed to a different one. Below are folders that have a growing number of log files. "ls" can be used to list the log files but "cd" do not work to reach the folder, while the path uses "*" in it.

$IAS_ORACLE_HOME/instances/*/diagnostics/logs/OHS/*/*.log*

$EBS_DOMAIN_HOME/servers/oacore*/logs/*.log*

$EBS_DOMAIN_HOME/servers/form*/logs/*.log*

$EBS_DOMAIN_HOME/servers/AdminServer/logs/*.log*

Fortunately, "find" works with path having "*" in it. Below lines work in cron to purge/delete old log files (on web/forms node).

$ crontab -l

05 05 * * * . /u07/app/.profile; /usr/bin/find $IAS_ORACLE_HOME/instances/*/diagnostics/logs/OHS/*/*log* -type f -mtime +30 -exec rm -f {} \;

05 15 * * * . /u07/app/.profile; /usr/bin/find $EBS_DOMAIN_HOME/servers/oacore*/logs/*log* -type f -mtime +30 -exec rm -f {} \;

Other logs or their locations:

$ADOP_LOG_HOME/*                   <== each ADOP session ID has a sub-folder

$INST_TOP/admin/log/MMDDHHMM/adconfig.log

$LOG_HOME/appl/admin/log/*      <== adstrtal.sh & adstpall.sh 

$LOG_HOME/appl/rgf/javacache.log 

$IAS_ORACLE_HOME/instances/*/diagnostics/logs/OPMN/opmn/* 

$EBS_DOMAIN_HOME/sysman/log/* 

$EBS_DOMAIN_HOME/servers/oac*/adr/diag/ofm/EBS_domain*/oac*/incident/* 

$EBS_DOMAIN_HOME/servers/forms_s*/adr/diag/ofm/EBS_domain*/forms_s*/incident/*

$INST_TOP/logs/ora/10.1.2/reports/cache/*.*

Locations or files for configuration and setups:

$IAS_ORACLE_HOME/instances/EBS_web*/config/OHS/EBS_web* 

$FMW_HOME/webtier/instances/EBS_web*/config/OPMN/opmn/* 

$CONTEXT_FILE

$INST_TOP/appl/admin/*.properties

$INST_TOP/ora/10.1.2/forms/server/default.env    (AutoConfig overwrites it)

$FND_TOP/fndenv.env

$EBS_DOMAIN_HOME/config/config.xml

$EBS_APPS_DEPLOYMENT_DIR/*/APP-INF/node_info.txt

.profile file (for account runs the crontab jobs on RHEL)

$ more .profile

PATH=/bin:/usr/bin:/usr/local/bin

export PATH

MANPATH=/usr/share/man:/usr/local/share/man:/usr/X11R6/man

export MANPATH

EDITOR=/bin/vi

export EDITOR

. /u07/app/EBSDEV/EBSapps.env RUN

isMaster="no"

if [ ! -z $APPS_VERSION ] && [ ${APPS_VERSION:0:4} == "12.2" ]

then

s_status=`cat $CONTEXT_FILE | grep -i s_adminserverstatus`

isMaster="${s_status:60:7}"

fi

if [ $isMaster == "enabled" ]

then

if [ `echo -n ${HOSTNAME%%.*} | tail -c -1` != "p" ]

then

PS1=$'

\e[0;31m$USER@${HOSTNAME%%.*}[$TWO_TASK]\e[m$PWD

-->$ '

else

PS1=$'

\e[0;31m$USER@${HOSTNAME%%.*}[$TWO_TASK]\e[m\E[32m$PWD \E[0m

-->$ '

fi

else

if [ `echo -n ${HOSTNAME%%.*} | tail -c -1` != "p" ]

then

PS1='

$USER@${HOSTNAME%%.*}[$TWO_TASK]$PWD

-->$ '

else

PS1=$'

$USER@${HOSTNAME%%.*}[$TWO_TASK]\E[32m$PWD \E[0m

-->$ '

fi

fi

alias rm='rm -i'

stty erase ^?