WARNING: inbound connection timed out (ORA-3136)

there is lots of articles on the web that describe inbound connection timed out (ora-3136) in the realms of oracle 10gR2 and the new default setting of its sqlnet.ora-parameter SQLNET.INBOUND_CONNECT_TIMEOUT (the tnsnames.ora-parameter INBOUND_CONNECT_TIMEOUT_listener alike). people propose to reset the parameter back to 0, from 60, seconds to achive an indefinite wait for login authentication against the database. so far, so bad. that is, just tackling problems by turning around control buttons will not solve the problem nor will it help in understanding the root cause of the problem. this is especially true, iff the problem appears spuriously, not in a determinable order or user/application segment.

but, what may be the root cause? many argue that network misdesign along firewalls or nat might provoke the inbound connection timed out (ora-3136) warning with the alert.log. hhm, do not know, never saw or experienced that. however, what i can show is that this very warning can be easily induced by connecting a privileged session to an idle instance (remember the difference between instance and database, especially on windows; an instance is represented by a service and the database will be bound to it during startup) and such a way prove, that also an database/host overload can be responsible. just change the missing or unbound database with a non-responding database in your mind to grasp the experiment approach.

ok, let us connect to an idle instance:

C:\TEMP>sqlplus "/@dgs as sysdba"
SQL*Plus: Release - Production on Fri Aug 27 13:03:20 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.

nealy exactly 60 seconds later, you will see this warning with the alert.log.

Fri Aug 27 13:04:21 2010
WARNING: inbound connection timed out (ORA-3136)

also, still trying to do anything after 60 seconds shows that oracle has already dropped the connection.

SQL> select * from v$version;
select * from v$version
ERROR at line 1:
ORA-03113: end-of-file on communication channel

iff you try to do anything before the 60 seconds limit, you will get the usual not available error.

SQL> select * from v$version;
select * from v$version
ERROR at line 1:
ORA-01034: ORACLE not available

in sum, do not only look out for network errors or misdesign upon inbound connection timed out (ora-3136) warnings with the alert.log. do also check the database for workload and the ability to at least respond to user requests in a given time.

have fun


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s