ideas needed to track down a mysterious problem

Hi,

Currently we are in the process of establishing a diagnostic sheet in order to help us track down a problem. One of our clients is running a 8.1.7.2 database on Solaris. Sometimes the application this database supports comes to a halt. When this happened we made sure that there were no locks to justify this situation and we started to log off everyone, set job_queue_processes to 0, killed the jobs, so basically stopped everyone from using the database. The database was still unresponsive and we could not get a system state dump (it just would not finish doing the dump). After restarting everything became normal.

Right now I started to establish a protocol, organizing the todo-s into steps which should be taken before restarting. It is something like:

• examine the active sessions
• examine the logs
• check the CPU and disk resources
• etc

Any ideas what to add to this list? Any queries that might help finding out what happens? Or better, any ideas why this might happen? What does the fact that a system state dump could not be taken mean to you? From my point of view this could well be an Oracle bug, or anything.

The application uses traditional heap organized tables, some global temp tables, advanced queueing, some java to send letters using javamail, dblinks to other systems and usually more than 70 users are logged in. The clients are mainly Magic 9.3, but there are some web apps logging in using PHP, and a java client is calling some stored procedures from time to time. There are two sessions running continously but they are sitting in a DBMS_AQ procedure waiting for messages.

Regards,

Krisztian Received on Sun May 13 2007 - 03:36:44 CDT