ORA-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程序
ORA-12520: TNS: 监听程序无法为请求的服务器类型找到可用的处理程序
这两个错误很常见了,意思是oracle进程数已满了.导致oracle无法分配出process处理客户的连接.一般的解决办法就是加大process,但是我们这里却不是这种情况.
客户的环境是AIX-10.2.0.4 首先在process爆满的时候,我们查询了v$process和v$session,发现process数有3000,而v$session只有300,这是很不正常的,正常情况下应该是差不太多.
以前遇到过由于密码错误导致的process爆满的情况,所以怀疑是有帐号密码错误登录导致,所以使用下面的触发器检查是否有错误密码登录:
CREATE OR REPLACE TRIGGER logon_denied_to_alert
AFTER servererror ON DATABASE
DECLARE
message VARCHAR2(168);
ip VARCHAR2(15);
v_os_user VARCHAR2(80);
v_module VARCHAR2(50);
v_action VARCHAR2(50);
v_pid VARCHAR2(10);
v_sid NUMBER;
v_program VARCHAR2(48);
BEGIN
IF (ora_is_servererror(1017)) THEN-- get ip FOR remote connections :
IF upper(sys_context('userenv', 'network_protocol')) = 'TCP' THEN
ip := sys_context('userenv', 'ip_address');
END IF;SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum < 2;
SELECT p.spid, v.program
INTO v_pid, v_program
FROM v$process p, v$session v
WHERE p.addr = v.paddr
AND v.sid = v_sid;v_os_user := sys_context('userenv', 'os_user');
dbms_application_info.read_module(v_module, v_action);message := to_char(SYSDATE, 'YYYYMMDD HH24MISS') ||
' Password Error ,logon denied from ' || nvl(ip, 'localhost') || ' ' ||
v_pid || ' ' || v_os_user || ' with ' || v_program || ' – ' ||
v_module || ' ' || v_action;sys.dbms_system.ksdwrt(2, message);
END IF;
END;
/
上面的触发器会将密码错误的登录写到alert日志中.
我们这里也没有发现有错误密码登录.所以我查阅了一下MOS,搜索到一篇文档:
这里文档中也说明了,process很高,session很低说明有很多进程连接到数据库,但是连接失败了,所以导致process比session高很多
文档里面也说了一种情况就是密码错误.
因此我们可以定位到问题的原因是:
有大量的客户端连接到了数据库,数据库在分配了进程处理连接之后,连接失败了,但是oracle分配了进程处理就会占用一个process,不管他是否成功还是失败,由于大量的失败进程导致进程数爆满.
那么具体是什么IP什么程序,连接到数据库呢?
这里我们可以想到可以通过监控tns日志的方式获取连接IP和程序的信息.查出异常的IP地址,再通过使用tns黑名单的方式将此IP封掉.
我们这里最后定位发现的是一个应用程序连接数据库,每次连接之后停留在输入用户名和密码阶段,而且每次都是批量产生大量的连接,导致客户进程爆满.
由于此进程还没有连接到数据库,所以在数据库级别是查不到任何信息的,只能从监听层面分析.
总结一下,遇到此问题的处理:
1.创建密码错误触发器,检查是否有错误密码登录(一般都是此问题,像我这次遇到的及其难得)
2.查看监听日志,最好写一个脚本能够统计出最近的连接的IP访问次数,使用awk来处理还是不难的
3.查出了是哪个ip,再配置sqlnet,将IP添加到黑名单中,这样直接在TNS层就将此连接拒绝,
4.最后再到此IP的机器上检查应用是否有问题.