This is a very well known problem and it appears in alert log of the database as:

ORA-00020: maximum NUMBER OF processes (200) exceeded
 ORA-20 errors will NOT be written TO the alert LOG FOR
 the next MINUTE. Please look AT trace files TO see ALL
 the ORA-20 errors.

The resolution is even simpler by increasing static initialization parameter PROCESSES. The annoying part of this error is the warning that often directly comes from your users. Even if you monitor (and you should !!) all the ORA- error messages in alert log file you users might be faster than you to see it (obviously they are directly impacted). And even if you see it before them as it is a static parameters there is nothing you can transparently do in the background.

You are also aware that this maximum number of processes does not include only the sessions processes but also the background processes (Database writers, log writer, …) as well as parallel processes. So be cautious on the value of PARALLEL_MAX_SERVERS, even the default value can be huge on a small system.

So how could you be more proactive on this subject ?

Continue reading

We recently upgraded our BI environment in 11gR2 (11.2.0.4). Yes, October 2014 and we upgraded in 11gR2 while the end of premium 11gR2 support in January 2015, fortunately first year of extended support is offered…

Processes initialization parameter has not been changed but we reach the limit (200) and I wanted to check how many connections were initiated over the window where we faced issues. I found the nice presentation of Tanel Poder located at Troubleshooting Complex Performance Issues – Part1 and found the information I wanted using the 10gR2 old fashion listener.log in old text format.

Continue reading