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

創(chuàng)新互聯(lián)公司-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比呂梁網(wǎng)站開(kāi)發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫(kù),直接使用。一站式呂梁網(wǎng)站制作公司更省心,省錢(qián),快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋呂梁地區(qū)。費(fèi)用合理售后完善，十余年實(shí)體公司更值得信賴。

數(shù)據(jù)庫(kù)版本
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

問(wèn)題如下
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

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

2.然后查看了一下用戶修改密碼的時(shí)間
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用戶，沒(méi)有修改過(guò)密碼，但是為什么會(huì)出現(xiàn)大量的library cache lock，沒(méi)有修改密碼，但是新業(yè)務(wù)配置的用戶密碼會(huì)不會(huì)有錯(cuò)誤呢，這樣詢問(wèn)了開(kāi)發(fā)人員，原來(lái)他們的配置有錯(cuò)誤，用戶密碼配置錯(cuò)誤了。也就是錯(cuò)誤的用戶和密碼批量請(qǐng)求導(dǎo)致了大量的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.問(wèn)題解決，正確的用戶密碼配置之后，并設(shè)置參數(shù)EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"，大量的library cache lock逐漸減少，最后消除。新業(yè)務(wù)也正常使用了

附表

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.

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


附表：

查詢錯(cuò)誤密碼的登錄者
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 ;

以上是“數(shù)據(jù)庫(kù)中批量錯(cuò)誤用戶名與密碼導(dǎo)致業(yè)務(wù)用戶HANG住怎么辦”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

分享標(biāo)題：數(shù)據(jù)庫(kù)中批量錯(cuò)誤用戶名與密碼導(dǎo)致業(yè)務(wù)用戶HANG住怎么辦
網(wǎng)站地址：http://www.rwnh.cn/article8/jipjop.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、定制網(wǎng)站、軟件開(kāi)發(fā)、網(wǎng)站設(shè)計(jì)公司、網(wǎng)站改版、搜索引擎優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)