TNSPING

Sample tnsnames.ora entry:

tns_entry =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = service_name)
    )
  )

Connecting thru sqlplus:

$> sqlplus scott/tiger@tns_entry

  tns will look in your tnsnames.ora for an entry called 'tns_entry'. Next, tns sends a request to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = service_name)

tnsping format: 
     requires ORACLE_HOME set in local machine

C:\>tnsping (address=(protocol=tcp)(address=myhost)(port=1521))

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 26-SEP-2012 09:56:36

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Attempting to contact (address=(protocol=tcp)(address=myhost)(port=1521))
OK (720 msec)

      tnsping uses tcp protocol. 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 (instead of 'myhost') in tnsnames.ora

Thereafter, 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.

tnsping goes no further than to verify there is a listener at the specified host/port. It DOES NOT prove anything regarding the status of the listener's knowledge of any particular database instance.

C:\>set oracle_sid=service_name

C:\>tnsping service_name

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 27-APR-2010 19:57:13

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
C:\app\han\product\11.2.0\dbhome_2\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = service_name))) OK (80 msec)

• The listener is a server side only process. It's entire purpose in life is the 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, 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. That would be like the telephone company building a separate switchboard for each customer.
• The tnsnames.ora file is a client side issue. It's purpose is for addressess resolution - the tns equivelent 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.

Possible errors:

• No be an entry for 'tns_entry' in your tnsnames => "ORA-12154: TNS:could not resolve the connect identifier specified"
• There is entry for 'tns_entry' was found, but myhost couldn't be resolved to an IP address (say there was no entry for myhost in the local hosts file) => "ORA-12545: Connect failed because target host or object does not exist"
• There iss an entry for myserver in the local hosts file, but it specified a bad IP address. => "ORA-12545: Connect failed because target host or object does not exist"
• If IP was good, but there is no listener running => "ORA-12541: TNS:no listener"
• If IP was good, there is a listener at myhost, but it is listening on a different port =>  "ORA-12560: TNS:protocol adapter error"
• If IP was good, there is a listener at myhost, it is listening on the specified port, but doesn't know about SERVICE_NAME = service_name => "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor"