Resolving Oracle networking problems..

This simple two part procedure will help to diagnose and fix the most common sqlnet and tnsnames configuration problems.

• 1. Test communication between the client and the listener
  
  We will use tnsping to complete this step. It's a common misconception that tnsping tests connectivity to the instance. In actual fact, it only tests connectivity to the listener.
  
  Here, we will use it to prove that a) the tnsnames.ora has the correct hostname and port, and b) that there is a listener listening on the specified host and port. Run tnsping:
  
  

  tnsping <your_tns_entry_name>

  If it is successful you will see something like this:
  
  

  [oradev@testerp] $ tnspinng dev
  
  Used TNSNAMES adapter to resolve the alias
  Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
  (PROTOCOL = TCP) (HOST = dev)(PORT = 1521))) (CONNECT_DATA = 
  (SERVER = DEDICATED) (SERVICE_NAME = scr9)))
  OK (40 msec)

  
  If not, here are some common errors, and some suggestions for fixing them:
  
  TNS-03505: Failed to resolve name
  The specified database name was not found in the tnsnames.ora, onames or ldap. This means that tnsping hasn't even got as far as trying to make contact with a server - it simply can't find any record of the database that you are trying to tnsping. Make sure that you've spelled the database name correctly, and that it has an entry in the tnsnames.ora.
  
  If you have a sqlnet.ora, look at for the setting NAMES.DEFAULT_DOMAIN. If it is set, then all entries in your tnsnames.ora must have a matching domain suffix.
  
  TNS-12545: Connect failed because target host or object does not exist
  The host specified in the tnsnames is not contactable. Verify that you have spelled the host name correctly. If you have, try pinging the host with 'ping <hostname>'. If ping returns 'unknown host', speak to your network admin. It might be that you have a DNS issue (you could try using the IP address if you have it to hand). If you get 'host unreachable', again speak to your network person, the problem could be down to a routing or firewall issue.
  
  TNS-12541: TNS:no listener
  The hostname was valid but the listener was not contactable. Things to check are that the tnsnames has the correct port (and hostname) specified, and that the listener is running on the server and using the correct port.
  
  tnsping hangs for a long time
  I've seen this happen in situations where there is something listening on the host/port - but it isn't an oracle listener. Make sure you have specified the correct port, and that your listener is running. If all looks ok, try doing a 'netstat -ap | grep 1521' (or whatever port you are using) to find out what program is listening on that port.
  
  
• 2. Attempt a connection to the instance
  
  Once you have proven that the tnsnames is talking to the listener properly, the next step is to attempt a full connection to the instance. To do this we.ll use sqlplus:
  
  

  sqlplus [username]/[password]@<your_tns_entry_name>

  If it works you will successfully log into the instance. If not, here are some common errors:
  
  ORA-01017: invalid username/password; logon denied
  This is actually a good error in these circumstances! Even though you didn't use the correct username or password, you must have successfully made contact with the instance.
  
  ORA-12505: TNS:listener does not currently know of SID given in connect
  Either the SID is misspelled in the tnsnames, or the listener isn't listening for it. Check the tnsnames.ora first. If it looks ok, do a 'lsnrctl status' on your server, to see what databases the listener is listening for.
  
  ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
  This is quite a common error and it means that, while the listener was contactable, the database (or rather the service) specified in the tnsnames wasn't one of the things that it was listening out for.
  
  Begin by looking at your tnsnames.ora. In it, you will a setting like SERVICE_NAME=<name>.
  
  If you are running a single instance database (ie. not RAC), and you are sure that you are not using services, it might be easier to change SERVICE_NAME= to SID= in your tnsnames. Using service names is the more modern way of doing things, and it does have benefits, but SID still works perfectly well (for now anyway).
  
  If you would prefer to continue using service names, you must first check that you have not misspelled the service name in your tnsnames. If it looks alright, next check that the listener is listening for the service. Do this by running 'lsnrctl services' on your server. If there isn't an entry for your service, you need to make sure that the service_names parameter is set correctly on the database.

Sukhwinder Singh

Apps DBA