ERP on DB

Tuesday, January 26, 2021

Apply patches required by checkMTpatch.sh (ETCC)

After rapidwiz completed successfully, the next step is to apply a list of patches reported by checkMTpatch.sh from patch 17537119 (see page 97 of R12.2 Upgrade Guide: Release 12.0 and 12.1 to 12.2, Part No. E73540-10). More details are in Section 4 of Doc ID 1594274.1 (R12.2: Consolidated List of Patches and Technology Bug Fixes).

The purpose is to patch and address issues in software installed by Rapid Install that was released a while ago. Oracle keeps updating patch 17537119, which may report a different list of patches depending on when patch 17537119 was downloaded.

1. Generate ETCC report

Add ". /u02/app/EBSDEV/fs1/EBSapps/appl/APPS*.env" to OS .profile, and start a new OS session. Or,

$ cd ../fs1/EBSapps/appl
$ . ./APPS*.env

$ strings -a checkMTpatch.sh |grep '$Header'
# $Header: checkMTpatch.sh 120.0.12020000.42 2020/08/18 04:16:12 chrhill noship $

$ ./checkMTpatch.sh

It needs APPS password, and creates a log file with the list of patches in it.

2. Apply patches: patching in 5 areas

1) Oracle Forms and Reports 10.1.2.3.0 (3 patches)

Patch 26825525
Patch 25342269

Patch 27491934

$ echo $FILE_EDITION

run
$ echo $ORACLE_HOME

/u02/app/$TWO_TASK/fs1/EBSapps/10.1.2

$ which opatch

~/$TWO_TASK/fs1/EBSapps/10.1.2/OPatch/opatch

$ unzip p26825525_101232_LINUX.zip

$ cd 26825525

$ opatch apply

Oracle Interim Patch Installer version 1.0.0.0.64 … ...

Rolling back patch 8557019 ...

Rolling back patch 18620223 ...

Rolling back patch 19434967 ...

Applying patch 26825525...

$ cd ..

$ unzip p25342269_101232_Generic.zip

$ cd 25342269

$ opatch apply

$ cd ..

$ unzip p27491934_101232_LINUX.zip

$ cd 27491934

$ opatch apply

$ opatch lsinventory

It lists 44 patches in my instance.

2) Oracle Fusion Middleware (FMW) - Web Tier 11.1.1.9.0 (4 patches)

Patch 22288381
Patch 31047338 (OHS 11.1.1.9.0 SPU FOR AprCPU2020, Doc ID 2650675.1)
Patch 31304503

Patch 23716938

$ echo $IAS_ORACLE_HOME
$ export ORACLE_HOME=$IAS_ORACLE_HOME

$ echo $ORACLE_HOME
/u02/app/$TWO_TASK/fs1/FMW_Home/webtier
$ export PATH=$IAS_ORACLE_HOME/OPatch:$PATH

$ echo $PATH

$ which opatch

~/$TWO_TASK/fs1/FMW_Home/webtier/OPatch/opatch

$ opatch lsinventory
Oracle Interim Patch Installer version 11.1.0.12.9 … ...

$ cd ..

$ unzip p22288381_111190_Generic.zip

$ cd 22288381

$ opatch apply

Rolling back interim patch '20922780' ...

Patch 22288381 successfully applied

$ cd ..

$ unzip p31047338_111190_Linux-x86-64.zip

$ cd 31047338

$ opatch apply

Patching component oracle.ohs2, 11.1.1.9.0...

Patch 31047338 successfully applied

$ cd ..

$ unzip p31304503_111190_Linux-x86-64.zip

$ cd 31304503

$ opatch apply

Patch 31304503: Optional component(s) missing : [ oracle.idm.oid, 11.1.1.9.0 ] … …

Is the local system ready for patching? [y|n]
Y
User Responded with: Y
Backing up files...

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

Patching component oracle.rdbms.rsf, 11.1.0.7.0...
Patch 31304503 successfully applied

$ cd ..

$ unzip p23716938_111190_Generic.zip

$ cd 23716938

$ opatch apply

Patching component oracle.opmn, 11.1.1.9.0...

Patch 23716938 successfully applied

3) RSF within FMW Web tier 11.1.0.7.0 (fs1/FMW_Home/webtier, 1 patch)
-------------------------------------------------
$ echo $IAS_ORACLE_HOME
$ export ORACLE_HOME=$IAS_ORACLE_HOME
$ export PATH=$IAS_ORACLE_HOME/OPatch:$PATH

$ echo $ORACLE_HOME
/u02/app/$TWO_TASK/fs1/FMW_Home/webtier (<= same as above 2)! )

$ which opatch

~/$TWO_TASK/fs1/FMW_Home/webtier/OPatch/opatch

$ cd ..

$ unzip p22290164_111070_Linux-x86-64.zip (<= it is a large patch)

$ cd 22290164

$ opatch apply

OPatch detects the Middleware Home as "/u02/app/$TWO_TASK/fs1/FMW_Home"

Applying interim patch '22290164' to OH '/u02/app/$TWO_TASK/fs1/FMW_Home/webtier'
Verifying environment and performing prerequisite checks...
Patch 22290164: Optional component(s) missing : [ oracle.network.listener, 11.1.0.7.0 ] , … …
… …

All checks passed.

Is the local system ready for patching? [y|n]
Y
User Responded with: Y
Backing up files...

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

Patching component oracle.network.rsf, 11.1.0.7.0...

Patching component oracle.xdk.parser.java, 11.1.0.7.0...

Patching component oracle.xdk, 11.1.0.7.0...

Patching component oracle.xdk.rsf, 11.1.0.7.0...

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

Patching component oracle.network.rsf, 11.1.0.7.0...

Patching component oracle.dbjava.ic, 11.1.0.7.0...

Patching component oracle.dbjava.jdbc, 11.1.0.7.0...

Verifying the update...
Patch 22290164 successfully applied
Log file location: /u02/app/$TWO_TASK/fs1/FMW_Home/webtier/cfgtoollogs/opatch/22290164_x/apply2021_x.log

OPatch succeeded.

$ opatch lsinventory

It lists 15 patches in my instance.

4) Oracle Fusion Middleware (FMW) - oracle_common 11.1.1.9.0 (12 patches)
---------------------------------------------------------
$ echo $FMW_HOME
$ export ORACLE_HOME=$FMW_HOME/oracle_common
$ export PATH=$ORACLE_HOME/OPatch:$PATH

$ echo $ORACLE_HOME
/u02/app/$TWO_TASK/fs1/FMW_Home/oracle_common

$ which opatch
~/$TWO_TASK/fs1/FMW_Home/oracle_common/OPatch/opatch

$ unzip p9905685_111190_Generic.zip

$ cd 9905685/oui

$ opatch apply

Patching component oracle.soa.mgmt, 11.1.1.9.0...

$ unzip p17428617_111190_Generic.zip
$ cd 17428617
$ opatch apply

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

$ unzip p21366277_111190_Generic.zip
$ cd 21366277
$ opatch apply

Patching component oracle.as.common.clone, 11.1.1.9.0...

$ unzip p21628307_111190_Generic.zip

$ cd 21628307
$ opatch apply

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

$ unzip p24606362_111190_Generic.zip
$ cd 24606362
$ opatch apply
Patching component oracle.as.common.clone, 11.1.1.9.0...

$ unzip p24843064_111190_Generic.zip
$ cd 24843064/oui

$ opatch apply

Patching component oracle.jrf.adfrt, 11.1.1.9.0...

$ unzip p13544814_111190_Generic.zip
$ cd 13544814
$ opatch apply

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

$ unzip p16089637_111190_Generic.zip

$ cd 16089637
$ opatch apply

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

$ unzip p20141119_111190_Generic.zip
$ cd 20141119
$ opatch apply

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

$ unzip p30368663_111190_Generic.zip
$ cd 30368663/oui

$ opatch apply

Patching component oracle.jrf.adfrt, 11.1.1.9.0...

$ unzip p27212806_111190_Generic.zip

$ cd 27212806
$ opatch apply

Patching component oracle.as.common.clone, 11.1.1.9.0...

$ unzip p28708563_111190_Generic.zip
$ cd 28708563
$ opatch apply

Rolling back interim patch '20484781' …

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

Patching component oracle.as.common.clone, 11.1.1.9.0...

$ opatch lsinventory

It lists 13 patches in my instance.

Exit from this OS session

5) Oracle WebLogic Server (WLS) 10.3.6.0.7
-------------------------------------------------------------------------------
Patch 29633432 [SU Patch [MXLE]: WLS PSU 10.3.6.0.190716]
- Filename: p29633432_1036_Generic.zip

Since Oracle site says 29633432 was originally replaced by patch 30109677, and the most recent replacement for this patch is 32052267 (WLS PATCH SET UPDATE 10.3.6.0.210119), I decided to apply patch 32052267, instead. (Notes on March 2021: Doc 2737201.1 (Table 2) says WLS patch 32052267 is part of R12.2 Jan2021 CPU patch. So, applying it was a good choice as it will save time when applying Jan2021 CPU ).

Start a new OS session (to get the right ORACLE_HOME)

$ echo $TWO_TASK

$ echo $ORACLE_HOME

/u02/app/$TWO_TASK/fs1/EBSapps/10.1.2

$ ls -al $FMW_HOME/wlserver_10.3

$ echo $FILE_EDITION

run

$ cd $FMW_HOME/utils/bsu

$ cp -p bsu.sh bsh.sh_orig

$ vi bsu.sh <== to increase Java cache size

$ diff bsu.sh bsu.sh_orig

< MEM_ARGS="-Xms4096m -Xmx4096m -XX:+UseParallelGC"

---

> MEM_ARGS="-Xms512m -Xmx1024m -XX:+UseParallelGC"

Download p32052267_1036_Generic.zip to WLS folder and apply it:

$ cd $FMW_HOME/utils/bsu/cache_dir

$ unzip p32052267_1036_Generic.zip
Archive: p32052267_1036_Generic.zip
inflating: 1YWL.jar
inflating: patch-catalog_27373.xml
inflating: README.html
inflating: README.txt

$ cd $FMW_HOME/utils/bsu

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

Checking for conflicts............ <= this takes 15 minutes or so.
Conflict(s) detected - resolve conflict condition and execute patch installation again
Conflict condition details follow:
Patch 1YWL is mutually exclusive and cannot coexist with patch(es): YHJK,2GYW,VKXF,FCX7,BLTG,Y5AP,CW9T,1LRI

$ ./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -remove -verbose -patchlist=YHJK

Checking for conflicts.......... <= this takes about 15 minutes.
No conflict(s) detected

Starting removal of Patch ID: YHJK

--- ---
Result: Success

$ ./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -remove -verbose -patchlist=2GYW

$ ./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -remove -verbose -patchlist=VKXF

$ ./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -remove -verbose -patchlist=FCX7
Checking for conflicts..........
Conflict(s) detected - resolve conflict condition and execute patch removal again
Conflict condition details follow:
The selected patch cannot be removed until the following patch(es) are removed first: 1LRI,CW9T,BLTG

./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -remove -verbose -patchlist=BLTG
./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -remove -verbose -patchlist=CW9T
./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -remove -verbose -patchlist=1LRI
./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -remove -verbose -patchlist=FCX7
./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -remove -verbose -patchlist=Y5AP

- Apply it again (after all removals)

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=1YWL -prod_dir=$FMW_HOME/wlserver_10.3
Checking for conflicts............
No conflict(s) detected

Installing Patch ID: 1YWL..
Result: Success

- To check the applied WLS patches:

$ ./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:    /u02/app/$TWO_TASK/fs1/FMW_Home
ProductHome:     /u02/app/$TWO_TASK/fs1/FMW_Home/wlserver_10.3
PatchSystemDir:   /u02/app/$TWO_TASK/fs1/FMW_Home/utils/bsu
PatchDir:              /u02/app/$TWO_TASK/fs1/FMW_Home/patch_wls1036
Profile: Default
DownloadDir:      /u02/app/$TWO_TASK/fs1/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:          CIH8 (17431995,17495356)
Patch ID:          7FC9 (17893334)
Patch ID:          S8H6 (20474010)
Patch ID:          YVDZ (13964737)
Patch ID:          1YWL (32052267)

- To determine the application of WebLogic Server PSU

$ . $FMW_HOME/wlserver_10.3/server/bin/setWLSEnv.sh

$ java weblogic.version

WebLogic Server 10.3.6.0.210119 PSU Patch for BUG32052267 Mon Nov 23 07:28:31 UTC 2020
WebLogic Server Temporary Patch for BUG13964737 Fri Dec 20 11:32:08 IST 2013
WebLogic Server Temporary Patch for BUG20474010 Sun Mar 01 17:22:18 IST 2015
WebLogic Server Temporary Patch for BUG17495356 Tue Sep 24 00:23:38 CDT 2013
WebLogic Server Temporary Patch for BUG17495356 Tue Sep 24 00:23:38 CDT 2013
WebLogic Server Temporary Patch for BUG17495356 Tue Sep 24 00:23:38 CDT 2013
WebLogic Server Temporary Patch for BUG17495356 Tue Sep 24 00:23:38 CDT 2013
WebLogic Server Temporary Patch for BUG17495356 Tue Sep 24 00:23:38 CDT 2013
WebLogic Server Temporary Patch for ${CRS} Mon Jul 30 16:45:20 EDT 2012
WebLogic Server Temporary Patch for ${CRS} Mon Jul 30 16:45:20 EDT 2012
WebLogic Server 10.3.6.0 Tue Nov 15 08:52:36 PST 2011 1441050

Use 'weblogic.version -verbose' to get subsystem information

Use 'weblogic.utils.Versions' to get version information for all modules

- To re-run ETCC

$ ./checkMTpatch.sh

Using context file from currently set applications environment:

/u02/app/$TWO_TASK/fs1/inst/apps/<CONTEXT_NAME>/appl/admin/<CONTEXT_NAME>.xml
Starting Application Tier Technology Codelevel Checker

Version: 120.0.12020000.42.
Tue Jan 26 17:35:43 EST 2021
Log file for this session: /u02/app/temp/log/checkMTpatch_463.log

Bugfix XML file version: 120.0.12020000.48
This file will be used for identifying missing bugfixes.

Mapping XML file version: 120.0.12020000.34
This file will be used for mapping bugfixes to patches.

Checking for prerequisite bugfixes in File Edition: run

Connecting to database.
Database connection successful.
The installed AD.B.8 codelevel does not support storing the results in the database.

======================================
Oracle Forms and Reports
======================================
Now examining product Oracle Forms and Reports.
Oracle Home = /u02/app/$TWO_TASK/fs1/EBSapps/10.1.2.
Product version = 10.1.2.3.0.
Checking required bugfixes for Oracle Forms and Reports 10.1.2.3.0.
All required bugfixes are present for Oracle Forms and Reports.

DST ts home /u02/app/$TWO_TASK/fs1/EBSapps/10.1.2.

Installed DST version under $ORACLE_HOME/oracore/zoneinfo is DST 18.

Checking required bugfixes for RSF within Forms 10.1.0.5.0.
All required bugfixes are present for RSF within Forms.

======================================
Oracle Fusion Middleware (FMW) - Web Tier
======================================

Now examining product Oracle Fusion Middleware (FMW) - Web Tier.

Oracle Home = /u02/app/$TWO_TASK/fs1/FMW_Home/webtier.
Product Version = 11.1.1.9.0
Checking required bugfixes for FMW - Web Tier 11.1.1.9.0.
All required bugfixes are present for FMW - Web Tier.

DST ts home /u02/app/$TWO_TASK/fs1/FMW_Home/webtier.

Installed DST version under $ORACLE_HOME/oracore/zoneinfo is DST 18.

Checking required bugfixes for RSF within FMW Web tier 11.1.0.7.0.
All required bugfixes are present for RSF within FMW Web tier.

========================================
Oracle Fusion Middleware (FMW) - oracle_common
========================================
Now examining product Oracle Fusion Middleware (FMW) - oracle_common.

Oracle Home = /u02/app/$TWO_TASK/fs1/FMW_Home/oracle_common.
Product Version = 11.1.1.9.0
Checking required bugfixes for FMW - oracle common 11.1.1.9.0.
All required bugfixes are present for FMW - oracle common.

=======================================
Oracle WebLogic Server (WLS)
=======================================
Now examining product Oracle WebLogic Server (WLS).

Oracle Home = /u02/app/$TWO_TASK/fs1/FMW_Home/wlserver_10.3.
Product Version = 10.3.6.0.210119
Unable to extract list of patches from Oracle WebLogic Server (WLS) for version 10.3.6.0.210119.

Verify XML file is correct and retry.

Seems WLS PSU patch 32052267 is too new for current version of ETCC to recognize it!

6. Notes in March 2021:

After ran a newer checkMTpatch.sh of patch 17537119, it says 3 more patches are missed. So, I applied 3 more WLS patches.

$ strings -a checkMTpatch.sh |grep '$Header'
# $Header: checkMTpatch.sh 120.0.12020000.43 2021/02/11 20:32:12 chrhill noship $

$ ./checkMTpatch.sh

… …

===============================================
Oracle WebLogic Server (WLS)
===============================================
Now examining product Oracle WebLogic Server (WLS).

Oracle Home = $FMW_Home/wlserver_10.3.
Product Version = 10.3.6.0.210119

Note that for Oracle WebLogic Server, patches rather than bugfixes are verified.

Checking required patches for Oracle WebLogic Server (WLS) 10.3.6.0.210119.
Missing Patch ID: 13845626
Missing Patch ID: 22323006
Missing Patch ID: 26795917
The above list shows missing patches for Oracle WebLogic Server.

Download & unzip 3 patch files to $FMW_HOME/utils/bsu/cache_dir folder and then applied them.

p13845626_10360210119_Generic.zip (HJT5.jar)

p22323006_1036_Generic.zip (DI8E.jar)

p26795917_1036_Generic.zip (DN1F.jar)

$ echo $ORACLE_HOME
/u02/app/$TWO_TASK/fs1/EBSapps/10.1.2

$ echo $FILE_EDITION

run

$ cd $FMW_HOME/utils/bsu

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

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

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=DN1F -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 DN1F is mutually exclusive and cannot coexist with patch(es): CIH8

$ ./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -remove -verbose -patchlist=CIH8
$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=DN1F -prod_dir=$FMW_HOME/wlserver_10.3

After all 3 WLS patches are applied successfully, no more patches is needed by ETCC:

$ ./checkMTpatch.sh
… …
================================
Oracle WebLogic Server (WLS)
================================
Now examining product Oracle WebLogic Server (WLS).
Oracle Home = $FMW_HOME/wlserver_10.3.
Product Version = 10.3.6.0.210119
Note that for Oracle WebLogic Server, patches rather than bugfixes are verified.
Checking required patches for Oracle WebLogic Server (WLS) 10.3.6.0.210119.
All required patches are present for Oracle WebLogic Server (WLS).

$ . $FMW_HOME/wlserver_10.3/server/bin/setWLSEnv.sh

$ java weblogic.version

WebLogic Server Temporary Patch for BUG13845626 Tue Jan 05 07:49:28 UTC 2021
WebLogic Server 10.3.6.0.210119 PSU Patch for BUG32052267 Mon Nov 23 07:28:31 UTC 2020
WebLogic Server Temporary Patch for BUG13964737 Fri Dec 20 11:32:08 IST 2013
WebLogic Server Temporary Patch for BUG20474010 Sun Mar 01 17:22:18 IST 2015
WebLogic Server Temporary Patch for ${CRS} Mon Jul 30 16:45:20 EDT 2012
WebLogic Server Temporary Patch for ${CRS} Mon Jul 30 16:45:20 EDT 2012
WebLogic Server 10.3.6.0 Tue Nov 15 08:52:36 PST 2011 1441050

Use 'weblogic.version -verbose' to get subsystem information

Use 'weblogic.utils.Versions' to get version information for all modules

NOTE: in this stage, under the new R12.2 environment, adadmin works and $ADMIN_SCRIPTS_HOME will point to R12.2 scripts (but do not run any of them).

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

Oracle E-Business Suite 12.2 Patching Technology Components Guide (Doc ID 1355068.1)

R12.2 Pre-install patches report (Doc ID 1448102.2) ?

Sunday, October 25, 2020

Pre steps and Rapid Install for creating R12.2 upgrade file system

R12.2 Rapid Install performs two functions during an upgrade. This post is for the first function that specifies the configuration values for Rapid Install to lay down a new file system and installs the new technology stack.

- Pre-steps

1. Make sure OS meets the requirements for installation (See my post). And R12.2 installation needs about additional 200GB diskspace.

$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7.9 (Maipo)

2. Activate all Apps services to only one master node if R12.1 is a multi-node environment.

3. Reduce number of concurrent Managers so that it will start less processes on OS level from starting EBS services during the upgrade.

4. Put some scheduled CM jobs on hold.

5. Disable cron jobs (if any).

- Database preparation (pre-steps in Release 12.1.0.2.0):

1. Collect stats by apps

SQL> conn apps/apps_PWD
SQL> exec apps.fnd_stats.gather_schema_statistics('ALL',estimate_percent=>100);

PL/SQL procedure successfully completed.

<== This takes long time, can do so via concurrent by System Admin.

SQL> SELECT package_name,object_name,argument_name,data_type
FROM all_arguments WHERE (package_name,object_name)
IN (SELECT call_package,call_procedure FROM hr_api_hook_calls)

AND data_type = 'LONG';

Ignore package PSB_HR_POPULATE_DATA_PVT because it is obsolete in R12.2 (Doc ID 1969202.1).

SQL> spool invalid_R12_1.txt

SQL> col owner for a27

SQL> col object_name for a45;

SQL> col object_typr for a27;

SQL> col status for a9;

SQL> set lines 222 pages 123;

SQL> select owner, count(*) from dba_objects where status = 'INVALID'

group by owner order by owner desc;

SQL> select owner,object_type,count(*) from dba_objects where status <> 'VALID'

group by owner,object_type order by owner;

SQL> select owner, substr(object_name,1,30) object_name, object_type, status

from dba_objects

where status = 'INVALID' and owner ='APPS'

order by object_name, object_type, object_name;

SQL> select fnd_web_sec.validate_login('GUEST','ORACLE') valid from dual;
VALID
-------------------------------
Y
If 'N' is returned, then query the error message for more info:
SQL> select fnd_message.get from dual;

2. Create xml file and run autoconfig (if it was run in long time ago).

3. Init parameters (Oracle Doc ID 396009.1 gives recommendations on init parameters). Some of them shall be set to:

ALTER SYSTEM SET job_queue_processes=60 scope=both; (SYSTEM can do it)
ALTER SYSTEM SET aq_tm_processes=1; (default: scope=memory?)
ALTER SYSTEM SET undo_retention=900;
ALTER SYSTEM SET plsql_optimize_level=2;
ALTER SYSTEM SET enable_ddl_logging=FALSE;
ALTER SYSTEM SET sec_case_sensitive_logon=FALSE;
ALTER SYSTEM SET resource_manager_plan=null;
ALTER SYSTEM SET recyclebin=OFF DEFERRED; (it takes effect in new sessions)
ALTER SYSTEM SET "_system_trig_enabled"=TURE; (for online patching enablement)

And, disable unneeded hidden parameters (if see any).

Notes: re-visit them after R12.2 upgrade.

4. Additional steps:

SQL> show user

USER is "SYSTEM"

SQL> show parameter service_names

NAME TYPE VALUE
---------------------------------- ----------- ------------------------------
service_names string EBSDEV

SQL> alter system set service_names='EBSDEV','ebs_patch' scope=both ;

SQL> select SERVICE_ID,NAME from dba_services where name='ebs_patch';
SERVICE_ID NAME
----------------- ------------
6 ebs_patch

SQL> show parameter local_listener

NAME TYPE VALUE
------------------- -------- ------------------------------
local_listener string EBSDEV_LOCAL

(or, SQL> alter system set local_listener='EBSDEV_LOCAL' scope=both;)

Disable AOL Audit Trail (conditional):

SQL> select distinct enabled from dba_audit_policies;

no rows selected

Fixed object and dictionary statistics: about 10 minutes

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

SQL> EXEC dbms_stats.gather_dictionary_stats;

Collect SYS stats as sysdba (SYSTEM can also do it)
SQL> begin
   dbms_stats.gather_schema_stats (
      'SYS',
      options=>'GATHER STALE',
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      method_opt => 'FOR ALL COLUMNS SIZE AUTO',
      cascade => TRUE);
end;

Get the ORACLE_HOME path if necessary:

SQL> var OH varchar2(200);

SQL> EXEC dbms_system.get_env('ORACLE_HOME', :OH) ;

PL/SQL procedure successfully completed.

SQL> PRINT OH

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

/ohome/u01/app/oracle/product/12.0.0.0/db_ebsdev

5. Disable triggers in custom schemas (Seems optional?):

col TRIGGER_NAME for a36;
col TABLE_OWNER for a27;
col TABLE_NAME for a36;
col STATUS for a12;
set lines 333 pages 123;
SQL> select owner,trigger_name,status,table_owner from dba_triggers

where status='ENABLED' and owner not in ('APPS','SYS','MDSYS');

SQL> select owner,trigger_name,status,table_owner from dba_triggers where owner in
(select username from dba_users where default_tablespace not in ('APPS_TS_TX_DATA','SYSAUX'));

SQL> select 'alter trigger '||owner||'.'||trigger_name||' disable;' from dba_triggers

where owner like 'XX%';

Note: not sure how to use $ALR_TOP/patch/115/sql/alrdtrig.sql

6. Important step: modify database profiles to avoid errors during installations (assume APPS uses TRUSTED_ID_NO_EXPIRE profile and Installer creates new users of R12.2, such as GHG, to use DEFAULT profile).

alter profile default limit PASSWORD_VERIFY_FUNCTION null;
alter profile default limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
alter profile TRUSTED_ID_NO_EXPIRE limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
alter profile TRUSTED_ID_NO_EXPIRE limit PASSWORD_REUSE_TIME UNLIMITED;
alter profile TRUSTED_ID_NO_EXPIRE limit PASSWORD_REUSE_MAX UNLIMITED;
alter profile TRUSTED_ID_NO_EXPIRE limit PASSWORD_VERIFY_FUNCTION null;

col profile for a27;
col resource_name for a36;
col limit for a27;
SQL> select profile,resource_name,limit from dba_profiles

where profile in ('DEFAULT','TRUSTED_ID_NO_EXPIRE')
and resource_type = 'PASSWORD' order by 1,2;

PROFILE RESOURCE_NAME LIMIT

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

DEFAULT    FAILED_LOGIN_ATTEMPTS    UNLIMITED
DEFAULT    PASSWORD_GRACE_TIME    7
DEFAULT    PASSWORD_LIFE_TIME 360
DEFAULT    PASSWORD_LOCK_TIME UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX    24
DEFAULT    PASSWORD_REUSE_TIME 365
DEFAULT    PASSWORD_VERIFY_FUNCTION NULL

TRUSTED_ID_NO_EXPIRE FAILED_LOGIN_ATTEMPTS UNLIMITED
TRUSTED_ID_NO_EXPIRE PASSWORD_GRACE_TIME UNLIMITED
TRUSTED_ID_NO_EXPIRE PASSWORD_LIFE_TIME UNLIMITED
TRUSTED_ID_NO_EXPIRE PASSWORD_LOCK_TIME 1
TRUSTED_ID_NO_EXPIRE PASSWORD_REUSE_MAX UNLIMITED
TRUSTED_ID_NO_EXPIRE PASSWORD_REUSE_TIME UNLIMITED
TRUSTED_ID_NO_EXPIRE PASSWORD_VERIFY_FUNCTION NULL

SQL> select profile, resource_name, limit from dba_profiles where profile in

(select profile from dba_users where username = 'APPS')
and resource_type = 'PASSWORD' order by resource_name;

7. ETCC: The EBS Technology Code Level Checker (ETCC) (See Doc ID 2008451.1)

Download patch 17537119, and unzip p17537119_R12_GENERIC.zip to a folder.

DBA can run checkDBpatch.sh, also called DBCC, on database server. It uses opatch to check installed patches in database and may come out about 20+ db patches required. But checkMTpatch.sh will not work on Apps nodes until Rapidwiz completes the installation.

$ ./checkDBpatch.sh contextfile= xxxxx.xml

Using context file from command line argument: xxxxx.xml

Starting Database EBS Technology Codelevel Checker, Version 120.81
Tue Oct 20 12:56:16 EDT 2020
Log file for this session : /u01/db_ebsdev/appsutil/etcc/log/checkDBpatch_65979616.log

Identifying database release.
Database release set to 12.1.0.2.

Connecting to database.
Database connection successful.

Identifying APPS and APPLSYS schema names.
- APPS schema : APPS
- APPLSYS schema : APPLSYS

Checking for DB-ETCC results table.
Creating DB-ETCC results table.
Created the table to store DB-ETCC results.

Checking if InMemory option is enabled.
InMemory option is not enabled in the database.

Bugfix file ./db/onprem/txk_R1220_DB_base_bugs.xml : 120.0.12020000.56
This file will be used for identifying missing bugfixes.

Mapping file ./db/onprem/txk_R1220_DB_mappings.xml : 120.0.12020000.31
This file will be used for mapping bugfixes to patches.

[WARNING] DB-ETCC: Bugfix XML file (./db/onprem/txk_R1220_DB_base_bugs.xml) is more than 30 days old.
Check if a newer version is available in patch 17537119.

Identified RDBMS DST version 19.

Checking Bugfix XML file for 12.1.0.2_PSU

Obtained list of bugfixes to be applied and the list to be rolled back.
Now checking Database ORACLE_HOME.
DB-ETCC is compatible with this opatch version.

Found patch records in the inventory.

Checking Mapping XML file for 12.1.0.2.200714

Missing Bugfix: 8975044 -> Patch 8975044
Missing Bugfix: 14666816 -> Patch 30614876
Missing Bugfix: 15894842 -> Patch 15894842
Missing Bugfix: 18485835 -> Patch 30216977
Missing Bugfix: 18689530 -> Patch 22098146
Missing Bugfix: 18881811 -> Patch 25599890
Missing Bugfix: 19239846 -> Patch 31469752
Missing Bugfix: 19472320 -> Patch 19472320
Missing Bugfix: 19509982 -> Patch 30216977
Missing Bugfix: 20123899 -> Patch 20123899
Missing Bugfix: 20181016 -> Patch 20181016
Missing Bugfix: 20476776 -> Patch 30614876
Missing Bugfix: 20887355 -> Patch 20887355
Missing Bugfix: 21028698 -> Patch 22098146
Missing Bugfix: 21321429 -> Patch 21321429
Missing Bugfix: 21614112 -> Patch 30216977
Missing Bugfix: 21751519 -> Patch 30614876
Missing Bugfix: 21813400 -> Patch 31469752
Missing Bugfix: 21864513 -> Patch 21864513
Missing Bugfix: 21904072 -> Patch 21904072
Missing Bugfix: 21967332 -> Patch 21967332
Missing Bugfix: 22229581 -> Patch 30614876
Missing Bugfix: 22452653 -> Patch 30614876
Missing Bugfix: 22666802 -> Patch 31469752
Missing Bugfix: 22737974 -> Patch 30614876
Missing Bugfix: 23604553 -> Patch 23604553
Missing Bugfix: 23632545 -> Patch 31469752
Missing Bugfix: 23645516 -> Patch 31469752
Missing Bugfix: 25139545 -> Patch 25139545
Missing Bugfix: 25305405 -> Patch 25305405
Missing Bugfix: 25906117 -> Patch 25906117
Missing Bugfix: 25971286 -> Patch 31469752
Missing Bugfix: 26742604 -> Patch 30614876
Missing Bugfix: 26798411 -> Patch 26798411
Missing Bugfix: 29125374 -> Patch 30216977

Generating Patch Recommendation Summary.

========================================================
PATCH RECOMMENDATION SUMMARY
========================================================
The default patch recommendations to install these missing bugfixes are:
-------------------------------------------------------------------------------
Oracle Database Release 12.1.0.2 (PATCHSET UPDATE 12.1.0.2.200714)
-------------------------------------------------------------------------------
Patch 8975044 [12.1.0.2.200414 version]
- Filename: p8975044_12102200414_AIX64-5L.zip

Patch 30614876 [12.1.0.2.171017 version]
- Filename: p30614876_12102171017_AIX64-5L.zip

Patch 15894842 [12.1.0.2.200714 version]
- Filename: p15894842_12102200714_Generic.zip

Patch 30216977 [12.1.0.2.191015 version]
- Filename: p30216977_12102191015_AIX64-5L.zip

Patch 22098146
- Filename: p22098146_121020_AIX64-5L.zip

Patch 25599890
- Filename: p25599890_121020_Generic.zip

Patch 31469752 [12.1.0.2.200714 version]
- Filename: p31469752_12102200714_AIX64-5L.zip

Patch 19472320
- Filename: p19472320_121020_AIX64-5L.zip

Patch 20123899 [12.1.0.2.5 version]
- Filename: p20123899_121025_Generic.zip

Patch 20181016
- Filename: p20181016_121020_AIX64-5L.zip

Patch 20887355
- Filename: p20887355_121020_Generic.zip

Patch 21321429 [12.1.0.2.170117 version]
- Filename: p21321429_12102170117_Generic.zip

Patch 21864513
- Filename: p21864513_121020_AIX64-5L.zip

Patch 21904072
- Filename: p21904072_121020_AIX64-5L.zip

Patch 21967332 [12.1.0.2.160419 version]
- Filename: p21967332_12102160419_AIX64-5L.zip

Patch 23604553 [12.1.0.2.200414 version]
- Filename: p23604553_12102200414_Generic.zip

Patch 25139545 [12.1.0.2.190416 version]
- Filename: p25139545_12102190416_Generic.zip

Patch 25305405 [12.1.0.2.160119 version]
- Filename: p25305405_12102160119_AIX64-5L.zip

Patch 25906117
- Filename: p25906117_121020_AIX64-5L.zip

Patch 26798411 [12.1.0.2.190716 version]
- Filename: p26798411_12102190716_AIX64-5L.zip

Apply the required patches and rerun this script

You should check the patch READMEs for minimum opatch version requirements.
The latest opatch is available from My Oracle Support via Patch 6880880.

+-----------------------------------------------------------------------------+
A consolidated zip file with the required patches for Database release
12.1.0.2.200714 is available on My Oracle Support via:

Patch 31381618 [12.1.0.2.200714 version]
- EBS RELEASE 12.2 CONSOLIDATED DATABASE FIXES FOR JUL 2020

Note: This zip does not include database Release Updates, bundles or PSUs.
+-----------------------------------------------------------------------------+

See Doc ID 1147107.1 for any special instructions for these patches.
Note: Footnotes in Doc ID 1594274.1 also apply to corresponding overlay patches.

Stored Technology Codelevel Checker results in the database EBSDEV successfully.

Finished checking fixes for Oracle Database: Tue Oct 20 12:56:49 EDT 2020

Log file for this session: /u01/db_ebsdev/appsutil/etcc/log/checkDBpatch_65979616.log

DBA has to apply patches reported from checkDBpatch.sh and run the script repeatedly until the list is cleared.

Database patches applied.
ETCC ran successfully
The opatch utility is version 12.2.0.1.21.
DB-ETCC is compatible with this opatch version.
Found patch records in the inventory.
Checking Mapping XML file for 12.1.0.2.200714
All the required one-off bugfixes are present in Database ORACLE_HOME.

8. If database is upgraded to 12c before EBS R12.2 upgrade, complete steps in Doc ID 1524398.1 (Interoperability Notes EBS 12.0 or 12.1 with RDBMS 12cR1).

Below steps might not be executed previously. It is necessary run them now to avoid error from ADZDPREP.sql in applying R12.2 online patch 13543062.

Post DB upgrade steps:
   # add SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 in sqlnet.ora
   #. run scripts:
sqlplus / as sysdba
SQL> @?/rdbms/admin/dbmsxdbschmig.sql
                SQL> @?/rdbms/admin/prvtxdbschmig.plb
                SQL> @?/rdbms/admin/utlrp.sql

- Run Rapid Installer to lay down R12.2.0 software ("Create Upgrade File System")

1. Backup APPS file system and database

2. Make sure R12.1 works and then stop all services

$ ps -ef | grep $LOGNAME
3. If pre-installation failed, you have to delete all related files in /tmp, and folders fs1, fs2, fs_ne. (See Doc ID 2091880.1: 12.2 E-Business Suite - How To Clean Up a Rapid Install Failure and Restart a Fresh Install).
4. If previous installation worked successfully and you want to re-run rapidwiz from the beginning, you have to delete the inventory folder defined in oraInst.loc file in addition to step 2 above to avoid below strange errors. Then, re-create the folder and attach two R12.1 ORACLE_HOMEs to it.

Fatal Error: TXK Install Service

oracle.apps.fnd.txk.config.ProcessStateException: OPatch process failed : Exit=160

make[1]: *** [/path/to/fs2/EBSapps/10.1.2/sysman/lib/libnmemso.so] Error 1

4. Launch the installer

$ vi .profile <== and re-login (NOT to use current R12.1 env file)

$ env <== make sure no Oracle related and strange env values

$ env | grep OPATCH_PLATFORM_ID

Make sure nothing is set for this to avoid Rapidwiz fatal error with patch 6275529 oracle.apps.fnd.txk.config.ProcessStateException (Doc ID 2336585.1)

$ more /etc/oraInst.loc

inventory_loc=/path/to/oraEBSdevInventory

S mv /path/to/oraEBSdevInventory /path/to/oraEBSdevInventory_R12.1

$ mkdir /path/to/oraEBSdevInventory

Notes: R12.1 file folders can be renamed after R12.2.10 is good and up.

$ export DISPLAY=xxx.xx.xxx:0.0 <== assume Exceed is installed on the desktop

$ cd startCD/Disk1/rapidwiz

$ ./rapidwiz

choose "Upgrade to Oracle E-Business Suite Release 12.2.0"

"Create Upgrade File System"

enter 41 & 42 (if database uses port 1562) in port pool

enter database info such as $ORACLE_BASE & $ORACLE_HOME from database node, and

choose "Use Existing Oracle Home (No new Database Home install)"

System check marks shall all be green, before continue to "Next".

The installation GUI stays at 0% (for about 30 minutes), 40% for a while and takes totally about 3 hours. It will post screen messages telling installation log location $INST_TOP/logs.

Configuration file written to: /…/EBSDEV/fs1/inst/apps/<CONTEXT_NAME>/conf_EBSEV.txt

Configuration file written to: /…/EBSDEV/fs2/inst/apps/<CONTEXT_NAME>/conf_EBSDEV.txt
Second File System logfile - /…/EBSDEV/fs2/inst/apps/<CONTEXT_NAME>/logs/10212202.log
First File System logfile - /…/EBSDEV/fs1/inst/apps/<CONTEXT_NAME>/logs/10212202.log

The .log file size shall be about 30k (under /fs1/) and the end of log file has entries (replace R12.2 env variables):

… …

Stopping the Service - /bin/sh $ADMIN_SCRIPTS_HOME/adnodemgrctl.sh stop

... ...

You are running adnodemgrctl.sh version 120.11.12020000.4
Enter the WebLogic Admin password:
Either the Node Manager is not up or the credentials are incorrect.
adnodemgrctl.sh: exiting with status 2
adnodemgrctl.sh: check the logfile $INST_TOP/logs/appl/admin/log/adnodemgrctl.txt for more information ...

stty: standard input: Inappropriate ioctl for device

Upgrade File
-------------
   APPL_TOP environment file passes instantiated variables test:
      File = $APPL_TOP/<CONTEXT_NAME>.env
   ADOVARS environment file passes instantiated variables test:
      File = $APPL_TOP/admin/adovars.env
   APPSCONFIG passes instantiated variables test:
      File = $APPL_TOP/admin/adconfig.txt
DoInstallPanel - Summary Text
The Rapid Install Wizard will now install the following:

If go to Apps Base Directory specified on the installation GUI, 3 new folders are created there:

$ du -sh fs*
28G     fs1
28G     fs2
32K     fs_ne

$ ps -ef | grep $LOGNAME
Nothing shall run.

Choose the RUN file system by run the .env file under /fs1/EBSapps/appl (or /fs2/EBSapps/appl) to set up R12.2 environment for continuing next steps.

$ cd <Apps Base Directory>/fs1/EBSapps/appl

$ . ./APPS<TWO_TASK>_<node_name>.env

$ echo $FILE_EDITION
run

$ echo $APPL_TOP
<Apps Base Directory>/fs1/EBSapps/appl

Now, The installation is completed.