7 Replies Latest reply: Jun 12, 2013 1:20 PM by clcarter RSS

    Getting Connection Time out while connecting and with tnsping

    Mangat


      Hello Experts,

       

      I am new to Oracle world, so please go easy on me.

       

      I installed Oracle 11 XE on Centos 6.4 VM. followed the official installation procedure and database was installed without any errors.

       

      I am able to login to database through sqlplus and able to run sql queries.

       

      Problem comes when i am trying to connect from sql developer or external application. i get Connection Time out error.

       

      Below are the tnsnames.ora and listener.ora file content

       

      # tnsnames.ora Network Configuration File:

      XE =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = basevm)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)
          )
        )

      EXTPROC_CONNECTION_DATA =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
          )
          (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
          )
        )

      -------------

      # listener.ora Network Configuration File:

      SID_LIST_LISTENER =
        (SID_LIST =
          (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
            (PROGRAM = extproc)
          )
        )

      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
            (ADDRESS = (PROTOCOL = TCP)(HOST = basevm)(PORT = 1521))
          )
        )

      DEFAULT_SERVICE_LISTENER = (XE)

      ----------------------------------------------

      lsnrctl stat shows service stats as UNKNOWN and tnsping also fails

       

      bash-4.1$ lsnrctl stat

      LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 11-JUN-2013 02:00:48

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENER
      Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
      Start Date                10-JUN-2013 19:39:03
      Uptime                    0 days 6 hr. 21 min. 45 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Default Service           XE
      Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
      Listener Log File         /u01/app/oracle/diag/tnslsnr/sagbase/listener/alert/log.xml
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=basevm)(PORT=1521)))
      Services Summary...
      Service "PLSExtProc" has 1 instance(s).
        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully

      -----------------

      bash-4.1$ tnsping xe

      TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 11-JUN-2013 02:01:16

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

      Used parameter files:


      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = basevm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
      TNS-12535: TNS:operation timed out

       

      I am stuck and don't know where else to check.

       

      Could you guys please help me out here?

       

      Thanks.

        • 1. Re: Getting Connection Time out while connecting and with tnsping
          clcarter

          >> don't know where else to check.

           

          firewall? Open port 1521. And port 8080 as well, if that is the port in use. However, apex is not going to work yet until the PORT=8080... endpoint shows up in your lisnrctl status output.

          • 2. Re: Getting Connection Time out while connecting and with tnsping
            yoonas

            Hi,

             

            Are you sure xe database is up? It seems database is not up yet.

             

            sqlplus / as sysdba

            startup

             

             

            and then post the output of lsnrctl status

             

            If you have more than one database running make sure the correct one is started

             

            If you are trying to connect from a remote pc,  ping and see if you can reach the database server.

            Also check the firewall on the database server

            • 3. Re: Getting Connection Time out while connecting and with tnsping
              Mangat

              I have iptables and firewall disabled.

               

              Oracle XE is installed in Centos 6.4 VM.

               

              listener.ora and tnsnames.ora looks fine to you?

              • 4. Re: Getting Connection Time out while connecting and with tnsping
                Mangat

                Yes data base is up and running. I am able to connect via sql plus and able to run queries on internal tables.

                 

                i have only one database installed.

                 

                Fire wall is disabled and i am working directly on Centos Machine. not accessing remotely.

                • 5. Re: Getting Connection Time out while connecting and with tnsping
                  yoonas

                  How are you connecting to query internal tables?

                  I did not understand which tables you are talking about when you say "internal" tables

                   

                   

                  can you run this query and post the output

                   

                  select open_mode from v$database

                   

                   

                   

                  Are you able to connect with system/<password>

                  • 6. Re: Getting Connection Time out while connecting and with tnsping
                    Mangat

                    By internal tables i mean tables which are used by oracle or tables which gets created when oracle is installed.

                    I am able to connect through sqlplus using sys and system username

                    below is the output you asked.

                     

                    SQL> connect system/<password> as sysdba

                    Connected.

                    SQL> select open_mode from v$database;

                    OPEN_MODE

                    --------------------

                    READ WRITE

                    • 7. Re: Getting Connection Time out while connecting and with tnsping
                      clcarter

                      That tnsping error indicates several possible problems, most related to the setup for the client. Or network setup on the database server host.

                       

                      Connecting to a database using sqlnet requires three things, the database host, the listener port, and the ORACLE_SID or instance SERVICE_NAME.

                       

                      The tnsping utility is only concerned with the first two items, host and port. It will not perform an actual connection, and will not verify the tnsalias ORACLE_SID or SERVICE_NAME values.

                       

                      An "OK" result from tnsping verifies that the specified host has a listener running at the specified port and it is servicing connections.

                       

                      Since you're not getting an "OK" from tnsping, could be something is blocking the connection (i.e. firewall), the host= specification is not correct, or the port= spec is not correct. Note that host= must either be a valid and correct hostname, or a an IPv4 address. If IPv6 is in the mix, only the correct hostname will work. A hostname that resolves correctly.

                       

                      Look at your network interface setup on the host, find its IP address, make sure the IP will `ping`, and try a new sqlnet.ora entry with the IPv4 address.

                       

                      ifconfig -a

                      ...

                      <interface>: flags=...

                      inet n.n.n.n

                      ...

                      # in sqlnet.ora:

                      xe2 = ... host=n.n.n.n ...

                      tnsping xe2

                      ... ok? ...