Oleksandr Denysenko's Blog

27.05.2013

Funny Package Non-Compilation Issue

Filed under: BUG,oracle — Oleksandr Denysenko @ 17:50
Tags: ,

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
About these ads

11 Comments »

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

    Comment by Sayan Malakshinov — 29.05.2013 @ 21:20 | Reply

    • 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

      Comment by odenysenko — 29.05.2013 @ 21:25 | Reply

      • 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

        Comment by Sayan Malakshinov — 29.05.2013 @ 21:39

    • Sayan,
      so how do you fixed mentioned issue ?

      Comment by odenysenko — 29.05.2013 @ 21:42 | Reply

      • Oops, i forgot to point it: we also fixed this with flushing of the shared pool

        Comment by Sayan Malakshinov — 29.05.2013 @ 21:52

  2. is the package body wrapped?

    Comment by doganay — 22.08.2014 @ 09:45 | Reply

    • no, it wasn’t
      it’s custom developed application with source code available

      Comment by odenysenko — 22.08.2014 @ 09:55 | Reply

      • Have you tried after compiling the object?
        SQL> select * from dba_errors;
        SQL> select * from user_errors;

        Comment by doganay — 22.08.2014 @ 10:52

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

    Comment by odenysenko — 22.08.2014 @ 11:31 | Reply

    • have you checked this?
      ORA-600 [17069] During Package Compilation (Doc ID 373315.1)

      Comment by doganay — 22.08.2014 @ 11:47 | Reply

      • 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

        Comment by odenysenko — 22.08.2014 @ 11:51


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 36 other followers

%d bloggers like this: