-2

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

  1. PHP would be unable to access the DB and get error: ORA-12519 TNS: No appropriate service handler found
  2. When checking the Java backend log the error reported when attempting to insert is ORA-00018: maximum number of sessions exceeded
  3. 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?

4
  • 1
    The above prevents starting new sessions, but previously started sessions function properly. Just leave a session open and view the contents of V$SESSION when this happens. – Balazs Papp Jun 11 '17 at 22:37
  • Where's that "crash" you are talking about? Not accepting additional connections is not a crash; the only "crash" I think is you doing shutdown abort, so that would be the last process run before the crash. – mustaccio Jun 12 '17 at 14:03
  • This sounds very much that your applications have a connection leak or are using connections too extensively. Instead of trying to "fix" that on the database, you should investigate why your applications open so many connections. Maybe a mis-configured connection pool? Or connections that are opened but not closed? – a_horse_with_no_name Jun 14 '17 at 9:00
  • @a_horse_with_no_name: this is true, but first one has to find out which application has the connection leak. – miracle173 Jun 14 '17 at 10:34
0

Explanation

Your Oracle instance has a restriction on the maximum amount of sessions allowed. This could be 100, 500, 1000 or even more. Check the processes parameter of your Oracle instance.

show parameter processes

Reference: ORA-00018 maximum number of sessions exceeded

According to the referenced article Oracle's sessions are limited to processes * 1.1 + 5

Because your processes/sessions are all used up you are receiving the ORA-00018 error message and because all processes/sessions are used up you are unable to fix it by logging in with SQLPlus.

So your number 1. is an error in the application because of 2. (run out of sessions) and 3 is a result of 2.

Shutting down the instance resolves the issue, by removing all open procesess/sessions.

Possible Fix

Check the processes parameter and change it to a higher value:

 alter system set processes=2000 scope=spfile;

You shouldn't encounter any issues any more. (Or at least until your java application consumes all the processes again).

0

Analyze the logfile of the listener. If a process logs into the database from a remote host, he first connects to the listener process and the listener process will make an entry in the logfile.


If this happens periodically you can open some sqlplus sessions and leave then open until the problems occur as Balasz Papp already mentioned in comment. Then you can run your scripts that analyzes the database. One possible script is the following

select count(*), username, machine, terminal, osuser, program, process
from v$session
group by username, machine, terminal, osuser, program, process
order by count(*)
/

Maybe you should increase the process parameter. If you use an spfile you can do this by an alter system statement. I assume ou currently have`processes=100* and increment it to 150. After an restart of the database the new paramter will be activated.

alter system set processes=150 scope=spfile;

Instead of shutting down the database you can do the following:

  • stop the listener (to avoid further sessions)
  • kill some database processes (not the background processes, but one of the newer one)
  • login as sysdba ( a local connection that does not use a listener)
  • startup the listener again
  • investigate the database

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.