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

最新推荐文章于 2025-05-29 10:32:00 发布

Hayden_yang

最新推荐文章于 2025-05-29 10:32:00 发布

阅读量1.1w

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle

本文链接：https://blog.youkuaiyun.com/keaihuilang/article/details/54910573

oracle 专栏收录该内容

35 篇文章

订阅专栏

本文记录了一次ORA-00020错误的排查过程，该错误源于数据库进程数达到上限。通过分析alert日志及trace文件，最终定位到归档日志满为根本原因，并给出了相应的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[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 by event,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 where saddr=( select kgllkses from x$kgllk where KGLLKhdl ='000000007C384160' and KGLLKMOD>0 );
EVENT
----------------------------------------------------------------
log file switch (archiving needed)

至此已经看到了导致ORA-00020错误的源头，归档满了，删除多余日志问题解决！