Last week we confronted ORA-00020. One of our development team complained that, whenever they were trying to connect to the DB (using JDBC) they were getting "ORA-12537: TNS: connection closed".
At the beginning we thought that it should be a problem related to network connectivity.
At first, we looked at the alert log and found several "ORA-00020", then tried to connect to the DB locally "as sysdba" got the error:
SQL> conn / as sysdba
ORA-00020: maximum number of processes (%s) exceeded
At this point oracle had reached to maximum limit for processes and was unable to create a new server process to serve a new session. But this error [ORA-00020] is visible only form a local connection [bypassing listener] like above and using connections via listener may show TNS error like "ORA-12537".
We also, have to remember that this is not an ORA-00018 [maximum session] error and there is no one to one relationship between number of session and number of process. But opening too many sessions from an application may be one of the causes of "ORA-00020".
In order to solve the issue we followed below steps:
- If there is any existing connection with privilege to kill oracle session then use it to kill some sessions
- If above is not true then follow below:
1. from OS use below commane to find oracle processes serving oracle DB sessions:
$ ps -ef|grep "oracle[DB_SID] (LOCAL=NO)" [ example "oracleORCL (LOCAL=NO)" for SID with ORCL ]
2. $kill - 9 some_process_id_from_above to reduce number of oracle processes to below limit, so that a new connection can be made.
3. Immediately try "conn / as sysdba"
4. Find the application/user, which may induced the problem by opening too many connections using below query:
"select count(*),username,program from v$session group by username,program"
5. Then do necessary action [ like kill session from DB,increase value parameter "process" etc]
In our case, we have found one application opening too many connections.
For more information please below:
MOS NOTE ID: 825045.1, ID 316916.1