Oraclue

Oracle internals, debugging and undocumented features

When you do not have time to wait for Oracle patch?

 
I got call from developer telling me  that he cannot run update on very small  table with (less than 50 rows)

Every time he runs statement his session got disconnected .From the experience with my folks that tells me that there is possible Oracle bug…
if session hang or slow that’s different but get disconnected every time  is different.. T
here are few known bugs with AUDITING  when parameter audit_trail=DB_EXTENDED and you can quickly see it in trace file ..

.
UPDATE test SET …
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 25510
Session ID: 170 Serial number: 122
but this was ORA-07445.. or exactly
ORA-07445: exception encountered: core dump [kntxslt()+3931] [SIGSEGV] [ADDR:0x0] [PC:0x4F189FD] [SI_KERNEL(general_protection)]
Alert log clearly shows it.And there are core dumps related …

Quick check on  meatalink . Nothing and nothing under ORA-600/7445 lookup tool…Nothing on google either.

Only on Dennis blog description of the kntxslt function:

* Function kntxslt

May call kntgsv (0x40)
May call ksdpec (0x7E9A)
May call ksdpec (0x2842)
May call ksdwrf (“kntxslt()\n”)
May call kntdslm (…)
May call kxsWorkHeap (ds:kxscio_)
May call kghalf (ds:ksmgpp_, ?, ?, 1, 0, “kntx.1”)
May call lstprintf (…)
May call lxsulen (…)
May call ksdpec (0x2AE3)
May call ksdwrf (…)
May call ksdwrf (offset asc_8007808)
May call kprbope (?, offset kntxauga, offset kntxfuga, ?, 6, …)
May call kprbprs (?, ?, ?, ?, ?, ?, 1)
May call kxsfreg (offset kntxcln, ?, 1)
May call kghfre (ds:ksmgpp_, ?, ?, 0x2000, “kntx.1”)
May call kprbbnd (?, 0x11, …)
May call kprbbnd (?, 0x11, ?, 0x17, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
May call kprbbnd (?, 0x11, ?, 2, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
May call kprbbnd (?, 0x11, ?, 0x0B, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
May call kprball (…)
May call kgesic0 (?, ?, 0x42CB)
May call kprbbad (?, 0x11, ?, ?, ?, 1, …)
May call kgegec (ds:ksmgpp_, 1, …)
May call kserbc2 (0x2F40, …)
May call kgerse (ds:ksmgpp_)
May call kgedec (ds:ksmgpp_, 1, …)
Here is link for more info:                                    

http://conus.info/oracle/oracle-10.2.0.4-linux/kntx.txt

I don not have that much time to dig all these calls…I would like to do it but developers are still waiting..
What’s next?  Open Service request and wait to hear from Oracle and hope that they have patch for it or try to fix it on your own.

Since this is development and I like to sharp my skills  I decided to fix it without involving Oracle..

So I started guessing .. What we have ? Memory , Disks, CPU …

Memory:

Started with flushing shared pool and buffer cache to eliminate possible soft memory corruption ..

alter system flush shared_pool;

alter system flusch buffer_cache;
even quickly bounced instance since this is development but errors was still there…
Focus on Disks or physical structures:
No solution yet.. Then I rebuild table and indexes .. Move them arround …  and still nothing..

alter table test move tablespace test_data;

alter index pk_test rebuild tablespace test_idx;

So finally I created another test table as  select * from < problem_table >  and did update on this new table without a problem..
SQL> create table test1 as select * from test;

Table created.

SQL> UPDATE test1 SET ….

1 row updated.

 

Now I am getting some feedback!

I did not check for corruption ( missed that one ! Always good to validate structure ) ..  and decided to completly to truncate it and re-load data and see what will happen..

I first export original data and then truncated table and imported it back…
SQL> truncate table test;

Table truncated.
Tried update .
UPDATE test SET…

and again..

ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 25510
Session ID: 170 Serial number: 122

ERROR:
ORA-03114: not connected to ORACLE
So,  not a problem with data but whole table is a big mess including related data dictionary entries…
Let me get give new life to table:
SQL> drop table test;

Table dropped.

and import back ..

impdp dumpfile=test.dmp  tables=test

Also compiled all invalid objects caused by this drop if any

@?/rdbsm/admin/utlrp.sql
and run my update again..
SQL> UPDATE test SET …:
1 row updated.

It works!
This was development and I was able to drop table and re-create it but even if it was prod there is a way ( I hope you have flashback enabled )..

Point is do not always wait for Oracle support to get a patch if you can workarround it.

Try few different options.You never know some of them migh work. 🙂

Advertisements

8 responses to “When you do not have time to wait for Oracle patch?

  1. Dennis Yurichev March 22, 2009 at 10:12 pm

    If you could supply me with exact version number and OS, I could look at this address (kntxslt()+3931) and see what can raise exception.

  2. oraclue March 23, 2009 at 1:40 am

    Oracle version 11.1.0.6 on Linux Red Hat release 5.2
    Kernel 2.6.18-53.el5 x86_64

  3. Dennis Yurichev March 23, 2009 at 10:32 pm

    Not sure if this information is useful, but this function may access “sys.cdc_rsid_seq$” table, may raise ORA-12096 error (“error in materialized view log on \”%s\”.\”%s\””), and may check for these events:
    32410 (“internal mv logging event”),
    10306 (“trace materialized view log trigger”),
    10979 (“trace flags for join index implementation”).

  4. luzp March 24, 2009 at 8:08 am

    hi,Dennis Yurichev.How can you do that?

  5. oraclue March 25, 2009 at 5:32 pm

    Thanks Dennis.Let me see if I can reproduce problem but this might be the case because I have MW on some of these tables.
    – Miladin

  6. yong huang March 29, 2009 at 1:28 am

    Miladin, could you check SQL trace to see the last SQL that ran successfully?

  7. oraclue March 31, 2009 at 2:52 am

    Yong

    Unfortunatelly this was development database which I already refreshed with new data so everything is gone.

    Miladin

  8. Yong Huang March 31, 2009 at 7:08 pm

    No problem. According to Note:175982.1, kntx is for “support for replication internal trigger operation”. (“n” is the last character of “replicatioN”.) Maybe dba_internal_triggers would show something interesting.

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

%d bloggers like this: