RE: investigating database lockup

[Steve McClure]

Title: RE: Debate on rc commands Solaris and Oracle

 

>>Do you have Oracle 9i Supplemental logging turned on?

Nope..still Oracle 8.1.7. 

 

>>Also information about high water mark, and extent information, data dictionary upkeep...   what are the stats of the table storage clause?

 

Well not sure what you may be looking for here, but here goes.  All of the tablespaces are locally managed with the extents  sized as per the now famous "SAFE" whitepaper.  All of the tablespaces/datafiles have room for many extents to be added.  As for High water mark..well there are actually very few deletes throughout the database, so I can't see where that would be affecting any of our tables let alone contribute to tying up the entire database.

 

I do appreciate the help, If you would like more specific answers please let me know and I will respond "off list"

 

 

Thanks,

Steve McClure

 

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Nick Wagner
Sent: Thursday, January 30, 2003 12:34 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: investigating database lockup

Do you have Oracle 9i Supplemental logging turned on?

 

Also information about high water mark, and extent information, data dictionary upkeep...   what are the stats of the table storage clause?

-----Original Message-----
From: Steve McClure [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 11:43 AM
To: Multiple recipients of list ORACLE-L
Subject: investigating database lockup

This morning I have experienced a couple of strange lockups of our database.  The symptoms were the database was nonresponsive to any activity, freeze on login to sqlplus, active sessions freeze as soon as they interact with the db.  There is no alert log activity.  The systems cpu usage soars to 100%, and an oracle process, once a shared server and once a dedicated server, is dominating the usage(info gathered from top). 

 

The first time I killed the shared server process, and the database picked up and operated normally.  Then a half hour later the dedicated connection popped up.  The dedicated connection was owned by one of our oltp processes that pretty well runs without issue 24/7, so I decided to bounce the db to hopefully clear up the issue.

 

Our system is Oracle 8.1.7 on Solaris 8.   In short, I am hoping to get some ideas in tracking down what happened.  I have some sql scripts that I have used to identify problem sessions/sql in the past.  They focus on v$session, v$sess_io, and v$sqltext.  Unfortunately, these did me little good because while the trouble was occurring I couldn't interact with the database while the sessions were active.

 

I am soliciting actively for any Hints, Tips, or Ideas you may have,

 

Thanks,

Steve McClure