ERROR:
ORA-00020: maximum number of processes (1000) exceeded
–alert日志：
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:
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.
Process m000 submission failed with error = 20
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.
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 发现如下错误
* (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=JYSD))) * (ADDRESS=(PROTOCOL=tcp)
* (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=JYSD))) * (ADDRESS=(PROTOCOL=tcp)
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 by
event,program order by 3;
EVENT PROGRAM COUNT(*)-DIAG idle wait oracle@lf1 (DIAG)
1class 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 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 where
saddr=(select kgllkses from x$kgllk
where KGLLKhdl=’000000007C384160′ and
KGLLKMOD>0);EVENT—————————————————————-log file switch (archiving needed)
至此已经看到了导致ORA-00020错误的源头，归档满了，删除多余日志问题解决！