6 Replies Latest reply: Aug 20, 2014 12:22 PM by Mark D Powell RSS

    Database Link Issue

    user975916

      I have 32 test databases that log into a common database A658 as user APP735TOTE01 via a database link to query a table on A658. The user account APP735TOTE01 locked out after 10 unsuccessful logins. When I unlocked the user account APP735TOTE01 and tested login via database link, it failed. I altered the database link and entered the same password. I was then able to successfully connect to A658 via that database link. I found that I had to repeat this process for the other 31 test databases.  A month later, it happened again. It seems that when that APP735TOTE01 account locks and I subsequently unlock it, all the database links no longer work. I have to alter them and re-enter the same password to get them to connect successfully. Any ideas on what could be causing the database links to fail?  That user account APP735TOTE01 only resides on database A658.  Database A658 is at Oracle version 11.2.0.3.0 and all 32 connecting databases are at Oracle version 11.2.0.4.0.

        • 1. Re: Database Link Issue
          sol.beach

          does password expire after 30 days?

          • 2. Re: Database Link Issue
            Mark D Powell

            user975916, I have not encountered or heard of this issue before.  Like Sol I think you should check to see if the user account being used has a password expiration date set.  If yes, I would use an account without a password expiration date.  Being that you are on 11g in all cases is the password for the account in question set up as case sensitive or insensitive?

            - -

            HTH -- Mark D Powell --

            PS - I intended to ask, what Oracle error is returned after the user account is unlocked when you try to query across the database link?

            • 3. Re: Database Link Issue
              user975916

              No, sol.beach,, I verified that it does not expire per the user profile. 

              • 4. Re: Database Link Issue
                user975916

                Mark,

                I just altered a db link and put in a wrong password on one of the test databases. I tried to log into A658 and got this Oracle error:

                Link  : "PSA_TOTE"

                Error : ORA-01017: invalid username/password; logon denied

                ORA-02063: preceding line from PSA_TOTE

                ****

                I then logged in 10 times and locked the APP735TOTE01 account and got this Oracle error:

                *********************************************************************

                Link  : "PSA_TOTE"

                Error : ORA-28000: the account is locked

                ORA-02063: preceding line from PSA_TOTE

                *********************************************************************

                 

                I unlocked the APP735TOTE01 account on A658 and then checked all 32 database links, expecting them to be bad as before. They were all good. Successful connections. What the heck?   Thanks for your help,  Ron

                • 5. Re: Database Link Issue
                  EdStevens

                  user975916 wrote:

                   

                  Mark,

                  I just altered a db link and put in a wrong password on one of the test databases. I tried to log into A658 and got this Oracle error:

                  Link  : "PSA_TOTE"

                  Error : ORA-01017: invalid username/password; logon denied

                  ORA-02063: preceding line from PSA_TOTE

                  ****

                  I then logged in 10 times and locked the APP735TOTE01 account and got this Oracle error:

                  *********************************************************************

                  Link  : "PSA_TOTE"

                  Error : ORA-28000: the account is locked

                  ORA-02063: preceding line from PSA_TOTE

                  *********************************************************************

                   

                  I unlocked the APP735TOTE01 account on A658 and then checked all 32 database links, expecting them to be bad as before. They were all good. Successful connections. What the heck?   Thanks for your help,  Ron

                  There is nothing special about a db link.  It is just defining a client connection to a remote database, for use by the local database.  The connection passes through TNS, arrives at the listener, and on to the target database exactly as if the request had originated with "sqlplus scott/tiger@orcl".  When the request arrives at the remote database, the username/password are presented for authentication.  An incorrect password here is the same as an incorrect password from a sqlplus session.  Too many incorrect passwords (from any source, or several sources in aggregate) and the account locks.  Unlock the account (still not changing the password) and a connection request using the correct password will succeed.  Of course changing a password does not change what the db links think the password is.

                  • 6. Re: Database Link Issue
                    Mark D Powell

                    LOL.  Your are welcome.  -- Mark --