Oracle Scratchpad

October 10, 2009

Bugs – 2

Filed under: Troubleshooting — Jonathan Lewis @ 12:00 pm BST Oct 10,2009

After asking people about their strangest bugs, it’s a little ironic that I should immediately be subject to one. Here’s a transcript (edited slightly for cosmetic reasons) from my laptop shortly after I started up my 10.2.0.3 test database.


SQL> select table_name, index_name, tablespace_name from user_indexes;

TABLE_NAME           INDEX_NAME           TABLESPACE_NAME
-------------------- -------------------- ------------------------------
EMP                  E_PK                 TEST_8K
USERS_NEW            SYS_C003026          TEST_8K
T1                   T1_B1                TEST_2K
T1                   T1_I1                TEST_8K

4 rows selected.

SQL> drop table t1;
drop table t1
           *
ERROR at line 1:
ORA-00600: internal error code, arguments: [15264], [], [], [], [], [], [], []

When I did a quick check on Metalink I found a couple of unlikely references for this error, dating back to 9.2.0.2 and mentioning foreign keys, but there was nothing immediately and obviously relevant.

Since this is only a scratchpad database, and it was only going to take me about 30 minutes to build a new one, I wasn’t going to spend much time trying to figure out what had gone wrong. But I thought I’d try to dropping the indexes before trying to drop the table itself, just to narrow down where the problem might be.


SQL> drop index t1_b1;

Index dropped.

SQL> drop index t1_i1;
drop index t1_i1
           *
ERROR at line 1:
ORA-01418: specified index does not exist

Just a quick thought – maybe there’s a problem with a corrupted index name.


SQL> select index_name, dump(index_name) from user_indexes;

INDEX_NAME
--------------------
DUMP(INDEX_NAME)
----------------------------------------------------------------------
SYS_C003026
Typ=1 Len=11: 83,89,83,95,67,48,48,51,48,50,54

E_PK
Typ=1 Len=4: 69,95,80,75

T1_I1
Typ=1 Len=5: 84,49,95,73,49

3 rows selected.

So no funny characters in the name.
Can we do anything else with the index or table ?


SQL> alter index t1_i1 rebuild;
alter index t1_i1 rebuild
*
ERROR at line 1:
ORA-01418: specified index does not exist

SQL> truncate table t1;
truncate table t1
               *
ERROR at line 1:
ORA-00600: internal error code, arguments: [16410], [10529], [1], [], [], [], []
, []

So we can’t bypass the problem.

Okay – it’s time to find out exactly where the error is occuring in the recursive statements, so enable tracing with bind variable dumping.


SQL> alter session set events '10046 trace name context forever, level 4';

Session altered.

SQL> truncate table t1;

Table truncated.

SQL> drop table t1;

Table dropped.

Don’t you just hate it when the problem goes away when you start looking at it ?!

Note: If this were a production system, I wouldn’t just walk away from the problem and hope for the best. There may still be something corrupt in the data dictionary – but for a scratchpad database … when it breaks, I’ll create a new one.

12 Comments »

  1. Sounds like my good old C programming days.
    You have a bug in your programm, you compile the program with debug options – the bug is away ;)

    Perhaps something similar has happend here? Maybe just a simple array overflow. Now more memory for the 10046 logging is allocated and oracle grateful uses it.

    Comment by Wolfgang — October 10, 2009 @ 3:35 pm BST Oct 10,2009 | Reply

  2. Yeah I know what you mean. What is even more bizarre is when you hit dozens of “unpublished” bugs in a new maintenance pack for Oracle Applications
    and the patch is documented then when you go to find the patch it is not available. I found many new bugs in 12.1.1 release of Oracle E-Business this month.

    I guess thats why we have jobs even though I feel like a guinea pig and that I am actually doing a lot of the QA testing for Oracle Corporation.

    Cheers,
    Ben

    Comment by Ben Prusinski — October 11, 2009 @ 3:31 am BST Oct 11,2009 | Reply

  3. Remembers me of when I programmed PHP.

    Sometimes, when I started debugging, It just started working. Maybe the interpreter just need some hints to know everthing was just fine lol.

    Comment by Daniel Stolf — October 12, 2009 @ 2:09 am BST Oct 12,2009 | Reply

  4. We have experienced similar problems to yourself with the ORA-00600: internal error code, arguments: [15264], [], [], [], [], [], [], [] message.

    I note that from your description you are using Oracle version 10.2.0.3 and so are we.

    The error code commonly occurs on our test systems when we issue DBMS_STATS.GATHER_DATABASE_STATS or a script that will call DBMS_STATS.GATHER_TABLE_STATS. The problem always goes away if the script is re-run. As it is intermittent, and on a test system also we have not devoted any more resources to solving the problem either. Our production systems do not suffer from this issue, we have assumed it is the windows memory management that has caused the issue on our laptops where we have the test databases. Sorry I can’t be more helpful, but thought it was worth logging our observations.

    Comment by Tony Sleight — October 12, 2009 @ 7:42 am BST Oct 12,2009 | Reply

  5. “Don’t you just hate it when the problem goes away when you start looking at it ?!”

    isn’t it a kind of “uncertainty principle for software”:
    debugging or tracing may disturb the behaviour of a bug

    and I think this is unevitable, isn’t it ?

    Comment by Sokrates — October 12, 2009 @ 10:06 am BST Oct 12,2009 | Reply

  6. Maybe it’s not so much a corruption in the data dictionary, but a corruption in the memory structures that say whether you are allowed to see the definition in memory. Perhaps the sequence of events is that an obscure procedure necessary for dealing with the recyclebin is getting pushed out of the library cache, so when someone runs catproc or turns on debugging, the necessary code is brought back into library cache. Maybe shrinking or loading the library cache until 7445 errors start appearing might come up with something reproducible.

    “The most exciting phrase to hear in science, the one that heralds new discoveries, is not ‘Eureka!’ (I’ve found it!), but ‘That’s funny…'” -Isaac Asimov.

    Comment by joel garry — October 12, 2009 @ 5:33 pm BST Oct 12,2009 | Reply

  7. Found this practically now:

    My working schema seams OK, but a select * from user_tab_partitions reveal this table:

    BIN$dcAR3Q2bZE3gRAALXeAgPQ==$0

    WHAT?????

    And if you do something like:

    select * from “BIN$dcAR3Q2bZE3gRAALXeAgPQ==$0″

    IT WORKS!!!!!

    The strange thing? The table is not listed by any of our development tools!

    Strange, ah?

    Bye,
    Antonio

    Comment by lascoltodelvenerdi — October 14, 2009 @ 6:28 am BST Oct 14,2009 | Reply

  8. Here is one of the strangest bug I discoverd:

    $ sqlplus sokrates

    SQL*Plus: Release 10.2.0.1.0 – Production on Fri Oct 16 13:26:24 2009

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Enter password:

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bit Production
    With the Partitioning, OLAP and Data Mining options

    SQL> create table temp(s varchar2(5));

    Table created.

    SQL> insert into temp values(‘abcde’);

    1 row created.

    SQL> insert into temp values(‘afghi’);

    1 row created.

    SQL> insert into temp values(‘xyzpq’);

    1 row created.

    SQL> select * from temp where cast(s as varchar2(1))=’a';

    S
    —–
    a
    a

    SQL> select s from temp where cast(s as varchar2(1))=’a';

    S
    —–
    a
    a

    SQL> select s, length(s) from temp;

    S LENGTH(S)
    —– ———-
    abcde 5
    afghi 5
    xyzpq 5

    SQL> select s, length(s) from temp where cast(s as varchar2(1))=’a';

    S LENGTH(S)
    —– ———-
    a 1
    a 1

    SQL>

    Comment by Sokrates — October 16, 2009 @ 11:28 am BST Oct 16,2009 | Reply

  9. Speaking of bugs. We ran into this one after upgrading to 10.2.0.4. Only reproducable on Oracle eBS databases at 10.2.0.4.

    create table test1 as select object_id,object_name from dba_objects;

    create table test2 as select object_id,object_name from dba_objects where mod(object_id,2)=0;

    insert into test2 select object_id,object_name from dba_objects where mod(object_id,2)=0;

    select count(*)
    from test1 t1
    , (select distinct object_id
    from test2
    ) t2
    where t2.object_id (+) = t1.object_id
    and t1.object_id in (1,2,3);

    COUNT(*)
    ——–
    265642

    select count(*)
    from test1 t1
    , (select distinct object_id
    from test2
    ) t2
    where t1.object_id = t2.object_id (+)
    and t1.object_id in (1,2,3);

    COUNT(*)
    ——–
    2

    Oracle ‘drops’ the first predicate on a left outer join, only in the legacy syntax, when it’s the first predicate and the predicate can not be pushed into the view.

    That’s what makes me enjoy my job :-)

    Comment by Arian — October 19, 2009 @ 8:47 pm BST Oct 19,2009 | Reply

  10. A little update:

    I was hit by the same bug during my demonstrations in the “Oracle Closed World” event. But restarting the database solved it.

    Then it appeared on a client-site pre-production system. I decided to do a metalink search (advanced, all words, for “OERI” and “15264” – the OERI search is for the Oracle Internal Error tag – and found bug 8227243 dating back to Feb 2009.

    We weren’t able to try all the workarounds (like restarting the database), so in the end we renamed the table and indexes as a temporary measure so that we could build a new copy.

    The owner of the table later managed to bypass the problem somehow. I think he moved the table, dropped all constraints on the table, and then found he could drop the indexes and table – but he doesn’t know which bit of this activity was key to working around the issue, so another “Req’d Info Not Avail” report to Oracle.

    Comment by Jonathan Lewis — November 10, 2009 @ 3:36 pm BST Nov 10,2009 | Reply


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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,257 other followers