Analysis of library cache lock caused by oracle11g password delay authentication

In Oracle 11g, Oracle introduces new features for "Password delay verification" for improved security. The effect of this feature is that if a user enters an incorrect password to try to log in, the time of the authentication before each logon increases as the number of login errors increases, slowing down the possibility of a password attempt to attack the database repeatedly.

However, for a normal system, due to changes in the password, there may be some missing clients, repeated attempts, resulting in the database for a long time to wait for the Library Cache lock, this situation is very common.

If you encounter this type of problem, you can turn off this feature through event 28401, thereby eliminating such effects, the following command modifies the settings in the parameters file:

ALTER SYSTEM SET EVENT =

' 28401 TRACE NAME CONTEXT FOREVER, Level 1 ' SCOPE = SPFILE;

This type of problem is typical of the AWR report presented below, first in the TOP 5, you may see significant Library Cache Lock waiting, if the wait time with SQL, then the username column is empty, the following example from the 11.2.0.3.0 version of the real situation:

In such cases, the time model will display the following indicator, where connection management call elapsed times occupies the primary db time, which is directly indicative of the creation of a database connection:

This type of problem, which is common and deterministic in Oracle 11g, can be found on MOS: High' Library cache lock ' Wait time Due to Invalid Login attempts (1309738.1) In addition, Oracle 11g turns on password case verification, and if you upgrade from Oracle 10g, you need to be particularly wary of this change, which can be controlled by initializing the parameter Sec_case_sensitive_logon.

Here is a case: online picking up someone else's case

1, the source of the problem

Previously encountered a problem modified user name password, found to log in with a new password is hang, and then the entire company's OA system completely paralyzed, detailed status see previous records.

Recently learned the new feature of oracle11g password delay, only to understand the problem is due to the password delay caused.

The general situation is: starting from oracle11g, if the user entered the wrong password login, then as the number of login errors increase, the time to wait for verification before each logon will also increase, The intention is to protect the database is malicious login time consuming too many DB resources caused by high database consumption caused the database server problems, but also caused a problem, if the use of bad password logon too much, it will affect the user's normal login, This means that the password has a validation delay that causes you to enter the correct password to log in and wait a long time. The experience for the user is that the database is stuck (in fact, you use other users to operate the database completely normal)

2, Case demo

Oracle version is 11g branch 11.2.0.1.0:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as [email protected]_vm128

Sql>

Set time display: Sql> set time on; 07:41:57 sql> Conn Timdba/timgood; Connected. 07:42:48 sql> Conn timdba/t; #开始尝试错误密码登录 ERROR: Ora-01017:invalid Username/password; Logon denied Warning:you is no longer connected to ORACLE. 07:42:49 sql> Conn timdba/t; #第1次错误登录消耗时间1秒 ERROR: Ora-01017:invalid Username/password; Logon denied 07:42:51 sql> Conn timdba/t; # 2nd Error Login time consuming 2 seconds ERROR: Ora-01017:invalid Username/password; Logon denied 07:42:52 sql> Conn timdba/t; # 3rd Error Login time consuming 1 seconds ERROR: Ora-01017:invalid Username/password; Logon denied 07:42:54 sql> Conn timdba/t; # 4th Error Login time consuming 2 seconds ERROR: Ora-01017:invalid Username/password; Logon denied 07:42:57 sql> Conn timdba/t; #第5次错误登录消耗时间3秒 ERROR: Ora-01017:invalid Username/password; Logon denied 07:43:02 sql> Conn timdba/t; # 6th Error Login time consuming 5 seconds ERROR: Ora-01017:invalid Username/password; Logon denied 07:43:07 sql> Conn timdba/t; # 7th Error Login time consuming 5 seconds ERROR: Ora-01017:invalid Username/password; Logon denied 07:43:13 sql> Conn timdba/t; # 8th Error Login time consuming 6 seconds ERROR: Ora-01017:invalid Username/password; Logon denied 07:43:20 sql> Conn timdba/t;# 9th Error Logon consumption time 7 seconds ERROR: Ora-01017:invalid Username/password; Logon denied 07:43:28 sql> 07:43:29 sql> Conn Timdba/timgood; Connected. 07:43:40 sql>

You can see the 4th time, the 5th time start, the error login verification time is getting longer. The basic delay is more than one second each time, and the back even if the correct password is entered, it will be delayed for more than 10 seconds.

In the course of testing, once the correct password is entered, after verification succeeds, the error delay will clear 0, starting from 0 to recalculate the number:

08:15:30 sql> Conn timdba/t; ERROR: Ora-01017:invalid Username/password; Logon denied 08:15:34 sql> Conn Timdba/timgood; Connected. 08:15:37 sql> Conn timdba/t; ERROR: Ora-01017:invalid Username/password; Logon denied Warning:you is no longer connected to ORACLE. 08:15:39 sql> Conn timdba/t; ERROR: Ora-01017:invalid Username/password; Logon denied 08:15:40 sql>

Everyone further diffuse under the thinking, this is only a single session to do the test, if it is an online environment, thousands of sessions come over, if the password is wrong, together with delay, according to a single operation delay of one second to calculate, the basic delay of 1000 seconds, that is, half an hour you login interface card where, This gives the customer experience is entered the correct password, the results click the login button, it stuck, dead and alive, the server is paralyzed, it means that the application system hang.

3, the new feature is a double-edged sword

Any new Oracle feature delivers performance improvements and security assurances, but Oracle is only a software software, software will have bugs and even be exploited by others.

Oracle does not give a thorough screen password delay in a few minor releases of 11g, but Oracle has powerful other accessibility features that can be handled by setting event events.

4, by setting the event screen password delay

This is usually set to 28401 is enough, if you encounter other special circumstances, you can set up again, then by setting the events 28401来 to implement blocking password delay verification:

ALTER SYSTEM SET EVENT = ' 28401 TRACE namecontext FOREVER, Level 1 ' SCOPE = SPFILE;

Alter system set event= "10949 tracename context forever:28401 Trace name Context FOREVER, Level 1" scope=spfile;

Sql> set time on; 08:56:22 sql> ALTER SYSTEM SET EVENT = ' 28401 TRACE NAME ' CONTEXT FOREVER, Level 1 ' SCOPE = SPFILE; System altered. 08:56:27 sql> create Pfile from SPFile; File created. 08:56:29 sql>

After that, restarting the Oracle database takes effect.

08:56:44 sql> shutdown Immediate; Database closed. Database dismounted. ORACLE instance shut down. 08:57:05 sql> startup; ORACLE instance started. Total System Global area 835104768 bytes Fixed Size 2217952 bytes Variable Size 545261600 bytes Database buffers 281018368 bytes Redo buffers 6606848 bytes Database mounted. Database opened. 08:57:46 sql>

Verify the error password delay verification again, and you can see that there is almost no delay:

08:58:28 sql> Conn Timdba/timgood; Connected. 08:58:33 sql> Conn timdba/t; ERROR: Ora-01017:invalid Username/password; Logon denied Warning:you is no longer connected to ORACLE. 08:58:37 sql> Conn timdba/t; ERROR: Ora-01017:invalid Username/password; Logon denied 08:58:38 sql> Conn timdba/t; ERROR: Ora-01017:invalid Username/password; Logon denied 08:58:39 sql> Conn timdba/t; ERROR: Ora-01017:invalid Username/password; Logon denied 08:58:39 sql> Conn timdba/t; ERROR: Ora-01017:invalid Username/password; Logon denied 08:58:40 sql> Conn timdba/t; ERROR: Ora-01017:invalid Username/password; Logon denied 08:58:41 sql> Conn timdba/t; ERROR: Ora-01017:invalid Username/password; Logon denied 08:58:42 sql> Conn timdba/t; ERROR: Ora-01017:invalid Username/password; Logon denied

Analysis of library cache lock caused by oracle11g password delay authentication