Odd behavior from broken DB Links question Options

[zekmoe]

I've begun experiencing a very odd issue with one instance and all of their their db links. When I'm connected via telnet to the unix server, and I connect:

connect myuser/password via sql plus, I connect fine, and the link works.

but when I connect this way:

connect myuser/password@db.tnsname.entry.com using the TNS name specified, I again connect fine but the link calls fails with a TNS cannot resolve service name issue.

This is an existing instance with existing db links, but I repeat the issue on this machine alone with new links. the fully qualified name is the same name entry I'm using to create the link, both when it succeeds and when it fails. This is an 11g instance and the TNSnames file is in the ASM home.
ALso, if I connect

connect sys as sysdba I get success as well. This behavoior happens with both private and public database links.
I'm not aware of any setting changes within this instance, and I've modifed (after backing up) the TNSNames file with values from another that works perfectly well. Connecting via remote sqlplus or other JDBC connections gives the same error. Success connecting locally but failure connecting to DB Links when I'm remote. Connecting to the instance shows no issue.

WHat should I be looking for? Another instance on the network? An IP config issue in DNS? We have 100's of servers so it's needle in a haystack time. Can I run some sort of trace to see what it's calling to when it works and when it doesn't? (It selects another db's single table and I've confirmed the target is correct and the data is correct). Anyone experience this issue before?

Bob

[HAL9000]

"I've begun experiencing a very odd issue with one instance and all of their their db links. "

Is this a new issue? If so, what changed?

Can you tnsping successfully?

"I again connect fine but the link calls fails with a TNS cannot resolve service name issue."

Please read, your solution is here.

Follow these steps:

http://www.dba-oracle.com/t_troubleshootin...vity_errors.htm

[burleson]

Hi Bob,

Invoke SQL*Plus from the OS command line "sqlplus fred@flintstone".

If this fails, check to ensure that your listener is the flintstone service defined


Also, bounce your listsner process:

lsnrctl stop

lsnrctl start

[zekmoe]

Hi Bob,

Invoke SQL*Plus from the OS command line "sqlplus fred@flintstone".

If this fails, check to ensure that your listener is the flintstone service defined


Also, bounce your listsner process:

lsnrctl stop

lsnrctl start

Listener bounced. The inline sql plus works and it seems to work as a DBA level user (names and spacing changed for formatting below:

SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 29 09:28:44 2012
Copyright © 1982, 2010, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> select count(*) from abc.prdt_dtl_d@ADQA;

COUNT(*)
----------
20072

SQL> connect abc_qa_user/pwuser1
Connected.
SQL> select count(*) from abc.prdt_dtl_d@ADQA;

COUNT(*)
----------
20072

SQL> connect abc_qa_user/pw4ebim1@aaqa.oracle.abc.com
Connected.
SQL> select count(*) from abc.prdt_dtl_d@ADQA;
select count(*) from abc.prdt_dtl_d@ADQA
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified

Logging in as a DBA or another user, I can compile the procedures that use this link, within the abc_qa_user schema. I can call the view's that use that link with no error. Within this owning schema, it cannot compile it's own views and cannot call across either a public or private db link. It seems to also fail when connected
The issue doesn't seem to be being able to connect. The issue seems to be being able to call a db link, public or private, successfully. I've recreated the link and even createing a new basic user. Connect as owner, FROM the server, success. Connect as owner FROM the server but specify the connect descriptor, I connect fine, can see all objects but fail to use my own, or public, database link with the TNS error above. Very weird.

Bob

[zekmoe]

Also, yes, have been able to resolve successfully using TNS Ping. I can connect to the remote db. It's only occurring when the owner of a db link trys to connect using it's db link. A call across schemas or over a public link by a "super" user seems to work fine.