Table of Contents
Enginatics always stays up to date with the latest technology. So right after the availability of Oracle E-Business Suite 12.2.10 was announced, our team was eager to upgrade our EBS Demo 12.2.9 instance to the latest release, so anyone can test Blitz Report™ on the most recent software. Blitz Report™ is the fastest reporting solution for Oracle E-Business Suite. Steps that we have taken to perform the upgrade are presented below.
Applying prerequisite patches
References
Oracle E-Business Suite Release 12.2.10 Readme (Doc ID 2495027.1)
Oracle E-Business Suite 12.2.10 Now Available
Upgrade Database to 11.2.0.4 or higher
Our EBS 12.2.9 instance already uses Oracle Database 19c. So this step will not be covered. Besides database upgrade alone is worth a dedicated article. Please refer to upgrade instructions for your specific Oracle Database version on MOS.
Apply Required Database Patches
Determine latest database PSU
Latest patch at the moment of this research was 31326362 Combo OJVM Release Update 19.8.0.0.200714 and Database Release Update 19.8.0.0.200714
It includes two patches:
31281355 - Database Release Update 19.8.0.0.200714 31219897 - Oracle JavaVM Component Release Update 19.8.0.0.200714 |
Download and unzip latest OPatch
Before patches application OPatch always should be updated to avoid unexpected issues.
[oracle@r1229a patches]$ cd $ORACLE_HOME [oracle@r1229a 19c]$ mv OPatch/ OPatch_bkp [oracle@r1229a 19c]$ cp ~/patches/p6880880_121010_Linux-x86-64.zip . [oracle@r1229a 19c]$ unzip p6880880_121010_Linux-x86-64.zip |
Determine patches conflicts
If any conflicts are faced, please refer to MOS Documents to resolve them. Alternatively, My Oracle Support Patch Conflict Checker Tool can be used or Oracle Service Request can be created.
$ cd 31326362/31281355 $ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ |
Prereq “checkConflictAgainstOHWithDetail” passed.
$ cd 31326362/31219897 $ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ |
Prereq “checkConflictAgainstOHWithDetail” passed.
Shutdown apps and db services
$ . /u01/install/APPS/EBSapps.env run $ cd $ADMIN_SCRIPTS_HOME $ ./adstpall.sh $ . /u01/install/APPS/19c/CDBVIS_demo.env $ echo "shu immediate"|sqlplus '/as sysdba' $ lsnrctl stop $ORACLE_SID |
Apply patches with OPatch
Start screen to save session in case of network interruptions. If screen utility is not available on your server, I recommend to install it. It’s a very handy tool.
$ screen -S patching $ cd 31281355 $ opatch apply $ egrep -i "error|fail|ORA-|ac-|rc-" /u01/install/APPS/12.1.0/cfgtoollogs/opatch/opatch2018-10-11_10-21-18AM_1.log |
$ cd ../31219897 $ opatch apply $ egrep -i "error|fail|ORA-|ac-|rc-" /u01/install/APPS/12.1.0/cfgtoollogs/opatch/opatch2018-10-11_10-23-02AM_1.log |
Load Modified SQL Files into the Database
Datapatch can be run once for both OJVM and DB PSU in upgrade mode. The below instructions are applicable for a multitenant database.
SQL> sqlplus '/as sysdba' SQL> startup upgrade SQL> alter pluggable database all open upgrade; SQL> quit; $ cd $ORACLE_HOME/OPatch $ ./datapatch -verbose SQL> sqlplus '/as sysdba' SQL> shutdown immediate SQL> startup SQL> alter pluggable database all open; |
For a standalone database please use the following instructions
SQL> sqlplus '/as sysdba' SQL> startup upgrade $ cd $ORACLE_HOME/OPatch $ ./datapatch -verbose SQL> sqlplus '/as sysdba' SQL> shutdown immediate; SQL> startup |
Recompile invalid objects
The following command can be used to recompile invalid objects in all containers in a multitenant database as per the following link.
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl --n 1 --e -l /tmp --b utlrp --d $ORACLE_HOME/rdbms/admin utlrp.sql |
For a standalone database please use the following instructions
$ cd $ORACLE_HOME/rdbms/admin $ sqlplus '/as sysdba' SQL> @utlrp.sql |
Apply latest WLS PSU to patch fs
Start a new patching cycle
For convenience passwords can be assigned to variables, so they don’t have to be provided manually.
$ read -s appspass $ read -s systempass $ read -s wlspass |
Start new patching cycle (WLS admin server will be started automatically on run fs)
{ echo $appspass; echo $systempass; echo $wlspass; }|adop phase=prepare |
Possible error:
Statement Handler Error Code: 4063 Statement Handler Error Message: ORA-04063: package body "APPS.AD_ZD_ADOP" has errors (DBD ERROR: OCIStmtExecute) Reference of statement-handler is valid, not attempting database reconnect. Failed to execute SQL statement: select AD_ZD_ADOP.GET_INVALID_NODES() from dual Error Message: ORA-04063: package body "APPS.AD_ZD_ADOP" has errors (DBD ERROR: OCIStmtExecute) [UNEXPECTED]Error determining whether this is a multi-node instance Statement Handler Error Code: 4063 Statement Handler Error Message: ORA-04063: package body "APPS.AD_ZD_ADOP" has errors (DBD ERROR: OCIStmtExecute) Reference of statement-handler is valid, not attempting database reconnect. Failed to execute SQL statement: select ad_zd_adop.validate_session_id('') from dual Error Message: ORA-04063: package body "APPS.AD_ZD_ADOP" has errors (DBD ERROR: OCIStmtExecute) [STATEMENT] Please run adopscanlog utility, using the command |
Solution per Doc ID 2175698.1:
$ sqlplus '/as sysdba' SQL>grant execute on SYS.DBMS_METADATA_UTIL to apps; SQL>alter package APPS.AD_ZD_ADOP compile body; SQL>exit; |
Remove conflicting patches applied on top of previous WLS PSU:
{ $ . /u01/install/APPS/EBSapps.env patch $ cd $FMW_HOME/utils/bsu $ $FMW_HOME/utils/bsu/bsu.sh -remove -patchlist=IJG4,9UBP,X1NW,YHJK,XNBA,IIYG -prod_dir=$FMW_HOME/wlserver_10.3 $ $FMW_HOME/utils/bsu/bsu.sh -remove -patchlist=MXLE -prod_dir=$FMW_HOME/wlserver_10.3 } |
Apply Patch 31178492: WLS PATCH SET UPDATE 10.3.6.0.200714 (I37G) to patch fs
$ . ~/EBSapps.env patch $ cp I37G.jar $FMW_HOME/utils/bsu/cache_dir/ $ cp patch-catalog_27147.xml $FMW_HOME/utils/bsu/cache_dir/ $ cd $FMW_HOME/utils/bsu $ $FMW_HOME/utils/bsu/bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=I37G -prod_dir=$FMW_HOME/wlserver_10.3 |
Check Weblogic version after patch application
$ . $FMW_HOME/wlserver_10.3/server/bin/setWLSEnv.sh $ java weblogic.version WebLogic Server 10.3.6.0.200714 PSU Patch for BUG31178492 Fri Jun 5 20:42:30 PDT 2020 |
Apply DB ETCC patches
Run ETCC report on DB tier
Run the latest version of the EBS Technology Codelevel Checker via Patch 17537119. ETCC patches are required as a pre-requisite for latest AD and TXK RUP patches.
$ cd /tmp/patches/patches/ETCC $ ./checkDBpatch.sh |
List of patches to be applied:
p31596956_198000DBRU_Linux-x86-64.zip p29802382_198000DBRU_Generic.zip p29867728_198000DBRU_Linux-x86-64.zip p30621255_198000DBRU_Linux-x86-64.zip p31113249_198000DBRU_Linux-x86-64.zip |
All above patches are contained in 31381618 – EBS RELEASE 12.2 CONSOLIDATED DATABASE FIXES FOR JUL 2020
Shutdown database instance, identify and rollback conflicting patches.
$ echo "shu immediate"|sqlplus '/as sysdba' $ lsnrctl stop $ORACLE_SID $ cd [patch number] $ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ { opatch rollback -id -silent } |
Apply patches
Required patches list:
p31596956_198000DBRU_Linux-x86-64.zip p29802382_198000DBRU_Generic.zip p29867728_198000DBRU_Linux-x86-64.zip p30621255_198000DBRU_Linux-x86-64.zip p31113249_198000DBRU_Linux-x86-64.zip |
For all above patches commands to apply are:
$ unzip 'p*zip' $ cd [patch number] $ opatch apply $ egrep -i "error|fail|ORA-|ac-|rc-" |
Post installation tasks
Run datapatch and recompile invalid objects
$ lsnrctl start $ORACLE_SID $ cd $ORACLE_HOME/OPatch $ echo "startup"|sqlplus '/as sysdba' $ ./datapatch -verbose |
Recompilation instructions for a multitenant database
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl --n 1 --e -l /tmp --b utlrp --d $ORACLE_HOME/rdbms/admin utlrp.sql |
Recompilation instructions for a standalone database
SQL>@?/rdbms/admin/utlrp.sql |
Re-run ETCC report to ensure, that all patches are applied now
[oracle@apps patches]$ . ~/EBSDB_database.env [oracle@apps patches]$ ./checkDBpatch.sh >>All the required one-off bugfixes are present in Database ORACLE_HOME. |
Set Database Parameters
Parameters are needed to be adjusted as follows.
For a database version 12.1.0.2 only
optimizer_adaptive_features=false |
For Oracle Database 11.2.0.4, Oracle Database 12.1.0.2, and Oracle Database 19c
“_disable_actualization_for_grant”=true |
Commands to setup the above parameters are:
SQL> alter system set "_disable_actualization_for_grant"=true; SQL> alter system set optimizer_adaptive_features=false; |
Apply MT ETCC patches
Run ETCC report on MT tier
Run the latest version of the EBS Technology Codelevel Checker via Patch 17537119. This step is a prerequisite for applying the Latest AD and TXK Delta RUPS.
$ cd /tmp/patches/patches/ETCC [oracle@apps patches]$ ./checkMTpatch.sh ======================================================================= PATCH RECOMMENDATION SUMMARY ======================================================================= One or more products have bugfixes missing. The default patch recommendations to install these missing bugfixes are: ------------------------------------------------------------------------------- Oracle Fusion Middleware (FMW) - Web Tier 11.1.1.9.0 ------------------------------------------------------------------------------- Patch 31304503 - Filename: p31304503_111190_Linux-x86-64.zip Patch 23716938 - Filename: p23716938_111190_Generic.zip Patch 31047338 - Filename: p31047338_111190_Linux-x86-64.zip ------------------------------------------------------------------------------- Oracle Fusion Middleware (FMW) - oracle_common 11.1.1.9.0 ------------------------------------------------------------------------------- Patch 30368663 - Filename: p30368663_111190_Generic.zip ------------------------------------------------------------------------------- Oracle WebLogic Server (WLS) 10.3.6.0.200714 ------------------------------------------------------------------------------- Patch 13845626 [SU Patch [DTN2]] - Filename: p13845626_10360200714_Generic.zip Apply the required patches and rerun this script. +-----------------------------------------------------------------------------+ A consolidated zip file with the required application tier patches is available on My Oracle Support via: Patch 31381605 - EBS RELEASE 12.2 CONSOLIDATED FMW FIXES FOR JUL 2020 |
Apply MT ETCC patches
Follow patch recommendation summary and apply patches. Patches below are tab-formatted showing dependencies. All pre-requisite patches should be applied before a dependent patch.
Oracle Fusion Middleware (FMW) – Web Tier 11.1.1.9.0
[-]31304503 [-]23716938 [-]31047338 |
Apply to patch fs Oracle Home
$ . ~/EBSapps.env patch $ . $FMW_HOME/SetWebtier.env #Verify that opatch is running from right OH $ which opatch $ echo $ORACLE_HOME |
Installation instructions:
$ cd [patch number] $ opatch apply $ egrep -i "error|warning|fail|ora-|ac-|rc-" /u01/install/APPS/fs2/FMW_Home/webtier/cfgtoollogs/opatch/[opatch logfile name].log |
Oracle Fusion Middleware (FMW) – oracle_common 11.1.1.9.0
[-]6880880 latest opatch 111000 [-]30368663 Extract latest opatch |
$ . ~/EBSapps.env patch $ . $FMW_HOME/SetOracleCommon.env $ cd $ORACLE_HOME $ mv OPatch/ OPatch_bkp $ cp ~/patches/p6880880_111000_Linux-x86-64.zip . $ unzip p6880880_111000_Linux-x86-64.zip $ rm -f p6880880_111000_Linux-x86-64.zip |
Apply to patch fs Oracle Home
$ . ~/EBSapps.env patch $ . $FMW_HOME/SetOracleCommon.env #Verify that opatch is running from right OH $ which opatch $ echo $ORACLE_HOME $ cd 30368663/oui $ opatch apply |
Check logfiles for errors:
egrep -i "error|warning|fail|ora-|ac-|rc-" \ /u01/install/APPS/fs2/FMW_Home/oracle_common/cfgtoollogs/opatch/27120730_Aug_26_2019_15_39_00/[opatch logfile name].log |
Oracle WebLogic Server (WLS) 10.3.6.0.200714
Patch 13845626 [SU Patch [DTN2]] - Filename: p13845626_10360200714_Generic.zip |
Increase memory allocation to prevent bsu fail
vi /u01/install/APPS/fs1/FMW_Home/utils/bsu/bsu.sh |
Change from
MEM_ARGS=”-Xms512m -Xmx1024m -XX:+UseParallelGC” |
to
MEM_ARGS=”-Xms1024m -Xmx2048m -XX:+UseParallelGC” |
Apply patches
. ~/EBSapps.env patch $ cp DTN2.jar patch-catalog_27158.xml $FMW_HOME/utils/bsu/cache_dir/ $ cd $FMW_HOME/utils/bsu $ $FMW_HOME/utils/bsu/bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=DTN2 -prod_dir=$FMW_HOME/wlserver_10.3 |
Re-run ETCC report to confirm that now all required patches present.
Apply patch environment as all above patches were installed to patch fs.
[oracle@apps ETCC]$ . ~/EBSapps.env patch ./checkMTpatch.sh |
All required one-offs are confirmed as present.
Apply Consolidated Seed Table Upgrade
This step is applicable only for new installation customers and customers upgrading from Oracle E-Business Suite Release 12.2.2 to Release 12.2.10. Skipping this step as we have 12.2.9 installation.
Apply the Latest AD and TXK Delta RUPS
Patch research
You can refer to the following post to check if a EBS patch is applied.
[-]30628681 R12.AD.C.Delta.12 [-]30735865 R12.TXK.C.Delta.12 |
Pre-steps
Execute adgrants script by following the instructions in the readme of Patch 30628681:
1. Create $ORACLE_HOME/appsutil/admin on the database server.
2. Compare the version of adgrants.sql(UNIX) in $APPL_TOP/admin to that in patch directory.
$ grep '$Header' $ORACLE_HOME/appsutil/admin/adgrants.sql REM $Header: adgrants.sql 120.67.12020000.60 2018/04/26 02:32:13 jwsmith ship $ $ grep '$Header' /backup/patches/EBS12.2/ADTKX/30628681/admin/adgrants.sql REM $Header: adgrants.sql 120.67.12020000.69 2020/03/20 00:56:38 jwsmith ship $ |
3. Copy the higher version of adgrants.sql (UNIX) to $ORACLE_HOME/appsutil/admin.
$ cp -iv /backup/patches/EBS12.2/ADTKX/30628681/admin/adgrants.sql /u01/install/APPS/19c/appsutil/admin |
4. Set the environment to point to ORACLE_HOME on the database server.
$ . ~/EBSDB_demo.env |
For a multitenant database source a CDB environment file and set ORACLE_PDB_SID variable
$ . ~/CDBVIS_demo.env $ export ORACLE_PDB_SID=EBSDB |
5. Use SQL*Plus to run the script:
$ sqlplus /nolog SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql APPS |
Got warnings:
2019/08/27 07:28:14 453 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE WARNING ORA-00942: table or view does not exist, SQL: grant SELECT on "DBMS_UPG_STATUS$" to "APPS" 2019/08/27 07:28:14 453 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE WARNING ORA-00942: table or view does not exist, SQL: grant SELECT on "DBMS_UPG_STATUS$" to "SYSTEM" with grant option 2019/08/27 07:28:15 453 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE WARNING ORA-04042: procedure, function, package, or package body does not exist, SQL: grant EXECUTE on "DBMS_SCHEMA_COPY" to "APPS" 2019/08/27 07:28:15 453 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE WARNING ORA-04042: procedure, function, package, or package body does not exist, SQL: grant EXECUTE on "DBMS_SCHEMA_COPY" to "SYSTEM" with grant option 2019/08/27 07:28:15 453 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE WARNING ORA-00942: table or view does not exist, SQL: grant WRITE on "DIRECTORY FND_DIAG_DIR" to "APPS" 2019/08/27 07:28:15 453 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE WARNING ORA-00942: table or view does not exist, SQL: grant READ on "DIRECTORY FND_DIAG_DIR" to "APPS" |
Solution:
Per Doc ID 2507343.1 those messages can be ignored.
Apply patches
read -s appspass read -s systempass read -s wlspass . ~/EBSapps.env run echo $appspass|adop -status -detail |
Apply R12.AD.C.Delta.12 along with NLS and critical AD patches with “merge=yes” option:
{ echo $appspass; echo $systempass; echo $wlspass; }|adop phase=apply patches=30628681_ZHS:u30628681.drv,30628681:u30628681.drv merge=yes patchtop=/backup/patches/12210_upgrade/EBS/ |
Apply R12.TXK.C.Delta.12 along with NLS and critical TXK patches with “merge=yes” option:
{ echo $appspass; echo $systempass; echo $wlspass; }|adop phase=apply patches=30735865_ZHS:u30735865.drv,30735865:u30735865.drv merge=yes patchtop=/backup/patches/12210_upgrade/EBS adopscanlog -latest=yes egrep -i "error|warning|fail|ora-|ac-|rc-" /u01/install/APPS/fs_ne/EBSapps/log/adop/68/20190827_081313/apply/apps/ADOP_MRG_20190827_1566886413/log/[adop log name].log |
Update Database Tier to Latest Code
Will perform that step after all EBS patches are applied and fs cutover completed.
Apply Oracle E-Business Suite 12.2.10 Release Update Pack
Finally after applying all pre-requisite patches upgrade to 12.2.10 can be performed. For our instance Path B — Existing Customers (Release 12.2.9) should be followed.
Apply patches with adop
{ echo $appspass; echo $systempass; echo $wlspass; }|adop phase=apply patches=30399999_ZHS:u30399999.drv,30399999:u30399999.drv merge=yes patchtop=/backup/patches/12210_upgrade/EBS |
Apply Oracle E-Business Suite Release 12.2.10 Online Help along with NLS
{ echo $appspass; echo $systempass; echo $wlspass; }|adop phase=apply patches=30399996_ZHS:u30399996.drv,30399996:u30399996.drv merge=yes patchtop=/backup/patches/12210_upgrade/EBS |
Check logfiles for errors
adopscanlog -latest=yes egrep -i "error|warning|fail|ora-|ac-|rc-" /u01/install/APPS/fs_ne/EBSapps/log/adop/68/20190827_090009/apply/apps/28840850/log/[adop_log_file].log |
Finish adop patching cycle
$ { echo $appspass; echo $systempass; echo $wlspass; }| adop phase=finalize $ { echo $appspass; echo $systempass; echo $wlspass; }| adop phase=cutover $ . ~/EBSapps.env run $ { echo $appspass; echo $systempass; echo $wlspass; }|adop phase=cleanup $ { echo $appspass; echo $systempass; echo $wlspass; }|adop phase=fs_clone |
Post-steps
Compile invalid objects
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl --n 1 --e -l /tmp --b utlrp --d $ORACLE_HOME/rdbms/admin utlrp.sql |
Verify security configurations (required)
The administrator must log in to Oracle E-Business Suite using the local login page (http(s)://[host]:[port]/OA_HTML/AppsLocalLogin.jsp) to navigate to the console and unlock the system.
Update Database Tier to Latest Code
1. Execute the admkappsutil.pl utility to create the appsutil.zip file in $INST_TOP/admin/out.
$ perl $AD_TOP/bin/admkappsutil.pl |
2. On the database tier (as the ORACLE user):
Source the environment for ORACLE_HOME.
$ cd $ORACLE_HOME $ . $ORACLE_HOME/$CONTEXT_NAME.env |
3. Uncompress appsutil.zip, under $ORACLE_HOME.
$ cd $ORACLE_HOME $ unzip -o appsutil.zip |
4. Run AutoConfig on $ORACLE_HOME.
$ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh $ egrep -i “error|warning|fail|ora-|ac-|rc-” /u01/install/APPS/12.1.0/appsutil/log/EBSDB_apps/10121547/adconfig.log |
5. Run AutoConfig on the run file system. Run AutoConfig on all application tier nodes (as the APPLMGR user) by executing the applicable command:
$ sh $INST_TOP/admin/scripts/adautocfg.sh |
Healthcheck
Restart application tier services and verify that following checks are passed:
– EBS login works.
– Forms open
– Number of target and actual concurrent processes are equal.
– Test concurrent requests successfully completed. Example of such requests: “CP PLSQL Regression Test”, “Active Users”
– Other components depending on instance-specific configuration. For example: workflow mailer.
Picture 1: Oracle EBS 12.2.10 home page
Picture 2: Oracle EBS 12.2.10 Administer Concurrent Managers form
Picture 3: Oracle EBS 12.2.10 Concurrent Requests form
Conclusion
Please keep in mind that every EBS upgrade is unique due to different software, hardware versions, customizations. So it is better not to use above instructions on production environment. But I hope that this article will help you to understand EBS upgrade process and prepare your own project.
Hi!
You removed Remove conflicting patches applied on top of previous WLS PSU + applied PSU on patch filesystem , outside of the prepape cycle..
Why?
Can you pls explain that ?
Thx,Laurel
Hi Laurel
It was a mistake and thank you for pointing it out – I corrected my article
Regards,
Alex
Hi Alex!
Thx for fixing :)
If I understood correctly you did all MTT+AD TXK patches+12.2.10 patchset in 1 adop cycle…
Affraid that alot of things can get wrong in 1 cycle and difficult to troubleshoot maybe..
What do you think if dividing as this is also notes are written.( AD TXK Note and separate upgrade note)
MTT+AD TXK deltas ( 1 cycle) and then 12.2.10 cycle… ?
and I am in process of taking 12.2.11 patchset :)
Thx!
Laurel
Hi Laurel
I didn’t face any issue applying all the patches in once cycle
I was too lazy to split patching in multiple cycles. Snapshots of my virtual machines help to rollback quickly in case of any issue.
Anyway – that’s what test instances for. If something goes wrong, yes you can split patching before going to the Prod
Regards,
Alex
Good article,
great work
https://oracleappsdba.com
Thank you for your feedback!