Oracle sqlplus doesn't connect to remote DB, only local

Question

I would appriciate some help with an issue that I'm facing.

I have two Oracle DB servers with same schemas. I want to run sqlplus from one server to another. I've added the tns_names entries in tnsnames.ora under both DB and Client folders under c:\oracle. When I do tnsping to the remote DB, it resolves properly and successfully pings remote DB. But, when I try sqlplus, it allways connects to the specified schema on the local server.

For example: I have serverA and serverB that both of them have shema named 'some_schema' with tns entries as follows in tnsnames on serverA:

some_shcema =(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=mst1))(CONNECT_DATA=(sid=mst1))(ADDRESS=(PROTOCOL=TCP)(HOST=10.111.22.3)(PORT=1521)))
some_shcemaB =(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=mst1))(CONNECT_DATA=(sid=mst1))(ADDRESS=(PROTOCOL=TCP)(HOST=11.222.33.4)(PORT=1521)))

from ServerA: tnsping some_shcemaB - resolves and pings some_shcema on serverB sqlplus some_schema/password@some_schemaB - connects to some_shcema on serverA

Hope someone could help me with that

• William Robertson: Just in passing, a tnsnames entry specifies an instance, not a schema.

Answer 1

When formatted for readability:

some_shcema = 
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=IPC)(KEY=mst1))
 (CONNECT_DATA=(sid=mst1))
 (ADDRESS=(PROTOCOL=TCP)(HOST=10.111.22.3)(PORT=1521))
 )
some_shcemaB =
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=IPC)(KEY=mst1))
 (CONNECT_DATA=(sid=mst1))
 (ADDRESS=(PROTOCOL=TCP)(HOST=11.222.33.4)(PORT=1521))
 )

The issue is the first ADDRESS entry in each, with the IPC protocol. That protocol is only for localhost communication. Because you have it listed first, without using ADDRESS_LIST or other related parameters, your client is never getting to the actual TCP network data or even attempting a network connection. Effectively some_schema and some_schemaB are pointing to the same place, on the localhost. If you want to use the network, list the correct info in the correct order, and omit the IPC protocol:

some_shcema = 
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=TCP)(HOST=10.111.22.3)(PORT=1521))
 (CONNECT_DATA=(sid=mst1))
 )
some_shcemaB =
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=TCP)(HOST=11.222.33.4)(PORT=1521))
 (CONNECT_DATA=(sid=mst1))
 )

Note: while using SID in the connect data is still common, it has technically been deprecated for over 20 years, in favor of the service_name parameter. Depending on the version of your server, you may need to specify service_name instead of sid, or you may need to configure your server to treat sid as a service_name.

Answer 2

In the end, I neede to set WALLET=NO in env variable in order to be able to connect to remote DB with SQLPLUS