Oracle密码延时特性

Oracle密码延时特性

问题：
NC登录缓慢；打开任意节点都特别慢；

问题现象：

查看后台SQL执行效率特别低，等待事件均为row cache lock

解决方案

可能和11g密码延时特性有关，数据库设置28401事件，重启数据库，解决此问题；

关于密码延时特性实验如下：

---密码连续输入次数为10次，同一个用户连续输入10次错误密码，用户会被锁定

select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS' and profile='DEFAULT';

 

 结论：

在同一个session下，同一用户输入密码错误时，每次的登录延时都会延长，延时由1秒延时到7秒，错误10次，用户被锁；

如果在两个或多个session下，其中一个session一直以错误的密码连接数据库用户，其他session即使是以正常的密码连接相同的数据库用户，也会出现延时阻塞；

解决办法：

alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS' and profile='DEFAULT';

 

---查看密码输入错误次数

select name,lcount from user$ where name='CHEN';

 

 

select name,lcount from user$ where name='CHEN';

即使正确的密码，也会出现密码延时问题

另一个会话，即使以正确的密码连接相同数据库用户，也会出现密码延时问题；

 

ALTER system SET event='28401 trace name context forever, level 1' scope=spfile;

 
再次测试，已经没有延时现象了；

 

---ALTER system SET event='28401 trace name context off' scope=spfile;

 

 

Bug 7715339 - Logon failures causes "row cache lock" waits - Allow disable of logon delay (文档 ID 7715339.8)

转到底部

---

修改时间: 2016-1-30 类型: PATCH

Bug 7715339  Logon failures causes "row cache lock" waits - Allow disable of logon delay  This note gives a brief overview of bug 7715339.   The content was last updated on: 24-JAN-2016  Click here for details of each of the sections below. Affects: Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions >= 11.1 Versions confirmed as being affected 11.1.0.7 Platforms affected Generic (all / most platforms affected) Fixed: The fix for 7715339 is first included in 11.2.0.1 (Base Release) Interim patches may be available for earlier versions - click here to check. Symptoms: Related To: Performance Affected (General) Waits for "row cache lock" Security ( Authentication / Privileges / Auditing ) Description In 11g there is an intentional delay between allowing failed logon attempts to retry. For some specific application types this can cause a problem as the row cache entry is locked for the duration of the delay . This can lead to excessive row cache lock waits for DC_USERS for specific users / schemas .   This "fix" allows the logon delay to be disabled in 11.2.0.1 onwards by setting event 28401 in the init.ora. eg:     event="28401 trace name context forever, level 1" # disable logon delay. This "event" will disable the logon sleep delay system-wide, ie. it will affect all user accounts, system-wide, and so should be used     with extreme caution.   Example scenario:  A mix of correct and incorrect logon attempts occur for user X  On each successive failed login attempt the failed logon count   is incremented for user X.    Without this fix (without the event set):   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).    With the fix (with the event set):   There is no sleep delay.    In either scenario the configured logon profile rules are still  applied (eg: The profile option FAILED_LOGIN_ATTEMPTS is still  honored and so if the account becomes locked due to exceeding  this FAILED_LOGIN_ATTEMPTS then further attempts to  log in will then correctly fail immediately with no delay).   Note:  One off fixes for this issue for 11.1.0.7 do not need an event set -  interim patches for 11.1 disable the delay unconditionally.   Work Around:  Ensure the correct password is used - especially for connection  intensive logons   Getting a Fix  Use one of the "Fixed" versions listed above  (for Patch Sets / bundles use the latest version available as   contents are cumulative - the "Fixed" version listed above is   the first version where the fix is included)  or  You can check for existing interim patches here: Patch:7715339   Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support. References Bug:7715339 (This link will only work for PUBLISHED bugs) Note:245840.1 Information on the sections in this article

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2124332/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-2124332/