db link not working from client sql developer but works from the sqlplus on db server

[etprash]

I created a db link on our 11g RAC database.

CREATE DATABASE LINK nst_test CONNECT TO eai_rt IDENTIFIED BY eai_rt USING 'EAIPREPROD';

When I try to use it from sql developer on my client machine in following way:

select * from dual@nst_test;

I get the error:
[tt]ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 - "TNS:could not resolve the connect identifier specified"[/tt]
But when I login to sqlplus on the server itself and try the same thing it works fine:

select * from dual@nst_test;

It seems a bit strange behavior. In both the cases the user is same. Tns entry for the EAIPREPROD is present in all the RAC nodes.
Any ideas?

Thanks,
Prashant

 

[karluk]

Have you tried logging directly into the remote database, nst_test, using SQL*Plus from each node in your RAC cluster?

 

[carp]

Also, are you using the same tnsnames.ora file for all of your tools? The behavior you describe sounds like SQL Developer is using a different file than SQL Plus.

 

[karluk]

Carp, remote calls through a database link get resolvedd on the database server where the instance is running. The fact that one of the calls resolves and the other does not strongly suggests that that resolution is happening on different servers in the RAC cluster. Hence my suggestion to log in directly to the remote database from every node in the RAC cluster. If there's a tnsnames resolution problem on one of the nodes, that sort of test should uncover it.

 

[carp]

Karluk -
You are quite right - the symptoms tripped a synapse, but I was reacting to client
side behavior rather than DB resolution. Play on!

 

[etprash]

Carp,Karluk,
Thanks for your responses!
Both the nodes are able to make sqlplus connection to the remote database. TNSPING also responds successfully on both the nodes. And as mentioned in my first post, db link works correctly on the servers itself.
This has been baffling us and even Oracle Support.
Please suggest anymore ideas if possible.
Thanks again!!
Prashant

 

[carp]

OK, let's confirm our assumptions.
You only have one database, right? I would start with querying
SELECT * FROM v$database
from SQL*Plus and SQLDeveloper to make sure you get identical results. It's a very long shot, but when you run out of possibilities, it's time to look at the impossibilities!

 

[etprash]

Carp,
You are talking about local database, right? Local meaning where the DB link is created.
I ran your query from sqlplus and from sql developer and found the result to be same.

 

[karluk]

I suppose you've already eliminated the obvious - for example, if you are using different ids, the db links might have different definitions even though they are named the same. Or one id might be using a private db link and the other a public db link.

Assuming different ids using differen db links isn't the problem, please run the following query in both sessions to verify that you are connected to the same instance running on the same server.

select instance_name, instance_name, host_name from v$instance;

 

[carp]

Karluk -
You read my mind for the next step!

 

[taupirho]

On the client machine do you have (or have you had) multiple Oracle clients installed. If so there may be a problem with some environment variable such as ORACLE_HOME etc ...


In order to understand recursion, you must first understand recursion.

 

[etprash]

Karluk, Carp,
I am using the same User Id in all cases. I tried both public and private db link (one after another). Issue remains in both the cases.
The result of the "Select" from v$instance is also same from both the places (sql developer on client machine and sql plus on server).
Oracle Support came back saying that most likely this is a case of multiple tnsnames.ora. But I don't understand how could this be since it is the database itself doing the resolution. But they could be right as when we change the definition of the DB link to include full tns entry instead of net alias, it works.
Taupirho,
Client machine does not have multiple Homes. At the same time this issue remains for all the machines except for db server itself. For example, when i try from application servers i still get the issue.
Thanks,
Prashant

 

[karluk]

Is your tnsnames.ora file located in $ORACLE_HOME/network/admin or is it somewhere else?

 

[etprash]

Hi Karluk,
Sorry for disappearing for so long!!
Yes tnsnames.ora is located in $ORACLE_HOME/network/admin