Oracle 11g, 12c large number of error logging attempts to bring database exceptions

Applies To:

Oracle Database - Enterprise Edition - Version 10.2.0.5 and laterInformation in this document applies to any platform.

Cause

A hang is possible in earlier versions of RDBMS as a result of an unpublished bug fixed in the following versions:    12.1.0.1 (Base Release)    11.2.0.2 (Server Patch Set)    11.1.0.7 Patch 42 on Windows Platforms

Document 9776608.8 Bug 9776608 - Hang from concurrent login to same account with a wrong passwordEven with this fix, numerous failed logins attempts can cause row cache lock waits and/or library cache lock waits. This was reported in:Bug 11742803 LOTS OF ‘LIBRARY CACHE LOCK‘ DURING USER LOGON AUTHENTICATIONThis was closed as not a bug because there is an intentional wait when a login fails.

Solution

In Oracle 11g Release 11.1.0.7,  the wait is disabled unconditionallyIn Oracle 11g Release 2 and higher, in order to disable the wait between login failures the event 28401 needs to be explicitly enabled:The event can be set as follows:alter system set event ="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" scope=spfile;To unset the event, set as follows:SQL> Alter system set event= ‘28401 trace name context off‘ scope=spfile ;

Summary:
About a large number of concurrent error passwords connecting to the database can cause the database to hang, or cause performance problems, the impact on the database is very large, recently encountered such a case. Of course, Oracle itself is a very good feature, but in the present there are some problems, you can disable this feature with event 28401. But it is better to manage the problem from the management, from the security level to control.

Oracle 11g, 12c large number of error logging attempts to bring database exceptions