Shulou(Shulou.com)05/31 Report--

I would like to share with you what to do when a batch of incorrect user names and passwords in the database lead to business users living in HANG. I believe most people don't know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

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 questions are as follows

SQL > conn doudou/oracle (HANG lives)

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 BY

CASE WHEN state! = 'WAITING' THEN' WORKING'

ELSE 'WAITING'

END

CASE WHEN state! = 'WAITING' THEN' On CPU / runqueue'

ELSE event

END

ORDER BY

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

Combined with waiting events to analyze

1.library cache lock waits seriously, on the other hand, considering that only this business user doudou cannot log in, and other business types of users doudou01 will not be affected. It is suspected again that this problem may be caused by the 11g password delay mechanism.

two。 Then check the time when the user changed the password

Select * from sys.user$ where name='DOUDOU'

PTIME=2013/11/6 11:22:09-PTIME is the date the password was last changed

CTIME=2013/11/6 11:22:09-CTIME is the date the user was created

From here, we can see that our DOUDOU users have not changed their passwords, but why there is a large number of library cache lock that have not changed their passwords, but will there be any errors in the user passwords of the new business configuration? this asked the developers that their configuration was wrong and the user password was misconfigured. That is, batch requests for incorrect users and passwords result in a large number of library cache lock.

Search MOS and find a similar case.

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. Problem solved, after the correct user password configuration, and set the parameter EVENT= "28401 TRACE NAME CONTEXT FOREVER, LEVEL 1", a large number of library cache lock gradually reduced, and finally eliminated. The new business is also in normal use.

Schedule

User$ View interpretation

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.

Log user login failure trigger:

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

/

Special thanks: Travel http://www.traveldba.com/

Schedule:

Query the logger of the wrong password

Select username

Os_username

Userhost

Client_id

Trunc (timestamp)

Returncode

Count (*) failed_logins

From dba_audit_trail

Where returncode=1017 and-1017 is invalid username/password

Timestamp < sysdate

Group by username,os_username,userhost, client_id,trunc (timestamp), returncode

Order by trunc (timestamp) desc

The above is all the contents of the article "what to do if the business user HANG is caused by a batch of incorrect usernames and passwords in the database?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.