6 Replies Latest reply: Feb 18, 2014 3:23 AM by KarK RSS

    tnsping failure

    sourav_biswas

      Hi All,

      I have installed two 11.2.0.1 databases on the same virtual machine and it allows me to connect to them separately. however, when I tnsping them I get the following error:-


      [oracle@diamond ~]$ tnsping cost1

      TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 18-FEB-2014 13:17:47

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

      Used parameter files:

      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = diamond.example.com)(Port = 1521))) (CONNECT_DATA = (SID = COST1)))
      TNS-12535: TNS:operation timed out

       

      Please find below ht evirtual environment:-

       

      VM Software: Virtual Box VM 4.3.6

      OS: Cent OS 6.4

      DB1_SID: cost1

      DB1_Home: /u01/app/oracle/product/11.2.0.1/db_1

      DB2_SID: cosp1

      DB2_Home: /u02/app/oracle/product/11.2.0.1/db_1

      I have placed listener.ora and tnsnames.ora file at /etc/ location and created softlink for them at /u01/app/oracle/product/11.2.0.1/db_1/network/admin and /u02/app/oracle/product/11.2.0.1/db_1/network/admin locations respectively.

       

      Contents of listener:

       

      LISTENER =

        (ADDRESS_LIST =

          (ADDRESS = (PROTOCOL = tcp)(PORT = 1532)(HOST = diamond.example.com))

        )

       

      CONNECT_TIMEOUT_LISTENER = 0

       

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

             (GLOBAL_DBNAME = COSP1)

             (SID_NAME = COSP1)

             (ORACLE_HOME = /u02/app/oracle/product/11.2.0.1/db_1)

         )

       

          (SID_DESC =

            (GLOBAL_DBNAME = COST1)

            (SID_NAME = COST1)

            (ORACLE_HOME = /u01/app/oracle/product/11.2.0.1/db_1)

          )

        )

       

      Contents of tnsnames:


      COSP1 =

        (DESCRIPTION =

          (ADDRESS_LIST =

              (ADDRESS = (PROTOCOL = TCP)(Host = diamond.example.com)(Port = 1532))

          )

          (CONNECT_DATA = (SID = COSP1))

        )

       

      COST1 =

        (DESCRIPTION =

          (ADDRESS_LIST =

              (ADDRESS = (PROTOCOL = TCP)(Host = diamond.example.com)(Port = 1532))

          )

          (CONNECT_DATA = (SID = COST1))

        )

       

      Please find the results of lsnrctl service, status and start

       

      ==========================================================================

      [oracle@diamond admin]$ lsnrctl service

       

      LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 18-FEB-2014 13:23:38

       

      Copyright (c) 1991, 2009, Oracle.  All rights reserved.

       

      Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=diamond.example.com))

      TNS-12543: TNS:destination host unreachable

      TNS-12560: TNS:protocol adapter error

        TNS-00513: Destination host unreachable

         Linux Error: 113: No route to host

      ==========================================================================

      [oracle@diamond admin]$ lsnrctl status

       

      LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 18-FEB-2014 13:23:56

       

      Copyright (c) 1991, 2009, Oracle.  All rights reserved.

       

      Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=diamond.example.com))
      TNS-12535: TNS:operation timed out
      TNS-12560: TNS:protocol adapter error
        TNS-00505: Operation timed out

       

      ==========================================================================

       

      [oracle@diamond admin]$ lsnrctl start

       

      LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 18-FEB-2014 13:46:57

       

      Copyright (c) 1991, 2009, Oracle.  All rights reserved.

       

      Starting /u02/app/oracle/product/11.2.0.1/db_1/bin/tnslsnr: please wait...

       

      TNSLSNR for Linux: Version 11.2.0.1.0 - Production

      System parameter file is /etc/listener.ora

      Log messages written to /u02/app/oracle/diag/tnslsnr/diamond/listener/alert/log.xml

      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=diamond)(PORT=1532)))

       

      Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1532)(HOST=diamond.example.com))

      TNS-12535: TNS:operation timed out

      TNS-12560: TNS:protocol adapter error

        TNS-00505: Operation timed out

       

      Please suggest how to establish successful tnsping between these database. My idea is to create another database and make it Recovery Catalog Database. So I need the connection string in place so that RMAN can connect it. Thank you in advance!

        • 1. Re: tnsping failure
          KarK

          Hi,

           

          Check whether the HOST name (diamond.example.com) is correct which you have specified in then listener.ora and tnsnames.ora.

           

          Can you post the output of the below:

           

          ping  diamond.example.com

          • 2. Re: tnsping failure
            Ratnesh Kumar Roy

            check the tnsnames.ora file before pinging.  is everything right ?

             

            if yes, then fire a command

             

             

             

            [ram@atra ~]$ tnsping ramdb

            • 3. Re: tnsping failure
              onkar.nath

              Hi,

               

              Used TNSNAMES adapter to resolve the alias
              Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = diamond.example.com)(Port = 1521))) (CONNECT_DATA = (SID = COST1)))
              TNS-12535: TNS:operation timed out

               

              Please find below ht evirtual environment:-

               

              VM Software: Virtual Box VM 4.3.6

              OS: Cent OS 6.4

              DB1_SID: cost1

              DB1_Home: /u01/app/oracle/product/11.2.0.1/db_1

              DB2_SID: cosp1

              DB2_Home: /u02/app/oracle/product/11.2.0.1/db_1

              I have placed listener.ora and tnsnames.ora file at /etc/ location and created softlink for them at /u01/app/oracle/product/11.2.0.1/db_1/network/admin and /u02/app/oracle/product/11.2.0.1/db_1/network/admin locations respectively.

               

              Contents of listener:

               

              LISTENER =

                (ADDRESS_LIST =

                  (ADDRESS = (PROTOCOL = tcp)(PORT = 1532)(HOST = diamond.example.com))

                )

               

              CONNECT_TIMEOUT_LISTENER = 0

               

              SID_LIST_LISTENER =

                (SID_LIST =

                  (SID_DESC =

                     (GLOBAL_DBNAME = COSP1)

                     (SID_NAME = COSP1)

                     (ORACLE_HOME = /u02/app/oracle/product/11.2.0.1/db_1)

                 )

               

                  (SID_DESC =

                    (GLOBAL_DBNAME = COST1)

                    (SID_NAME = COST1)

                    (ORACLE_HOME = /u01/app/oracle/product/11.2.0.1/db_1)

                  )

                )

               

              Contents of tnsnames:


              COSP1 =

                (DESCRIPTION =

                  (ADDRESS_LIST =

                      (ADDRESS = (PROTOCOL = TCP)(Host = diamond.example.com)(Port = 1532))

                  )

                  (CONNECT_DATA = (SID = COSP1))

                )

               

              COST1 =

                (DESCRIPTION =

                  (ADDRESS_LIST =

                      (ADDRESS = (PROTOCOL = TCP)(Host = diamond.example.com)(Port = 1532))

                  )

                  (CONNECT_DATA = (SID = COST1))

              It seems that PORT is different. You might have multiple tnsnames.ora on your system under multiple homes. Reason: your tnsping shows PORT=1521 but the contents of the file shows PORT=1532.

               

              Onkar

              • 4. Re: tnsping failure
                sourav_biswas

                Thank you very much guys!

                 

                KarK you really helped me out....Well here is what happened.. since I am using a Virtual Environment, it was NAT settings for network connectivity on Virtual box. so I was getting 10.1.5.14 IP address which was updated on /etc/hosts. Later I changed my Virtual box network settings to Bridge network and now I am getting 192.168.1.7 IP address from my router. And obviously I missed out on updating it on /etc/hosts

                 

                So thank you again KarK, the moment I pinged diamond.example.com, I figured what was going wrong. You are my HERO!!!!!

                • 5. Re: tnsping failure
                  sourav_biswas

                  Hi Onkar,

                   

                  It was my mistake. the output is old but later I changed the Port to 1532. But anyways, the Issue is resolved. it was the IP Address which I didn't update on /etc/hosts as I changed network settings from NAT to bridge network

                   

                  Best Reagrds,

                  Sourav Biswas

                  • 6. Re: tnsping failure
                    KarK

                    You are welcome