oracle11g 密码延迟验证测试三

接着上两篇文章：

oracle11g密码延迟验证测试一

oracle11g密码延迟验证测试二

测试三：

关闭密码延迟功能

设置event事件

To disable this behavior i.e no sleep delay set following event

event=”28401 trace name context forever, level 1″

You can set it dynamically too using

alter system set events ’28401 trace name context forever, level 1′;

To go back to original behavior

alter system set events ’28401 trace name context off’;

SQL> show parameter event
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string
xml_db_events                        string      enable
SQL> 
SQL> 
SQL> alter system set events '28401 trace name context forever, level 1';            
System altered.
SQL> 
----重启数据库后，该设置失效，未了保证不失效，需要在spfile中配置
----alter system set event='28401 trace name context forever, level 1' scope=spfile;
########################################################################################
SQL> 
SQL> alter system set event='28401 trace name context forever, level 1' scope=spfile;
System altered.
SQL> 
SQL> show parameter event
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string
xml_db_events                        string      enable
SQL> 
SQL> startup force
ORACLE instance started.
Total System Global Area  626327552 bytes
Fixed Size                  2230952 bytes
Variable Size             184550744 bytes
Database Buffers          432013312 bytes
Redo Buffers                7532544 bytes
Database mounted.
Database opened.
SQL> 
SQL> 
SQL> 
SQL> show parameter event
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      28401 trace name context forev
                                                 er, level 1
xml_db_events                        string      enable
SQL> 
########################################################################################

alert日志中出现：
Sat Apr 06 23:45:03 2013
OS Pid: 9851 executed alter system set events '28401 trace name context forever, level 1'

然后先重新执行
for i in {1..30}
do
 echo 'No.' $i
 time echo 'select sysdate from dual;' | sqlplus -s hcn/hcn_123
 echo ' '
done
查看延时功能是否已经屏蔽
[oracle@hcn rs]$ 
[oracle@hcn rs]$ for i in {1..30}
> do
>  echo 'No. $i'
>  time echo 'select sysdate from dual;' | sqlplus -s hcn/hcn_123
>  echo ' '
> done
No. $i
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/][@]
      <proxy> ::= <proxyuser>[][/][@]
real    0m0.062s
user    0m0.013s
sys     0m0.013s
 
No. $i
ERROR:
ORA-01017: invalid username/password; logon denied
.....内容省略
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/][@]
      <proxy> ::= <proxyuser>[][/][@]
real    0m0.042s
user    0m0.006s
sys     0m0.006s
 
[oracle@hcn rs]$ 
可以看到延迟功能已经被屏蔽
再执行
ksh batch_login.sh
查看是否还会大量产生library cache lock
执行前：
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
VKTM Logical Idle Wait                                                    1
pmon timer                                                                1
smon timer                                                                1
class slave wait                                                          1
SQL*Net message to client                                                 1
Space Manager: slave idle wait                                            2
DIAG idle wait                                                            2
SQL*Net message from client                                               8
rdbms ipc message                                                        17
14 rows selected.
session3：
SQL> /
NAME                               LCOUNT
------------------------------ ----------
HCN                                  1271
SQL> 
SQL> 
结束时：
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
VKTM Logical Idle Wait                                                    1
pmon timer                                                                1
smon timer                                                                1
class slave wait                                                          1
SQL*Net message to client                                                 1
Space Manager: slave idle wait                                            2
DIAG idle wait                                                            2
SQL*Net message from client                                               8
rdbms ipc message                                                        17
14 rows selected.
SQL> 
SQL> /
NAME                               LCOUNT
------------------------------ ----------
HCN                                  1611
SQL> 
屏蔽oracle11g密码延迟功能后，library cache lock并为大量增加
批量连接中偶尔出现了
SQL> /
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
ASM background timer                                                      1
wait for unread message on broadcast channel                              1
Streams AQ: waiting for time management or cleanup tasks                  1
Streams AQ: qmn coordinator idle wait                                     1
VKTM Logical Idle Wait                                                    1
SQL*Net message to client                                                 1
Disk file operations I/O                                                  1
asynch descriptor resize                                                  1
pmon timer                                                                1
smon timer                                                                1
Streams AQ: qmn slave idle wait                                           1
class slave wait                                                          1
Space Manager: slave idle wait                                            2
DIAG idle wait                                                            2
SQL*Net message from client                                               8
rdbms ipc message                                                        17
enq: HW - contention                                                     28
library cache load lock                                                  77
18 rows selected.
SQL> 
library cache load lock增加的情况，但是很快就消失了

结论：通过设置28401 event来关闭密码延迟验证功能后，虽然也会有library cache lock增加的情况，但是由于没有延迟验证的影响，所以很快就释放掉。

使用新特性一定要小心啊。。。。。。。