Oleksandr Denysenko's Blog

27.05.2013

Funny Package Non-Compilation Issue

Filed under: BUG,oracle — odenysenko @ 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‘:

SYS@orcl> ALTER PACKAGE PKG_ERP_MAIN COMPILE;
Package altered.

SYS@orcl> SHOW ERR
No errors.

SYS@orcl> ALTER PACKAGE PKG_ERP_MAIN COMPILE BODY;
Package body altered.

SYS@orcl> SHOW ERR
No errors.

SYS@orcl> select object_type,status from dba_objects where object_name='PKG_ERP_MAIN';
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        <strong>INVALID</strong>

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’ ?

About these ads

5 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


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 Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: