14 Replies Latest reply on Mar 8, 2009 7:28 AM by 686680

    user is getting locked (Timeout) Intermittently!!!

    686680
      Hi all,

      one user is getting locked sporadically, without any reason that I can see in the Alertlog file.

      SQL> host date
      Sat Mar 7 07:32:25 WAT 2009

      SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

      USERNAME ACCOUNT_STATUS
      ------------------------------ --------------------------------
      INTEGRATION_TEST LOCKED(TIMED)

      SQL> alter user INTEGRATION_TEST account unlock;

      User altered.
      SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

      USERNAME ACCOUNT_STATUS
      ------------------------------ --------------------------------
      INTEGRATION_TEST OPEN
      .........................................................
      SQL> host date
      Sat Mar 7 07:41:20 WAT 2009

      SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

      USERNAME ACCOUNT_STATUS
      ------------------------------ --------------------------------
      INTEGRATION_TEST LOCKED(TIMED)

      SQL> alter user INTEGRATION_TEST account unlock;

      User altered.

      SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

      USERNAME ACCOUNT_STATUS
      ------------------------------ --------------------------------
      INTEGRATION_TEST OPEN
      ...........................................................
      SQL> host date
      Sat Mar 7 07:56:02 WAT 2009

      SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

      USERNAME ACCOUNT_STATUS
      ------------------------------ --------------------------------
      INTEGRATION_TEST LOCKED(TIMED)

      SQL> alter user INTEGRATION_TEST account unlock

      User altered.

      SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

      USERNAME ACCOUNT_STATUS
      ------------------------------ --------------------------------
      INTEGRATION_TEST OPEN
      ..........................................................
      SQL> host date
      Sat Mar 7 08:08:06 WAT 2009

      SQL> /

      USERNAME ACCOUNT_STATUS
      ------------------------------ --------------------------------
      INTEGRATION_TEST LOCKED(TIMED)

      SQL> alter user INTEGRATION_TEST account unlock;

      User altered.

      SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

      USERNAME ACCOUNT_STATUS
      ------------------------------ --------------------------------
      INTEGRATION_TEST OPEN
      ...............................................................

      SQL> host date
      Sat Mar 7 08:19:56 WAT 2009

      SQL> /

      USERNAME ACCOUNT_STATUS
      ------------------------------ -----------------
      INTEGRATION_TEST LOCKED(TIMED)

      SQL> alter user INTEGRATION_TEST account unlock;

      User altered.
      USERNAME ACCOUNT_STATUS
      ------------------------------ --------------------------------
      INTEGRATION_TEST OPEN
      ..................................................

      5 days before I saw an error WARNING INBOUND CONNECTION TIMEOUT (ORA-3136)
      in the Alert log file then I followed the metalink doc *465043.1* it suggest to increase the sqlnet.inbound_connect_timeout to a greater value than 60 sec, I made that 120 sec and still the user is getting locked, but since 4 days there is no Inbound connect timeout error being raised in the Alert log file.
      This workaround didn't work in stopping the account from being locking. The metalink doc suggests checking the local connections and the network delay, but the local connections are fine and there is no network delay.

      $ tnsping central

      TNS Ping Utility for HPUX: Version 10.2.0.4.0 - Production on 04-MAR-2009 16:25:54

      Copyright (c) 1997, 2007, Oracle. All rights reserved.

      Used parameter files:

      /u01/app/oracle/db/network/admin/sqlnet.ora

      Used TNSNAMES adapter to resolve the alias

      Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
      (HOST = hrdb03)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAM
      E = central) (INSTANCE_NAME = central)) (HS = OK))
      OK (0 msec)

      PLEASE help!!!
        • 1. Re: user is getting locked (Timeout) Intermittently!!!
          OrionNet
          Hello,

          Do you have default profile asssigned to this user? Or you have some profile assinged to it? CHeck profile assinged to this user.

          Regards
          • 2. Re: user is getting locked (Timeout) Intermittently!!!
            Anand...
            Hi..

            The LOCKED(TIMED) value is what you always see if the account was locked due to the number of failed logins > FAILED_LOGIN_ATTEMPTS. So what is the FAILED_LOGIN_ATTEMPTS parameter set in the user's profile.Check whther the password entered is correct or not.


            HTH
            Anand
            • 3. Re: user is getting locked (Timeout) Intermittently!!!
              686680
              Thanks for your reply!!

              SQL> select username,profile from dba_users where username='INTEGRATION_TEST';

              USERNAME PROFILE
              ------------------------------ ------------------------------
              INTEGRATION_TEST DEFAULT

              SQL> select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS';

              PROFILE RESOURCE_NAME RESOURCE LIMIT
              ---------- --------------- -------- -------- --------------------------------
              DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

              I just now set the sqlnet.inbound_connect_timeout=0 in sqlnet.ora and inbound_connect_timeout=0 in listener.ora files respectively, still that user is getting locked.
              • 4. Re: user is getting locked (Timeout) Intermittently!!!
                Anand...
                Hi can you post the output of

                sql> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';

                You can have a look at Doc ID: 752057.1 on metalink.

                I don't think sqlnet.inbound_connect_timeout has to do anything with the account getting locked(times) because SQLNET.INBOUND_CONNECT_TIMEOUT parameter specifies the time, in seconds, for a client to connect with the database server and provide the necessary authentication information.If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection.

                [http://download.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm#sthref481]

                HTH
                Anand

                Edited by: Anand... on Mar 8, 2009 11:31 AM
                Info on inbound_connect_timeout
                1 person found this helpful
                • 5. Re: user is getting locked (Timeout) Intermittently!!!
                  OrionNet
                  Hello,

                  You can changed failed login attempts to unlimited in your profile and you should be good.

                  regards
                  1 person found this helpful
                  • 6. Re: user is getting locked (Timeout) Intermittently!!!
                    Anand...
                    You can changed failed login attempts to unlimited in your profile and you should be good.
                    It would be better if the OP creates a new profile with failed login attempts to unlimited.Changing the default profile i think is not a good idea and also not recommended by oracle.So,create a new profile and assign that profile to the user.



                    Anand
                    • 7. Re: user is getting locked (Timeout) Intermittently!!!
                      686680
                      Thanks again for your quick reply,

                      I created a new profile with the FAILED_LOGIN_ATTEMPTS limit UNLIMITED and assigned that profile to the user
                      SQL> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='I
                      NTG_DEFAULT';

                      RESOURCE_NAME RESOURCE LIMIT
                      ------------------------------ -------- ----------
                      COMPOSITE_LIMIT KERNEL UNLIMITED
                      SESSIONS_PER_USER KERNEL UNLIMITED
                      CPU_PER_SESSION KERNEL UNLIMITED
                      CPU_PER_CALL KERNEL UNLIMITED
                      LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
                      LOGICAL_READS_PER_CALL KERNEL UNLIMITED
                      IDLE_TIME KERNEL UNLIMITED
                      CONNECT_TIME KERNEL UNLIMITED
                      PRIVATE_SGA KERNEL UNLIMITED
                      FAILED_LOGIN_ATTEMPTS          PASSWORD UNLIMITED
                      PASSWORD_LIFE_TIME PASSWORD UNLIMITED
                      PASSWORD_REUSE_TIME PASSWORD UNLIMITED
                      PASSWORD_REUSE_MAX PASSWORD UNLIMITED
                      PASSWORD_VERIFY_FUNCTION PASSWORD NULL
                      PASSWORD_LOCK_TIME PASSWORD UNLIMITED
                      PASSWORD_GRACE_TIME PASSWORD UNLIMITED

                      16 rows selected.



                      Lets see what will happen next, the fellow stubborn user wiil stick to its behaviour or not.

                      Thanks & Regards.
                      • 8. Re: user is getting locked (Timeout) Intermittently!!!
                        OrionNet
                        Hello,

                        Some application might have wrong password and after trying connection 10 times it might be failing or some end user forgot the password and kept trying and end up locking.
                        *Next time when you post your code or query output enclose it between tag \
                         to preserve formatting*.
                        
                        Regards
                        
                        Edited by: OrionNet on Mar 8, 2009 1:38 AM
                        
                        Edited by: OrionNet on Mar 8, 2009 1:38 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                        • 9. Re: user is getting locked (Timeout) Intermittently!!!
                          JustinCave
                          Rather than changing the user's profile, it would seem to make sense to me to figure out what process is trying to use this account with the wrong password to try to log in to the database.

                          What version of Oracle are you using? Despite the version number on the tnsping output, I'm guessing that you are on 11.1 since the default profile had a non-unlimited FAILED_LOGIN_ATTEMPTS setting. Prior versions did not have that default behavior.

                          If my guess is right, then assuming that you haven't changed the default audit settings, Oracle is auditing failed login attempts (and login attempts generally) to the database audit trail. What does
                          SELECT *
                            FROM dba_audit_trail
                           WHERE return_code = 1017
                             AND username = <<user name that is getting locked>>
                          return? The COMMENT_TEXT field should tell you what machine the failed connection attempt is coming from.

                          Justin
                          • 10. Re: user is getting locked (Timeout) Intermittently!!!
                            Anand...
                            Hi Sir,
                            I'm guessing that you are on 11.1 since the default profile had a non-unlimited FAILED_LOGIN_ATTEMPTS setting. Prior versions did not have that default behavior.
                            From 10.2.0.1 the FAILED_LOGIN_ATTEMPTS attribute in DEFAULT profile has been changed from UNLIMITED to 10.And the tnsping too shows 10.2.0.4 in his first post.Although, its a good recommendation to figure out the process and check for why is it happening.


                            Anand
                            • 11. Re: user is getting locked (Timeout) Intermittently!!!
                              686680
                              I am using *10.2.0.4* I gave a new profile with FAILED_LOGING_ATTEMPTS to UNLIMITED to the locking user, and the below query did not return any rows.

                              SQL> SELECT * FROM dba_audit_trail WHERE returncode = 1017
                              AND username ='INTEGRATION_TEST';

                              no rows selected.
                              • 12. Re: user is getting locked (Timeout) Intermittently!!!
                                Anand...
                                Hi..

                                So is the problem recurring.


                                Anand
                                • 13. Re: user is getting locked (Timeout) Intermittently!!!
                                  JustinCave
                                  OK, if you're on 10.2.0.4, are you auditing failed login attempts? If not, DBA_AUDIT_TRAIL won't record those events.

                                  Justin
                                  • 14. Re: user is getting locked (Timeout) Intermittently!!!
                                    686680
                                    After giving the new profile with UNLIMITED FAILED_LOGIN_ATTEMPTS, the locking of the user is not happening, I think the problem resolution suggested by the metalink doc *752057.1* worked in my scenario as well. If the problem comes again then I will disturn u guyz again.

                                    Thanks gurus specially Anand and OrionNet.
                                    Take care.