This discussion is archived
7 Replies Latest reply: Sep 5, 2012 9:14 AM by user138400 RSS

Instant Client/TNSPing

user138400 Explorer
Currently Being Moderated
I've installed the Instant Client on a couple of servers just to have the small footprint. As I'm trying to validate my tnsnames I thought I would use tnsping. The problem is that it doesn't look like Instant Client installs tnsping. Where can I go to get just tnsping without a lot of other unwanted stuff?

Thanks.
  • 1. Re: Instant Client/TNSPing
    sb92075 Guru
    Currently Being Moderated
    user2502141 wrote:
    I've installed the Instant Client on a couple of servers just to have the small footprint. As I'm trying to validate my tnsnames I thought I would use tnsping. The problem is that it doesn't look like Instant Client installs tnsping. Where can I go to get just tnsping without a lot of other unwanted stuff?

    Thanks.
    you can't
    if you use EZCONNECT, then tnsnames.ora file is not required.

    http://www.orafaq.com/wiki/EZCONNECT
  • 2. Re: Instant Client/TNSPing
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    tnsping is primitive - it reads the HOST and PORT parameters from the TNS connection string, opens a socket connection, and sends a "ping" to the Listener, with the Listener responding with a "pong".

    It does not validate any other parameters in the TNS string. It does not determine database instance and database service availability. It does not confirm that any of the other parameters, like SID, SERVICE_NAME, or INSTANCE_NAME and so on, are valid.

    tnsping is very much overrated in testing a TNS connection string - testing merely the hostname with port is trivial.

    You can emulate tnsping by doing a telnet <host> <port> and then disconnecting if the attempt was successful.

    If you want to have something more sophisticated to test a host port for a Listener, you can try something like nmap instead. To properly test a TNS connection string, a client connection to the database (using that TNS string) needs to be attempted. And sqlplus is available for doing that, with InstantClient.
  • 3. Re: Instant Client/TNSPing
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    An example of how useless tnsping actually is in validating a TNS string:
    /home/billy> tnsping "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.251.95.80)(PORT=1521))(CONNECT_DATA=(SID=MakeItAnyDatabase)(SERVER=error)(FOO=ThisIsWrong)(FUNKY=Hello_World)))"
    
    TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 29-AUG-2012 08:48:53
    
    Copyright (c) 1997, 2005, Oracle.  All rights reserved.
    
    Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.251.95.80)(PORT=1521))(CONNECT_DATA=(SID=MakeItAnyDatabase)(SERVER=error)(FOO=ThisIsWrong)(FUNKY=Hello_World)))
    OK (0 msec)
  • 4. Re: Instant Client/TNSPing
    user138400 Explorer
    Currently Being Moderated
    Bill,

    Thanks for your response.

    Obviously I'm not a DBA. It was my understanding that TNSNAMES.ORA identified the server and port that the client (or in my case an app server) can use to communicate to the database. If TNSNAMES doesn't exist for Instant Client, how does the Oracle client know what box to contact?
  • 5. Re: Instant Client/TNSPing
    sb92075 Guru
    Currently Being Moderated
    user2502141 wrote:
    Bill,

    Thanks for your response.

    Obviously I'm not a DBA. It was my understanding that TNSNAMES.ORA identified the server and port that the client (or in my case an app server) can use to communicate to the database. If TNSNAMES doesn't exist for Instant Client, how does the Oracle client know what box to contact?
    JDBC::THIN does not require any tnsnames.ora
  • 6. Re: Instant Client/TNSPing
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user2502141 wrote:

    It was my understanding that TNSNAMES.ORA identified the server and port that the client (or in my case an app server) can use to communicate to the database.
    The tnsnames.ora file is similar to the hosts file.

    The hosts file is used to resolve a human friendly and readable hostname into an IP address. So you can for example use hostname "+mail.mydomain.com+" in your mail reader client, and the protocol stack resolves that using the local hosts file (or DNS) to the actual IP address.

    The tnsnames.ora file is used to resolve a human friendly and readable TNS alias into a connection string. So you can for example use TNS alias "+my-database.mydomain.com+" in a SQL client, and the Oracle client driver resolves that to a connection string.

    A connection string is in the format of "+(DESCRIPTION= (ADDRESS= <address details>) (CONNECT_DATA= <database details>)+".

     
    Instead of using a friendly hostname in your mail reader, you can use the actual IP address. e.g. +192.168.0.10+.

    Likewise, instead of a friendly TNS name in your Oracle client, you can use the actual connection string, e.g. "+(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=my-dev.mydomain.com)(PORT=1521)) (CONNECT_DATA= (SID=orcldev) (SERVER=dedicated)))+".

    The TNSPING utility tests 3 things:
    1) if you specify a TNS alias, it resolves that to a connection string (if you supply a connection string, this step is skipped)
    2) the IP address of the Listener to connect according to the HOST parameter of the connection string
    3) the port of the Listener to connect according to the PORT parameter of the connection string

    And that is it. It does not verify any other parameters (valid or invalid) in the connection string. You can do the same basic tests (steps 2 & 3) using telnet <hostname> <port>.
    If TNSNAMES doesn't exist for Instant Client, how does the Oracle client know what box to contact?
    By using a TNS connection string instead of a TNS alias. E.g.
    sqlplus scott/tiger@"(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=my-dev.mydomain.com)(PORT=1521)) (CONNECT_DATA= (SID=orcldev) (SERVER=dedicated)))"
    You also can create tnsnames.ora and sqlnet.ora files in the same local directory (anywhere accessible on your PC/notebook), and then use the TNS_ADMIN environment variable to point to that local directory. The Oracle driver will read these files from that directory, courtesy of this environment variable. So with InstantClient you can use a tnsnames.ora file, without having an ORACLE_HOME set and installed.

    Using EZCONNECT - that allows you to specify the basic connection data that the Oracle client driver needs to use to create a connection string. E.g.
    sqlplus scott/tiger@//my-dev.mydomain.com:1521/orcldev
    Keep in mind though that with EZCONNECT you need to specify the database service name - not the database SID. With a connection string itself, you can specify the SID, service name or instance name - by using the appropriate parameter. EZCONNECT only supports the service name.
  • 7. Re: Instant Client/TNSPing
    user138400 Explorer
    Currently Being Moderated
    Billy,

    Thank you again. Your explanation was helpful. It also shows me how much I don't know. Time to hit the manuals again.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points