TNS service name problem with TOAD

[Lily_Liu_2004]

Hi DBAs,

i got a werid problem with TOAD. For some instances in the same
server, I could login through SQL*PLUS. But when I tried to
use TOAD to connect them, the system gave the error message--
ORA-12154: TNS: could not resolve the service name.

How could this be? I am not familiar with TOAD.

Any input is appreciated.

[slimdave]

Perhaps you have multiple oracle homes on your client machine -- TOAD is using one of them, and SQL*Plus is from the other. The two homes have different tnsnames files.

[Lily_Liu_2004]

Thank you, slimdave.

I thought that they might use different tnsnames.ora and checked it out.
Axtually, there is just one tnsnames.ora in my client machine.
Weird.

[khussain]

Definitely sounds like a ORACLE_HOME issue; unless the TOAD install was not clean (did this work at any point) ?

Here are some ideas:

from a DOS prompt, type PATH. TOAD is most likely using the ORACLE_HOME that is the first one in the PATH.

OR

you could simply force the ORACLE_HOME. In Control Panel, System, Advanced, Environment Variables, create a system variable TNS_ADMIN, and point that to the location of the tnsnames.ora file. That will force all connections to resolve the SID using the tnsnames.ora in this directory:

Variable Name: TNS_ADMIN
Variable Value: c:\oracle\oraHome9i\network\admin

Good Luck.

[Lily_Liu_2004]

Thanks for the tips.

Finally found out that there are two entries for some instances which cannot be accessed by TOAD but could by sql*plus. It seems that
sql*plus used the right one but TOAD used the wrong one.

After fixing the tnsnames.ora and I can connect to them using TOAD and OEM.

Another weird problem is :
for one instance:
I can access it using system accoount with the password through sql*plus,but cannot by TOAD or OEM STANDALONE using system account and
the same password. They always reported ORA-01017:invalid username/password. logon denied.

I am sure the password is right.
I already log in as sysdba to do
alter user system identified by 'mypassword';

Really strange.