OraFAQ Forum: SQL & PL/SQL » ORA-28002 error while connecting DB LINK

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-28002 error while connecting DB LINK

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-28002 error while connecting DB LINK [message #612651]

Wed, 23 April 2014 02:17

Bikash.p
Messages: 11
Registered: April 2014
Location: Banglore

Junior Member

Today I created a db link .

Show User;

USER is ORACLE

alter session set global_names=false;

session SET altered.

select * from dual@test1;

I am getting the below error.
Error at Command Line : 5 Column : 26
Error report -
SQL Error: ORA-28002: the password will expire within 9 days
28002. 00000 - "the password will expire within %s days"
*Cause: The user's account is about to about to expire and the password
needs to be changed
*Action: change the password or contact the DBA

And I have a db link previously to the same destination from another user, While I am using that user through db link then i am getting the output.

Show user;
USER is TEST

alter session set global_names=false;

session SET altered.

select * from dual@test1;

DUMMY
-----
X

Why this error is coming ? Please Provide the solution. Thanks In Adv.

Regards,

Report message to a moderator

Re: ORA-28002 error while connecting DB LINK [message #612653 is a reply to message #612651]

Wed, 23 April 2014 02:40

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

The error means what it says. If the other link doesn't give the same error then I can only assume it's not connected to the same user.

Report message to a moderator

Re: ORA-28002 error while connecting DB LINK [message #612700 is a reply to message #612651]

Wed, 23 April 2014 07:21

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

As cookiemonster said, the error means exactly what it says.

To explain a bit further and perhaps clarify your understanding ...

A database link connects to another database just like any other client process. If dbA has a link pointing to dbB, as far as dbB is concerned, dbA is just another client - no different than you connecting to dbB with sqlplus. As such, part of the link definition is the userid and password that the link needs to use to connect to dbB. Just exactly like you using a userid and password to connect to dbB with sqlplus. So if your failing link is connecting to dbB with username SNUFFY, your error means that the password for user SNUFFY in dbB is about to expire.

Report message to a moderator

Re: ORA-28002 error while connecting DB LINK [message #612730 is a reply to message #612700]

Wed, 23 April 2014 23:26

Bikash.p
Messages: 11
Registered: April 2014
Location: Banglore

Junior Member

Oh So sorry for that @cookiemonster. The destination is not same. They are Different users.

@EdStevens :- If i change the password of that db then i will connect it by DB link only if i am not wrong . Please Verify me?

Or any other option is there without changing the password.

Regards.

Report message to a moderator

Re: ORA-28002 error while connecting DB LINK [message #612732 is a reply to message #612730]

Thu, 24 April 2014 00:17

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If I understood the "password" question, you wonder what happens when target database link user's password changes. DB link won't work any more, so you'll have to recreate it. Here's an example:

SQL> show user
USER is "SCOTT"
SQL> create database link dbl_mike
  2  connect to mike
  3  identified by lion
  4  using 'ora10';

Database link created.

SQL> select * from dual@dbl_mike;

D
-
X

OK, everything is OK. I'll now change MIKE's password:

SQL> connect mike/lion@ora10
Connected.
SQL> alter user mike identified by fish;

User altered.

Back to SCOTT:

SQL> connect scott/tiger@ora10
Connected.
SQL> select * from dual@dbl_mike;
select * from dual@dbl_mike
                   *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from DBL_MIKE


SQL>

Report message to a moderator

Re: ORA-28002 error while connecting DB LINK [message #612742 is a reply to message #612730]

Thu, 24 April 2014 06:40

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Quote:

Or any other option is there without changing the password.

Again, think about what the mechanism is.

If a process (any process, including a dblink from another database) tries to connect to a database, it must have a valid username and password. If either is wrong (as indicated by your reported error) then one of two things must necessarily happen to get a good connection:

1) The client must use the credentials that are defined in the target database (use the correct username/password), or
2) The target database must be modified to accept the credentials being passed by the client. And how do you modify the password of a user account?

There is no silver bullet, magic, back-door, dba secret method of fixing the error outside of the above well documented methods. You either change the password of the user in the target database, or you change the password defined to the db_link.

Of course, if (as it SHOULD be) the username used by the db_link is created specifically for use by the db_link and is in reality used only by the db_link) then the fix should be trivial. It only becomes a concern if your db_link is using the same username as a lot of other processes.

Report message to a moderator

Re: ORA-28002 error while connecting DB LINK [message #612743 is a reply to message #612742]

Thu, 24 April 2014 07:24

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

EdStevens wrote on Thu, 24 April 2014 12:40

You either change the password of the user in the target database, or you change the password defined to the db_link.

Since the password specified in the db link is currently valid, changing it (without also changing the password on the target account to match) would only make matters worse.
OP either needs to change the password at both ends or turn off password expiry on the target db account.

Report message to a moderator

Re: ORA-28002 error while connecting DB LINK [message #612744 is a reply to message #612743]

Thu, 24 April 2014 07:39

Bikash.p
Messages: 11
Registered: April 2014
Location: Banglore

Junior Member

Thanks a Lot @cookiemonster,@EdStevens .So its better to change the password of the db and turn off the password expiry on the db,

Best Regards Smile

Report message to a moderator

Re: ORA-28002 error while connecting DB LINK [message #612745 is a reply to message #612744]

Thu, 24 April 2014 07:43

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No it's not better to turn off password expiry, it is part of the security.
Just change the passwords as required.

Report message to a moderator

Re: ORA-28002 error while connecting DB LINK [message #612751 is a reply to message #612743]

Thu, 24 April 2014 09:47

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

cookiemonster wrote on Thu, 24 April 2014 07:24

Since the password specified in the db link is currently valid, changing it (without also changing the password on the target account to match) would only make matters worse.
OP either needs to change the password at both ends or turn off password expiry on the target db account.

ah, yes. Over the course of the thread, and trying to explain the principles, I'd forgotten we were dealing with expired password rather than incorrect password.

Report message to a moderator

Previous Topic:	New to sql and need some help please (merged)
Next Topic:	Case Statement - Best Practice - Close with Else Null?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 11:13:24 CDT 2024