oracle異常:library cache lock
FROM : http://blog.itpub.net/26442936/viewspace-1067479
批次錯誤使用者名稱與密碼導致業務使用者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;
/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26250550/viewspace-1323783/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- oracle 10049 event之library cache lockOracle
- LIBRARY CACHE LOCK 等待事件事件
- library cache pin和library cache lock的診斷分析
- library cache lock和library cache pin區別總結
- [Oracle]--Library cache lock 故障解決一例Oracle
- latch:library cache lock等待事件事件
- 定位Library Cache pin,Library Cache lock等待的解決方法
- 重啟大法失效?詳述Oracle11g因JDBC bug引發異常Library Cache Lock等待處理事件OracleJDBC事件
- zt_如何平面解決library cache lock和library cache pin
- enq:Library cache lock/pin等待事件ENQ事件
- library cache lock 阻塞程式查詢
- Library cache lock/pin詳解(轉)
- 常用定位library cache lock的方法
- LIBRARY CACHE LOCK WAITS AND NO BLOCKER FOUNDAIBloC
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle
- Library cache pin/lock 在Oracle 10g的增強Oracle 10g
- Oracle Library cacheOracle
- 短連線 引起的 library cache lock
- 查詢library cache lock的源頭
- 【ASM_ORACLE】Library Cache最佳化篇(二)Library cache load lock的概念和解決辦法ASMOracle
- zt_如何使用event 10049分析定位library cache lock and library cache pin
- 【ASK_ORACLE】Library cache pin 與 library load lock的關係和區別Oracle
- 一次library cache lock 問題分析
- library cache pin/lock的解決辦法
- zt_library cache pin和lock等待分析
- 尋找 library cache lock 等待事件的session事件Session
- 'library cache lock'等待事件的處理方法事件
- 俺也談談 library cache lock 等待事件事件
- 深入理解shared pool共享池之library cache的library cache lock系列四
- sql version count引發cursor:pin s wait x及library cache latch library cache lockSQLAI
- library cache lock\pin的查詢與處理
- RAC 環境Library Cache Lock的處理方法
- 0317Library Cache Pin/Lock Wait EventsAI
- Oracle 11g 密碼延遲認證與 library cache lock 等待Oracle密碼