By William Sescu

Tim Hall had the idea that as many people as possible would write a small blog post about their favorite Oracle feature and we all post them on the same day. I do have a lot of favorite Oracle tools, and the one I choose today is: tnsping

tnsping tells you, if your connect string can be resolved and if the listener where the connect string is pointing to, is available, and in the end, it displays an estimate of the round trip time (in milliseconds) it takes to reach the Oracle Net service.

All in all, tnsping is very easy to use and that’s why I love it, and not so overloaded like e.g. crsctl.  In fact, tnsping knows only 2 parameters. <address> and optionally <count>, like shown in the following example.

Usage: tnsping <address> [<count>]

Getting the option list of tnsping, a few lines are enough. I don’t need to scroll down several pages, like e.g. for emctl.  emctl is another one besides crsctl were you can spend a lifetime only reading the manual.  No, I picked tnsping this time because I like the option list.

Here we go … now I run one tnsping without and one with count.

oracle@oel001:/home/oracle/ [OCM121] tnsping RMAN
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 11-OCT-2016 14:28:14
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oel001)(PORT = 1521))) 
(CONNECT_DATA = (SERVICE_NAME = OCM121)))
OK (0 msec)

oracle@oel001:/home/oracle/ [OCM121] tnsping RMAN 5
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 11-OCT-2016 14:28:20
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oel001)(PORT = 1521))) 
(CONNECT_DATA = (SERVICE_NAME = OCM121)))
OK (0 msec)
OK (10 msec)
OK (0 msec)
OK (0 msec)
OK (0 msec)

But … wait a second … my RMAN connect string points to host oel001, but it should point to oel002. Let’s take a look in $ORACLE_HOME/network/admin/tnsnames.ora

oracle@oel001:/u00/app/oracle/ [OCM121] cat /u00/app/oracle/product/12.1.0.2/network/admin/tnsnames.ora
RMAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel002)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = OCM121)
    )
  )

It looks correct. So what is going on here. There are several explanations to this issue.

1.) You might have set the TNS_ADMIN environment variable, which points to a total different directory
2.) Or your sqlnet.ora might point to a LDAP server first, which resolves the name
3.) Or a total different tnsnames.ora file is taken into account, but which one?
4.) Or something totally different, e.g. corrupt nscd, symlinks …

For quite a long time, Oracle is not searching first in the $ORACLE_HOME/network/admin/tnsnames.ora
to get the name resolved. The tnsnames.ora search order is the following:

1.) $HOME/.tnsnames.ora    # yes, it looks up the a hidden file in your home directory first
2.) /etc/tnsnames.ora    # then, a global tnsnames.ora in the /etc directory
3.) $ORACLE_HOME/network/admin/tnsnames.ora    # and last but not least, it looks it up in the $ORACLE_HOME/network/admin

To prove it, simply run a strace on your tnsping command and take a look at the trace file.

$ strace -o /tmp/tnsping.trc -f tnsping RMAN
$ cat /tmp/tnsping.trc | grep tnsnames

21919 access("/home/oracle/.tnsnames.ora", F_OK) = 0
21919 access("/etc/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
21919 access("/u00/app/oracle/product/12.1.0.2/network/admin/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
21919 stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=173, ...}) = 0
21919 open("/home/oracle/.tnsnames.ora", O_RDONLY) = 3

Here we go … in my case, the “/home/oracle/.tnsnames.ora” was taken into account. Let’s take a look.
Indeed, I have found an entry here.

oracle@oel001:/home/oracle/ [OCM121] cat /home/oracle/.tnsnames.ora
RMAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel001)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = OCM121)
    )
  )

Have fun with tnsping.

Cheers,
William