INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

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

CODE -->

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:

CODE -->

select * from dual@nst_test; 
I get the error:
ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 - "TNS:could not resolve the connect identifier specified"

But when I login to sqlplus on the server itself and try the same thing it works fine:

CODE -->

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

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

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

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

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.

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

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.

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

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

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

(OP)
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

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

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!

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

(OP)
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.

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

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.

CODE

select instance_name, instance_name, host_name from v$instance; 

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

Karluk -
You read my mind for the next step!

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

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.

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

(OP)
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

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

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close