Home » RDBMS Server » Server Administration » SLOW login
SLOW login [message #545801] Fri, 02 March 2012 09:07 Go to next message
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.
Re: SLOW login [message #545802 is a reply to message #545801] Fri, 02 March 2012 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>Is there something I can do to trace what is happneing behind the scences to determine what the login delay may be..
yes

Does this problem exist from remote client or local to DB Server itself.
What is OS name & version?
post results from following SQL below

SELECT * FROM V$VERSION;
SLOW login [message #545808 is a reply to message #545802] Fri, 02 March 2012 09:25 Go to previous messageGo to next message
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

Re: SLOW login [message #545809 is a reply to message #545801] Fri, 02 March 2012 09:33 Go to previous messageGo to next message
guyfomi
Messages: 11
Registered: March 2012
Location: Cameroon
Junior Member
did you customize a login.sql for this user ?
SLOW login [message #545810 is a reply to message #545809] Fri, 02 March 2012 09:38 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
No there is not custom login.sql. How can I trace what is
happneing for this user, a trigger maybe?
Re: SLOW login [message #545811 is a reply to message #545809] Fri, 02 March 2012 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

do as below from terminal window

script capture.log
truss sqlplus slowuser/password
exit
CTRL-D

what clues exist within capture.log?
SLOW login [message #545824 is a reply to message #545811] Fri, 02 March 2012 10:43 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
There are no clue in the capture log but that was a good idea
thank you. This DB was cloned from my prod yeseterday using
active clone.

I feel that some how this login got courrupted in some way. For
example, though v$sesssion show no logins for this ID I was
unable to drop the id.

I created another user with the same exactt privs/password and
one character difference in the name and this appesrs to work
fine like my other logins.

I will not get Oracle involved.. Thank you all for your input.
SLOW login [message #546964 is a reply to message #545824] Sat, 10 March 2012 15:09 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
for those of you who are interested I had applications running
from several clients.. One of the APP's was trying to login
to the DB with the wrong password over and over again. There
was no evidence the account was locked.

To solve the issue, my app was changed and used the correct
password. In order for this to take effect, I locked the
acocunt and unlocked it.

After that logins via sqlplus on the server were instantaneous,
like all other logins.

Can anybody explain what Oracle is doing under the covers
when getting a bad password. I would not have thought it would
effected other sessions if the account was not locked.

Anyway, I just want to share in case somebody is experincing
something similiar.

SLOW login [message #549339 is a reply to message #546964] Thu, 29 March 2012 11:40 Go to previous messageGo to next message
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"
Re: SLOW login [message #549343 is a reply to message #549339] Thu, 29 March 2012 13:32 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think you should write this up as a blog article, it might help a few people.
Previous Topic: Instance RAC down - Listener High Processing
Next Topic: Archive log file's sequence number reduced.
Goto Forum:
  


Current Time: Thu Mar 28 02:55:38 CDT 2024