Accessing non-ORCL instance from SQLNet

From: Vinay Joshi <vjoshi_at_pinnacle.co.uk>
Date: 1997/10/08
Message-ID: <717BDE76D864D011870F00A0C9283F1E07B639@exchange.pinnacle.co.uk>#1/1

A few things to check!
On the workstation that you are trying to connect from, look at the tnsnames.ora file which would normally be located in the <oracle_home>/network/admin directory. Check the entry named XABC in your case, and the SID name. I am including an example just to clarify things (tnsnames.ora):

orac7.world =
  (DESCRIPTION =
    (ADDRESS_LIST =

        (ADDRESS = 


          (COMMUNITY = tcp.world)

          (PROTOCOL = TCP)

          (Host = script)

          (Port = 1521)

        )

    )
    (CONNECT_DATA = (SID = orac73)
    )
  )

In the above example I would use
sqlplus scott/tiger_at_orac7 to login into sqlplus. The orac7 is the service name. Check the CONNECT_DATA = (SID ... entry is correct. Then check the Host and Port entries...
The port = 1521 is the port that is referenced in the services file on the host machine.
After checking that above is correct, You can try to ping the host machine and make sure it is reachable. If it is then you can try tnsping which tries to connect to the database. After all this if things still fail
then you might want to copy tnsnames.sam to tnsnames.ora and make the necessary changes and try again.

One thing that I came across which sounds very strange is as follows: when i pinged the host machine by name, in my case script, the ip address returned was completely different from when I looked at the <oracle_home>/network/listener.log.:
Look at the entry which says:
Listening on:
(ADDRESS=(PROTOCOL=tcp)(DEV=144)(HOST=172.21.64.2)(PORT=1521))

You might want to change your host entry in the tnsnames.ora to reflect the ip address as mentioned in your listener.log on the host machine instead. It did the trick for me.

Hope this helps!
Vinay Joshi
Pinnacle Insurance Plc
DBA/Support

> ----------
> From: 	Dick Willis[SMTP:rmw_at_synchrony.com]
> Posted At: 	07 October 1997 21:23
> Posted To: 	server
> Conversation: 	Accessing non-ORCL instance from SQLNet
> Subject: 	Accessing non-ORCL instance from SQLNet
> 
> This should be straightforward, but something is preventing me from
> connecting to an instance named XABC (for example) over SQLNet.
> 
> Let's say that my new instance is named XABC.  I can connect locally (
> Service name 2:XABC), no problems, but whenever I try to connect from
> another workstation, it comes back with the error
> 
>  ORA 12505 - TNS:Listener could not resolve SID given in connect
> descriptor.
> 
> This is true whether I use the SYS, Internal, SYSTEM or other
> DBA-granted
> user account, or a CONNECT or RESOURCE-granted user account.  It
> doesn't
> even seem to get to the point of authenticating a user.
> 
> SysInfo:	ORACLE 7.3
> 	SQLNet v2.0
> 	Win NT 4.0
> 
> Thanks.
> 

Received on Wed Oct 08 1997 - 00:00:00 CDT