ORA-00020: maximum number of processes (1000) exceeded 故障一列

2019-11-09 13:31:43

字体：大中小

来源：转载

供稿：网友

[root@ijxdg ~]# su - Oracle[oracle@ijxdg ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 PRoduction on Thu Sep 18 18:14:15 2014Copyright (c) 1982, 2009, Oracle. All rights reserved.ERROR:ORA-00020: maximum number of processes (1000) exceeded--alert日志：Thu Sep 18 18:13:59 2014Process J002 submission failed with error = 20kkjcre1p: unable to spawn jobq slave processErrors in file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_cjq0_11338.trc:Thu Sep 18 18:14:26 2014ORA-00020: maximum number of processes 1000 exceededORA-20 errors will not be written to the alert log forthe next minute. Please look at trace files to see allthe ORA-20 errors.Thu Sep 18 18:15:17 2014Process m000 submission failed with error = 20Thu Sep 18 18:15:27 2014ORA-00020: maximum number of processes 1000 exceededORA-20 errors will not be written to the alert log forthe next minute. Please look at trace files to see allthe ORA-20 errors.......Thu Sep 18 18:20:03 2014Process W000 submission failed with error = 20Process W000 submission failed with error = 20Process W000 submission failed with error = 20Process W000 submission failed with error = 20--查看listener.log 发现如下错误18-SEP-2014 18:10:08 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=JYSD))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.217)(PORT=54584)) * establish * orcl * 018-SEP-2014 18:10:08 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=JYSD))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.217)(PORT=54586)) * establish * orcl * 12518TNS-12518: TNS:listener could not hand off client connectionTNS-12547: TNS:lost contact TNS-12560: TNS:protocol adapter error TNS-00517: Lost contact linux Error: 32: Broken pipe --此时已经登录不进库ps -ef|grep LOCAL --可以看到很多连接使用os命令kill掉一些进程以释放资源 --head 取前10条ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $2}'|head|xargs kill -9 登陆后台数据库进一步观察会话使用情况select event,program,count(*) from v$session group byevent,program order by 3;EVENT PROGRAM COUNT(*)---------------------------------------- ------------------------------ ----------DIAG idle wait oracle@lf1 (DIAG) 1class slave wait oracle@lf1 (GCR0) 1buffer busy waits oracle@lf1 (J004) 1library cache lock OMS 59从结果输出发现大量的会话处于library cache lock等待事件中，接下来定位阻塞者是哪个会话select event,p1raw,p2 from v$session where event='library cache lock';EVENT P1RAW P2-------------------- ---------------- ----------library cache lock 000000007C384160 1737585656library cache lock 000000007C384160 1737462296library cache lock 000000007C384160 1737338936library cache lock 000000007C384160 1737215576library cache lock 000000007C384160 1737092216library cache lock 000000007C384160 1736968856。。。。select event from v$session wheresaddr=(select kgllkses from x$kgllk where KGLLKhdl='000000007C384160' andKGLLKMOD>0);EVENT----------------------------------------------------------------log file switch (archiving needed) 至此已经看到了导致ORA-00020错误的源头，归档满了，删除多余日志问题解决！

上一篇：创建索引遇见ORA-00054 的一种解决方案

下一篇：mssql日志文件ldf过大的解决方案