ANBOB™

数据库为了防止频繁的错误密码登录或暴力破解，如果user profile中配置了无限次失败而不lock用户，或当修改了应用用户的数据库密码，有遗漏的应用程序配置未及时更新，就会因密码错误而导致性能问题，Oracle 11g引入了密码延迟验证的新特性, 想法虽好但也成了问题特性。 错误的密码尝试在不同的版本中，对数据库带来的性能问题等待事件可能不同, Oracle 10g R2, 11g R1 等待事件的是row cache lock， 11g R2等待事件library cache lock， 12C是的等待事件Failed Logon Delay。

现象
1, ASH  & AWR  show ” Connection Management” , top call “OAUTH”
2, DBA_HIST_ACTIVE_SESS_HISTORY&V$ACTIVE_SESSION_HISTORY show TOP_LEVEL_CALL_NAME=”OAUTH”
3, row cache lock waits for DC_USERS
4, Call Stack contains one of the following functions:

kziavua
kziaia
kziasfc

5, Checking the exclusive holder from DBA_DDL_LOCKS, a session may be seen holding a lock type (kglhdnsp) 79 on object (kglnaobj) 5:

SQL> select * from dba_ddl_locks where mode_held='Exclusive';
SESSION_ID OWNER NAME      TYPE MODE_HELD   MODE_REQU
---------- --------- ---------- ---------- ----------
612                5         79 Exclusive        None

6, p3 值是 100*mode+namespace

SQL> select p3,count(*) from v$active_session_history where event like 'library cache lock%' group by p3
-- query v$session.p3

sql> @hex p3_VALUE
HEX
---------
4F00N 
4F ---- dec 79  NAMESPACE
00N --- MODE    S/X

SQL>  select kglstdsc from x$kglst where indx=79
-- ACCOUNT_STATUS

Note:
如果是namespace是127， 对应的是Last_Successful_Logon_Time，有可能是12c 以后的新特性，在登录时记录用户最后登录时间 ，查看另一篇<Oracle 12c New Feature: Last Login Time for Non-Sys Users>

7, P1 值是handl addr

select kglnahsv,kglnaobj,kglhdnsd,kglhdnasp from x$kglob where kglhdadr='P1 VALUE'

kglhdnasp  == ACCOUNT_STATUS
kglnaobj  == USER_ID

sql > SELECT NAME,LCOUNT FROM USER$ WHERE USER#=x$kglob.kglnaobj (-- WHEN NAMESPACE IS ACCOUNT_STATUS)

找查登录失败的应用
1, LOGON TRIGGER
How to find out who caused the database user locked（ora-1017 or ORA-28000）(捕捉登录失败)

2, AUDIT trail

select username, os_username, userhost, client_id, trunc(timestamp), count(*) failed_logins 
from dba_audit_trail 
where returncode = 1017 and timestamp > sysdate -1 
group by username, os_username, userhost, client_id, trunc(timestamp);

收集信息

$sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266

从trace file中从等待PID的下的内容以waiting for‘为关键字查找，找到handle address，以具体的地址为关键字查找，owner为“calls cur”找到locked 对象和request Mode，继续以为handle address关键字继续查找，找到当前handle的其它持有会话。

或者从 x$kgllk表kgllkhdl是Handle , kgllkses是session addr.

解决办法
最根本的是找到正在频繁尝试错误密码的应用，修正密码。
在Oracle 11g 11.1.0.7版中, 没有办法禁用了等待，只能及时的更改应用密码。在11.2及以后的版本也可以尝试禁用这个密码延迟认证的特性
The event can be set as follows:

-- spfile
alter system set event ="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" scope=spfile;
-- or --
-- memory
alter system set events '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1';

About logon delay

After 3 successive failures a sleep delay is introduced starting
at 1 second and extending to 10 seconds max. During each delay
the user X row cache lock is held in exclusive mode preventing
any concurrent logon attempt as user X (and preventing any
other operation which would need the row cache lock for user X).

案例

PROCESS 39:
  ----------------------------------------
  SO: 0x700000fd0aadce8, type: 2, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x700000fd0aadce8, name=process, file=ksu.h LINE:12616 ID:, pg=0
  (process) Oracle pid:39, ser:238, calls cur/top: 0x700000f35836778/0x700000f35836778
            flags : (0x0) -
            flags2: (0x0),  flags3: (0x10)
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 0 0 80
              last post received-location: kji.h LINE:3418 ID:kjata: wake up enqueue owner
              last process to post me: 700000fc8aa46e8 1 6
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 0x700000fc0b81778
    O/S info: user: grid, term: UNKNOWN, ospid: 26477280
    OSD pid info: Unix process pid: 26477280, image: oracle@ANBOB2
    Short stack dump:
ksedsts()+360<-ksdxfstk()+44<-ksdxcb()+3384<-sspuser()+116<-48bc<-sskgpwwait()+32<-skgpwwait()+180<-ksliwat()+11032<-kslwaitctx()+180<-kjusuc()+3652<-ksipgetctxi()+1892<-kqlmLock()+1296<-kqlmClusterLo
ck()+256<-kgllkal()+1984<-kglLock()+1276<-kglget()+264<-kziasfc()+1836<-kpolnb()+6840<-kpoauth()+672<-opiodr()+720<-ttcpip()+1028<-opitsk()+1508<-opiino()+940<-opiodr()+720<-opidrv()+1132<-sou2o()+136
<-opimai_real()+608<-ssthrdmain()+268<-main()+204<-__start()+112
    ----------------------------------------
    SO: 0x700000fd8e2a3e8, type: 4, owner: 0x700000fd0aadce8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0x700000fd0aadce8, name=session, file=ksu.h LINE:12624 ID:, pg=0
    (session) sid: 1417 ser: 32923 trans: 0x700000fc6a3f890, creator: 0x700000fd0aadce8
              flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
              flags2: (0x9) -/-/INC
              DID: , short-term DID:
              txn branch: 0x0
              oct: 0, prv: 0, sql: 0x0, psql: 0x0, user: 0/SYS
    ksuxds FALSE at location: 0
    service name: anbob
    client details:
      O/S info: user: , term: , ospid: 1234
      machine: bao-176 program:
    Current Wait Stack:
     0: waiting for 'library cache lock'
        handle address=0x70000100d60d478, lock address=0x700001004b94110, 100*mode+namespace=0x4f0003
        wait_id=7 seq_num=8 snap_id=1
        wait times: snap=2 min 58 sec, exc=2 min 58 sec, total=2 min 58 sec
        wait times: max=infinite, heur=2 min 58 sec
        wait counts: calls=359 os=359
        in_wait=1 iflags=0x15a2
    There is at least one session blocking this session.
      Dumping 1 direct blocker(s):
        inst: 2, sid: 441, ser: 33695
      Dumping final blocker:
        inst: 2, sid: 441, ser: 33695
    Wait State:
      fixed_waits=0 flags=0x22 boundary=0x0/-1

打赏

微信扫一扫，打赏作者吧～