SLOW login [message #545801] |
Fri, 02 March 2012 09:07 |
|
BeefStu
Messages: 208 Registered: October 2011
|
Senior Member |
|
|
I have a situation where when I login as a user to my DB
via sqlplus no service name it takes about 20 secs to connect.
Yet when I login as a user with DBA privs it logs in immediately.
Is there something I can do to trace what is happneing behind
the scences to determine what the login delay may be..
Any help or code would be greatly appreciated.
Thanks to all who answer.
|
|
|
|
SLOW login [message #545808 is a reply to message #545802] |
Fri, 02 March 2012 09:25 |
|
BeefStu
Messages: 208 Registered: October 2011
|
Senior Member |
|
|
This happens on the same server the DB is on.
I am working with Oracle 11.1.0.7 Solaris 2.10
What can I do to figure out what the problem maybe?
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
I dont see anything in the alert log nor do I see .trc files
being generated.
I also granted this user DBA privs and it is still extrenly
slow to login (20 seconds or so) compared to other DBA logins
(under a second).
[Updated on: Fri, 02 March 2012 09:31] Report message to a moderator
|
|
|
|
|
|
|
|
SLOW login [message #549339 is a reply to message #546964] |
Thu, 29 March 2012 11:40 |
|
BeefStu
Messages: 208 Registered: October 2011
|
Senior Member |
|
|
FYI, Here is the scenario I ran into
Problem Statement:
Multiple concurrent connection requests with invalid password saturates the UDP ports and can bring down the 11g Database infrastructure.
Oracle Explanation:
This is due to a code enhancement that was done in 11g to protect customers from password hacking if a flurry of login attempts with wrong password is seen. The code enhancement introduces a sleep() after the 3rd unsuccessful login attempt and this sleep() time can be from 2 seconds up to 10 seconds. So after every unsuccessful attempt, the account status is updated to get the number of successful attempts and lock the account if required based on the FAILED_LOGIN_ATTEMPTS profile limit , and then this sleep() time is set accordingly. What this does is, every session(after the 3rd unsuccessful attempt) trying to make a login with a wrong password, will hold the library cache lock, on the object needed to update the account login information, for a longer time, such that the next session trying to make a login attempt waits.
This creates the queue of sessions and they take time to clear out as the "library cache locks" take that much longer to clear out. This would explain why the timeouts were seen and why the sessions take time to clear out after the application processes have exited. Since "every" database process created will have to get the "library cache lock" in order to throw the ORA-1017 (invalid username/password) and then exit.
Workaround:
Add the below event in - spfile or the init file and restart the database
EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"
|
|
|
|