數據庫中批量錯誤用戶名與密碼導致業務用戶HANG住怎么辦

小編給大家分享一下數據庫中批量錯誤用戶名與密碼導致業務用戶HANG住怎么辦，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

數據庫版本
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

問題如下
SQL> conn doudou/oracle （HANG住了）

查看等待事件
select
count(*),
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END AS sw_event
FROM
v$session_wait
GROUP BY
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END
ORDER BY
1 DESC, 2 DESC
/

library cache lock WAITING 585
rdbms ipc message WAITING 16
Space Manager: slave idle wait WAITING 3
jobq slave wait WAITING 2
Streams AQ: waiting for time management or cleanup tasks WAITING 1
VKRM Idle WAITING 1
smon timer WAITING 1
Streams AQ: qmn coordinator idle wait WAITING 1
pmon timer WAITING 1
Streams AQ: qmn slave idle wait WAITING 1
DIAG idle wait WAITED KNOWN TIME 1
DIAG idle wait WAITING 1
library cache lock WAITED KNOWN TIME 1
VKTM Logical Idle Wait WAITING 1
asynch descriptor resize WAITED SHORT TIME 1
SQL*Net message from client WAITING 1

結合等待事件去分析
1.library cache lock 等待嚴重，另一方面考慮只有單獨的這個業務用戶doudou不能登錄，其他業務類型的用戶doudou01不受任何影響。再次懷疑可能是11g 密碼延遲機制導致的這個問題。

2.然后查看了一下用戶修改密碼的時間
select * from sys.user$ where name='DOUDOU';
PTIME=2013/11/6 11:22:09     --PTIME is the date the password was last changed
CTIME=2013/11/6 11:22:09     --CTIME is the date the user was created
從這里可以看出我們DOUDOU用戶，沒有修改過密碼，但是為什么會出現大量的library cache lock，沒有修改密碼，但是新業務配置的用戶密碼會不會有錯誤呢，這樣詢問了開發人員，原來他們的配置有錯誤，用戶密碼配置錯誤了。也就是錯誤的用戶和密碼批量請求導致了大量的library cache lock。

搜索MOS找到了類似的案例
Library Cache Locks Due to Invalid Login Attempts (Doc ID 1309738.1)

Cause
Numerous failed logins attempts can cause row cache lock waits and/or library cache lock waits.
Set the below event in the spfile or init.ora file and restart the database:

alter system set event ="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" scope=spfile;

or

EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"

3.問題解決，正確的用戶密碼配置之后，并設置參數EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"，大量的library cache lock逐漸減少，最后消除。新業務也正常使用了

附表

user$ 視圖解釋
Test cases below show:

?CTIME is the date the user was created.
?LTIME is the date the user was last locked. (Note that it doesn't get NULLed when you unlock the user).
?PTIME is the date the password was last changed.
?LCOUNT is the number of failed logins.

記錄用戶登錄失敗觸發器：
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') ||
               ' 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;
/

特別鳴謝：Travel http://www.traveldba.com/


附表：

查詢錯誤密碼的登錄者
select username,
os_username,
userhost,
client_id,
trunc(timestamp),
returncode,
count(*) failed_logins
from dba_audit_trail
where returncode=1017 and --1017 is invalid username/password
timestamp < sysdate
group by username,os_username,userhost, client_id,trunc(timestamp),returncode
order by trunc(timestamp) desc ;

以上是“數據庫中批量錯誤用戶名與密碼導致業務用戶HANG住怎么辦”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！