记录一次ORA-12516 process爆满的特殊故障

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的机器上检查应用是否有问题.