[20211014]19C Failed Logon Delay.txt

--//看了生产系统awk报表出现Failed Logon Delay.从来没有遇到这个等待,也许19c以后特有的,探究一下:

1.环境:
SYS@127.0.0.1:17101/DDHHH> @ ver1
SYS@127.0.0.1:17101/DDHHH> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@127.0.0.1:17101/DDHHH> @ ev_name "Failed Logon Delay"
SYS@127.0.0.1:17101/DDHHH> @ prxx
==============================
EVENT#                        : 1405
EVENT_ID                      : 387973045
NAME                          : Failed Logon Delay
PARAMETER1                    :
PARAMETER2                    :
PARAMETER3                    :
WAIT_CLASS_ID                 : 1893977003
WAIT_CLASS#                   : 0
WAIT_CLASS                    : Other
DISPLAY_NAME                  : Failed Logon Delay
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@127.0.0.1:17101/DDHHH> @ ashtop machine,event "upper(event) like '%FAILED%'" sysdate-1 sysdate
    Total
  Seconds     AAS %This   MACHINE                                  EVENT                                    FIRST_SEEN          LAST_SEEN
--------- ------- ------- ---------------------------------------- ---------------------------------------- ------------------- -------------------
      166      .0   89% | localhost.localdomain                    Failed Logon Delay                       2021-10-13 12:00:36 2021-10-14 11:40:36
       19      .0   10% | WorkGroup\MS-EVYNMRYAYERK                Failed Logon Delay                       2021-10-13 11:44:15 2021-10-14 11:38:54
        1      .0    1% | WORKGROUP\WEIP-XP-PB11                   Failed Logon Delay                       2021-10-14 10:57:44 2021-10-14 10:57:44

--//嗯怎么是本机的程序呢.

SYS@127.0.0.1:17101/DDHHH> @ dashtop machine,event "upper(event) like '%FAILED%'" (sysdate)-100 sysdate
                                                                                             Total
%This  MACHINE                                  EVENT                                      Seconds FIRST_SEEN          LAST_SEEN
------ ---------------------------------------- ---------------------------------------- --------- ------------------- -------------------
  88%  localhost.localdomain                    Failed Logon Delay                            2590 2021-09-29 12:50:34 2021-10-14 12:20:36
   6%  WorkGroup\MS-EVYNMRYAYERK                Failed Logon Delay                             190 2021-09-27 11:23:03 2021-10-14 10:10:32
   2%  WORKGROUP\WEBSERVICE-11                  Failed Logon Delay                              50 2021-09-17 19:26:16 2021-10-09 16:15:10
   1%  JAJA                                     Failed Logon Delay                              30 2021-09-02 16:54:54 2021-09-02 16:57:48
   1%  WORKGROUP\DESKTOP-BQD5V1H                Failed Logon Delay                              20 2021-08-24 15:06:34 2021-09-24 17:01:43
   0%  WORKGROUP\DESKTOP-2S0NO58                Failed Logon Delay                              10 2021-10-11 10:15:58 2021-10-11 10:15:58
   0%  WORKGROUP\DESKTOP-AB23BGD                Failed Logon Delay                              10 2021-08-23 08:52:03 2021-08-23 08:52:03
   0%  WORKGROUP\DESKTOP-CDINB53                Failed Logon Delay                              10 2021-08-19 12:37:25 2021-08-19 12:37:25
   0%  WORKGROUP\DESKTOP-KG36OJT                Failed Logon Delay                              10 2021-08-31 11:57:19 2021-08-31 11:57:19
   0%  WORKGROUP\PC-DY000                       Failed Logon Delay                              10 2021-09-06 10:27:28 2021-09-06 10:27:28
   0%  WORKGROUP\PC-DY149                       Failed Logon Delay                              10 2021-08-24 10:52:58 2021-08-24 10:52:58
   0%  WORKGROUP\YAOHH                          Failed Logon Delay                              10 2021-09-16 08:52:02 2021-09-16 08:52:02
12 rows selected.
--//dashtop脚本查询的是dba_hist_active_sess_history视图,时间被放大10倍,也就是30秒相当于仅仅出现3次.主要集中在前3个,也许是2个.

SYS@127.0.0.1:17101/DDHHH> @ashtop machine,event "upper(event) like '%FAILED%'" trunc(sysdate)+12/24 sysdate
    Total
  Seconds     AAS %This   MACHINE                                  EVENT                                    FIRST_SEEN          LAST_SEEN
--------- ------- ------- ---------------------------------------- ---------------------------------------- ------------------- -------------------
       33      .0  100% | localhost.localdomain                    Failed Logon Delay                       2021-10-14 12:00:36 2021-10-14 16:40:36

SELECT *
  FROM V$ACTIVE_SESSION_HISTORY
 WHERE event = 'Failed Logon Delay' AND sample_time >= TRUNC (SYSDATE) + 12/24
--//结果不贴出了,不知道谁安装的服务器,机器名就是localhost.localdomain,真心无语.真是人越多干活的人越少.

select * from v$session where machine='localhost.localdomain';
--//确定sid.

SYS@127.0.0.1:17101/DDHHH> @ sid 4265
sid = 4265
SPID       PID        SID    SERIAL# CLIENT_INFO          PNAME  TRACEFILE                                                          PROGRAM          TERMINAL     SQL_ID STATUS   C50
------ ------- ---------- ---------- -------------------- ------ ------------------------------------------------------------------ ---------------- ------------ ------ -------- --------------------------------------------------
69428      274       4265      15259                             /u01/app/oracle/diag/rdbms/DDHHH/DDHHH1/trace/DDHHH1_ora_69428.trc JDBC Thin Client unknown             INACTIVE alter system kill session '4265,15259' immediate;

--//理论讲程序是这个是开发写的程序,不应该出现口令错误.而且我没有权限访问数据库主机,主要想知道该机器的IP地址.

SELECT count( return_code),return_code
  FROM unified_AUDIT_trail
 WHERE     EVENT_TIMESTAMP >= TRUNC (SYSDATE)
       AND UNIFIED_AUDIT_POLICIES = 'ORA_LOGON_FAILURES'
       AND userhost = 'localhost.localdomain'
       group by return_code;
       
COUNT(RETURN_CODE) RETURN_CODE
------------------ -----------
               117        1017
      
--//注:视图unified_AUDIT_trail的字段AUTHENTICATION_TYPE,可以知道连接的IP地址,不过这个IP不是真实的IP,是nat后的IP地址.

$ oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:
--//昏,还真是口令不对.
--//很奇怪既然这样,还有连上的时候,为什么,不知道...

SYS@127.0.0.1:17101/DDHHH> show parameter sec_
NAME                                 TYPE     VALUE
------------------------------------ -------- ------------
db_securefile                        string   PREFERRED
optimizer_secure_view_merging        boolean  TRUE
sec_case_sensitive_logon             boolean  TRUE
sec_max_failed_login_attempts        integer  3
sec_protocol_error_further_action    string   (DROP,3)
sec_protocol_error_trace_action      string   TRACE
sec_return_server_release_banner     boolean  FALSE
sql92_security                       boolean  TRUE

--//现在的版本sec_max_failed_login_attempts=3次,这样如果不对,更加频繁.
--//sec_protocol_error_further_action = (DROP,3),11g以前的版本是CONTINUE.

--// https://www.anbob.com/archives/3034.html
这个等待事件常常是因为有程序尝试使用错误的用户密码登录数据库， 如暴力破解程序.

这是一个安全特性用于控制延迟失败的登录，在oracle 11g版本是引入,但是在11g时常因为这个特性带来性能 问题，需要用event 28401
禁用密码延迟认证的特性。 控制认证失败尝试特性是有 sec_max_failed_login_attempts 和sec_protocol_error_further_Action 参数
控制，但是在oracle 12c后对于以上参数值有了新的变化， sec_max_failed_login_attempts尝试失败次数(多个用户)11G是10次，在
12ck中减少为3， 所以延迟的登录会更多, 这个参数不同于user profile中的失效次数主要是单个用户失败和多个用户失败。
sec_protocol_error_further_Action  这个参数控制失败后的处理方式，在11g时是CONTINUE 也就是可以继续，但是在12c 中默认改变
为(DROP, 3)， 为了系统稳定牺牲一个连接。

--//在12c中默认改变为(DROP,3)，为了系统稳定牺牲一个连接,如何理解,难道在等待事件看到1次Failed Logon Delay吗?

解决方法就是找错误尝试的主机，修正密码后即可。

_sys_logon_delay

另外对于12c中引入的对于SYS用户的尝试失败登录后的延迟是有参数新的参数"_sys_logon_delay"控制的，默认为1秒，加大参数可以
防止非法尝试，配置值为0 可以禁用该特性。
==================================================

SYS@127.0.0.1:17101/DDHHH> @ hide _sys_logon_delay
NAME             DESCRIPTION                                      DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------- ------------------------------------------------ ------------- ------------- ------------ ----- ---------
_sys_logon_delay The failed logon delay for the database instance TRUE          1             1            FALSE FALSE

/* Formatted on 2021/10/14 15:51:15 (QP5 v5.269.14213.34769) */
SELECT program,count(*)
  FROM V$ACTIVE_SESSION_HISTORY
 WHERE event = 'Failed Logon Delay' AND sample_time >= TRUNC (SYSDATE)-100
 and machine<>'localhost.localdomain'
 group by program
 
PROGRAM                                    COUNT(*)
---------------------------------------- ----------
PlSqlDev.exe                                      1
plsqldev.exe                                      1
pb90.exe                                         17

SELECT count(*),client_program_name
  FROM unified_AUDIT_trail
 WHERE     EVENT_TIMESTAMP >= TRUNC (SYSDATE)
       AND UNIFIED_AUDIT_POLICIES = 'ORA_LOGON_FAILURES'
       AND userhost <> 'localhost.localdomain'
       group by client_program_name

  COUNT(*) CLIENT_PROGRAM_NAME
---------- ------------------------------------------------
         1 PlSqlDev.exe
        17 pb90.exe

--//从这里也基本排除其它程序登录的错误,这些基本是开发登录错误引起的.
--//既然这样提交叫同事解决问题,有点奇怪的,应用不出问题吗,怎么没人反馈呢.