Oracle Apps Upgrade

11.5.x to 11.5.9 Upgrade Issues

Posted on April 17, 2007. Filed under: Oracle Apps Upgrade |

1159 Maintenance Pack & Consolidated Update Patches 2669606 (MP), 3171663 (CU2)

Issues & Resolutions

Based on the personal experience on performing various Oracle Apps Upgrades, we had listed down issues which we had faced during 11.5.x to 11.5.9 Upgrade. Feel free to put a comment and to add more issues and solutions.

Issue:

FAILED: file oklskhdc.sql on worker  2.
 
@/sid/applmgr/1158/okl/11.5.0/patch/115/sql/ oklskhdc.sql
sqlplus -s APPS/***** @/sid/applmgr/1158/okl/11.5.0/patch/115/sql/ oklskhdc.sql
  l_stmv_rec               OKL_STREAMS_PUB.stmv_rec_type;
                           *
ERROR at line 13:
ORA-06550: line 13, column 28:
PLS-00905: object APPS.OKL_STREAMS_PUB is invalid
ORA-06550: line 13, column 28:
PL/SQL: Item ignored
ORA-06550: line 207, column 9:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 207, column 9:
PL/SQL: Statement ignored
ORA-06550: line 214, column 48:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 208, column 9:
PL/SQL: Statement ignored

Resolution:

Conn apps/xxxxxxx
SQL> alter package OKL_STM_PVT compile;
Package altered.
SQL> alter package OKL_STREAMS_PVT compile;
Package altered.
SQL> alter package OKL_STREAMS_PUB compile;
Package altered


Issue:

FAILED: file egoimctx.sql on worker  1.
 
ATTENTION: Please fix the above failed worker(s) so the manager can continue.
Adwork log has the following errors:
 
HIDEPW: sqlplus -s APPS/xxxx @/talini/applmgr/1158/ego/11.5.0/patch/115/sql/ egoimctx.sql APPS CTXSYS EGO
sqlplus -s APPS/***** @/talini/applmgr/1158/ego/11.5.0/patch/115/sql/egoimctx.sql &un_apps CTXSYS &un_ego
**** Creating EGO_ITEM_TEXT_CTX_PKG package spec
 
PL/SQL procedure successfully completed.
 
**** Creating EGO_ITEM_TEXT_CTX_PKG package body
 
PL/SQL procedure successfully completed.
 
**** Compiling EGO_ITEM_TEXT_CTX_PKG package
DECLARE
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at line 6

Resolution:

Apply patch 3714619


Issue:

FAILED: file egoimdrp.sql on worker  1 for product ego username EGO
sqlplus -s APPS/***** @/tamdbi/applmgr/1157/ego/11.5.0/patch/115/sql/ egoimdrp.sql
   
**** Drop iMT index and truncate text table
DECLARE
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body “APPS.EGO_ITEM_TEXT_PVT” has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 6

Resolution:

1. SQL> select password from dba_users where username like ‘CTXSYS’;
PASSWORD
——————————
F76EF8143CF3A2F4
2. SQL> alter  user ctxsys identified by ctxsys ;
User altered.
3. SQL> select password from dba_users where username like ‘CTXSYS’;
PASSWORD
——————————
24ABAB8B06281B4C
4.  Follow Metalink Note.265414.1to fix the issues of egoimdrp.sql .
5.
SQL> alter user ctxsys identified by values ‘F76EF8143CF3A2F4’;
User altered.
6.
SQL> select password from dba_users where username like ‘CTXSYS’;
PASSWORD
——————————
F76EF8143CF3A2F4
SQL>
6. Re-started the failed worker.
 
Or
1.Connect ctxsys/ctxsys
2.grant all on CTX_OUTPUT to APPS;
3.alter package APPS.EGO_ITEM_TEXT_PVT compile;
4.Restarted the worker.


Issue:

Failure on: invtable.ldt

Resolution:

Apply Patch 2773876
Or
do following:
delete from fnd_index_columns
where application_id = 401
and table_id = 67636
and index_id = 19358
and column_sequence = 0
and column_id < 0;
commit;
Restart worker.


Issue:

Failure on: wmsrule.odf

Resolution:

Apply Patch 3073795


Issue:

Failure on: edwwhtab.ldt and wmstable.ldt

Resolution:

Apply Patch 3139883


Issue:

Updating the master archive with command:
   
adjava -mx128m -nojit oracle.apps.ad.jri.adjcopy @/taveni/applmgr/1158/admin/
<SID>/out/apps.cmd
AutoPatch error:
ERROR Updating the master archive.
AutoPatch error:
Error updating master archive
An error occurred while Updating Oracle Applications Java files.
Continue as if it were successful [No] :
Error in the u2669606.log

Resolution:

Change ADJREOPTS to “-mx256m” from “-mx128m”


Issue:

File:    paupgr27.sql
Error: ORA-20001: Updating pa_projects_all:ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

Resolution:

Per Note:- 243208.1
select INDEX_NAME, STATUS, GLOBAL_STATS, DOMIDX_STATUS, DOMIDX_OPSTATUS
from DBA_INDEXES
where TABLE_NAME = ‘PA_PROJECT_CTX_SEARCH’;
   
INDEX_NAME                     STATUS   GLO DOMIDX_STATU DOMIDX
—————————— ——– — ———— ——
PA_PROJECT_CTX_SEARCH_U1       VALID    NO
PA_PROJECT_CTX_SEARCH_C1       VALID    NO  VALID        FAILED
   
1. Drop the index PA_PROJECT_CTX_SEARCH_C1
2. Run the following to recreate the index:
sqlplus -s APPS/xxx @$PA_TOP/patch/115/sql/ paxincx1.sql (enter PA when
prompted)
   
INDEX_NAME                     STATUS   GLO DOMIDX_STATU DOMIDX
—————————— ——– — ———— ——
PA_PROJECT_CTX_SEARCH_U1       VALID    NO
PA_PROJECT_CTX_SEARCH_C1       VALID    NO  VALID        VALID
 
Restart worker


Issue:

File:    potxns.ldt

Resolution:

See note 160474.1. After implementing this (XML parser), do following:
SQL> select ‘alter package ‘|| OBJECT_NAME||’ compile;’ from user_objects
where status=’INVALID’ and object_name  like ‘ECX%’ and object_type =’PACKAGE’;
 
SQL> select ‘alter package ‘|| OBJECT_NAME||’ compile body;’ from user_objects
where status=’INVALID’ and object_name  like ‘ECX%’
and object_type =’PACKAGE BODY’;
   
SQL> select ‘alter view ‘|| OBJECT_NAME||’ compile;’ from user_objects
where status=’INVALID’ and object_name  like ‘ECX%’ and object_type =’VIEW’;
 
Restart worker


Issue:

File:    egoimdrp.sql
Error:     PLS-00904: insufficient privilege to access object CTXSYS.CTX_OUTPUT

Resolution:

SQL> connect ctxsys/ctxsys
SQL> grant execute on CTX_OUTPUT  to apps;
SQL> grant execute on CTX_OUTPUT  to ego;
Restart worker


Issue:

FAILED: file jtfihdpfpp.ldt

Resolution:

Modify $JTF_TOP/ patch/115/import/ jtfdpf.lct by replacing
UPLOAD JTF_DPF_LGCL_FLOW_PARAMS
open c1(l_flow_id, :PARAMETER_SEQUENCE);
fetch c1 into l_last_updted_by;  <- change l_last_updted_by to l_last_updated_by
close c1;

and

UPLOAD JTF_DPF_LGCL_FLOW_PARAMS
l_flow_id := jtf_dpf_logical_flows_pkg.find_oldest_prefer_owned_by
(:LOGICAL_FLOW_NAME, :LOGICAL_FLOW_APPLICATION_ID, user_id);  <-  replace user_id  by l_user_id
Restart worker


Issue:

FAILED: file pat351.odf   on worker  1.
 FAILED: file pat254.odf   on worker  2.
Reading table PA_TASKS  …
 
===== Table PA_TASKS all columns match.
===== Index PA_TASKS_N1 all columns match.
===== Index PA_TASKS_N10 all columns match.
*******************************************************
The table is missing the index PA_TASKS_N11
or index PA_TASKS_N11 exists on another table.
Create it with the statement:
 
Start time for statement below is: Wed Feb 02 2005 04:40:53
 
CREATE INDEX PA.PA_TASKS_N11 ON PA.PA_TASKS (JOB_BILL_RATE_SCHEDULE_ID)
 LOGGING STORAGE (INITIAL 4K NEXT 4M MINEXTENTS 1 MAXEXTENTS UNLIMITED
 PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE 10 INITRANS 11
 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE PAX
 
Statement executed.
 
The CREATE INDEX statement above failed because there is
another index on the same columns.
Looking for the existing index on the same columns…
 
AD Worker error:
Unable to find existing index on the same columns
 
AD Worker error:
Unable to compare or correct tables or indexes or keys because of the error above

Resolution:

as per bug 3268290
sqlplus apps/<appspwd>
drop index apps.PA_PROJECTS_N10;
drop index apps.PA_TASKS_N11;
drop index apps.PA_TASKS_N12;
drop index apps.PA_PROJECTS_N8 ;
drop index apps.PA_TASKS_N13;
drop index apps.PA_PROJECTS_N9;

Restarted worker


Issue:

FAILED: file b3059755.sql on worker  1.
Start time for statement below is: Fri Feb 04 2005 06:40:32

CREATE index HZ_DQM_SYNC_INTERFACE_N3 on HZ_DQM_SYNC_INTERFACE ( PARTY_ID )
PCTFREE 10 INITRANS 4 MAXTRANS 255 TABLESPACE ARX STORAGE ( INITIAL 4K
NEXT 256K MINEXTENTS 1 MAXEXTENTS 60 PCTINCREASE 0 ) COMPUTE STATISTICS

AD Worker error:
The following ORACLE error:

ORA-01630: max # extents (60) reached in temp segment in tablespace ARX

Resolution:

Update /dhyp2i/applmgr/1158/ar/11.5.0/patch/115/sql/ b3059755.sql,
maxextents for HZ_DQM_SYNC_INTERFACE_N3 and for HZ_DQM_SYNC_INTERFACE_N4

Change maxextents to 500    from 50.
Make a backup of file b3059755.sql
Vi the file and change the value of MAXEXTENTS from 50 to 500.
 
Restart worker


Issue:

Failed on poorgdef.sql

Resolution:

Restart worker


Issue:

Worker failed
FAILED: file cskbmi7.sql  on worker  4.
 
sqlplus -s APPS/***** @/sid/applmgr/1158/cs/11.5.0/patch/115/sql/ cskbmi7.sql
DECLARE
*
ERROR at line 1:
ORA-29881: failed to validate indextype
ORA-06512: at line 64

Resolution:

sqlplus apps/<appspwd>
drop index cs.CS_KB_SETS_TL_N3;
drop index cs.CS_KB_SETS_TL_N5;
@$CS_TOP/patch/115/sql/ cskbctxp.sql apps cs ctxsys      (-> apps username, cs username, ctxsys username)
@$CS_TOP/patch/115/sql/ cskbctxc.sql cs <cspwd> APPS   (-> cs username, cs password, apps username)
Restarted worker


Issue:

11.5.9 MP completed with following error:
AutoPatch error:
ORA-01403: no data found
ORA-06512: at “APPS.AD_VERSION_UTIL”, line 245
ORA-06512: at “APPS.AD_VERSION_UTIL”, line 279
ORA-06512: at “APPS.AD_VERSION_UTIL”, line 347
ORA-06512: at line 1
 
Error in adphistUpdatePatchLevel(CLN, A)
 
AutoPatch error: Error calling adphistUpdateRlseAndPatchLvl().

Resolution:

Apply Patch 2644311
 
Make sure that following files are removed before applying patch 2644311:
 
$APPL_TOP/admin/$TWO_TASK/ applprod.tmp
$APPL_TOP/admin/$TWO_TASK/ applUS.tmp
 
Verification after running 2644311:
select fa.APPLICATION_ID, fp.STATUS, fa.APPLICATION_SHORT_NAME asn
from fnd_product_installations fp, fnd_application fa
where fa.APPLICATION_ID=fp.APPLICATION_ID and fa.APPLICATION_SHORT_NAME in (‘JTF’,’IBC’,’IBE’,’OKL’ ,’AMF’,’BNE’,’CSM’,’DDD’,’DOM’,’EGO’,’PJI’,’PON’,’QOT’,’QRM’,
‘CLN’,’EDR’,’PRP’,’XNB’,’ZFA’,’ZSA’);
 
Should give 20 rows


Issue:

FAILED: file XLIFFImporter.class on worker  1.
FAILED: file XLIFFImporter.class on worker  2.
FAILED: file XLIFFImporter.class on worker  3.
FAILED: file XLIFFImporter.class on worker  4.
FAILED: file XLIFFImporter.class on worker  5.
FAILED: file XLIFFImporter.class on worker  6.
 
Checked the  worker logs
adwork01.log
===============
adjava -mx256m -nojit oracle.jrad.tools.trans.imp.XLIFFImporter
&fullpath:prp:mds/component/webui/ESA:
ComponentCreateTrainButtonBarRN.xlf  -username &un_apps
-password &pw_apps -dbconnection &jdbc_db_addr
 
 
Reading language information from applUS.txt …
  Temporarily resetting CLASSPATH to:
  “/PNATUI/applmgr/1158/ad/11.5.0/java/adjri.zip:/PNATUI/applmgr/common/util
/jre/1.1.8/lib/rt.jar:/PNATUI/applmgr/common/util/jre/1.1.8/lib/i18n.jar:/PNATUI/
applmgr/common/java/appsborg.zip:/PNATUI/product/806/forms60/java:/PNATUI/
applmgr/common/java”
 
  Calling /PNATUI/applmgr/common/util/jre/1.1.8/bin/jre …
Could not import translations in repository  : “/oracle/apps/prp/component/webui/
ComponentCreateTrainButtonBarRN” not found in repository.

Resolution:

After patch completes, verify the failed imports
grep XLIFF adwork*log | sort | uniq | wc -l
Put all the failed imports in a shell script (xliff_imports )and run it
run $HOME/xliff_imports to complete the imports
 
Steps to create the shell script :forvRunning XLIFFImport jobs “manually”
 
grep XLIFF adwork*log > ~/xliff_imports_ja
cd
vi xliff_imports_ja
:%s/adwork..\.log://
:wq
mv xliff_imports_ja xliff_imports_ja_2
grep “^adjava” xliff_imports_ja_2 > xliff_imports_ja
vi xliff_imports_ja
:%s/&un_apps/apps/
:%s/&pw_apps/apps_password/
:%s/&jdbc_db_addr/”(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=auohsnatu01)(PORT=10010))(CONNECT_DATA=(SID=PNATUI)))”
:%s!&fullpath:!$APPL_TOP/!
:%s!:!/11.5.0/!
:%s!:!/!
:wq
chmod 755 xliff_imports_ja
./xliff_imports_ja 2>&1 | tee xliff_imports_ja.log
note: lines starting with “:” are commands for the vi editor.


Issue:

Start of adlibout session
Date/time is  Wed Mar 24 04:42:55 PST 2004
Log file is  /wnappi/applmgr/1156/admin/WNAPPI/log/adlibout.log
Command line arguments are
“filelist=/wnappi/applmgr/1156/admin/WNAPPI/out/ paoapp00.txt”
 adlibout: PLATFORM environment variable not set.
End of adlibout session
Date/time is  Wed Mar 24 04:42:55 PST 2004
**********************************************************
An error occurred while extracting files from library.
Continue as if it were successful [No] :

Resolution:

Source the environment file.
Restart the patch.


Issue:

invimro2.sql (Database portion)
 
ALTER table MTL_ITEM_REVISIONS rename to MTL_ITEM_REVISIONS_B
AD Worker error:
The following ORACLE error:
ORA-26563: renaming this table is not allowed
occurred while executing the SQL statement:
ALTER table MTL_ITEM_REVISIONS rename to MTL_ITEM_REVISIONS_B
Error occurred in file
/wnappi/applmgr/1156/inv/11.5.0/patch/115/sql/ invimro2.sql
with arguments ‘&un_inv &pw_inv &un_apps &pw_apps’.

Resolution:

Drop snapshot log
Recreate Snapshot log using:-
Create snapshot log on inv.mtl_item_revisions tablespace invd storage
(initial 100m next 50m maxextents unlimited pctincrease 0) with rowid,
(inventory_item_id, organization_id)
Restart Failed worker.


Issue:

adexpath(); Unable to get txk_jdbc_zip from Env.

Resolution:

Per note 253920.1 made manual adjustment to
$FND_TOP/admin/template/ adjborg2.txt
and reran adconfig.sh.  Apply 2902755 as prereq to 11.5.9 MP as
permanent solution


Issue:

FAILED: file ECXOXTAS.pls on worker  3 for product ecx username ECX.
ORA-04021: timeout occurred while waiting to lock object APPS.ECX_OXTA_PKG

Resolution:

conn apps/xxxxxx
Connected.
SQL> exec dbms_aqadm.stop_queue (‘ECX_OUTBOUND’);   
PL/SQL procedure successfully completed.
 
After the maintenance pack, we need to run the following….
   
exec dbms_aqadm.start_queue (‘ECX_OUTBOUND’);


Issue:

failed @13k Jobs
 
 
   Uploading FND_PROFILE_OPTION_VALUES 10001 ** Site **
    Error occured for FND_PROFILE_OPTION_VALUES key name LEVEL_VALUE_APP
with value  and parent key   value ICX_DEFAULT_EUL
    A database error occurred:
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at line 66

Resolution:

Run the following SQL statement login as apps user
select * from FND_PROFILE_OPTION_VALUES
where PROFILE_OPTION_ID = 3568
and APPLICATION_ID = 178
and LEVEL_ID = 10001
and LEVEL_VALUE = 0;
   
2.This statment will return 2 rows.
3.Delete one row with the following sql command
   
delete FND_PROFILE_OPTION_VALUES where profile_option_id = 3568
and level_value_application_id is null;


Issue:

asocruls.sql

Resolution:

Modify asocruls.sql
From:
create index &&3..AQ$_ASO_ORDER_FTH_TEMP ON
&&3..AQ$_ASO_ORDER_FEEDBACK_T_H (subscriber#)
nologging parallel pctfree 0;
To:
create index &&3..AQ$_ASO_ORDER_FTH_TEMP ON
&&3..AQ$_ASO_ORDER_FEEDBACK_T_H (subscriber#)
storage ( maxextents unlimited)
nologging parallel pctfree 0;


Issue:

cevw.odf has been running for the last 3.5 hrs

Resolution:

At around 43k jobs, verify that these two view are valid, otherwise compile them…
SQL> alter view apps.CE_222_REVERSAL_V  compile;
View altered.
SQL> alter view apps.CE_REVERSAL_TRANSACTIONS_V compile;
View altered.
SQL>


Issue:

adjava -mx128m -nojit oracle.apps.ad.jri.adjcopy @/dte
rji/applmgr/1158/admin/DTERJI/out/ apps.cmd
AutoPatch error:
ERROR Updating the master archive.
AutoPatch error:
Error updating master archive
An error occurred while Updating Oracle Applications Java files.
Continue as if it were successful [No] :

Resolution:

Modified the ADJREOPTS to 512 in $APPL_TOP/admin/adovars.env & $APPL_TOP/admin/
<SID>_hostanme.xml.
Restarted the patch.


Issue:

FAILED: file pat351.odf   on worker  1.
FAILED: file pat254.odf   on worker  5.

Resolution:

The indexes are supposed to be owned by PA and not APPS, as found above.
Dropped these indexes. Restarted the failed workers
OWNER        INDEX_NAME    
———— ——————————–                       
APPS         PA_TASKS_N11  
APPS         PA_PROJECTS_N10
APPS         PA_TASKS_N12  
APPS         PA_PROJECTS_N8
APPS         PA_TASKS_N13  
APPS         PA_PROJECTS_N9


Issue:

Failure when running wsmcrind.sql:

Resolution:

vi $WSM_TOP/patch/115/sql/ wsmcrind.sql
Change from (maxextents changed from 50 to 200):
CREATE INDEX WIP_ENTITIES_N_TMP
                    ON WIP_ENTITIES ( GEN_OBJECT_ID )
    STORAGE (INITIAL 4K NEXT 100K MINEXTENTS 1 MAXEXTENTS 50 PCTINCREASE 0 FREELISTS 4)
To:
CREATE INDEX WIP_ENTITIES_N_TMP
                    ON WIP_ENTITIES ( GEN_OBJECT_ID )
    STORAGE (INITIAL 4K NEXT 100K MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 4)
Restart worker


Issue:

Failure when running wsmcrind.sql:

Resolution:

vi $WSM_TOP/patch/115/sql/ wsmcrind.sql
Change from (maxextents changed from 50 to 200):
CREATE INDEX WIP_ENTITIES_N_TMP
ON WIP_ENTITIES ( GEN_OBJECT_ID )
STORAGE (INITIAL 4K NEXT 100K MINEXTENTS 1 MAXEXTENTS 50 PCTINCREASE 0 FREELISTS 4)
To:
CREATE INDEX WIP_ENTITIES_N_TMP
ON WIP_ENTITIES ( GEN_OBJECT_ID )
STORAGE (INITIAL 4K NEXT 100K MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 4)
Restart worker

Read Full Post | Make a Comment ( 1 so far )

Liked it here?
Why not try sites on the blogroll...