oracle 11g 密碼延遲驗證問題

前幾天遇到一個大量 library cache lock 的情況，具體情況類似在pub上看到的一個帖子，轉載一下：

 

 

系統：LINUX
資料庫版本：
--------------------------------------------------------------------------------
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 Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

故障描述：

    領導需求要修改線上生成資料庫使用者fonewapuser的密碼，修改密碼完成後過了不一會程式設計師就反應使用者fonewapuser連線不上，使用fonewapuser使用者登陸資料顯示一直等待狀態。

問題現象後，在資料庫中使用如下語句查詢等待事件出現大量的library cache lock。
select * from gv$session_wait where event like 'library cache lock';


在v$session檢視中查詢library cache lock等待相關的會話資訊，發現username為空而且不是oracle後臺進行。這就是說這些會話還沒有連線到資料庫，一直在等待驗證狀態。
select * from gv$session_wait where event like 'library cache lock';

因為以前也遇到過'library cache lock'等待事件的問題直接使用下面的語句查佔用'library cache lock'的會話，但是返回的確實沒有結果這可就神奇了。難道沒人阻塞它自己就產生library cache 鎖了嗎？詭異啊

SELECT SID,USERNAME,STATUS,SCHEMANAME,PROCESS,MACHINE,SQL_ID,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in  
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A  
   WHERE KGLLKREQ = 0
     AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
                 WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
                 AND KGLLKREQ > 0)
  );


現在問題就hang這裡了，library cache lock等待的會話不斷的增加，而又找不到那個會話阻塞了它。最後資料庫連線滿了，沒有辦法把資料庫重啟了。



資料庫起來以後，檢查library cache lock等待事件沒有輸出資料庫顯示正常。不過等待1個小時之後library cache lock又大量的出現，不過一會又消失了，有點詭異。在網上看到yangtingkun的一篇關於oracle 11g 密碼延長驗證的文章感覺和上面的問題很像，於是寫了個記錄登入失敗的觸發器，過了不一會就在alert中發現大量的連線失敗記錄，找到負責192.168.0.234這個臺機器的程式設計師讓他查程式連線使用者和密碼是否正確。結果發現密碼不對，而且這個程式是每隔一段時間就向資料庫傳送連線請求，把密碼修改正確後library cache lock就沒有在出現過。

哎，oracle 11g 密碼延長驗證真是害死人啊！！！11g的新特性確實很好，很強的，但是你要是不熟悉、不瞭解它，它很容易害死你。從出事開始經理佔我後面足足監視了4小時快壓死我了。4個多小時線上生成庫無法使用錢嘩嘩的沒，我被經理罵，經理上面領導罵。

最後問下，有沒有倒黴的童鞋遇到過這樣的情況，嘿嘿！

記錄使用者登入失敗觸發器：
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;
/

如有登入失敗則在alert中記錄如下資訊：
Thu Apr 26 20:16:45 2012
20120426 201645 logon denied from 192.168.0.234 29458 root with JDBC Thin Client ? JDBC Thin Client