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過大的解決方案