Recently, I had to help a developer, whou could not connect to his local Oracle instance using SQLDeveloper, but had no problem connecting using SQL*Plus.
I found nothing suspicious in the network configuration files (listener.ora, tnsnames.ora, sqlnet.cfg).
After issuing netstat from a command line prompt (DOS), we noticed that the server was running on port 1522, instead of 1521 (as mentionned in the network configuration).
This did not appear at first: the XE - Instance had been installed on the machine in its own home (under C:\oraclexe) Oracle Client was also installed in a separated home (under C:\Oracle), and ORACLE_HOME and TNS_ADMIN were pointing to the home where the client had been installed.
U:\>set ora
ORACLE_HOME=C:\oracle\product\1120\client
U:\>set tns
TNS_ADMIN=C:\oracle\tns_admin
The tnslsnr was started from the bin directory where the XE instances had been installed : ( C:\oraclexe\app\oracle\product\11.2.0\server\BIN\).
One would expect the network configuration in use would be the one in the home from which tnslsrn was started, but the settings of the tns_admin takes precedence. This explains why the instances in the network were accessible when running tnsping from either home, but not the local instance: TNS_ADMIN points to the network config in the home under C:\Oracle, which does not know of the local XE - Instance.
When setting TNS_ADMIN to C:\oraclexe\app\oracle\product\11.2.0\server\network\admin, it is possible to access the local instance (but not the instances in the network):
$set tns_admin=C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN
$tnsping yopdn1
...
Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\sqlnet.ora
TNS-03505: Failed to resolve name
$tnsping xe
...
Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = S72D19.mobi.mobicorp.ch)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (70 msec)
Points to remember:
- TNS_ADMIN determines which network configuration will be used.
- listener.ora only matters for the server.
- tnsnames.ora only matters for the client software.
- In SQLNet.ora, the parameter NAMES.DEFAULT_DOMAIN detemines the access to the local instance.
When using multiple homes, the listener process should be started from the home where the instance is installed.
In order to access instances in the network, TNS_ADMIN should point to a home that "knows" about these instances.
