昨天开发人员反映SIT环境用户登录缓慢,我使用sqlplus登录数据库查找原因时遇到ORA-00020错误,检查告警日志发现大量ORA-00020错误,如下:

Wed Jul 01 15:50:01 2015
ORA-00020: maximum number of processes (600) 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.
Wed Jul 01 15:51:01 2015
ORA-00020: maximum number of processes (600) 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.

查询当前数据库远程连接的进程,发现580做个的远程连接进程。

[oracle@SL010A-ISITDB1 expbak]$ ps -ef | grep LOCAL | grep -v IVLIF| wc -l
573

查看数据库的连接数限制,发现设置的是600。

SYS@IVLDB > show parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     600

查看数据库的SESSION情况。

SYS@IVLDB > select PROGRAM,count(*) from v$session group by PROGRAM having count(*)>2;

PROGRAM                                            COUNT(*)
------------------------------------------------ ----------
                                                         12
SQL Developer                                             3
plsqldev.7.10.1339.exe                                    7
extract@SL010A-ISITDB1 (TNS V1-V3)                        3
JDBC Thin Client                                        425
plsqldev.exe                                             86
sqlplus@SL010A-ISITDB1 (TNS V1-V3)                        4

7 rows selected.

发现425个JDBC连接，连接数降下几个后，应用登录正常，连接数一上来，用户登录就会卡住。今天中午修改了数据库的最大连接数，经过今天的监控，进程数一直是400多，用户登录再未出现问题。

本文固定链接: http://www.dbdream.com.cn/2015/07/ora-00020%e9%94%99%e8%af%af%e5%af%bc%e8%87%b4%e5%ba%94%e7%94%a8%e7%b3%bb%e7%bb%9f%e7%99%bb%e5%bd%95%e7%bc%93%e6%85%a2/ | 信春哥，系统稳，闭眼上线不回滚！