I was all set to write a shell script that will parse the patch log file and arrive at top time consuming sqls that will help me know the time taken by the sqls for the upgrades. After constantly watching the Maintenance Pack ( 3480000 ) session for 30+ hours. The following lines of the autopatch session caught my attention.
-- Clip --
sqlplus -s APPS/***** @/xxxx/xxxx/xxxxappl/ad/11.5.0/admin/sql/adtimrpt.sql 5578 adt05578
A job timing report has been generated for the current session.
You should check the file
/xxxx/xxxx/xxxxappl/admin/XXXXXX/out/adt05578.lst
for details.
-- End Clip --
Out of curiosity I opened the file "adt05578.lst", I saw the information that I was exactly looking for.
-- Clip --
Job Timing Report for AutoPatch session 5578 14-DEC-2007 19:37
Summary page 1
Jobs that ran successfully on the first try : 96048
Jobs Failed, deferred, then run successfully : 35
Jobs Failed, restarted, then run successfully : 3
Jobs Failed and Skipped : 0
Total number of jobs : 96086
^L
Job Timing Report for AutoPatch session 5578 14-DEC-2007 19:37
Top 100 Time Consuming Jobs (out of 96086 total jobs) page 1
Elapsed
Task Time Wrk Start Time
Num Prod Job (HH:MM:SS Phase Id (Mon DD YYYY HH:MI:SS)
----- ------ ------------------ ---------- ------------ ---- -----------------------
0 inv inviusi1.sql 3:25:18 dat+10 16 Dec 13 2007 21:28:57
0 inv inviusi1.sql 3:25:15 dat+10 10 Dec 13 2007 21:28:57
0 inv inviusi1.sql 3:25:15 dat+10 9 Dec 13 2007 21:28:59
0 inv inviusi1.sql 3:25:13 dat+10 18 Dec 13 2007 21:28:57
0 inv inviusi1.sql 3:25:08 dat+10 3 Dec 13 2007 21:28:59
0 inv inviusi1.sql 3:25:08 dat+10 13 Dec 13 2007 21:29:06
0 inv inviusi1.sql 3:25:07 dat+10 8 Dec 13 2007 21:28:57
0 inv inviusi1.sql 3:24:58 dat+10 19 Dec 13 2007 21:29:17
0 inv inviusi1.sql 3:24:57 dat+10 27 Dec 13 2007 21:29:01
0 inv inviusi1.sql 3:24:57 dat+10 6 Dec 13 2007 21:29:18
0 inv inviusi1.sql 3:24:56 dat+10 12 Dec 13 2007 21:28:58
-- End Clip --
Summary section that this log shows is important and can be a source of truth for auditing purposes.
Friday, December 14, 2007
Autopatch Timing Report. Useful information that helps you to plan your Production Downtime.
Tuesday, December 4, 2007
adpcpcmp.pls - Takes an hour while applying any patch after upgrading to 11.5.10.2
Any simple problem after the upgrade that is not resolved will have long term maintenance related issues in production.
It is very important to watch the alert log while applying Oracle Applications patch as it gives wealth of information. As a matter of practice I watch the alert log while applying a patch and noticed that Invalid's Compilation stage of adpatch performed by "adpcpcmp.pls" was taking 60 mins. The alert log watch of mine caught the following error.
-- Clip --
Wed June 5 11:55:00 2007
Waited too long for library cache load lock. More info in file /xxxx/xxxx/udump/orcl_ora_7064.trc.
-- End Clip --
The trace file shows the following object
-- Clip --
LIBRARY OBJECT HANDLE: handle=3b3a2f458
name=TESTDB.MY_TEST_TABLE@db_link_name
hash=7e342709
-- End Clip --
Thats all was needed for me to cut down the compilation time from 60 mins to 10 mins. The host referenced in SID used by the above database link was not reachable.
So, watching the alert log while applying Oracle Apps patches pays off !!.
Enjoy !!
Thursday, October 4, 2007
Worflow Background Process errors with ORA-00600 internal error code, arguments: [4414]
Workflow Background Process concurrent request fails with the following error message when the request is submitted with Order Management Item type.
Symptom:
ORA-00600: internal error code, arguments: [4414], [12226], [1], [12226], [1], [], [],
ORA-01403: no data found
Workflow Background Process concurrent request is a Workflow Background Engine. Workflow Backedground Process concurrent request needs to be scheduled at regular intervals as it processes deferred activities and timed out activities.
Generally the 'Workflow Background Process' fails with the following error after upgrading to Oracle Applications 11.5.10.2 and if the database is upgraded from 8.1.7.4 to 9i.
What does "ORA-600 [4414] " followed by an "ORA-01403: no data found" mean ?
If a queue has messages enqueued for rule based subscribers prior to the upgrade, then dequeueing these messages on the upgraded database results in an "ORA-600 [4414]" followed by an "ORA-01403: no data found" error.
Solution:
sqlplus "/ as sysdba"
SQL> exec sys.dbms_prvtaqis.upgrade_rulesub_msgs;
The package "
Wednesday, October 3, 2007
Package 'CSI_ITEM_INSTANCE_PVT_W' Invalid after 11.5.10.2 Upgrade
Symptom:
If the existing .pls in your environment has the following version & you have just upgraded to Oracle Apps 11.5.10.2, then read further.
$ strings -a ./patch/115/sql/csiviiws.pls | grep -i Header
REM $Header: csiviiws.pls 115.4.1159.2 2006/03/10 10:04:55 abhgupta ship $
/* $Header: csiviiws.pls 115.4.1159.2 2006/03/10 10:04:55 abhgupta ship $ */
procedure construct_inst_header_rec(p_inst_id NUMBER
SQL> alter package apps.CSI_ITEM_INSTANCE_PVT_W compile body ;
Warning: Package Body altered with compilation errors.
SQL> show error;
Errors for PACKAGE BODY APPS.CSI_ITEM_INSTANCE_PVT_W:
LINE/COL ERROR
-------- -----------------------------------------------------------------
997/5 PLS-00306: wrong number or types of arguments in call to
'GET_PARENT_SORT_ORDER'
997/5 PL/SQL: Statement ignored
2855/5 PL/SQL: Statement ignored
2855/22 PLS-00302: component 'VERSION_LABEL' must be declared
2856/5 PL/SQL: Statement ignored
2856/22 PLS-00302: component 'VERSION_LABEL_MEANING' must be declared
2998/5 PL/SQL: Statement ignored
2998/33 PLS-00302: component 'VERSION_LABEL' must be declared
2999/5 PL/SQL: Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
2999/33 PLS-00302: component 'VERSION_LABEL_MEANING' must be declared
3279/5 PL/SQL: Statement ignored
3279/22 PLS-00302: component 'VERSION_LABEL' must be declared
3280/5 PL/SQL: Statement ignored
3280/22 PLS-00302: component 'VERSION_LABEL_MEANING' must be declared
3420/5 PL/SQL: Statement ignored
3420/33 PLS-00302: component 'VERSION_LABEL' must be declared
3421/5 PL/SQL: Statement ignored
3421/33 PLS-00302: component 'VERSION_LABEL_MEANING' must be declared
3568/5 PLS-00306: wrong number or types of arguments in call to
'ROSETTA_TABLE_COPY_IN_P22'
LINE/COL ERROR
-------- -----------------------------------------------------------------
3568/5 PL/SQL: Statement ignored
Solution:
Apply the patch:6264601. The patch:6264601 might make CSE_PROJ_ITEM_IN_SRV_PKG / CSE_PROJ_TRANSFER_PKG invalid as well. If at all these packages go invalid. No need to worry. Follow the Metalink Note:403993.1 to fix CSE_PROJ_ITEM_IN_SRV_PKG / CSE_PROJ_TRANSFER_PKG.
