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

[root@ijxdg ~]# su - oracle
[oracle@ijxdg ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 18 18:14:15 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-00020: maximum number of processes (1000) exceeded

--alert日志：
Thu Sep 18 18:13:59 2014
Process J002 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_cjq0_11338.trc:
Thu Sep 18 18:14:26 2014
ORA-00020: maximum number of processes 1000 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.
Thu Sep 18 18:15:17 2014
Process m000 submission failed with error = 20
Thu Sep 18 18:15:27 2014
ORA-00020: maximum number of processes 1000 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.
......

Thu Sep 18 18:20:03 2014
Process W000 submission failed with error = 20
Process W000 submission failed with error = 20
Process W000 submission failed with error = 20
Process 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 * 0
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=54586)) * establish * orcl * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-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)                       1
class slave wait                         oracle@lf1 (GCR0)                       1

buffer busy waits                        oracle@lf1 (J004)                       1

library 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 1737585656
library cache lock   000000007C384160 1737462296
library cache lock   000000007C384160 1737338936
library cache lock   000000007C384160 1737215576
library cache lock   000000007C384160 1737092216
library 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错误的源头，归档满了，删除多余日志问题解决！