LOGON_DENIED_TO_ALERT触发器导致数据库hang

89 阅读 0 评论 59 点赞

我是靠谱客的博主老实河马，最近开发中收集的这篇文章主要介绍LOGON_DENIED_TO_ALERT触发器导致数据库hang，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

今早发现一个库rac环境11.2.0.4，1节点能登陆，但是所有sql都跑不了，2节点正常。

因为找不到alert明显报错和会话阻塞信息，直接kill掉了所有LOCAL=NO的连接，等了大概10分钟，1节点恢复。

下面开始分析原因

直接生成awr报告

快照生成异常，1节点hang住了，没有成功生成快照。在实例正常后才生成的快照。

其他信息就不透露了，直接翻到sql那部分

可以看出第一条sql明显有问题

sqltext：

declare message varchar2(120); IP varchar2(15); v_os_user varchar2(80); v_module varchar2(50); v_action varchar2(50); v_pid varchar2(10); v_sid number; begin IF (ora_is_servererror(1017)) THEN if sys_context('userenv', 'network_protocol') = 'tcp' then IP := sys_context('userenv', 'ip_address'); else select distinct sid into v_sid from sys.v_$mystat; SELECT p.SPID into v_pid FROM V$PROCESS p, V$SESSION v WHERE p.ADDR = v.PADDR AND v.sid = v_sid; end if; v_os_user := sys_context('userenv', 'os_user'); dbms_application_info.READ_MODULE(v_module, v_action); message := to_char(sysdate, 'Dy Mon dd HH24:MI:SS YYYY') || ' logon denied from ' || nvl(IP, v_pid) || ' ' || v_os_user || ' with ' || v_module || ' ' || v_action; sys.dbms_system.ksdwrt(2, message); end if; end;

进数据库找到该sql调用的一个触发器

select * from dba_source where text like '%sys_context%userenv%network_protocol%';
1 SYS LOGON_DENIED_TO_ALERT TRIGGER 13 " if sys_context('userenv', 'network_protocol') = 'tcp' then"

从alert日志中也可以找到login denied from的信息:

Wed May 29 19:26:18 2019
Wed May 29 19:26:18 2019 logon denied from xx.xxx.xx.xx root with JDBC Thin Client
Wed May 29 19:26:18 2019
Wed May 29 19:26:18 2019 logon denied from xx.xxx.xx.xx root with JDBC Thin Client
Wed May 29 19:26:18 2019
Wed May 29 19:26:18 2019 logon denied from xx.xxx.xx.xx root with JDBC Thin Client
Wed May 29 19:26:18 2019
Wed May 29 19:26:18 2019 logon denied from xx.xxx.xx.xx root with JDBC Thin Client
Wed May 29 19:26:18 2019
Wed May 29 19:26:18 2019 logon denied from xx.xxx.xx.xx root with JDBC Thin Client
Wed May 29 19:26:18 2019
Wed May 29 19:26:18 2019 logon denied from xx.xxx.xx.xx root with JDBC Thin Client

Tue Jun 04 01:35:28 2019
Tue Jun 04 01:35:28 2019 logon denied from xxxxx.xx.xx with
Tue Jun 04 01:35:28 2019
Tue Jun 04 01:35:28 2019 logon denied from xx.xxx.xx.xx with
Tue Jun 04 01:35:29 2019
Tue Jun 04 01:35:29 2019 logon denied from xx.xxx.xx.xx with

刚开始我以为是密码延迟验证触发的问题

所以查了下event，是否关闭了密码延迟验证

（11g密码延迟验证特性可以看我之前的文章https://blog.csdn.net/qq_40687433/article/details/81126371）

SQL> show parameter event

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
event string 28401 trace name context forev
er,level 1, 10949 trace name c
ontext forever,level 1

说明该特性应该是关闭的

那么密码延迟验证和这个触发器LOGON_DENIED_TO_ALERT有关系吗？

我又查了许多环境，发现不论我是否开启或关闭密码延迟验证，都没有这个触发器

SQL> show parameter event

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event string
xml_db_events string enable
SQL> select * from dba_source where text like '%sys_context%userenv%network_protocol%';

no rows selected

回到生产环境，通过dba_objects查到该触发器是16年创建的，如果diable掉该触发器，alert日志中就不会记录密码错误的信息。

所以，解决办法有2个

1.通过alert日志中deny的ip，找到密码错误的程序源头，把密码调成正确的

2.关闭触发器LOGON_DENIED_TO_ALERT。alter trigger LOGON_DENIED_TO_ALERT diable

总结：

早上数据库用户登录频繁，程序发现密码错误后，不停的尝试登录数据库，该触发器被调用次数急剧升高，从awr报告看有20w+次，从而导致了library cache load lock等待事件，library cache一直没有释放，SQL执行受到阻碍，从而导致了这次故障。

所以，数据库级的触发器还是谨慎使用