14 Replies Latest reply on May 3, 2010 12:26 PM by Boopathy Vasagam

    How to do tnsping?

    Boopathy Vasagam
      How to do tnsping?
      I am new to databse.
      i am using Oracle 10.2 database in windows XP. How to do 'tnsping' in that?
        • 1. Re: How to do tnsping?
          orawiss
          windows run => cmd => tnsping
          • 2. Re: How to do tnsping?
            user12003658
            Alternately,Go to OS command prompt, set environment variables for ORACLE_HOME and ORACLE_SID. Enter "tnsping <ORACLE_SID>"
            • 3. Re: How to do tnsping?
              orawiss
              http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/connect.htm#i427276
              • 4. Re: How to do tnsping?
                Boopathy Vasagam
                This is my service_name entry in the tnsnames.ora

                "orcl=(DESCRIPTION =
                     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.41.147)(PORT = 1521))
                     (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))"

                Then should i do like this?
                <tnsping orcl>
                ???
                • 5. Re: How to do tnsping?
                  orawiss
                  yes it should work like this
                  tnsping orcl
                  • 6. Re: How to do tnsping?
                    Azar
                    C:\Users\Azar>set oracle_sid=azar

                    C:\Users\Azar>tnsping azar

                    TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 27-APR-2010 19:57:13

                    Copyright (c) 1997, 2010, Oracle. All rights reserved.

                    Used parameter files:
                    C:\app\Azar\product\11.2.0\dbhome_2\network\admin\sqlnet.ora


                    Used TNSNAMES adapter to resolve the alias
                    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = azar)))
                    OK (80 msec)

                    C:\Users\Azar>sqlplus

                    SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 27 19:57:24 2010

                    Copyright (c) 1982, 2010, Oracle. All rights reserved.

                    Enter user-name: sys/sys@azar as sysdba

                    Connected to:
                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options

                    SQL>
                    • 7. Re: How to do tnsping?
                      768668
                      As above said

                      tnsping orcl

                      should work after setting ORACLE_SID and ORACLE_HOME. By any chance if you see "unknown command tnsping" error.

                      then you should set PATH. you can set it for this session or you can go to environment parameters in Windows, and append path to your oracle's bin directory to the existing path in Windows environment variables. This way you dont need to set evry time.


                      Regards,
                      Krmreddy.
                      1 person found this helpful
                      • 8. Re: How to do tnsping?
                        EdStevens
                        Boopathy Vasagam wrote:
                        How to do tnsping?
                        I am new to databse.
                        i am using Oracle 10.2 database in windows XP. How to do 'tnsping' in that?
                        Others are helping you with how to run a command. In anticipation of what your next question will be ....


                        Assume you have the following in your tnsnames.ora:
                        larry =
                          (DESCRIPTION =
                            (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
                            )
                            (CONNECT_DATA =
                              (SERVICE_NAME = curley)
                            )
                          )
                        Now, when you issue a connect, say like this:
                        $> sqlplus scott/tiger@larry
                        tns will look in your tnsnames.ora for an entry called 'larry'. Next, tns sends a request to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley).

                        Where is (HOST = myhost) on the network? When the request gets passed from tns to the next layer in the network stack, the name 'myhost' will get resolved to an IP address, either via a local 'hosts' file, via DNS, or possibly other less used mechanisms. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora.

                        Next, the request arrives at port 1521 on myhost. Hopefully, there is a listener on myhost configured to listen on port 1521, and that listener knows about SERVICE_NAME = curley. If so, you'll be connected.

                        A couple of important points.

                        First, the listener is a server side only process. It's entire purpose in life is the receive requests for connections to databases and set up those connections. Once the connection is established, the listener is out of the picture. It creates the connection. It doesn't sustain the connection. One listener, running from one oracle home, listening on a single port, will serve multiple database instances of multiple versions running from multiple homes. It is an unnecessary complexity to try to have multiple listeners. That would be like the telephone company building a separate switchboard for each customer.

                        Second, the tnsnames.ora file is a client side issue. It's purpose is for addressess resolution - the tns equivelent of the 'hosts' file further down the network stack. The only reason it exists on a host machine is because that machine can also run client processes.


                        What can go wrong?

                        First, there may not be an entry for 'larry' in your tnsnames. In that case you get "ORA-12154: TNS:could not resolve the connect identifier specified" No need to go looking for a problem on the host, with the listener, etc. If you can't place a telephone call because you don't know the number (can't find your telephone directory (tnsnames.ora) or can't find the party you are looking for listed in it (no entry for larry)) you don't look for problems at the telephone switchboard.

                        Maybe the entry for larry was found, but myhost couldn't be resolved to an IP address (say there was no entry for myhost in the local hosts file). This will result in "ORA-12545: Connect failed because target host or object does not exist"

                        Maybe there was an entry for myserver in the local hosts file, but it specified a bad IP address. This will result in "ORA-12545: Connect failed because target host or object does not exist"

                        Maybe the IP was good, but there is no listener running: "ORA-12541: TNS:no listener"

                        Maybe the IP was good, there is a listener at myhost, but it is listening on a different port. "ORA-12560: TNS:protocol adapter error"

                        Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesn't know about SERVICE_NAME = curley. "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor"



                        Also, please be aware that tnsping goes no further than to verify there is a listener at the specified host/port. It DOES NOT prove anything regarding the status of the listener's knowledge of any particular database instance.
                        1 person found this helpful
                        • 9. Re: How to do tnsping?
                          Boopathy Vasagam
                          Hi,
                          Your reply gave me a good understanding about tnsnames and listner.
                          You have given the possible errors.But i got the following error while i did the tnsping.

                          TNS-03505: Failed to resolve name

                          What might be the issue in this case?
                          Still i can connect to the database from sqlplus.
                          • 10. Re: How to do tnsping?
                            orawiss
                            http://www.shutdownabort.com/errors/TNS-03505.php
                            • 11. Re: How to do tnsping?
                              dialaya
                              Try this:
                              tnsping <IP Address>
                              tnsping <HostName>

                              If your pc is in a network try to ping it with HostName as argument. (ping <HostName>).

                              So after tnsping command, if you have tns error such as failed to resolve the host name it mean that your host is not found or not exists in your network.

                              I resolve this by modifying the hosts file of Windows OS and manually add mapping <IP address> <HostName>.

                              Hope it help you too.
                              1 person found this helpful
                              • 12. Re: How to do tnsping?
                                Boopathy Vasagam
                                Since i am a newbie to database could you please tell me how to set the ORACLE_HOME and ORACLE_SID?

                                Edited by: Boopathy Vasagam on 3 May, 2010 2:37 PM
                                • 13. Re: How to do tnsping?
                                  Boopathy Vasagam
                                  i did tnsping<IP Address>
                                  and i got the following:

                                  Line: -----

                                  C:\>tnsping 172.31.41.147

                                  TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 03-MAY-2
                                  010 14:39:46

                                  Copyright (c) 1997, 2007, Oracle. All rights reserved.

                                  Used parameter files:
                                  C:\product\11.1.0\db_1\network\admin\sqlnet.ora

                                  Used HOSTNAME adapter to resolve the alias
                                  Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
                                  COL=TCP)(HOST=172.31.41.147)(PORT=1521)))
                                  OK (30 msec)

                                  Line: -----

                                  I am using  oracle 10.2.0.1.0 database and OWB 11g.So if i do tnsping used parameter files should be
                                  C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\sqlnet.ora
                                  right?

                                  but in case i am getting
                                  C:\product\11.1.0\db_1\network\admin\sqlnet.ora
                                  • 14. Re: How to do tnsping?
                                    Boopathy Vasagam
                                    Hi all,
                                    Thanks to all for answering my question.
                                    Finally i got the solution for this issue.
                                    I am writing the suggestion which i got.
                                    As i am a newbie please correct me if my understanding in this suggestion is wrong.

                                    -----
                                    My PATH variable earlier looked like this:
                                    C:\>path
                                    PATH=C:\product\11.1.0\db_1\bin;C:\oracle\product\10.2.0\db_1\bin;C:\WINDOWS\sys
                                    tem32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\system32\WindowsPowerShell\
                                    v1.0;
                                    -----

                                    So when the tnsping searched to the path C:\product\11.1.0\db_1\bin initially, this will initially use the used parameter file as:

                                    Used parameter files:
                                    C:\product\11.1.0\db_1\network\admin\sqlnet.ora

                                    This used parameter file corresponds to the OWB 11g. So i dont have a 'tnsnames.ora' atleast there. So the possible solutions now to get rid off the TNS-03505: Failed to resolve name error are:
                                    1)To put a tnsnames.ora file in the path specified in the used parameter files:
                                    2)To change the order of the oracle database path in the environment variables. This path *(C:\oracle\product\10.2.0\db_1\bin;)* should be the first one in the ENVIRONMENT VARIABLES' PATH

                                    I did the second one and now my PATH Variable looks like:
                                    -----
                                    C:\>path
                                    PATH=C:\oracle\product\10.2.0\db_1\bin;C:\product\11.1.0\db_1\bin;C:\WINDOWS\sys
                                    tem32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\system32\WindowsPowerShell\
                                    v1.0;
                                    -----

                                    Once again Thank you all for your answers.
                                    Will meet with different questions.