ERP on DB: April 2022

Friday, April 29, 2022

Rebuild R12.2 Central Inventory

- The problem: adop failures on a server which has multiple EBS instances

ADOP reported unexpected errors on an UAT instance. "adop -validate" also failed on running script $AD_TOP/patch/115/bin/txkADOPEvalSrvStatus.pl

AdopValidate failed or are incomplete on node(s): Node2name

[UNEXPECTED]Unable to continue processing on other available nodes: Node1name

[UNEXPECTED]Error running "AdopValidate" on node(s): Node2name.

[UNEXPECTED]Remote action failed.

Seems the problem was on the 2nd node/server Node2name. Logs on Node2name gave more details on the error:

$ cd $NE_BASE/EBSapps/log/adop/4/20220425_174046/validate/Node2name

$ egrep -i 'erro|fail' *.*

AdopValidate.log: [PROCEDURE] [START 2022/04/25 17:42:49] Validating failed hotpatch cycle

AdopValidate.log: [PROCEDURE] [END 2022/04/25 17:42:49] Validating failed hotpatch cycle

AdopValidate.log: [UNEXPECTED]Error calling TXK validations procedure

ValidationResults.log: Validate failed hotpatch cycle

ValidationResults.log:SUCCESS: No failed hotpatch cycle present for this Node2name node.

ValidationResults.log: ERROR: The value of inventory_loc in $FMW_HOME/oracle_common/oraInst.loc is not the same as /u03/app/oraQA2Inventory present in other files

ValidationResults.log: ERROR: The value of inventory_loc in $FMW_HOME/webtier/oraInst.loc is not the same as /u03/app/oraQA2Inventory present in other files

ValidationResults.log: ERROR: The value of inventory_loc in $FMW_HOME/Oracle_EBS-app1/oraInst.loc is not the same as /u03/app/oraQA2Inventory present in other files

It indicates there was a confusion on the Central Inventory location. After checked pointer file /etc/oraInst.loc, I realized that when Node2name was added to the instance by clone, a new central inventory was not created for it and cloning used the inventory for QA2 which is a different instance on the host.

"opatch apply" also reported a similar error if file /etc/oraInst.loc does not point its Central Inventory location to the one for QA2.

The Oracle Home /u02/app/EBSUAT/fs1/EBSapps/10.1.2 is not registered with the Central Inventory. OPatch was not able to get details of the home from the inventory.

ERROR: OPatch failed because of Inventory problem.

I saw file /u03/app/oraQA2Inventory/ContentsXML/inventory.xml file has many entries for UAT instance. That confirms Inventory for ORACLE HOMEs was messed up.

- Re-build central inventory for ORACLE HOMEs

The only permanent fix is to follow Doc ID 1586607.1 (R12.2 How To Re-attach Oracle Homes To The Central Inventory) to build/create its Central Inventory in below steps:

$ $FMW_HOME/oracle_common/oui/bin/runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/u02/app/EBSUAT/fs1/FMW_Home/oracle_common" ORACLE_HOME_NAME="fs1_FMW_common" CLUSTER_NODES="{}"

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 14566 MB Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-04-26_12-58-12PM. Please wait ...

$ The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u02/app/oraEBSUATinventory

'AttachHome' was successful.

$ $FMW_HOME/oracle_common/oui/bin/runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/u02/app/EBSUAT/fs2/FMW_Home/oracle_common" ORACLE_HOME_NAME="fs2_FMW_common" CLUSTER_NODES="{}"

$ $FMW_HOME/oracle_common/oui/bin/runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/u02/app/EBSUAT/fs1/FMW_Home/webtier" ORACLE_HOME_NAME="fs1_FMW_webtier" CLUSTER_NODES="{}"

$ $FMW_HOME/oracle_common/oui/bin/runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/u02/app/EBSUAT/fs2/FMW_Home/webtier" ORACLE_HOME_NAME="fs2_FMW_webtier" CLUSTER_NODES="{}"

$ $FMW_HOME/oracle_common/oui/bin/runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/u02/app/EBSUAT/fs1/FMW_Home/Oracle_EBS-app1" ORACLE_HOME_NAME="fs1_FMW_app1" CLUSTER_NODES="{}"

$ $FMW_HOME/oracle_common/oui/bin/runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/u02/app/EBSUAT/fs2/FMW_Home/Oracle_EBS-app1" ORACLE_HOME_NAME="fs2_FMW_app1" CLUSTER_NODES="{}"

$ $RUN_BASE/EBSapps/10.1.2/oui/bin/runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/u02/app/EBSUAT/fs1/EBSapps/10.1.2" ORACLE_HOME_NAME="fs1_tool_EBSapps_10_1_2" CLUSTER_NODES="{}"

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-04-26_01-13-13PM. Please wait ...

$ Java HotSpot(TM) Server VM warning: You have loaded library /tmp/OraInstall2022-04-26_01-13-13PM/oui/lib/linux/liboraInstaller.so which might have disabled stack guard. The VM will try to fix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>', or link it with '-z noexecstack'.

'AttachHome' was successful.

$ $PATCH_BASE/EBSapps/10.1.2/oui/bin/runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/u02/app/EBSUAT/fs2/EBSapps/10.1.2" ORACLE_HOME_NAME="fs2_tool_EBSapps_10_1_2" CLUSTER_NODES="{}"

$ cd /u02/apps/oraEBSUATinventory/ContentsXML

$ more inventory.xml

<?xml version="1.0" standalone="yes" ?>

<COMPOSITEHOME_LIST>

</COMPOSITEHOME_LIST>

<VERSION_INFO>

<SAVED_WITH>10.1.0.6.0</SAVED_WITH>

<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>

</VERSION_INFO>

<HOME_LIST>

<NODE_LIST>

</NODE_LIST>

</HOME>

<NODE_LIST>

</NODE_LIST>

</HOME>

</HOME_LIST>

</INVENTORY>

After Central Inventory was rebuilt, ADOP and opatch worked on the node using the new & correct Central Inventory.

Friday, April 22, 2022

EBS Apps steps for upgrading R12.2 database to 19c

There are two parts for the db upgrade: before the upgrade and after the upgrade on EBS side.

A. Apply patches to R12.2 BEFORE database is upgraded from 12c to19c

A list of EBS patches are needed before 19c database upgrade. Doc ID 2552181.1 (Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c) has details for that.

1. Apply required R12.2 patches (Section 2.1)

The following 9 patches must exist in the environment before database is upgraded to 19c.

SELECT adb.bug_number, aat.name appl_top_name, adb.language, adb.creation_date,

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

FROM ad_bugs adb,

(select aat.name, aat.appl_top_id

from applsys.ad_appl_tops aat,

(select distinct fat.name from applsys.fnd_appl_tops fat) fat

where aat.name=fat.name ) aat

where adb.bug_number in (

'25452805', -- Applied

'26052406', -- Applied

'26521736', -- Need (19c interoperability patch for Release 12.2)

'30433124', -- Applied

'31088182', -- Need to apply

'31349591', -- Need to apply

'31800803', -- Need to apply

'30611319', -- Applied (only if using Integrated SOA Gateway (ISG))

'28732161' -- Need (Release 12.2.6 or later versions)

) order by adb.bug_number,aat.name,adb.language;

In my instance, 4 patches were applied. I used downtime mode to apply 5 patches:

26521736

31088182

31349591

31800803

28732161

$ sh $ADMIN_SCRIPTS_HOME/adstpall.sh apps/apps_PWD

$ ps -ef | grep $LOGNAME

$ echo $FILE_EDITION

run

$ echo $PATCH_TOP

$NE_BASE/EBSapps/patch

$ cd $PATCH_TOP

$ unzip p26521736_12.2.0_R12_LINUX.zip

$ unzip p31088182_R12.FND.C_R12_GENERIC.zip

$ unzip p31349591_R12.OTA.C_R12_GENERIC.zip

$ unzip p31800803_R12.TXK.C_R12_GENERIC.zip

$ unzip p28732161_R12.FRM.C_R12_GENERIC.zip

$ adop phase=apply apply_mode=downtime patches=26521736,31088182,31349591,31800803,28732161

Run the SQL statement again to confirm all 9 patches were applied. I also applied patch 31405457 (See Doc ID 2554156.1 for 19c database Export/Import):

$ unzip p31405457_R12_GENERIC.zip

$ adop phase=apply apply_mode=downtime patches=31405457

If needed, additional patches for business functions can be applied before run FS_CLONE to get it ready for database upgrade.

$ adop phase=fs_clone

2. Optional: Instead of FS_CLONE, complete patching cycle and remove ADOP created editions (Section 2.3, Doc ID 2552181.1)

As the owner of the source administration server, run any of the following commands not previously run. This will clean up the editions created by previous adop patching cycles. These commands also complete any open patching cycle.

I did clone the 2nd node from 1st node in one of my instances, AFTER applying above 5 patches and BEFORE running below ADOP cycle.

On the current RUN file system, I run below lines in order. The ADOP session will run on all nodes.

$ vi /etc/oraInst.loc

$ adop phase=prepare <= It may take a couple of hours

… …

AutoConfig completed successfully.

Synchronizing patch filesystem with run filesystem.
Log: $NE_BASE/EBSapps/log/adop/5/20210X16_132846/prepare/$EBS_HOSTNAME

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

SUMMARY OF PATCHES BEING SYNCHRONIZED...

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

[1] SINGLE PATCH (DRV=u30399996.drv): 30399996
[2] SINGLE PATCH (DRV=u31948857.drv): 31948857
[3] ADADMIN action : GEN_JARS
[4] SINGLE PATCH (DRV=u26521736.drv): 26521736
[5] SINGLE PATCH (DRV=u31088182.drv): 31088182
[6] SINGLE PATCH (DRV=u31349591.drv): 31349591
[7] SINGLE PATCH (DRV=u31800803.drv): 31800803
[8] SINGLE PATCH (DRV=u28732161.drv): 28732161
[9] SINGLE PATCH (DRV=u31405457.drv): 31405457
[10] SINGLE PATCH (DRV=u32072819.drv): 32072819
[11] SINGLE PATCH (DRV=u32157516.drv): 32157516
[12] SINGLE PATCH (DRV=u32181474.drv): 32181474
[13] SINGLE PATCH (DRV=u32266274.drv): 32266274
SYNCHRONIZATION is in progress. This may take a little longer depending on the number and size of patches that are being synchronized.
Check log file $NE_BASE/EBSapps/log/adop/....../prepare/$EBS_HOSTNAME/TXK_SYNC_update/txkADOPPreparePhaseSynchronize.log for details.

LOGPATH is set to $NE_BASE/EBSapps/log/adop/....../prepare/$EBS_HOSTNAME/TXK_SYNC_update/log

… …

$ adop phase=actualize_all

It takes 4 or more hours staying below file for very long time:

$NE_BASE/EBSapps/log/adop/5/20210X16_142206/actualize_all/$EBS_HOSTNAME/log/actualize_all.log, executing ACTUALIZE_CHILD_OBJS ($AD_TOP/sql/ADZDSHOWOBJS.sql ?)

It does not change Status on any patching cycle Phase.

$ adop phase=finalize finalize_mode=full

It take a few minutes.

$ adop phase=cutover <== it may take half hour

... ...

Creating list of nodes where cutover needs to be run.

The cutover phase needs to be run on node: 2nd_node

The cutover phase needs to be run on node: master_node

Running cutover phase on node(s): [2nd_node and master_node].

Broadcast message from applmgr@master_node.domain.com (Wed xxx 18:50:56 2022):

EBSDEV environment has changed.

All users must re-source the environment.

Broadcast message from applmgr@master_node.domain.com (Wed xxx 18:56:58 2022):

EBSDEV environment has changed.

All users must re-source the environment.

Output: $NE_BASE/EBSapps/log/adop/....../cutover/remote_execution_result_level1.xml

Statement handler reference is invalid.

Session killed.

Note that this is normal in the online patching cutover phase.

DB-Handle Error Code: 28

DB-Handle Error Message: ORA-00028: your session has been killed (DBD ERROR: OCIStmtExecute/Describe)

Reconnecting to the database. Log: $NE_BASE/EBSapps/log/adop/....../cutover/master_node

txkADOPEvalSrvStatus.pl returned SUCCESS

Generating node-specific status report.

Output: $NE_BASE/EBSapps/log/adop/....../adzdnodestat.out

Summary report for current adop session:

Node 2nd_node: Completed successfully

- Cutover status: Completed successfully

Node master_node: Completed successfully

- Cutover status: Completed successfully

For more details, run the command: adop -status -detail

adop exiting with status = 0 (Success)

EBS services will be down for 5 to 10 minutes

It changes APPLY phase from ACTIVE to COMPLETED, and CUTOVER phase from "NOT STARTED" to COMPLETED.

After "adop phase=cutover", run the env again or login as a new session. That will change the File system from fs1 to fs2. On the new RUN file system:

$ adop phase=cleanup cleanup_mode=full <= It takes about 2 hours.

Enter the APPS password:

Enter the SYSTEM password:

Enter the WLSADMIN password:

Validating credentials.

Initializing.
    Run Edition context : xxx.xml
    Patch edition context: xxx.xml
    Patch file system free space: 62.62 GB

Validating system setup.
Node registry is valid.

Checking for existing adop sessions.

===============================================
ADOP (C.Delta.12)
Session ID: 5
Node: node_name
Phase: cleanup
Log: $NE_BASE/EBSapps/log/adop/5/20210416_185701/adop.log
===============================================

Processing cleanup actions in parallel.
Log: $NE_BASE/EBSapps/log/adop/5/20210416_185701/cleanup/xifsapw3d/log/cleanup.log

Generating report of queued DDL actions.
Output: $NE_BASE/EBSapps/log/adop/5/20210416_185701/cleanup/xifsapw3d/adzdallddls.out

Running cleanup in FULL mode.
    Log: @ADZDSHOWLOG.sql "2021/04/16 19:01:04"
    [ERROR]     ORA-04045: errors during recompilation/revalidation of APPS.ap_custom_PKG
ORA-04052: error occurred when looking up remote object APPLSYS.FND_PROFILE_OPTION_VALUES@a_db_link.WORLD
ORA-00604: error occurred at recursive SQL level 3
ORA-02019: connection description for remote database not found, SQL[ORA$BASE]: drop PACKAGE "APPS"."ap_custom_PKG"
    [ERROR]     Could not drop covered object [ORA$BASE] APPS.ap_custom_PKG (PACKAGE): ORA-04045: errors during recompilation/revalidation of APPS.custom_PKG
ORA-04052: error occurred when looking up remote object APPLSYS.FND_PROFILE_OPTION_VALUES@a_db_link.WORLD
ORA-00604: error occurred at recursive SQL level 3
ORA-02019: connection description for remote database not found

Generating log report.
Output: $NE_BASE/EBSapps/log/adop/5/20210416_185701/cleanup/node_name/adzdshowlog.out

The cleanup phase completed successfully.

adop exiting with status = 0 (Success)

"adop -status -detail" to confirm all phases are COMPLETED.

Also, confirm by SQL query that 9 pre-patches still exist in all nodes (after adop CUTOVER).

3. Create a new appsutil.zip file and copy it over for database server:

$ cd $AD_TOP/bin
$ ls -al ad*mk*
$ perl admkappsutil.pl

Copy 4 files to database node for DBA steps:

$INST_TOP/admin/out/appsutil.zip

$AD_TOP/patch/115/sql/adctxprv.sql

$APPL_TOP/admin/adstats.sql

$APPL_TOP/admin/adgrants.sql <== it is the same file as from CPU patch 30370733

4. Run hcheck as sysdba (Doc ID 136697.1)

If you get below error, you have to fix the problem before moving forward or Export/import may be the only option to upgrade the database.

Problem Summary

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

HCKW-0027: System Tablespace does not have relative file number of 1

Problem Description

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

Running hcheck.sql

Returned warning:

- SystemNotRfile1 ... 1201000200 > 902000000 03/04 16:03:08 WARN

HCKW-0027: System Tablespace does not have relative file number of 1 (Doc ID 2364065.1)

Confirm file names caused the mess-up:

SQL> select RELATIVE_FNO fno, file_id, substr(file_name,1,50) fileName

from dba_data_files where file_name like '%sys%';

FNO File_ID FILENAME

16 1 /oradata/u10/EBSDEV/system01.dbf

8 8 /oradata/u10/EBSDEV/system06.dbf

87 87 /oradata/u10/EBSDEV/system07.dbf

etc … …

SQL> select RELATIVE_FNO, file_id, substr(file_name,1,50) fileName

from dba_data_files where file_id =16;

FNO File_ID FILENAME

16 16 /oradata/u11/EBSDEV/APPS_TS_TX_IDX36.dbf

5. DBA to upgrade the database to 19c.

B. Connect to 19c database after DB upgrade completed.

In EBS R12.1, all TNS entries use SID. After database is upgraded to 19c, same TNS file in R12.1 shall continue working if the SID is kept the same.

$ tnsping ebsdev

TNS Ping Utility for Linux: Version 10.1.0.5.0 - Production on 24-MAR-2022 13:36:22

... ...

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = ebsDB1d.domain.com)(Port = 1560))) (CONNECT_DATA = (SID = EBSDEV)))

OK (20 msec)

If SID was changed during DB upgrade, SQL*Plus may give error:

$ sqlplus apps/appsPWD

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 2878

Additional information: -308473993

For EBS R12.2, the TNS entries do not use SID. After the database is upgraded to 19c, tnsping still work, but Sql*Plus may not:

$ tnsping ebsdev

... ...

Used parameter files:

$INST_TOP/ora/10.1.2/network/admin/sqlnet_ifile.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=ebsDB1d.domain.com)(PORT=1560)) (CONNECT_DATA= (SERVICE_NAME=EBSDEV) (INSTANCE_NAME=EBSDEV)))

OK (0 msec)

When trying to make connection to the database, it will give error:

$ sqlplus apps/appsPWD

ERROR:

ORA-12521: TNS:listener does not currently know of instance requested in

connect descriptor

This is because 19c database has new names. DBA has to provide the new info:

CDB = CDBEDEV

PDB = EBSDEV (Usually, the same as old DB name)

(and new SYSTEM password)

2. Edit tnsnames.ora file

$ cd $TNS_ADMIN

$ vi tnsnames.ora

replace every INSTANCE_NAME to CDBEDEV

replace every SERVICE_NAME to ebs_EBSDEV (if it is different from 12c)

$ tnsping aebsdev

... ...

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=ebsDB1d.domain.com)(PORT=1560)) (CONNECT_DATA= (SERVICE_NAME=ebs_EBSDEV) (INSTANCE_NAME=CDBEDEV)))

OK (0 msec)

$ sqlplus apps/appsPWD

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>

SQL statements can be used to confirm them:

SQL> SELECT sys_context('userenv','instance_name') FROM dual;

SYS_CONTEXT('USERENV','INSTANCE_NAME')

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

CDBEDEV

SQL> SELECT sys_context('userenv','service_name') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')

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

ebs_EBSDEV

SQL> select sys_context('userenv','db_name') from dual;

SYS_CONTEXT('USERENV','DB_NAME')

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

EBSDEV

Notes:

a). "Starting Oracle 12.2 sys_context(‘USERENV’,’DB_NAME’) will show the name of the Database in CDB$ROOT and the name of the PDB inside the PDB."

b). In 19c, db_name and global_name could be different from above. could be confusing.

SQL> select * from global_name;

GLOBAL_NAME

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

EBSDEV.WORLD

SQL> select name from v$database;

NAME

---------

CDBEDEV

c). SID is the same as instance_name. Or, use query to get it:

SQL> select instance from v$thread;

INSTANCE

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

CDBEDEV

d). "SELECT sys_context('USERENV', 'SID') FROM DUAL;" gives session_id in database.

To connect to the 19c database from EBS mid-tier nodes, DBAs have to make sure

Service "ebs_EBSDEV" and Instance "CDBEDEV" are running and have status READY.

$ ps -ef | grep _pmon_

oracle 41419846 1 0 Apr 13 - 0:14 ora_pmon_CDBEDEV

$ export ORACLE_HOME=/path/to/

$ lsnrctl status CDBEDEV

It should list several Services, including CDBEDEV, EBSDEV, ebs_EBSDEV, ebs_patch.

3. Edit $CONTEXT_FILE

Update the following values in the context file.

Variable Name Value

s_dbhost New database hostname (if changed)

s_dbport New database port (if changed)

s_dbGlnam CDBEDEV

s_apps_jdbc_connect_descriptor NULL (<jdbc_url oa_var="s_apps_jdbc_connect_descriptor"/>)

s_dbport New database port

s_applptmp Defined in UTL_FILE_DIR (shall not use /usr/tmp)

4. Run AutoConfig on all nodes

Notes: after autoconfig, s_apps_jdbc_connect_descriptor will get new value.

Check table.FND_OAM_CONTEXT_FILES, which may be cleaned during database upgrade. If CONTEXT FILE records for PATCH system are missing, run AutoConfig on PATCH file system as well. Otherwise, ADOP session will fail.

SQL> select * from FND_OAM_CONTEXT_FILES;

5. DBA runs some reports, compile invalid objects and make necessary grants.

6. Run AutoConfig on all apps nodes and start all EBS services.

All shall work by now.

SQL> @$AD_TOP/sql/ADZDSHOWLOG.sql

SQL> @$AD_TOP/sql/ADZDSHOWOBJS.sql

SQL> @ $AD_TOP/sql/ADZDSHOWED.sql

SQL> show parameter filesystemio_options

NAME TYPE VALUE

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

filesystemio_options string SETALL

SQL> show parameter utl_file

NAME TYPE VALUE

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

utl_file_dir unknown

Friday, April 8, 2022

Apply R12.2 January 2022 CPU patches

Oracle E-Business Suite Release 12.2 Critical Patch Update Availability Document (January 2022) (Doc ID 2815550.1) gives al details on applying January 2022 CPU patches. The document is not easy to follow. An easy way is to run the latest ETCC script checkMTpatch.sh from patch 17537119. It will list all patches needed for Technology Stack Components.

Download and copy all .zip files to a shard location /u46/ora/R122patch_PGRND/CPU_JAN22

$ ZIPfolder=/u46/ora/R122patch_PGRND/CPU_JAN22

$ echo $ZIPfolder

$ cd $ZIPfolder

Back up database and EBS file systems on all nodes.

Shutdown all EBS services.

1. Oracle Fusion Middleware 10.1.2.3 ORACLE_HOME (Table 3)

26825525 (CPUOct2017. Applied)

32922089 (For patching Oracle Forms))

$ vi /etc/oraInst.loc

$ echo $FILE_EDITION

run

$ echo $ORACLE_HOME

/aebsu07/app/AEBSGOLD/fs1/EBSapps/10.1.2

$ which opatch

$ cd /aetnas46/oracmprod/R122patch_IFSPGRND/CPU_JAN22

$ unzip p32922089_101232_LINUX.zip

$ cd 32922089

$ opatch apply

... ...

Removing patch 26825525...

Applying patch 32922089...

OPatch succeeded.

$ opatch lsinventory

44 patches.

Do not see file orion-web.xml asked in post-patch instruction. Skip it by assuming it does not apply to EBS.

2. Oracle Fusion Middleware 11.1.1.9 - Oracle Common Home (Table 3)

Patch 31985571 (A superset of patch 30358663. Already applied)

Patch 26933408 (for patching JDBC)

$ export ORACLE_HOME=$FMW_HOME/oracle_common

$ export PATH=$ORACLE_HOME/OPatch:$PATH

$ echo $ORACLE_HOME

$ which opatch

$ cd /aetnas46/oracmprod/R122patch_IFSPGRND/CPU_JAN22

$ unzip p26933408_111190_Generic.zip

$ cd 26933408

$ opatch apply

... ...

Patching component oracle.jrf.thirdparty.jee, 11.1.1.9.0...

Verifying the update...

Patch 26933408 successfully applied

$ opatch lsinventory

Interim patches (14) : ...

3. Oracle Fusion Middleware 11.1.1.9 - Web Tier Home (4 patches, Table 3)

32287205 (OSS BUNDLE PATCH 11.1.1.9.210420)

33311587 (OHS 11.1.1.9.0 SPU FOR OctCPU2021)

23716938 (OPMN patch. Already applied)

32928416 (OPMN patch)

33144848 (OHS PLUGIN 11.1.1.9.0 SPU FOR APRCPU2021)

$ echo $IAS_ORACLE_HOME

/aebsu07/app/AEBSGOLD/fs1/FMW_Home/webtier

$ export ORACLE_HOME=$IAS_ORACLE_HOME

$ echo $ORACLE_HOME

/aebsu07/app/AEBSGOLD/fs1/FMW_Home/webtier

$ export PATH=$IAS_ORACLE_HOME/OPatch:$PATH

$ which opatch

~/AEBSGOLD/fs1/FMW_Home/webtier/OPatch/opatch

$ cd ..

$ unzip p33311587_111190_Linux-x86-64.zip

$ cd 33311587

$ opatch apply -jre $ORACLE_HOME/jdk/jre

... ...

Backing up files...

Rolling back interim patch '31047338' from OH '/aebsu07/app/AEBSGOLD/fs1/FMW_Home/webtier'

Patching component oracle.ohs2, 11.1.1.9.0...

RollbackSession removing interim patch '31047338' from inventory

OPatch back to application of the patch '33311587' after auto-rollback.

Patching component oracle.ohs2, 11.1.1.9.0...

Verifying the update...

Patch 33311587 successfully applied

$ cd ..

$ unzip p32287205_111190_Linux-x86-64.zip

$ cd 32287205

$ opatch apply

... ...

Backing up files...

Rolling back interim patch '31304503' from OH '/aebsu07/app/AEBSGOLD/fs1/FMW_Home/webtier'

Patching component oracle.ldap.rsf, 11.1.1.9.0...

Patching component oracle.rdbms.rsf, 11.1.0.7.0...

RollbackSession removing interim patch '31304503' from inventory

OPatch back to application of the patch '32287205' after auto-rollback.

Patching component oracle.ldap.rsf, 11.1.1.9.0...

Patching component oracle.rdbms.rsf, 11.1.0.7.0...

Verifying the update...

Patch 32287205 successfully applied

$ cd ..

$ unzip p33144848_111190_Linux-x86-64.zip

$ cd 33144848

$ opatch apply -jre $ORACLE_HOME/jdk/jre

... ...

Backing up files...

Patching component oracle.wlsplugins, 11.1.1.9.0...

Verifying the update...

Patch 33144848 successfully applied

$ cd ..

$ unzip p32928416_111190_Linux-x86-64.zip

$ cd 32928416

$ opatch apply

... ...

Backing up files...

Patching component oracle.opmn, 11.1.1.9.0...

Verifying the update...

Patch 32928416 successfully applied

$ opatch lsinventory

Interim patches (17) : ... ...

4. Oracle WebLogic Server 10.3.6.0 (Table 2)

On 2/15/2022, patches 33796519 and 33845432 were added to Doc ID 2815550.1 (Table 3) for Log4j vulnerability.

Start a new OS session (to get the correct ORACLR_HOME)

$ cd $ZIPfolder/WLS_CPU

a) Get WLS batch files ready: 6 .zip files (Do this once for all instances)

Patch 33845432 replaces patch 32898996 to patch Smart Update Tool (bsu) and BUG33681492 - CVE-2021-4104, CVE-2022-23305 AND CVE-2022-23307:

$ unzip -o p33845432_R12_GENERIC.zip

Archive: p33845432_R12_GENERIC.zip

inflating: Readme.txt

extracting: p33800106_1036_Generic.zip

Totally, 5 patches to be applied by bsu:

p31241365_1036_Linux-x86-64.zip [CW7X]

p33494814_1036_Generic.zip [HYG5]

p13964737_10360220118_Generic.zip [7GCA]

p13845626_10360220118_Generic.zip [IJC1]

p33796519_10360220118_Generic.zip [TNS1]

$ unzip -o p31241365_1036_Linux-x86-64.zip

Archive: p31241365_1036_Linux-x86-64.zip

inflating: CW7X.jar

inflating: patch-catalog_27153.xml

inflating: README.txt

$ unzip -o p33796519_10360220118_Generic.zip

Archive: p33796519_10360220118_Generic.zip

inflating: README.txt

inflating: patch-catalog_27832.xml

extracting: TNS1.jar

But three of them are wrapped by other patch numbers:

$ unzip p33528262_R12_GENERIC.zip

Archive: p33528262_R12_GENERIC.zip

extracting: p33494814_1036_Generic.zip

inflating: Readme.txt

Patch 33494814: WLS PATCH SET UPDATE (PSU) 10.3.6.0.220118 [HYG5]

$ unzip -o p33494814_1036_Generic.zip

Archive: p33494814_1036_Generic.zip

extracting: HYG5.jar

inflating: patch-catalog_27794.xml

inflating: README.html

inflating: README.txt

Patch 33570839 (Consolidated Patch) has two patches. See

Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID 1594274.1)

$ unzip p33570839_R12_LINUX.zip

<= It includes a lot of .zip files. Two of them are needed

cp etcc-bundle/LINUX_X86-64/wls/10.3.6.0.220118/p13964737_10360220118_Generic.zip .

cp etcc-bundle/LINUX_X86-64/wls/10.3.6.0.220118/p13845626_10360220118_Generic.zip .

$ unzip -o p13964737_10360220118_Generic.zip

Archive: p13964737_10360220118_Generic.zip

inflating: README.txt

inflating: 7GCA.jar

inflating: patch-catalog_27820.xml

$ unzip -o p13845626_10360220118_Generic.zip

Archive: p13845626_10360220118_Generic.zip

inflating: README.txt

inflating: patch-catalog_27818.xml

inflating: IJC1.jar

In other instances, just copy the files:

$ cp -p p33800106_1036_Generic.zip $FMW_HOME/utils/bsu

$ cp *.jar $FMW_HOME/utils/bsu/cache_dir <= 5 .jar files.

$ cp *.xml $FMW_HOME/utils/bsu/cache_dir

NOTES: .xml file is necessary to avoid error: Encountered unrecognized patch ID: xxxx

$ ls -altr $FMW_HOME/utils/bsu/cache_dir/*.jar

b) Fist, apply patch 33800106 (SMART UPDATE TOOL ENHANCEMENT V5) to bsu

NOTE: After this parch was applied, bsu runs much faster, reducing time from 20 minutes to one minute or so for applying one patch.

$ cd $FMW_HOME/utils/bsu

$ unzip p33800106_1036_Generic.zip

Archive: p33800106_1036_Generic.zip

inflating: bsu_update.bat

inflating: bsu_update.sh

creating: bsu_update/GA/

creating: bsu_update/GA/modules/

inflating: bsu_update/GA/modules/com.bea.cie.comdev_6.1.3.0.jar

inflating: bsu_update/GA/modules/com.bea.cie.patch-client_3.3.0.0.jar

inflating: bsu_update/GA/modules/com.bea.cie.patch-common_3.3.0.0.jar

inflating: bsu_update/GA/modules/com.bea.core.apache.log4j_1.2.13.jar

creating: bsu_update/GA/modules/features/

inflating: bsu_update/GA/modules/features/com.bea.cie.patch-client_3.3.0.0.jar

inflating: bsu_update/GA/modules/features/com.bea.cie.patch-client_3.3.0.0.xml

creating: bsu_update/Patch/

creating: bsu_update/Patch/modules/

inflating: bsu_update/Patch/modules/com.bea.cie.comdev_6.1.3.1.jar

inflating: bsu_update/Patch/modules/com.bea.cie.patch-client_3.3.0.0.jar

inflating: bsu_update/Patch/modules/com.bea.cie.patch-common_3.3.0.0.jar

inflating: bsu_update/Patch/modules/com.bea.core.apache.log4j_1.2.13.jar

creating: bsu_update/Patch/modules/features/

inflating: bsu_update/Patch/modules/features/com.bea.cie.patch-client_3.3.0.0.jar

inflating: bsu_update/Patch/modules/features/com.bea.cie.patch-client_3.3.0.0.xml

inflating: README.txt

$ chmod +x bsu_update.sh

$ bsu.sh -version

Oracle Smart Update. Version: 3.3.0.0

$ ./bsu_update.sh install <== very quick.

Installing Smart Update V5...

Updating bsu modules

Update was successful.

c) Apply 5 WLS patches using bsu.sh

$ ls -altr $FMW_HOME/utils/bsu/cache_dir

$ cd $FMW_HOME/utils/bsu

$ echo $ORACLE_HOME

$RUN_BASE/EBSapps/10.1.2

$ echo $FILE_EDITION

run

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=CW7X -prod_dir=$FMW_HOME/wlserver_10.3

Checking for conflicts..

No conflict(s) detected

Installing Patch ID: CW7X..

Result: Success

Optional:

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=HYG5 -prod_dir=$FMW_HOME/wlserver_10.3

Checking for conflicts..

Conflict(s) detected - resolve conflict condition and execute patch installation again

Conflict condition details follow:

Patch HYG5 is mutually exclusive and cannot coexist with patch(es): YVDZ,1YWL

$ ./bsu.sh -remove -patchlist=YVDZ,1YWL -prod_dir=$FMW_HOME/wlserver_10.3

Checking for conflicts..

No conflict(s) detected

Removing Patch ID: YVDZ..

Result: Success

Removing Patch ID: 1YWL..

Result: Success

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=HYG5 -prod_dir=$FMW_HOME/wlserver_10.3

Checking for conflicts..

No conflict(s) detected

Installing Patch ID: HYG5..

Result: Success

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=IJC1,7GCA -prod_dir=$FMW_HOME/wlserver_10.3

Checking for conflicts..

No conflict(s) detected

Installing Patch ID: IJC1.

Result: Success

Installing Patch ID: 7GCA..

Result: Success

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=TNS1 -prod_dir=$FMW_HOME/wlserver_10.3

Checking for conflicts..............

No conflict(s) detected

Installing Patch ID: TNS1..

Result: Success

$ ./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -view -status=applied

ProductName: WebLogic Server

ProductVersion: 10.3 MP6

Components: WebLogic Server/Core Application Server,WebLogic Server/

Administration Console,WebLogic Server/Configuration Wizard and

Upgrade Framework,WebLogic Server/Web 2.0 HTTP Pub-Sub Server

WebLogic Server/WebLogic SCA,WebLogic Server/WebLogic JDBC

Drivers,WebLogic Server/Third Party JDBC Drivers,WebLogic Server/

WebLogic Server Clients,WebLogic Server/WebLogic Web Server

Plugins,WebLogic Server/UDDI and Xquery Support,WebLogic

Server/Evaluation Database,WebLogic Server/Workshop Code

Completion Support

BEAHome: $FMW_HOME

ProductHome: $FMW_HOME/wlserver_10.3

PatchSystemDir: $FMW_HOME/utils/bsu

PatchDir: $FMW_HOME/patch_wls1036

Profile: Default

DownloadDir: $FMW_HOME/utils/bsu/cache_dir

JavaVersion: 1.6.0_29

JavaVendor: Sun

Patch ID: 32I2

Patch ID: S9MN (14272383)

Patch ID: TTEM (14621810)

Patch ID: 7FC9 (17893334)

Patch ID: S8H6 (20474010)

Patch ID: DI8E (22323006)

Patch ID: DN1F (26795917)

Patch ID: CW7X (31241365)

Patch ID: HYG5 (33494814)

Patch ID: IJC1 (13845626)

Patch ID: 7GCA (13964737)

Patch ID: TNS1 (33796519)

Run checkMTpatch.sh again to confirm all recommended patches are applied.

5. Apply EBS patches (Table 1 & 2)

Patch 33487428 CPU PATCH FOR JAN 2022

Notes: its adgrants.sql is older that $APPL_TOP/admin/adgrants.sql

Patch 33207251:R12.FND.C (included in 33487428)

Patch 33168664:R12.FWK.C

Patch 32980025:R12.AK.C

Patch 33286000:R12.OAM.C

Patch 29631318:R12.OWF.C (already applied)

Patch 32564885:R12.OWF.C

Patch 33625264:R12.OWF.C (replaces 33559216)

Notes: skip below two WF patches, since we did not apply Jan 2021 CPU patch

Patch 32117360:R12.OWF.C

Patch 32460404:R12.OWF.C

Copy and unzip 6 patch files (from $ZIPfolder/EBS_CPU) to all nodes:

$ cd $PATCH_TOP

$ cp -p $ZIPfolder/EBS_CPU/*.zip .

$ ls -altr

unzip p33487428_12.2.0_R12_LINUX.zip

unzip p33168664_R12.FWK.C_R12_GENERIC.zip

unzip p32980025_R12.AK.C_R12_GENERIC.zip

unzip p33286000_R12.OAM.C_R12_GENERIC.zip

unzip p32564885_R12.OWF.C_R12_GENERIC.zip

unzip p33625264_R12.OWF.C_R12_GENERIC.zip

$ echo $ORACLE_HOME

$ echo $FILE_EDITION

run

$ adop phase=apply apply_mode=downtime patches=33487428 workers=8 patchtop=$NE_BASE/EBSapps/patch

<== takes 30 minutes on each node

Post-patch steps (from README):

a) Run autoConfig in RUN file system on all Apps-tie nodes

$ ./adautocfg.sh

b) $ perl $AD_TOP/bin/admkappsutil.pl

c) Run autoConfig on database node

$ adconfig.sh contextfile=$ORACLE_HOME/.../appsutil/$CONTEXT_NAME.xml

Optionally, start Apps services on all nodes for a check-out.

Apply other 5 patches:

$ adop phase=apply apply_mode=downtime patches=33168664,32980025 workers=8 patchtop=/aebsu07/app/AEBSGOLD/fs_ne/EBSapps/patch

$ adop phase=apply apply_mode=downtime patches=33286000,32564885,33625264 workers=8 patchtop=/aebsu07/app/AEBSGOLD/fs_ne/EBSapps/patch

Confirm all 8 patches were applied on 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 (

'33487428',

'33207251',

'33168664',

'32980025',

'33286000',

'29631318',

'32564885',

'33625264'

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

6. Start Apps services

7. Run FS_CLONE and make sure it completes successfully.

$ adop phase=fs_clone