Monday, June 7, 2010

WARNING: inbound connection timed out (ORA-3136)

WARNING: inbound connection timed out (ORA-3136)


WARNING: inbound connection timed out (ORA-3136)
Mon Jun 7 15:03:02 2010
Errors in file /d04/admin/stlbas/bdump/stlbas_j000_21589.trc:
ORA-07445: exception encountered: core dump [ksuklms()+672] [SIGSEGV] [Address not mapped to object] [0x000000062] [] []
Mon Jun 7 15:03:04 2010
WARNING: inbound connection timed out (ORA-3136)
Mon Jun 7 15:03:06 2010
WARNING: inbound connection timed out (ORA-3136)
Mon Jun 7 15:05:04 2010
WARNING: inbound connection timed out (ORA-3136)
Mon Jun 7 15:10:10 2010
Thread 1 advanced to log sequence 53804 (LGWR switch)
Current log# 1 seq# 53804 mem# 0: /d01/oracle/oradata/stlbas/redo01.log
Mon Jun 7 15:35:13 2010
Timed out trying to start process P643.
Mon Jun 7 15:37:32 2010
Timed out trying to start process PA55.
Mon Jun 7 15:39:00 2010
Timed out trying to start process PE56.
Mon Jun 7 15:40:13 2010
Timed out trying to start process PH77.
Mon Jun 7 15:44:49 2010
Error occured while spawning process PO17; error = 3135
Mon Jun 7 15:46:58 2010
Timed out trying to start process PO79.
Mon Jun 7 15:47:01 2010
Timed out trying to start process PO84.
Mon Jun 7 15:47:02 2010


We facing this error in our Database

-----------------------------------
Explanation of this problem:
-----------------------------------

This problem can occur on any platform, When you use Oracle Net services - Version 10.2 up.

Because; The Oracle Net 10G parameter
"SQLNET.INBOUND_CONNECT_TIMEOUT and
INBOUND_CONNECT_TIMEOUT_listenername default to 0 in 10.1.
To address Denial of Service (DOS) issues, the parameter were set to have a default of 60 (seconds) in 10.2

The following may be seen in the alert log:

WARNING: inbound connection timed out (ORA-3136)

SQLNET.INBOUND_CONNECT_TIMEOUT is set to a value in seconds and determines how long a client has to provide the necessary authentication information to a database.

INBOUND_CONNECT_TIMEOUT_listenername is set to a value in seconds and determines how long a client has to complete its connect request to the listener after the network connection has been established.

To protect both the listener and the database server, Oracle Corporation recommends setting INBOUND_CONNECT_TIMEOUT_listenername in combination with the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.

Cause:
======
Whenever default timeouts are assigned to a parameter, there may be cases where this default does not work well with a particular application. However, some type of timeout on the connection establishment is necessary to combat Denial of Service attacks on the database. In this case, SQLNET.INBOUND_CONNECT__TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername were given default values of 60 seconds in Oracle 10.2. It is these timeout values that can cause the errors described in this note.

Also note that it is possilbe the reason the database is slow to authenticate, may be due to an overloaded Oracle database or node.

Solution:
=======
Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite) or to an approprate value for the application yet still combat DOS attacks (120 for example).

These parameters are set on the SERVER side:
listener.ora: INBOUND_CONNECT_TIMEOUT_listenername
sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT



Another cause may be
---------------------------
Maximum processes exceed, so no new connection occurs......

First find out the parameter values by this query:-

sessions
processes
transactions

select * from v$resource_limit where RESOURCE_NAME in ('sessions','processes','transactions');


This is the formula, to calculate their values
------------------------------------------------
processes = a
sessions =a*1.1+5
transactions =sessions*1.1

example:-

processes=150
sessions=170
transactions= 187

Change the value in parameter file:-

SQL> alter system set processes=1000 scope=spfile
/

SQL>alter system set transactions=1215 scope=spfilE
/

note:- sessions value automatically change after shutdown the db.

SHUTDOWN THE DATABASE
AND
START THE DATABASE

No comments: