[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.

--//
這個等待事件常常是因為有程式嘗試使用錯誤的使用者密碼登入資料庫，如暴力破解程式.

這是一個安全特性用於控制延遲失敗的登入，在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

--//從這裡也基本排除其它程式登入的錯誤,這些基本是開發登入錯誤引起的.
--//既然這樣提交叫同事解決問題,有點奇怪的,應用不出問題嗎,怎麼沒人反饋呢.

[20211014]19C Failed Logon Delay.txt

相關文章