Solved

unable to connect to Oracle DB..TNS operation timed out error..

Posted on 2007-03-28
11
1,671 Views
Last Modified: 2012-05-05
I am unable to connect an Oracle database from my machine. The following entry is there in the tnsnames.ora file.

XXX =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = YYY.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = XXX)
    )
  )

There is another db in the same server, which has a similar database, and I am able to connect to it with no problem.

When I try to tnsping XXX, I get
TNS-12535 : TNS: operation timed out error.

Any ideas why this error is happening?

 LM


0
Comment
Question by:LubomirMasar
11 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 84 total points
ID: 18809175
99% of the time a TNS-12535 is caused by a firewall between the client machine and database server.  Can you confirm this?
0
 

Author Comment

by:LubomirMasar
ID: 18809400
slightwv,

Thanks..I may have to ask my sys admin about this..But he is unavailable for the rest of the week..that is the problem..
Is there a way I can check this myself? How do I know if there is a firewall between my machine and server?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18809489
I don't know a way to detect a firewall.  If a single person in your organization is the only one that knows how things are put together, you had better keep him REAL happy...  Someone else should know.  They may not know what ports are allowed through the firewall but they should now the layout of the network.

You might post a question over in one of the network zones to see if they know a way.
0
 
LVL 13

Expert Comment

by:riazpk
ID: 18814171
what is the result if you try to PING the db server?
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 83 total points
ID: 18814219
I can not believe that the DBA has running firewall on the Oracle server
but this is a brand new installation.
Investigate if other colleagues can connect that server.
If yes, try to find the firewall on your machine.
Keep in mind that Windows XP SP2 by default starts a firewall.
Find it in Control Panel and shut it down. Sort out hidden firewalls
(in PROCESSES - e.g. MicroTrend Personal Firewall)

If more users complain that they cannot connect the server
you have to investigate the server machine:
1. If the Listener is running
2. If the Oracle Instance is bounced
3. If the server machine is visible on the network

In Oracle 10g this also happens because of failed dynamic registration of the Oracle Instance
by the Listener. So you have post here the LISTENER.ORA file from the server machine.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18815564
>>what is the result if you try to PING the db server?

I don't think ping will tell you anything here.  A TNS-12535 typically means that the Listener responded and the client was waiting for the server to talk on the new pipe created by the listener.

Most shops that don't fully understand Oracle networking, allow the listener port through the firewall thinking that's all they need.  The Firewall blocks the random high port that the listener creates and generates the 12535.
0
 
LVL 7

Assisted Solution

by:gattu007
gattu007 earned 83 total points
ID: 18817896
1. Find out if you are going through any Firewalls or Routers.
 
2. Try to "ping" the ip address or hostname from the client.
   Check the timing response and see if it is acceptable.
   If it is not, then you need to request assistance from your System or
   Network Administrator.

   With 8i and earlier, you can increase the listener.ora file parameter
   CONNECT_TIMEOUT_<listener_name> and make it a higher value.
   Doing this, however, will correct the connectivity problem but will be
   masking the real problem of the physical network.

   (See the Sqlnet / Net8 Administrators guide for details on how to make these
    changes)

   With 9i and 10g, the CONNECT_TIMEOUT_<listener_name> parameter is
   obsoleted. You should consult the Net Services Administrator's Guide
   for your version to find out what connection timeout parameters are
   available.

3. With 10.1 and 10.2, an ORA-12535 or ORA-12170 can be received by the client
   if the time to connect is too slow. This can be true if the listener's
   parameter INBOUND_CONNECT_TIMEOUT_<listener_name> and/or Server's sqlnet.ora
   parameter SQLNET.INBOUND_CONNECT_TIMEOUT are set too low. This is especially
   true for 10.2 (see Note 345197.1).
   The corresponding error on the Server side may be an ORA-3136 in the
   alert log and the sqlnet.log may contain an ORA-12170.

You can check the below note from Metalink

Troubleshooting Guide TNS-12535 & ORA-12535 Errors
        Doc ID:       Note:119706.1
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20591310
Forced accept.

Computer101
EE Admin
0
 

Expert Comment

by:KrishnaNSingh
ID: 24174386
Adding INBOUND_CONNECT_TIMEOUT value is a only workaround but what could be the possible reason to occur WARNING: inbound connection timed out (ORA-3136).
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24174425
Slow connection of course.
Defect NIC card, defect LAN card, dual home mess on the server ....
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now