Click here to Skip to main content
15,867,308 members
Articles / All Topics

Quick Tip: tnsping

Rate me:
Please Sign up or sign in to vote.
4.80/5 (2 votes)
28 Dec 2013CPOL2 min read 6.6K  
Tnsping is a nice little utility that comes with Oracle. This is a quick tip about it.

Tnsping is a nice little utility that comes with Oracle. Lots 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 issues 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 ids, 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)


Written By
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!

Comments and Discussions

 
-- There are no messages in this forum --