ORA-00020: maximum number of processes (%s) exceeded

来源:互联网 发布:苏州网络品牌营销 编辑:程序博客网 时间:2024/06/02 01:31
Question: 

we encounter the below problem yesterday morning:

(1) All running batch application hang running half-way

(2) Attempt to login via sqlplus giving below error:
 

ERROR:

ORA-00020: maximum number of processes (%s) exceeded

REMARK:
current parameter value for PROCESSES = 300


(3) Attempt to connect as sys to shutdown the database giving the below:
Connected to an idle instance.

(at this point, we check that all background
processes like pmon, smon, etc still exists)

(4) the instance later being terminated by LGWR, & we manage to startup (without
need to shut it down)

**********************************************************

referring to item (3), is anyone out there able to provide me a PRECISE explanation for what does it mean here "connected to an idle instance" in such a scenario?

Answer by Mark:

"Connected to an idle instance" usually means that you connected "AS SYSDBA" and the instance you connected to is not started.

However, if your instance is out of processes, then a non-SYSDBA connection will error out with ORA-0020, and a SYSDBA connection will error out with "Connected to idle instance." It's a little misleading, but that's what happens.

Usually, when this happens, I want to get connected to the instance, so that I can try to figure out which user is causing the problem. One way to do that is to use the 'ps' command to identify some of the oldest server processes, and kill the 3-5 oldest processes, and then quickly try connecting to the database. Once you're in, you can look at things like V$SESSION to try and determine which user(s) is (are) consuming so many connections, and then go from there.
 

原创粉丝点击