Click here to Skip to main content
Click here to Skip to main content

Quick Tip: tnsping

, 28 Dec 2013
Rate this:
Please Sign up or sign in to vote.
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 t

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

  1. http://edstevensdba.wordpress.com/2011/02/27/tnsping-101/
  2. http://www.orafaq.com/wiki/Tnsnames.ora
  3. http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/TDT2/Default.aspx
  4. http://docs.oracle.com/cd/B19306_01/network.102/b14212/connect.htm#sthref1535
  5. http://www.dartmouth.edu/~rc/help/faq/permissions.html

Filed under: CodeProject, Databases, Oracle Tagged: Connectivity, Oracle, SQL*Net, TNS, TNSPing

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Sam Varadarajan
Software Developer (Senior) City of Los Angeles
United States United States
Originally a Physics major, fell in love with Microprocessors and switched to Computer Science 20+ years ago. Since then, dabbled in various languages including, PowerBuilder, Oracle, Java, C, C++, Perl, Python etc. Constantly striving for quality and performance too.
 
I try to help fellow developers with technology as a way of "giving back to the community". Blogging became a natural extension of that effort. Still learning to perfect that art. If one new programmer out there benefits from this blog, my time and effort are fully worth it.
 
The underlying theme in my blogs is power and beauty of programming (and technology in general). A well written program gives me the sense of awe you get when you look at a man made wonder like Angkor Wat. You experience poetry, art, mystique, power all at once. A program and the troubleshooting that ensues also gives you a feeling you get while reading a mystery novel!
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 28 Dec 2013
Article Copyright 2013 by Sam Varadarajan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid