[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
[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
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
---------------------------------------- ------------------------------ ----------
DIAG idle wait oracle@lf1 (DIAG) 1
class slave wait oracle@lf1 (GCR0) 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,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)
EVENT
----------------------------------------------------------------
log file switch (archiving needed)
至此已经看到了导致ORA-00020错误的源头,归档满了,删除多余日志问题解决!