Power to Build (Software): Quick Tip: tnsping

Tnsping is a nice little utility that comes with Oracle. Lot of developers don't know about this. If you have SQL*Plus available on your machine, chances are you also have tnsping. You can use this tool to troubleshoot Oracle connectivity issues, sort of like ping for TCP/IP. To use it, just type

tnsping <Service Alias> [count]

Where Service Alias is defined in tnsnames.ora.

Typically, if tnsping returns an error, chances are the alias doesn't exist in tnsnames.ora file or a typo. Just add/correct that in the file, you will be able to ping the Oracle instance.

On a Windows system, this is where an introduction to tnsnames.ora would end. But not on Unix.

Gotcha on Unix

When we had connectivity issue with Oracle 11g database this morning, we kept getting errors while trying to tnsping.I logged in with the oracle id and checked the contents of tnsping.ora file. It looked OK. Also, with this id, I was able to login to Oracle without any problem. This was a puzzle.

Then I remembered the file level permission on *nix systems. I looked at the permissions attribute of the file, tnsnames.ora (If you do a ls -l on Unix, it shows you that). Bingo! The file had didn't have "read" access for Other (public).

-rw-rw-r-- tnsping.ora

In this case, even though the file was there, it wasn't "visible" to other id's, because of missing read permission. When I added that (r in bold), the other user was able to tnsping and connect to Oracle finally.

Note on connectivity

If tnsping is successful, it merely tells us that the SQL*Net listener is running correctly on the server side. This doesn't guarantee that the database itself is running. You need to login to find that out.

References