RE: Database Hung with all processes waiting on locks

Mary Jo,

Are you sure that MTS is supported in 11i (or at least in an upgrade)? My understanding is that it is not - the work required is just too heavy for MTS.... As a start, you could take out MTS, and try and use IPC if it your APPL_TOP and Oracle DB are on the same server. There is an incredibly _huge_ number of jobs to be run through the workers and tons of SQL is loaded. I wouldn't be surprised by lib cache/row cache related waits. How many workers did you use? The recommendation is # of cpus + 2, although I would bump that number up just a little. Is LARGE_POOL_SIZE configured?

More questions than answers, but I hope this shines a light in the right direction!

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Tortorelli, Mary Jo [mailto:tortorelli_at_ppg.com]
> Sent: Thursday, June 27, 2002 8:23 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Database Hung with all processes waiting on locks
>
>
> We were running 11i Autoupgrade (from 10.7) on HP-UX 8.1.7.3
> 64-bit Oracle.
>
> Yesterday the database hung with every session having a
> WAITING status in v$session_wait. The MTS processes were
> 'rdbms ipc message', several workers were waiting on 'row
> cache lock' and 'library cache pin' and background processes
> on 'pmon timer'.
>
> I flushed the shared pool but it didn't help. I did a trace
> level 10 dump and recycled the database. Recycling worked
> but now I'm trying to figure out what may have happened. I
> don't know how to interpret the trace file to glean a cause.
>
> Any ideas or advice are welcome. I wonder if the workers
> stepped on each other or MTS somehow got hosed. There were
> 18 active workers. Some samples from the trace file below.
>
> Thanks in advance!!!
>
> ----------------------------------------
> SO: c0000000e81e8ce0, type: 3, owner: c0000000ed03f580,
> pt: 0, flag: INIT/-/-/0x00
> (session) trans: 0, creator: c0000000ed03f580, flag:
> (80000e5) USR/- BSY/-/-/-/-/-
> DID: 0001-002B-00000ACD, short-term DID:
> 0000-0000-00000000
> txn branch: 0
> oct: 0, prv: 0, user: 51/APPS
> O/S info: user: applmgr, term: pts/tb, ospid: 11226,
> machine: hr03
> program:
> client info:
> 0
> waiting for 'library cache pin' blocking sess=0x0
> seq=38949 wait_time=0
> handle address=c0000001133639f0, pin
> address=c0000000ed159bf8, 10*mode+namespace=1e
> ------------------------------------------
> SO: c0000000e81bcde0, type: 3, owner: c0000000e815c3a8,
> pt: 0, flag: INIT/-/-/0x00
> (session) trans: 0, creator: c0000000e815c3a8, flag: (51)
> USR/- BSY/-/-/-/-/-
> DID: 0001-0002-00000002, short-term DID:
> 0000-0000-00000000
> txn branch: 0
> oct: 0, prv: 0, user: 0/SYS
> waiting for 'pmon timer' blocking sess=0x0 seq=7330 wait_time=0
> duration=12c, =0, =0
> ----------------------------------------
> SO: c0000000e81d10e0, type: 3, owner: c0000000e815e958,
> pt: 0, flag: INIT/-/-/0x00
> (session) trans: 0, creator: c0000000e815e958, flag: (41)
> USR/- BSY/-/-/-/-/-
> DID: 0001-000B-00001AA1, short-term DID:
> 0000-0000-00000000
> txn branch: 0
> oct: 0, prv: 0, user: 0/<none>
> O/S info: user: , term: , ospid: , machine:
> program:
> waiting for 'row cache lock' blocking sess=0x0 seq=2114
> wait_time=0
> cache id=7, mode=0, request=3
> ----------------------------------------
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Tortorelli, Mary Jo
> INET: tortorelli_at_ppg.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).