ORA-3136 Error Analysis--warning Inbound Connection Timed out

Summary description

　

Warning:inbound Connection timed out (ORA-3136)

This mistake I have never encountered before, this morning the customer on MSN, his one of the Oracle server is busy, alert in the frequent occurrence of this error prompts, causing the connection failed. The following are the error prompts for the alert file:

......

Wed Feb 09:03:02 2008 Completed checkpoint up to RBA [0x184d.2.10], scn:1203810646 Wed Feb 09:03:27 2008 Nbound Connection timed out (ORA-3136) Wed Feb-09:03:27 2008 Warning:inbound Connection timed out (ORA-3136) Wed Feb 2 7 09:03:27 2008 Warning:inbound Connection timed out (ORA-3136) Wed Feb 09:03:27 2008 Warning:inbound Connection D out (ORA-3136) Wed Feb-09:03:27 2008 Warning:inbound Connection timed out (ORA-3136) Wed Feb-09:03:27 2008 Warnin G:inbound Connection timed out (ORA-3136) Wed Feb-09:03:27 2008 Warning:inbound Connection timed out (ORA-3136) Wed F EB-09:03:27 2008 Warning:inbound Connection timed out (ORA-3136) Wed-Feb-09:04:30 2008 incremental checkpoint up to RBA [0x184d.e5a6.0], current log tail at RBA [0x184d.43aaa.0] Wed Feb 27 09:05:02 2008

......

　

This warning I have not contacted before, but literally can see is connect time out, formerly common is ORA-12170. So I'm wondering.

　

Preliminary Analysis

　

1 Check listener.ora and Sqlnet.ora parameter settings, no other exceptions found

    $ cat Listener.ora ################ # Filename ...: Listener.ora # Name ...: # Date .......:.... £ º
             ################ Admin_restrictions_listener = on LISTENER = (Address_list = (address =
             (PROTOCOL = IPC) (KEY = HS5. World)) (address= (PROTOCOL = IPC) (KEY = HS5)) (A ddress = (COMMUNITY = SAP. World) (PROTOCOL = TCP) (HOST = gvshs5db) (PORT = 1527))) ST
      Artup_wait_time_listener = 0 Connect_timeout_listener = Ten Trace_level_listener = off Sid_list_listener =
      (Sid_list = (Sid_desc = (Sid_name = HS5) (Oracle_home =/oracle/hs5/102_64)) ) $ cat Sqlnet.ora ################ # Filename ...: sqlnet.ora ################ AUTOMATIC_IPC = O N trace_level_client = Off NAMES. Default_domain = world # 05.01.06 unsorported parameter now #NAME. Default_zone = world # 05.01.06 Set the default to ten sqlnet. Expire_time = # 05.01.06 set to default #TCP.
 Nodelay=yes # 05.01.06 set to 32768 default_sdu_size=32768 $

2 through Topas, Vmstat can see the current system load is very high, CPU is basically 100% (slightly)

So the initial conclusion is that the system overload causes the connection to timeout.

　

Further analysis

　

I have not seen this warning before, so I have directly consulted Oracle related information. It turns out that this is a new attribute on the 10GR2, which can be set by Sqlnet.inbound_connect_timeout, which by default is 60 seconds.

　

The main causes of this warning may be:

1) Server Gets a connection request from a malicious (malicious) client which is not supposed to connect to the database, in which Case the error thrown is the correct behavior. You can get the client address for which the error is thrown via sqlnet log file.
2) The server receives a valid client connection request but the client takes a long time to authenticate more than the DEF Ault seconds.
3) The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.
　

So how to locate what caused this warning to appear.

　

The default value of seconds is good enough into most conditions for the database server to authenticate a client conn Ection. If its taking longer period, then it worth checking all below points-before for the going:

1. Check Whether local connection on the database server is sucessful & quick.
2. If local connections are quick, then check for underlying network delay and the help of your network administrator .
3. Check Whether your Database performance has degraded by anyway.
4. Check alert log for no critical errors for eg, ORA-600 or ORA-7445 and get them resolved.
These critical errors might have triggered the the database server.

As a workaround to avoid only this warning messages, you can set the parameters sqlnet. Inbound_connect_timeout
and inbound_connect_timeout_<listenername> to the value of more than.

For e.g 120. So that the client would have more time to provide the authentication information to the database. You could have to further tune this parameter values according to your setup.

To set these parameter
1. In Server side Sqlnet.ora file add Sqlnet. Inbound_connect_timeout
for e.g
Sqlnet. Inbound_connect_timeout =
2. In Listener.ora file-inbound_connect_timeout_<listenername> = '
for e.g if ' listener name is listener the N-
Inbound_connect_timeout_listener = $

Note:from Oracle version 10.2.0.3 onwards the default value of Inbo Und_connect_timeout_<listenername> is seconds. For previous releases it are zero by default.

How to check whether inbound timout are active for the listener and database server

for eg. inbound_connect_timeout_<listener_name> =4

You can check whether the parameter are active or not by simply Doi ng Telnet to the listener port.
$ telnet <database server ip> <listener port>

For eg.
$ telnet 192.168.12.13 1521
The Telnet session should disconnect after 4 seconds which indicates this inbound connection for the timeout is active.

To check whether the database server sqlnet.inbound_connect_timeout is active:
Eg. Sqlnet.inbound_connect_timeout =5

A. For dedicated server Setup, enable the support level Sqlnet server tracing'll show the timeout value as below:

Niotns:enabling CTO, value=5000 (milliseconds) <== 5 seconds Niotns:not enabling dead connection. Niotns:listener bequeathed shadow coming to life ...

B. For shared Server setup,
$ telnet <database server ip> <dispatcher port>
For eg.
$ telnet 192.168.12.13 51658
The Telnet session should disconnect after 5 seconds which indicates this sqlnet.inbound_connection_timeout is active.

Turn from: http://tomszrp.itpub.net/post/11835/467438