Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Odd behavior from broken DB Links question
zekmoe
post Mar 28 2012, 06:49 PM
Post #1


Newbie
*

Group: Members
Posts: 7
Joined: 15-August 05
Member No.: 2,740



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
Go to the top of the page
 
+Quote Post
HAL9000
post Mar 28 2012, 06:59 PM
Post #2


Advanced Member
***

Group: Members
Posts: 880
Joined: 25-September 07
Member No.: 12,336



"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
Go to the top of the page
 
+Quote Post
burleson
post Mar 28 2012, 07:03 PM
Post #3


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
zekmoe
post Mar 29 2012, 08:55 AM
Post #4


Newbie
*

Group: Members
Posts: 7
Joined: 15-August 05
Member No.: 2,740



QUOTE (burleson @ Mar 29 2012, 12:03 AM) *
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
Go to the top of the page
 
+Quote Post
zekmoe
post Mar 29 2012, 09:22 AM
Post #5


Newbie
*

Group: Members
Posts: 7
Joined: 15-August 05
Member No.: 2,740



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.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th October 2014 - 10:27 PM