Re: Database link from 8.1.6 to 8.0.5

From: Oweson Flynn <Oweson.Flynn_at_liberty.co.za>
Date: Thu, 25 Jan 2001 10:48:37 +0200
Message-Id: <10752.127442@fatcity.com>

Hi John,

Where is the tnsnames.ora file that you are quoting?

I assume (perhaps fallaciously) that you have a PC which is running your SQL*Plus session. I also assume that you can connect to both instances from your SQL*Plus session on your PC.

However (the way I understand it) is that the definition of the db link must be defined in the tnsnames.ora on the server to which you are connected. So (if it is Unix) telnet onto the box, and try running a sqlplus session to the 'remote' instance - if it still fails, it may be that the tnsnames.ora on the server also need to have the instance added.

Let me see if I can state this more clearly:- If you think about it, you are connecting to instance A from your PC. Then you are asking instance A to retrieve data from instance B via the db link. It isn't your PC (which has instance B defined in it's tnsnames.ora) that is retrieving the data, but instance A. Therefore instance B must be defined in the tnsnames.ora on the server hosting instance A.

I admit I am making a couple of very large assumptions here, and may be totally off target ...

Let me know if this helps at all ...

Regards
Oweson Flynn

Certified Oracle DBA
The Flynn Consultancy
Tel: 082-600-7-006
Fax: (011) 782-9313
EMail: oef_at_icon.co.za
----- Original Message -----
From: "John Dunn" <john.dunn_at_sefas.co.uk> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, January 24, 2001 6:40 PM Subject: Database link from 8.1.6 to 8.0.5 - error ORA-12505

> Hi All
>
> I have created a database link :
>
> CREATE DATABASE LINK TESTREMOTE USING 'FW01.43P';
>
> TNSNAMES.ORA contains the following entry :
>
> FW01.43P =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = fw_sw_rs11)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SID=FW01)
> (SERVICE_NAME = FW01.43P)
> )
> )
>
> When I try to do select * from user.table_at_TESTREMOTE I get the following
> error :
>
> ORA-12505: TNS:listener could not resolve SID given in connect descriptor
>
> What am I missing?
>
> John
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: John Dunn
> INET: john.dunn_at_sefas.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. Received on Thu Jan 25 2001 - 02:48:37 CST

Re: Database link from 8.1.6 to 8.0.5 - error ORA-12505