RE: Alert for hung database -- ie enq HW:

From: Patterson, Joel <Joel.Patterson_at_crowley.com>
Date: Wed, 21 Nov 2012 11:49:01 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1C7647C90D_at_JAXMSG01.crowley.com>



Perhaps another question is -- is this the type of event that anyone out there does monitor for? If so, what parameters do you use? #of waiting sessions, total time of 'any' wait event, etc.? Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----

From: D'Hooge Freek [mailto:Freek.DHooge_at_uptime.be] Sent: Wednesday, November 21, 2012 11:00 AM To: Patterson, Joel; oracle-l_at_freelists.org Subject: RE: Alert for hung database -- ie enq HW:

For locking issues you can monitor the number of waiters and the wait duration. v$wait_chains can also provide you with some useful information for monitoring / diagnosing.

Regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Patterson, Joel Sent: woensdag 21 november 2012 16:52
To: oracle-l_at_freelists.org
Subject: Alert for hung database -- ie enq HW:

11.2.0.3.3 on Solaris 10.

A database got stagnant due to a connection pooling issue, some parameters where changed in the app, and the app started working again for a short time, but only until the connections again began piling up behind the processes that were left dormant in the first place -- so the app team stopped and started the application, which of course left all the original oracle processes that were messed up to begin with holding locks and whatever other resources, and were not able to communicate back to the client. Restarting the database fixed all that permanently.

DBOptimizer did identify an enq: HW on a particular table that is used all the time, but otherwise was unable to go farther, (no info in tabs etc on any other window).

So now the question has become, can we monitor for this and get an alerted? If so, does anyone have any good ideas on it? We do have EM.

Thank you,

Joel
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 21 2012 - 17:49:01 CET

Original text of this message