Unable To Use DB Link

[Ger9]

We had a Database Link Set up to access an external database on a server I have no control over through our database (to run queries). This has stopped working within the last day or so, the code used to create the link was:

CREATE PUBLIC DATABASE LINK "LINKNAME"
CONNECT TO USERID
IDENTIFIED BY "PASSWORD"
USING 'DBNAME';

Testing the link:
SELECT * FROM DUAL@LINKNAME

Gives an error of "ORA-12541: TNS:no listener", however the database can be accessed directly (from our database server) using SQL-Plus (and the same details as in the Database link).

Our TNSNames.ORA file on the server contains the following entry for the DBLINK (IP address correct but masked by me):

DBLINK =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
)
(CONNECT_DATA =
(SID = TEST)
)
)

I'm not sure if they've changed anything there end which could have caused this or not, does anyone know anything which may shed some light on this, whether it is possible to restrict access like this?

We've tried restarting our server here and restarted our listener separately, plus recreated the link (on my PC and directly on the server).


Thanks

Ger

 

[nagornyi]

Different applications may use their own tns files. Search your PC for tnsnames.ora file, and chances are you will find many of them, unless you have special settings to use a single tnsnames.ora file for all applications. Check thread759-853570 for details.

 

[MPeccorini]

I don't think nagornyi has address the correct issue since in the case of a database link the TNSNAMES.ORA used is the one in the server's installation. However, he could have a point if you have multiple Oracle Homes in your server. Make sure you are trying to access the remote database from SQL*Plus in the database's Oracle Home.

Other things that I can think of are the next:
- How is your SQLNET.ORA configured in respect to NAMES parameters? have someone changed NAMES.DEFAULT_DOMAIN or something? personally I don't think this is the problem since you're getting an "TNS No Listener" but what do I know?

- Have you added a firewall between your servers? If you have, opening port 1521 might not be enough if Multithreaded Server is configured in the remote machine because several other ports are asigned for the shared servers. Adding SERVER=DEDICATED to your local TNSNAMES.ORA should correct this situation.

- So far I always thought the database link should be named as the alias in the TNSNAMES.ORA. Once again, what do I know?

Best regards and hope this put you in the right direction

Mauricio Peccorini

 

[SantaMufasa]

Ger9,

Mauricio's suggestions are all excellent.

As a test to help troubleshoot and isolate where your problem exists, try the following experiment:

1) log into your machine that houses the Oracle server that contains your database link,
2) from a standard o/s prompt, issue the following command:

tnsping <tns_alias>

...where <tns_alias> is the alias label for the remote instance that appears in your tnsnames.ora file. For example, if my db-link-create code looks like this:

create database link whatever connect to dontdrop identified by dontdrop using 'FOS9204';

Database link created.

...then your tnsping would look like this:

tnsping fos9204

If the tnsping is successful, then the results will look like this:

D:\oracle\ora92\bin>tnsping fos9204

TNS Ping Utility for 32-bit Windows: Version 9.2.0.4.0 - Production on 29-JUL-20 04 10:51:28

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
D:\Oracle\Ora92\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (SDU = 4256) (ADDRESS = (PROTOCOL = TCP)(Host = foster)(Port = 1521)) (CONNECT_DATA = (SERVICE_NAME = FOS9204)))
OK (1090 msec)

Notice the "OK (1090 msec)" result. This means that the connection attempt successfully occurred in 1.09 seconds.

Let us know what happens as a result of the above test, because if your tnsping fails, then your db link will not properly connect either.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:56 (29Jul04) UTC (aka "GMT" and "Zulu"), 10:56 (29Jul04) Mountain Time)

 

[carp]

Is it possible the remote DB server's IP address changed? I have had this happen if you are using dynamic IP allocation (or possibly a failover scheme wherein another server hosts the database - hence the new IP address). If this happened, then your dblink would find the definition in the TNSNAMES.ORA file, but no listener at the designated IP address.

If this is the case, you might want to look at using DNS names instead of explicit IP addresses in your configuration files. While they take a little longer to resolve, they help avoid things like broken DB links!

 

[Ger9]

Hi

Thanks for all your comments so far. However the I can still make connections to the remote database directly and run queries on it, from my local Database server using the same details as the DB-Link.

It's just the DB-Link that doesn't work.

The TNSPING returned(I've masked the IP address):

C:\>tnsping sgsgears.world

TNS Ping Utility for 32-bit Windows: Version 8.1.7.4.0 - Production on 30-JUL-20
04 07:48:52

(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521))
OK (400 msec)


Thanks once again.

Ger

 

[sem]

Can you clarify whether you used the same name in place of DBLINK(tnsnames.ora) and DBNAME (CREATE statement)?

Regards, Dima

http://seminihin.narod.ru