watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=


Q           The title is as follows :

stay Oracle in , What is password delay verification ? How to shield ?


     
A           The answer is as follows :          



answer : from Oracle 11g Start , If a user attempts to log in to the database with an incorrect password , So as the number of login failures increases , The delay waiting time before each login verification also increases .

By setting EVENTS 28401 Can block password delay verification :

ALTER SYSTEM SET EVENT = '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE;

After setting the event, restart the database . About 28401 The explanation of the event is as follows :

[[email protected] ~]$ oerr ora 28401

28401, 00000, "Event to disable delay after three failed login attempts"

// *Document: NO

// *Cause: N/A

// *Action: Set this event in your environment to disable the login delay

//          which will otherwise take place after three failed login attempts.

// *Note: THIS IS NOT A USER ERROR NUMBER/MESSAGE. THIS DOES NOT NEED TO BE

//        TRANSLATED OR DOCUMENTED.

For a normal system , Because of the password change , There may be some missing clients , Repeatedly trying to log in to the database with the wrong password , This will cause a long time of “library cache lock” or “row cache lock” The waiting for , This is very common . This phenomenon Oracle 10.2 and 11.1 The waiting events in are :“row cache lock”, And in the Oracle 11.2 The waiting events in are :“library cache lock”.

When there are such problems , Very typical AWR The report presents the following . First, in the TOP 5 in , You may see significant library cache lock The waiting for , The following example is from Oracle 11.2.0.3 The reality of the version :

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

 

If the database version is less than 11.2, It's in AWR The possible scenarios in are as follows :

 

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=


In such cases , Time model (Time Model Statistics) The following indicators will be displayed in , among ,“connection management call elapsed time” Occupy the main DB Time, This wait directly indicates that it was generated when the database connection was established :

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

 

Besides , stay AWR Medium Dictionary Cache Stats Part of it can also see the obvious wait .

The problem lies in MOS The corresponding document on is :“High 'library cache lock' Wait Time Due to Invalid Login Attempts(1309738.1)”. Besides , from Oracle 11g Start , Enabled password case verification , If the database is from Oracle 10g Upgraded or migrated , So we need to pay special attention to this change , By initializing the parameters SEC_CASE_SENSITIVE_LOGON To control this feature .