System HANG caused by password delay Verification

It is also a problem caused by a new 11g feature. This new feature has been studied long ago and has encountered similar problems with other customers. Starting from 11g, if a user attempts to log on to the database with an incorrect password, as the number of Logon failures increases, the waiting time before each login verification will also increase: SQLSETTIMEON: 30: 54SQL:

It is also a problem caused by a new 11g feature. This new feature has been studied long ago and has encountered similar problems with other customers. Starting from 11g, if a user attempts to log on to the database with an incorrect password, as the number of Logon failures increases, the waiting time before each login verification will also increase: SQL set time on: 30: 54 SQL:

It is also a problem caused by a new 11g feature.
This new feature has been studied long ago and has encountered similar problems with other customers. Starting from 11g, if a user attempts to log on to the database with an incorrect password, as the number of Logon failures increases, the waiting time before each login verification will also increase:

SQL> SET TIME ON:30:54 SQL> :30:58 SQL> conn test/testConnected.:31:25 SQL> :31:25 SQL> conn test/aconn test/aconn test/aconn test/aconn test/aconn test/aconn test/aconn test/testconn test/aERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected TO ORACLE.:31:26 SQL> ERROR:ORA-01017: invalid username/password; logon denied:31:26 SQL> ERROR:ORA-01017: invalid username/password; logon denied:31:26 SQL> ERROR:ORA-01017: invalid username/password; logon denied:31:27 SQL> ERROR:ORA-01017: invalid username/password; logon denied:31:29 SQL> ERROR:ORA-01017: invalid username/password; logon denied:31:32 SQL> ERROR:ORA-01017: invalid username/password; logon denied:31:36 SQL> Connected.:31:36 SQL> ERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected TO ORACLE.:31:36 SQL>

As you can see, from the third logon with a wrong password, the delay starts to 2 s, 3 s, and increases progressively. At this time, the correct password is provided for logon, and the session will be delayed for N seconds and then verified. However, once the verification is successful, the failure count will be cleared, and subsequent logon errors will be recalculated.
However, this is only the case where a single session fails to log on. If two sessions exist at the same time, the verification delay will soon reach the level of 10 s and 20 s. If a large number of connections use incorrect passwords at the same time, the user's logon will be completely HANG.
A similar situation occurs in the customer's database. The database version is 11.2.0.3 RAC. in the database, it is observed that the number of SESSIONS on each of the three nodes is close to 3000 of the SESSIONS set by the SESSIONS parameter, the background advanced log has a ORA-20 error. Because the customer system has only one key user, almost all sessions cannot log on to the database normally. In the database, a large number of session usernames, events, and PROGRAM information are found to be NULL, which indicates that these sessions have not been successfully verified and are logged on to the database. The CPU resources of the current host are not high, and those processes that have been connected to the database can work normally. Try to use SYSTEM and other users to quickly log on to the database. All of this indicates that one or more middleware servers are currently using the wrong password to connect to the database. Due to the password delay authentication policy, all subsequent connections are HANG.
When any new feature improves performance or functionality, related bugs will also be introduced. Obviously, this security consideration sometimes brings about performance verification problems, it has even become a means of attacking databases.
In the past few times, there have been no means to completely block password delay verification. The most powerful thing in Oracle is that almost all functions and features have corresponding switches, by setting EVENTS 28401, you can disable password delay Verification:

SQL> ALTER SYSTEM SET EVENT = ‘28401 TRACE NAME CONTEXT FOREVER, LEVEL 1’ SCOPE = SPFILE；

Set the event and restart the database.

Original article address: The HANG system caused by password delay verification. Thank you for sharing it with the original author.