oracle11g 密码延迟验证测试二

接着上一篇文章：oracle11g密码延迟验证测试一

测试二：

测试大量密码错误链接，是否会引起大量library cache lock

甚至在超过process限制的时候报错ORA-00020错误

In 11g there is an intentional delay between allowing failed logon attempts to retry.

After 3 successive failures a sleep delay is introduced starting

at 3 seconds and extending to 10 seconds max. ----实际测试的结果是20s

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).

SQL> 
SQL> show parameter processes
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150
SQL> 

在session1中编译一下脚本：
[oracle@hcn rs]$ cat login.sh
sqlplus -s hcn/oracle_123 <<EOF
EOF
[oracle@hcn rs]$ 
[oracle@hcn rs]$ 
[oracle@hcn rs]$ cat batch_login.sh
#!/bin/bash
for i in {1..1000}
do
echo 'No.' $i
nohup ksh login.sh &
done
[oracle@hcn rs]$ 
然后执行
ksh batch_login.sh

然后在session2和session3中进行观察
开始时：
session2：
SQL> /
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
ASM background timer                                                      1
wait for unread message on broadcast channel                              1
Streams AQ: qmn slave idle wait                                           1
Streams AQ: waiting for time management or cleanup tasks                  1
Streams AQ: qmn coordinator idle wait                                     1
pmon timer                                                                1
smon timer                                                                1
Space Manager: slave idle wait                                            1
SQL*Net message to client                                                 1
VKTM Logical Idle Wait                                                    1
DIAG idle wait                                                            2
SQL*Net message from client                                               4
rdbms ipc message                                                        17
13 rows selected.
session3：
SQL> /
NAME                               LCOUNT
------------------------------ ----------
HCN                                    10


结束时：
session2:
SQL> /
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
ASM background timer                                                      1
wait for unread message on broadcast channel                              1
Streams AQ: qmn coordinator idle wait                                     1
pmon timer                                                                1
SQL*Net message to client                                                 1
smon timer                                                                1
Streams AQ: waiting for time management or cleanup tasks                  1
Streams AQ: qmn slave idle wait                                           1
Space Manager: slave idle wait                                            1
VKTM Logical Idle Wait                                                    1
DIAG idle wait                                                            2
SQL*Net message from client                                               4
rdbms ipc message                                                        17
library cache lock                                                      113
14 rows selected.
SQL> 

session3:
SQL> /
NAME                               LCOUNT
------------------------------ ----------
HCN                                    65
SQL> 
查看进程数已经达到149，最大为150
SQL> 
SQL>  select count(*) from v$process;
  COUNT(*)
----------
       149
SQL> 
alert日志报了大量的
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 ORA-20 errors will not be written to the alert log for
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
Sat Apr 06 23:26:33 2013
ORA-00020: maximum number of processes (150) exceeded
 ORA-20 errors will not be written to the alert log for
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
 the ORA-20 errors.
select distinct ses.ksusenum      sid,
                ses.ksuseser      serial#,
                ses.ksuudlna      username,
                KSUSEMNM          module,
                ob.kglnaown       obj_owner,
                ob.kglnaobj       obj_name,
                lk.kgllkcnt       lck_cnt,
                lk.kgllkmod       lock_mode,
                lk.kgllkreq       lock_req,
                w.state,
                w.event,
                w.wait_Time,
                w.seconds_in_Wait
  from x$kgllk lk, x$kglob ob, x$ksuse ses, v$session_wait w
 where lk.kgllkhdl in (select kgllkhdl from x$kgllk where kgllkreq > 0)
   and ob.kglhdadr = lk.kgllkhdl
   and lk.kgllkuse = ses.addr
   and w.sid = ses.indx
 order by seconds_in_wait desc;
 
You will notice that seconds_in_wait value will increase . 
Also ‘alter system kill session’  takes long time to kill session (it seems as if hung). 
As per bug ,any operation involving row cache lock for that user will not be allowed. 
I confirmed by trying to reset profile to Default and session was stuck.

停止操作后，library cache lock的数量逐渐降低，process也逐渐降低
SQL> 
SQL>  select count(*) from v$process;
  COUNT(*)
----------
        67
SQL> /
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message to client                                                 1
wait for unread message on broadcast channel                              1
Streams AQ: qmn coordinator idle wait                                     1
pmon timer                                                                1
ASM background timer                                                      1
smon timer                                                                1
Streams AQ: waiting for time management or cleanup tasks                  1
Streams AQ: qmn slave idle wait                                           1
VKTM Logical Idle Wait                                                    1
Space Manager: slave idle wait                                            2
DIAG idle wait                                                            2
SQL*Net message from client                                               7
rdbms ipc message                                                        17
library cache lock                                                       33
复制

结论：

测试大量并发密码错误链接，会引起大量library cache lock等待

甚至在超过process限制的时候报错ORA-00020错误

停止操作后，library cache lock的数量逐渐降低，process也逐渐降低