TNS Connection Failure Resolution

Many dbas has no single day in a week that not telling  their database  users about how to make TNS connection with the newly created accounts in their hands. I am providing below decision tree, to our users in our company for resolution of their TNS connection failures on their own.

Follow below decision tree for a sample TNS entry in$ORACLE_HOME/network/admin/tnsnames.ora file like below:

MYRAC=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))

1. From cammand line issue: ping mydb.com
   
   
   
   1. If not work, WINS server client configuration problem !
   
   
   2. If works: tnsping MYRAC
   
   
   
   

i.      If works, there must not be any problemJ

ii.      If not work, from command line issue below command:

tnsping  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))

1. If not work:  Oracle client configuration/installation problem ! Or you are editing wrong tnsnames.ora file!


2. If works: Check $ORACLE_HOME/network/admin/sqlnet.ora  file. Note the NAMES.DEFAULT_DOMAIN parameter value if other than null and place it to your $ORACLE_HOME/network/admin/tnsnames.ora:

MYRAC.<names default domain>=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME= MYSERVICE)))