This discussion is archived
7 Replies Latest reply: Jun 12, 2013 11:20 AM by clcarter RSS

Getting Connection Time out while connecting and with tnsping

Mangat Newbie
Currently Being Moderated


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 Expert
    Currently Being Moderated

    >> 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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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? ...

Legend

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