User names and passwords are incorrect in batches, leading to library cache lock)

User names and passwords are incorrect in batches, leading to library cache lock)

Database Version
SQL> select * from v $ version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
PL/SQL Release 11.2.0.3.0-Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0-Production
NLSRTL Version 11.2.0.3.0-Production

The problem is as follows:
SQL> conn doudou/oracle (HANG)

View wait events
Select
Count (*),
Case when state! = 'Waiting' THEN 'working'
ELSE 'waiting'
End as state,
Case when state! = 'Waiting' then' On CPU/runqueue'
ELSE event
End as sw_event
FROM
V $ session_wait
GROUP
Case when state! = 'Waiting' THEN 'working'
ELSE 'waiting'
END,
Case when state! = 'Waiting' then' On CPU/runqueue'
ELSE event
END
ORDER
1 DESC, 2 DESC
/

Library cache lock WAITING 585
Rdbms ipc message WAITING 16
Space Manager: slave idle wait WAITING 3
Jobq slave wait WAITING 2
Streams AQ: waiting for time management or cleanup tasks WAITING 1
VKRM Idle WAITING 1
Smon timer WAITING 1
Streams AQ: qmn coordinator idle wait WAITING 1
Pmon timer WAITING 1
Streams AQ: qmn slave idle wait WAITING 1
DIAG idle wait waited known time 1
DIAG idle wait WAITING 1
Library cache lock waited known time 1
VKTM Logical Idle Wait WAITING 1
Asynch descriptor resize waited short time 1
SQL * Net message from client WAITING 1

Analysis based on wait events
1. The library cache lock wait is serious. On the other hand, it is considered that only the single business user doudou cannot log on, and other business users doudou01 will not be affected. Again, I suspect this problem may be caused by the 11g password delay mechanism.

2. Check the time when the user changed the password.
Select * from sys. user $ where name = 'douu ';
PTIME = 11:22:09 -- PTIME is the date the password was last changed
CTIME = 11:22:09 -- CTIME is the date the user was created
We can see from this that we have not modified the password for DOUDOU users, but why is there a large number of library cache locks and no password changed, but will the User Password configured for the new business be wrong, in this way, I asked the developers that their configuration was incorrect and the user password was incorrectly configured. That is, a large number of library cache locks are caused by batch requests of wrong users and passwords.

Search for MOS and find similar cases
Library Cache Locks Due to Invalid Login Attempts (Doc ID 1309738.1)

Cause
Numerous failed logins attempts can cause row cache lock waits and/or library cache lock waits.
Set the below event in the spfile or init. ora file and restart the database:

Alter system set event = "28401 trace name context forever, LEVEL 1" scope = spfile;

Or

EVENT = "28401 trace name context forever, LEVEL 1"

3. Solve the problem. After the correct user password is configured, set the parameter EVENT = "28401 trace name context forever, LEVEL 1". A large number of library cache locks are gradually reduced and finally eliminated. New services are also in normal use

 

Appendix

User $ view description
Test cases below show:

• CTIME is the date the user was created.
• LTIME is the date the user was last locked. (Note that it doesn' t get NULLed when you unlock the user ).
• PTIME is the date the password was last changed.
• LCOUNT is the number of failed logins.

Trigger for logging User Logon Failure:
Create or replace trigger logon_denied_to_alert
AFTER servererror ON DATABASE
DECLARE
Message VARCHAR2 (168 );
Ip VARCHAR2 (15 );
V_ OS _user VARCHAR2 (80 );
V_module VARCHAR2 (50 );
V_action VARCHAR2 (50 );
V_pid VARCHAR2 (10 );
V_sid NUMBER;
V_program VARCHAR2 (48 );
BEGIN
IF (ora_is_servererror (1017) THEN
 
-- Get ip FOR remote connections:
IF upper (sys_context ('userenv', 'network _ Protocol') = 'tcp 'THEN
Ip: = sys_context ('userenv', 'IP _ address ');
End if;
 
SELECT sid INTO v_sid FROM sys. v _ $ mystat WHERE rownum <2;
SELECT p. spid, v. program
INTO v_pid, v_program
FROM v $ process p, v $ session v
WHERE p. addr = v. paddr
AND v. sid = v_sid;
 
V_ OS _user: = sys_context ('userenv', 'OS _ user ');
Dbms_application_info.read_module (v_module, v_action );
 
Message: = to_char (SYSDATE, 'yyyymmdd HH24MISS ') |
'Logon denied from' | nvl (ip, 'localhost') | ''|
V_pid | ''| v_ OS _user | 'with' | v_program | '-' |
V_module | ''| v_action;
 
Sys. dbms_system.ksdwrt (2, message );
 
End if;
END;
/