Re: "ORA-27300: OS system dependent operation:GetThreadTimes failed" Causes Instance Crash 10.2.0.2

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 21 Dec 2006 05:59:47 -0800
Message-ID: <1166709587.619445.219890@80g2000cwy.googlegroups.com>

hpuxrac wrote:
> Charles Hooper wrote:
> > metatech_at_flashmail.com wrote:
> > > Charles Hooper a écrit :
> > >
> > > > The alert log shows this:
> > > > ---------------------------------
> > > > Sat Oct 28 00:00:34 2006
> > > > Process startup failed, error stack:
> > > > Sat Oct 28 00:00:34 2006
> > > > Errors in file
> > > > d:\oracle\product\10.2.0\admin\DB\bdump\vmfg_psp0_2196.trc:
> > > > ORA-27300: OS system dependent operation:GetThreadTimes failed with
> > > > status: 6
> > > > ORA-27301: OS failure message: The handle is invalid.
> > > > ORA-27302: failure occurred at: skgpspawn
> > > >
> > >
> > > See bug fix 5163569
> > > RDBMS: There is a timing window at background process spawn time that
> > > can cause an ORA-00600 [784].
> > > This is fixed in 10.2.0.2.0 Patch 8 (10.2.0.2.8P) 32-Bit Patch 5502226
> > > 64-Bit (Itanium) Patch 5500894 64-Bit (x64) Patch 5500921
> >
> > I installed patch 9 hoping to cure the problem, and now I can crash the
> > database sessions every 40 hours if desired. I opened an SR on
> > Metalink on 28-NOV-06, and have yet to receive a suggestion from
> > Oracle. I came up with a work around 15 days ago.

>

> Do you have a re-creatable test case ( or can you produce one ) so that
> Oracle can reproduce this at will? If so I would submit that and get
> it after a week or two consider getting it bumped up to sev 1.

>

> I just got oracle to admit to a bug fix in the 9208 patchset ( second
> one for this site ) with a service request that's been open since early
> October. The explanation for the delay was that it was assigned to an
> analyst who was ( apparently ) looking for another job and that person
> wasn't motivated to work on it. ( I did have a workaround but still
> ... maybe I shouldn't have let it sit open at sev 2 for so long ). My
> problem though wasn't nearly as severe as a bug that brings down the
> instance.

I tried to generate a re-creatable test case using my test database (located on the same server), but was unsuccessful. The ORA-07445 errors were being triggered by very simple single table SELECT statements. The test case program that I created pounded on the test database with 50,000 of the triggering SQL statement with different constants provided for each SQL statement (CURSOR_SHARING set to FORCE). I then executed the test case program simultaneously using three sessions, and I repeated the three simultaneous test runs back to back a dozen times. I even introduced variants of the SQL statement by inserting random spaces into the statement to force additional hard parses.

What I believe is happening: we are taking advantage of the automatic SGA features, allowing the shared pool to grow and shrink, and the default buffer cache to also grow and shrink. Most of our tables do not use the default buffer pool. As the shared pool shrinks, cached SQL statements are aged out of the shared pool. If CURSOR_SHARING is set to FORCE, and one of these SQL statements is aged out of the shared pool, the next time the SQL statement is executed with a different constant value, Oracle throws an ORA-07445 and terminates the session.

After testing by flipping various parameters to avoid the affected buggy code path for several days while waiting for a response from Oracle, I found that setting CURSOR_SHARING to FORCE and then flushing the shared pool immediately stopped the problem - that was 15 days ago.  The problem is similar to that reported in the Metalink article "Bug 5680308 Abstract: ORA-7445 ERRORS AND DEAD DISPATCHERS"

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Dec 21 2006 - 07:59:47 CST