共計 3622 個字符，預計需要花費 10 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下數據庫中批量錯誤用戶名與密碼導致業務用戶 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
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 住怎么辦”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！
