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)
