密码延迟验出现大量library cache lock

浏览数：79 / 时间：2015年06月20日

昨天一业务系统在做数据迁移时，迁移完毕后，导致系统业务非常慢，数据一直查询不出来。通过操作系统TOP命令查看系统压力，数据库没有任何压力，非常空闲，后来怀疑是网络问题，于是远程登录到主机上，使用数据库用户登录数据库，发现其他数据库用户登录非常快，而该业务系统的使用的用户登录非常缓慢，几乎要等10多秒才能登陆上。
问题出现象后，通过v$session， gv$session_wait查询等待事件，在数据库中出现大量的library cache lock。而且username全部为空。
select * from gv$session_wait where event like ‘library cache lock‘;
在v$session视图中查询library cache lock等待相关的会话信息，发现username为空而且不是oracle后台进行。这就是说这些会话还没有连接到数据库，一直在等待验证状态。
1.首先我是通过下面语句查询的。
select sid,username,event,schemaname from v$session;

1 DIAG idle wait SYS

2 ASM background timer SYS

3 class slave wait SYS

4 class slave wait SYS

7 library cache lock SYS

8 library cache lock SYS

9 library cache lock SYS

10 library cache lock SYS

11 Streams AQ: waiting for time management or cleanup tasks SYS

12 library cache lock SYS

16 library cache lock SYS

17 Space Manager: slave idle wait SYS

18 library cache lock SYS

19 library cache lock SYS

21 library cache lock SYS

22 library cache lock SYS

23 library cache lock SYS

25 library cache lock SYS

26 library cache lock SYS

27 library cache lock SYS

28 library cache lock SYS

29 library cache lock SYS

30 library cache lock SYS

31 library cache lock SYS

33 library cache lock SYS

34 library cache lock SYS

36 library cache lock SYS

37 library cache lock SYS

38 library cache lock SYS

39 library cache lock SYS

40 library cache lock SYS

41 library cache lock SYS

42 library cache lock SYS

43 library cache lock SYS

44 library cache lock SYS

45 library cache lock SYS

47 library cache lock SYS

49 library cache lock SYS

51 library cache lock SYS

52 library cache lock SYS
....................................
后来和同事分析了下，怀疑是不是oracle 11g 新特性密码错误验证延迟导致的。

密码错误验证延迟

Preventing passwords from being broken. If a user tries to log in to Oracle Database multiple times using an incorrect password, Oracle Database delays each login after the third try. This protection applies for attempts made from different IP addresses or multiple client connections. For the first three attempts, there is no delay. Afterwards, it gradually increases the time before the user can try another password, up to a maximum of about 10 seconds. If the user enters the correct password, he or she is able to log in successfully without any delay.
This feature significantly decreases the number of passwords that an intruder would be able to try when attempting to log in. It is designed to prevent repeated attacks on password checking.
我们通过修改参数。屏蔽了密码错误验证延迟EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"

SQL> ALTER SYSTEM SET EVENT = ‘28401 TRACE NAME CONTEXT FOREVER, LEVEL 1’；
后来数据库正常了。
我们也可以使用oerr ora 28401查看密码错误验证延迟事件。
[oracle@test ~]$ oerr ora 28401
28401, 00000, "Event to disable delay after three failed login attempts"
// *Document: NO
// *Cause: N/A
// *Action: Set this event in your environment to disable the login delay
// which will otherwise take place after three failed login attempts.
// *Note: THIS IS NOT A USER ERROR NUMBER/MESSAGE. THIS DOES NOT NEED TO BE
// TRANSLATED OR DOCUMENTED.

总结：

导致这次错误的原因是应用系统在做数据迁移时，我业务用户删除了，drop user test cascade;把用户相关的表一起删除了，随后建立了相同的用户。在此期间应用一直没停下来，应用系统一直在尝试登陆数据库，oracle 数据库在11g的新特性中新增密码错误后延迟验证的功能，在密码错误登陆次数过多后，用户验证延迟时间是递增的，从开始的两秒递增到六秒，因此下次做数据迁移时，需要先停止应用系统，大家也不要尝试着以错误的密码过多的登陆数据库。

补充从10g升级到11g之后需要注意的几个密码方面问题：

1. 11g默认开始密码区分大小写，可以通过把参数设置为SEC_CASE_SENSITIVE_LOGON =FALSE 屏蔽

2. 11g密码默认有效期180天，可以通过修改ALTER PROFILE DEFAULT[根据实际的profile] LIMIT PASSWORD_LIFE_TIME UNLIMITED; 注意需要修改密码生效

3. 密码错误验证延迟，可以通过设置EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" 屏蔽