RE: Excessive ORA-12805 parallel query server died unexpectedly errors

Thanks to Binley Tim and Jonathan Lewis for their inputs on this. We seem to have a handle on this issue now.

One of senior DBA's noticed that there were alot of sniped/killed processes in the database and decided to bounce the instance while everything was down. There were 4 zombie parallel processes that did not go away after we shut down the database. Here's his theory:  

These processes still appear in v$session as program oracle_at_ase1db1 (P002) but in killed status and the process ora_p002_aciprd01 still exists on the OS, however, oracle may somehow be seeing that the p002 process is available for use and attempts to execute a parallel query to use this slave, but can't create this process somehow. I ended up killing these shadow processes using UNIX command 'pstop <pid>'.  

Since the database was bounced over the weekend, we have seen any more of ORA-12805 errors. We are keeping our fingers crossed!  

Thanks,
Govind

-----Original Message-----
Sent: Tuesday, May 13, 2003 9:12 PM
To: Multiple recipients of list ORACLE-L errors

To put another perspective, I don't believe PAT itself is a problem. PAT is available from 8.0(?) onwards. If you don't set the other related parameters explicitly, PAT=true adjusts other parameters in a way that handicaps most systems that uses Parallel Query (ie less likely to cause problems).

A likely reason is running out of "resources" somewhere, so parallel_adaptive_multi_user, parallel_threads_per_cpu and large_pool_size are something to look at. The odd occasion I have seen this, tweaking the above parameters have made the problem "gone away". Has the other par% parameters been set explicitly?

RAC is a another possible reason, depending on what you have done with the configuration previously. Which-ever way, insist on an answer from Oracle Support -- I know this is easier said than done ;-)

• Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Wednesday, May 14, 2003 10:59 AM errors

>
> From past experiences - not this version of oracle, though,
> I would do the following for experimental reasons. Set:
> parallel_automatic_tuning FALSE
> parallel_max_servers less than 256
>
> I would also look out for bitmap indexes, especially
> multi-column bitmap indexes and bitmap indexes on
> partitioned tables to see if they appear regularly in
> the crash traces.
>
>
> This is purely based on paranoia - P_A_T is the
> most likely bit to break because it is the newest -
> pessimism - if there's a silly mistake, it's on a
> one-byte boundary so 280 is a threat - and previous
> bugs (bitmaps and partitioning).
>
> I take it you are seeing "BAD MAGIC NUMBER" in
> the trace files, or Oracle probably wouldn't ask you
> to set 10235.
>
>
> Regards
>
> Jonathan Lewis

> > List,
> >
> > We are starting to see a very high number of this error on one of
> Production databases running 9i. We have not made any changes related
> to parallel query processing to the best of my knowledge.
> >
> > One workaround was to turn off parallel query at the session level.
> Though this takes care of the batch programs, these programs are now
> running for a long time.
> >
> > We have parallel degree set at 4 for tables having more than 1
> million rows.
> >
> > We have opened a TAR with Oracle and the recommendations include
> disabling rac at the oracle executable level since we are not using
> RAC anymore. Oracle expects the right tracing info. by setting event
> "10235 trace name context forever, level 2" at the database level.
> >
> > I am curious to find out whether anyone has seen /worked on this
> error before.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Binley Lim
  INET: Binley.Lim_at_xtra.co.nz

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <Govind.Arumugam_at_alltel.com
  INET: Govind.Arumugam_at_alltel.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).