A couple of important points.
First, the listener is a server side only process. It’s entire purpose in life is to receive requests for connections to databases and set up those connections. Once the connection is established, the listener is out of the picture. It creates the connection. It doesn’t sustain the connection. One listener, with the default name of LISTENER, running from one oracle home, listening on a single port, will serve multiple database instances of multiple versions running from multiple homes. It is an unnecessary complexity to try to have multiple listeners or to name the listener as if it belongs to a particular database. That would be like the telephone company building a separate switchboard for each customer.
Additional notes on the listener: One listener is capable of listening on multiple ports. But please notice that it is the listener using these ports, not the database instance. You can’t bind a specific listener port to a specific db instance. Similarly, one listener is capable of listnening on multiple IP addresses (in the case of a server with multiple NICs) But just like the port, you can’t bind a specific ip address to a specific db instance.
Second, the tnsnames.ora file is a client side issue. It’s purpose is for address resolution – the tns equivalent of the ‘hosts’ file further down the network stack. The only reason it exists on a host machine is because that machine can also run client processes.
Assume you have the following in your tnsnames.ora:
larry = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = curley) ) )
Now, when you issue a connect, say like this:
$> sqlplus scott/[email protected]
tns will look in your tnsnames.ora for an entry called ‘larry’. Next, tns sends a request to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley).
Where is (HOST = myhost) on the network? When the request gets passed from tns to the next layer in the network stack, the name ‘myhost’ will get resolved to an IP address, either via a local ‘hosts’ file, via DNS, or possibly other less used mechanisms. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora.
Next, the request arrives at port 1521 on myhost. Hopefully, there is a listener on myhost configured to listen on port 1521, and that listener knows about SERVICE_NAME = curley. If so, you’ll be connected.
What can go wrong?
First, there may not be an entry for ‘larry’ in your tnsnames. In that case you get “ORA-12154: TNS:could not resolve the connect identifier specified” No need to go looking for a problem on the host, with the listener, etc. If you can’t place a telephone call because you don’t know the number (can’t find your telephone directory (tnsnames.ora) or can’t find the party you are looking for listed in it (no entry for larry)) you don’t look for problems at the telephone switchboard.
Maybe the entry for larry was found, but myhost couldn’t be resolved to an IP address (say there was no entry for myhost in the local hosts file). This will result in “ORA-12545: Connect failed because target host or object does not exist”
Maybe there was an entry for myserver in the local hosts file, but it specified a bad IP address. This will result in “ORA-12545: Connect failed because target host or object does not exist”
Maybe the IP was good, but there is no listener running: “ORA-12541: TNS:no listener”
Maybe the IP was good, there is a listener at myhost, but it is listening on a different port. “ORA-12560: TNS:protocol adapter error”
Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesn’t know about SERVICE_NAME = curley. “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”
Third: If the client is on the same machine as the db instance, it is possible to connect without referencing tnsnames and without going through the listener.
Now, when you issue a connect, say like this:
$> sqlplus scott/tiger
tns will attempt to establish an IPC connection to the db instance. How does it know the name of the instance? It uses the current value of the enviornment variable ORACLE_SID. So…
$> export ORACLE_SID=fred $> sqlplus scott/tiger
It will attempt to connect to the instance known as “fred”. If there is no such instance, it will, of course, fail. Also, if there is no value set for ORACLE_SID, the connect will fail.
check executing instances to get the SID
[[email protected] ~]$ ps -ef|rgrep pmon oracle 4236 1 0 10:30 ? 00:00:00 ora_pmon_vlnxora1 oracle 4878 4854 0 10:42 pts/0 00:00:00 grep pmon
set ORACLE_SID appropriately, and connect
[[email protected] ~]$ export ORACLE_SID=’vlnxora1 [[email protected] ~]$ sqlplus scott/tiger SQL*Plus: Release 10.2.0.4.0 – Production on Wed Sep 22 10:42:37 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Now set ORACLE_SID to a bogus value, and try to connect
SQL> exit [[email protected] ~]$ export ORACLE_SID=FUBAR [[email protected] ~]$ SQLPsqlplus scott/tigere SQL*Plus: Release 10.2.0.4.0 – Production on Wed Sep 22 10:42:57 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux Error: 2: No such file or directory Enter user-name:
Now set ORACLE_SID to null, and try to connect
[[email protected] ~]$ export ORACLE_SID= [[email protected] ~]$ SQLsqlplus /scott/tiger SQL*Plus: Release 10.2.0.4.0 – Production on Wed Sep 22 10:43:24 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ORA-12162: TNS:net service name is incorrectly specified
