批次錯誤使用者名稱與密碼導致業務使用者HANG住（library cache lock）

批次錯誤使用者名稱與密碼導致業務使用者HANG住（library cache lock）

資料庫版本
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;
/

特別鳴謝： 附表：

查詢錯誤密碼的登入者
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 ;