Today I was contacted by one of our customers with very non-typical issue on their 10.2.0.4 database. They have added new column to one table at the middle of the day and now they are not able to compile some of invalidated packages.
Now I’m clarifying what does it mean ‘NOT ABLE TO COMPILE‘:
ALTER PACKAGE PKG_ERP_MAIN COMPILE; Package altered. SHOW ERR No errors. ALTER PACKAGE PKG_ERP_MAIN COMPILE BODY; Package body altered. SHOW ERR No errors. select object_type,status from dba_objects where object_name='PKG_ERP_MAIN'; OBJECT_TYPE STATUS ------------------- ------- PACKAGE VALID PACKAGE BODY INVALID
Funny! Isn’t ?
All of this was accomplished by next errors in alert.log:
Errors in file /oracle/admin/ERP/bdump/erp_s003_5185704.trc: ORA-00600: internal error code, arguments: [17069], [0x7000005FECF17A8], [], [], [], [], [], [] Mon May 27 12:59:09 2013 Errors in file /oracle/admin/ERP/bdump/erp_j001_5443704.trc: ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [17069], [0x70000060993F4E0], [], [], [], [], [], [] ORA-06508: PL/SQL: could not find program unit being called: "ERP.PKG_ERP_MAIN" ORA-06512: at line 2 ], [], [], [], [], [], [], []
We have tried to compile with debug and without debug option, using utlrp and utlirp, but nothing worked, more precisely, nothing was changing status of this magic objects.
Later we have received permission to reboot the whole instance, because complete jobs subsystem was non-function and system was quite critical for customer, but just before the shutdown I remembered that Oracle sometimes uses TRIPLE FLUSHING of SHARED POOL, so I have executed next statements that COMPLETELY FIXED mentioned issue:
ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM FLUSH SHARED_POOL; @?/rdbms/admin/utlrp;
Question:
does anyone has any comments/thoughts/suggestions on the topic ‘what it was’ ?
References:
Oleksandr Denysenko's Blog 
I had a similar case, but not exactly the same: after compiling a new version of the package, when we called function from this package, the old version still was executed. But I know why it was so: it was just a bug of fine grained dependency tracking with latest improvements, that appeared in the 11.2: http://docs.oracle.com/cd/E18283_01/server.112/e17128/chapter1_2.htm#FEATURENO09126
Sayan,
thank for pointing on similar issue,
but are you sure it was because of fine grained dependency tracking ?
this feature was introduced to restrict dependent objects invalidation…
Do you have particular BUG number ?
PS:it looks-like an edition-based redefinition issue
We did not create SR, because we see that they are not in a hurry to fix other our latest SR’s, i think it’s because of the soon release of 12c.
And we don’t use edition-based redefinition, so there is “ora-base” edition only in database
Sayan,
so how do you fixed mentioned issue ?
Oops, i forgot to point it: we also fixed this with flushing of the shared pool
is the package body wrapped?
no, it wasn’t
it’s custom developed application with source code available
Have you tried after compiling the object?
SQL> select * from dba_errors;
SQL> select * from user_errors;
doganay,
I didn’t remenber that I had checked $_ERRORS
but the main questions of this post were about
1) why ALTER PACKAGE PKG_ERP_MAIN COMPILE;
finished as “Package altered” when actually where where ORA-00600 [17069] in alert.log
2) how TRIPLE FLASHING SHARED_POOL could help ?
have you checked this?
ORA-600 [17069] During Package Compilation (Doc ID 373315.1)
it was the time when critical productin ERP system wasn’t working properly…
so there wasn’t time to detailed analysis
i have included mentioned document in a Reference section
Hi. Thank You for sharing this problem. I was facing today the same issue when downgrade from 11XE to 10.2.0.4. Package dependend from another package via dblink cannot been compiled and throwed ora-600. After restatt database everything compiled succesfuly.