Funny Package Non-Compilation Issue

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:

  • 421711.1 Understanding and Diagnosing ORA-00600 [17069] Errors
  • 373315.1 ORA-600 [17069] During Package Compilation

12 thoughts on “Funny Package Non-Compilation Issue

    • 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

  1. 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 ?

      • 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

  2. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s