We have a Windows 2003 server with a Oracle 11g DB installed and running a Java backend application that mines and inserts data onto the DB. The data is then distributed with PHP via a web server
Until recently, the DB started to crash on a daily basis, with the following pattern
- PHP would be unable to access the DB and get error: ORA-12519 TNS: No appropriate service handler found
- When checking the Java backend log the error reported when attempting to insert is ORA-00018: maximum number of sessions exceeded
- When attempting to solve via SQLPLUS, we would log on and when trying to access any table we get ORA-01012:Not logged on
At that point, the only thing we could do was perform "shutdown abort" and then start the DB again. But the same would happen again the next day...
Checking the table DBA_HIST_ACTIVE_SESS_HISTORY we can see that the number of sessions per sampled time goes close to the session limit (170) and then no more samplings are taken until we restart.
We have also added loging for user and application connections and we cannot see a pattern that suggest a user is executing a statement before crashes.
We assume there is a process running periodically that is generating the max sessions and crashing the DB, but we would like to get help in looking for the event that is generating this crashes.
Is there a way of monitoring processes being run? Is there a way of getting the last process that was ran before the crash?