How To Apply Consolidated Upgrade Patch (CUP American Patch) For E-Business Suite 12.2.x

 Note: We will follow this document for CUP Patch and AMERICAN Patch “Oracle E-Business Suite Release Notes, Release 12.2 (Doc ID 1320300.1)”

Section 3: Instructions for Upgrade Customers

3.1 Apply Consolidated Upgrade Patch and Run 12.2.0 Upgrade (required)

 Source Application Environment and unzip CUP Patch with check RUN File Edition

Step-1

$ source /u01/applmgr/upg/fs1/EBSapps/appl/APPSDELL_upgebs.env

$ echo $FILE_EDITION

run

$ cd /u01/install/patches/apps/cup_patches/

$ unzip -o  p28853097_12.2.0_R12_LINUX.zip

Apply CUP Patch

Step-2 

$ cd /u01/install/patches/apps/cup_patches/28853097

$

$ adpatch preinstall=y

Filename [adpatch.log] : 28853097.log

The default directory is [/u01/install/patches/apps/cup_patches/28853097] :

Please enter the name of your AutoPatch driver file : u28853097.drv

AutoPatch is complete.

AutoPatch may have written informational messages to the file

/u01/applmgr/upg/fs1/EBSapps/appl/admin/DELL/log/28853097.lgi

Errors and warnings are listed in the log file

/u01/applmgr/upg/fs1/EBSapps/appl/admin/DELL/log/28853097.log

and in other log files in the same directory.

[oracle@upgebs 28853097]$

During American Patch Errors & Solution (Fixing) 

Note: Before Apply American Patch we will have fixes all issues as per below Errors.

##### ERROR #####

sqlplus -s APPS/***** @/u01/applmgr/upg/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u01/applmgr/upg/fs1/EBSapps/appl/fnd/12.0.0/patch/115/sql/affdlredef.sql &un_apps &pw_apps'

Connected.

PL/SQL procedure successfully completed.

     dbms_redefinition.start_redef_table(l_applsys_schema,'FND_DOCUMENTS_LONG_TEXT','FND_DOCUMENTS_LONG_TEXTINT',col_mapping);

      *

ERROR at line 106:

ORA-06550: line 106, column 7:

PLS-00201: identifier 'DBMS_REDEFINITION' must be declared

ORA-06550: line 106, column 7:

PL/SQL: Statement ignored

ORA-06550: line 109, column 7: 

#### SOLUTION ####

SQL> conn system/manager

Connected.

SQL> grant execute on dbms_redefinition to applsys;

change table FND_DOCUMENTS_LONG_TEXT datatype long - clob

run adgrant.sql applsys and apps

run utlrp.sql via sqlplus sys / as sysdba

##### ERROR #####

(This error was not occurring

Connecting to AP......Unable to connect. 

AutoPatch error: The following ORACLE error: 

ORA-28000: the account is locked   occurred while executing the SQL statement:  CONNECT AP/*****

#### SOLUTION ####

select username,ACCOUNT_STATUS,PROFILE from dba_users where username='APPS';

select resource_name, limit from dba_profiles

where resource_type = 'PASSWORD' and profile = 'DEFAULT';

SQL> conn system/manager

Connected.

SQL> alter user ap account unlock;

User altered.

SQL> conn ap/ap

Connected.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@upgebs admin]$

##### ERROR #####

Uploading from the data file /u01/applmgr/upg/fs1/EBSapps/appl/ast/12.0.0/patch/115/import/US/astprofs.ldt

Altering database NLS_LANGUAGE environment to AMERICAN

Dumping from LCT/LDT files (/u01/applmgr/upg/fs1/EBSapps/appl/fnd/12.0.0/patch/115/import/afscprof.lct(120.14), /u01/applmgr/upg/fs1/EBSapps/appl/ast/12.0.0/patch/115/import/US/astprofs.ldt) to staging tables

Dumping LCT file /u01/applmgr/upg/fs1/EBSapps/appl/fnd/12.0.0/patch/115/import/afscprof.lct(120.14) into FND_SEED_STAGE_CONFIG

Dumping LDT file /u01/applmgr/upg/fs1/EBSapps/appl/ast/12.0.0/patch/115/import/US/astprofs.ldt into FND_SEED_STAGE_ENTITY

Dumped the batch (PROFILE AST_SCRIPTING_SERVER , PROFILE AST_WRAPUP_CALL_STATS_END_DATE ) into FND_SEED_STAGE_ENTITY

Uploading from staging tables

  Error loading seed data for PROFILE:  PROFILE_NAME = AST_WRAPUP_CALL_STATS_START_DATE,  ORA-04091: table APPLSYS.FND_PROFILE_OPTIONS is mutating, trigger/function may not see it

ORA-06512: at "APPS.FND_PROFILE", line 177

ORA-06512: at "APPS.FND_PROFILE", line 1716

ORA-06512: at "APPS.ALR_FND_PROFILE_OPTIONS_IAR", line 1

ORA-04088: error during execution of trigger 'APPS.ALR_FND_PROFILE_OPTIONS_IAR'

ORA-06512: at "APPS.FND_PROFILE_OPTIONS_PKG", line 46

ORA-06512: at "APPS.FND_PROFILE_OPTIONS_PKG", line 1238

ORA-01403: no data found

  Error loading seed data for PROFILE:  PROFILE_NAME = AST_WRAPUP_CALL_STATS_END_DATE,  ORA-04091: table APPLSYS.FND_PROFILE_OPTIONS is mutating, trigger/function may not see it

ORA-06512: at "APPS.FND_PROFILE", line 177

ORA-06512: at "APPS.FND_PROFILE", line 1716

ORA-06512: at "APPS.ALR_FND_PROFILE_OPTIONS_IAR", line 1

ORA-04088: error during execution of trigger 'APPS.ALR_FND_PROFILE_OPTIONS_IAR'

ORA-06512: at "APPS.FND_PROFILE_OPTIONS_PKG", line 46

ORA-06512: at "APPS.FND_PROFILE_OPTIONS_PKG", line 1238

ORA-01403: no data found

Concurrent request completed

Current system time is Mon Feb 10 23:00:44 2020

##### SOLUTION #####

ORA-04091: Table APPLSYS.FND_PROFILE_OPTION_VALUES Is Mutating, Trigger/function May Not See

It Error While Running ADOP Prepare Phase from Clone (Doc ID 2481349.1)

[oracle@upgebs log]$ sqlplus apps/apps

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Feb 11 09:34:12 2020

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> Alter TRIGGER APPS.ALR_FND_PROFILE_OPTIONS_IAR disable;

Trigger altered.

SQL>

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

ERROR (its would be take lots of time during American patch we have applying another patch p20655260_R12.OWF.C_R12_GENERIC.zip)

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

 4  Run       AutoPatch R120 pl  wfdspifx.sql                Restarted

sqlplus -s APPS/APPS @/u01/applmgr/upg/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u01/applmgr/upg/fs1/EBSapps/appl/fnd/12.0.0/patch/115/sql/wfdspifx.sql'

Connected.

PL/SQL procedure successfully completed.

###### SOLUTION ######

E:\upgrade_abc_1213_122x\patches\apps\Pre_patch_owf

p20655260_R12.OWF.C_R12_GENERIC

How To Reduce The Size Of Tables WF_LOCAL_ROLES, WF_LOCAL_USER_ROLES, And WF_USER_ROLE_ASSIGNMENTS ? (Doc ID 1454205.1)

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

The size of tables wf_local_roles, wf_local_user_roles, and wf_user_role_assignment can be reduced by purging adhoc roles,

and their assignments for expired roles This can be done through the use of either of the following API's:

wf_purge.directory(<end_date>, <orig_system>)

wf_purge.AdHocDirectory(<end_date>)

The API's will purge all roles that past the end_date and with no outstanding notification, and it also checks

the ORIGINAL_RECIPIENT as well as the RECIPIENT_ROLE just in case a 'Delegate' happened, the original owner will not get purged.

Even though the naming convention implies that wf_purge.directory allows purge of roles from different orig_system,

but the API has a hard-coded condition that the roles to be purged are those with orig_system set to WF_LOCAL_ROLES,

or WF_LOCAL_USERS, and partition_id set to zero, to ensure that only expired adhoc roles are purged. So, either of them can be used.

This limitation is an intended design to ensure historical data exists of previous assignments of non-adhoc roles.

To execute the API, please run the following to purge all roles that were expired till current date:

sqlplus apps/<apps_password>

exec WF_PURGE.directory(sysdate);

commit;

 To identify the records that are valid for purging, please use the following query:

select local.NAME, local.ORIG_SYSTEM, local.ORIG_SYSTEM_ID,

          local.USER_FLAG

    from  WF_LOCAL_ROLES local

    where PARTITION_ID = 0

    and   ORIG_SYSTEM in ('WF_LOCAL_ROLES', 'WF_LOCAL_USERS')

    and    EXPIRATION_DATE <= sysdate

    and not exists

          (select NULL

           from   WF_ROLE_HIERARCHIES

           where  SUPER_NAME = local.NAME

           or     SUB_NAME   = local.NAME)

    and not exists

          (select NULL

           from   WF_USER_ROLE_ASSIGNMENTS

           where  USER_NAME = local.NAME

           and     RELATIONSHIP_ID <> -1)

    and not exists

          (select NULL

             from WF_NOTIFICATIONS wn

            where wn.RECIPIENT_ROLE = local.NAME

               or wn.ORIGINAL_RECIPIENT = local.NAME)

    and not exists

          (select NULL

             from WF_ITEMS wi

            where wi.OWNER_ROLE = local.NAME);

The tables affected by the API are as follows:

WF_LOCAL_USER_ROLES

WF_USER_ROLE_ASSIGNMENTS

WF_LOCAL_ROLES

###### Extra Steps ######

[oracle@upgebs trace]$ cd /u01/applmgr/upg/fs1/EBSapps/appl/fnd/12.0.0/patch/115/sql/

[oracle@upgebs sql]$ ls wfdspifx.sql

wfdspifx.sql

[oracle@upgebs sql]$ mv wfdspifx.sql wfdspifx.sql_org

[oracle@upgebs sql]$ pwd

/u01/applmgr/upg/fs1/EBSapps/appl/fnd/12.0.0/patch/115/sql

File "wfdspifx.sql" copy from

windows directory=E:\upgrade_abc_1213_122x\patches\apps\Pre_patch_owf\p20655260_R12.OWF.C_R12_GENERIC\20655260\fnd\patch\115\sql

to fs1 directory=/u01/applmgr/upg/fs1/EBSapps/appl/fnd/12.0.0/patch/115/sql

[oracle@upgebs sql]$ ls wfdspifx.sql

wfdspifx.sql

[oracle@upgebs sql]$ ls wfdspifx.sql*

wfdspifx.sql  wfdspifx.sql_org

[oracle@upgebs sql]$

###### ERROR ######

sqlplus -s APPS/***** @/u01/applmgr/upg/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u01/applmgr/upg/fs1/EBSapps/appl/xla/12.0.0/patch/115/sql/xlaaxuap.sql &un_ax &batchsize'

Connected.

PL/SQL procedure successfully completed.

                AX_DOCUMENT_STATUSES AD,

                *

ERROR at line 549:

ORA-06550: line 549, column 3:

PL/SQL: ORA-00942: table or view does not exist

ORA-06550: line 268, column 9:

PL/SQL: SQL Statement ignored

###### SOLUTION ######

R12 AP/XLA: xlaaxuap.sql Failed With ORA-00942: Table or View Does Not Exist During Upgrade From R12.1.3 To R12.2 (Doc ID 2166763.1)

SQL> conn apps/apps

Connected.

SQL> set lines 250 pages 250

SQL> col OWNER format a20

SQL> col SUBOBJECT_NAME format a20

SQL> col EDITION_NAME format a20

SQL> col EDITION_NAME format a10

SQL> col OBJECT_NAME format a25

SQL> select * from dba_objects where object_name like 'AX_DOCUMENT_STATUSES%';

OWNER      OBJECT_NAME               SUBOBJECT_NAME        OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE             CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NA SHARING       E O

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

AX         AX_DOCUMENT_STATUSES_U1                             48915          48915 INDEX                   14-NOV-10 14-NOV-10 2010-11-14:10:27:04 VALID   N N N          4            NONE        N

AX         AX_DOCUMENT_STATUSES                                48914          48914 TABLE                   14-NOV-10 17-MAY-13 2010-11-14:10:27:04 VALID   N N N          1            NONE        N

SQL> create synonym AX_DOCUMENT_STATUSES for ax.AX_DOCUMENT_STATUSES ;

Synonym created.

SQL>

Apply American Patch (Upgrade Path)

Merge American Upgrade Patch

Step-1

$ cd $AU_TOP/patch/115/driver

$ ls

augenmsg.drvx  ausstats.drvx  u10124646.drv  u10201000.drv

$ admrgpch -d . -preinstall -master u10124646.drv

Executing the merge of the patch drivers

 -- Processing file: /u01/applmgr/upg/fs1/EBSapps/appl/admin/DELL/preinstall/u28853097.drv

 -- Done processing file: /u01/applmgr/upg/fs1/EBSapps/appl/admin/DELL/preinstall/u28853097.drv

 -- Processing file: u10124646.drv

 -- Done processing file: u10124646.drv

  2 unified drivers merged.

Patch merge completed successfully

Please check the log file at ./admrgpch.log.

$ ls

admrgpch.log  augenmsg.drvx  ausstats.drvx  u10124646.drv  u10201000.drv  u_merged.drv

                                                    Apply American Upgrade Patch

Step-2 

$ adpatch options=nocopyportion,nogenerateportion

Filename [adpatch.log] : 10201000.log

Enter the password for your 'SYSTEM' ORACLE schema: 

Enter the ORACLE password of Application Object Library [APPS] :

The default directory is [/u01/applmgr/upg/fs1/EBSapps/appl/au/12.0.0/patch/115/driver] :

Please enter the name of your AutoPatch driver file : u_merged.drv

sqlplus -s APPS/***** @/u01/applmgr/upg/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/u01/applmgr/upg/fs1/EBSapps/appl/ad/12.0.0/sql/adtpurge.sql 10 1000'

Done purging timing information for prior sessions.

AutoPatch is complete.

AutoPatch may have written informational messages to the file

/u01/applmgr/upg/fs1/EBSapps/appl/admin/DELL/log/adpatch.lgi

Errors and warnings are listed in the log file

/u01/applmgr/upg/fs1/EBSapps/appl/admin/DELL/log/adpatch.log

and in other log files in the same directory.

[oracle@upgebs driver]$

AMERICAN PATCH Completed.

Note: You can apply this document its alternate of CUP Patch

R12.1 and 12.2 Oracle E-Business Suite Preinstall Patches Report (Doc ID 1448102.2)