ORA-01017 May Cause ORA-3136 WARNING : Inbound Connection Timed Out in Alert Log

Goal
How to reproduce the error ORA-3136 WARNING : inbound connection timed out ?
This is the most common reason when you notice the warning message in the alert log.

Solution

The most common reason for warning message in alert log is when an user gets error at connect time, ORA-01017 invalid username /password , and on the same prompt, if no update is made for 1 minute(default) then the alert loggets WARNING: inbound connection timed out (ORA-3136)

Lets try to reproduce it.

Place the below parameter in the sqlnet.ora of the server (this is the default value of the parameter)

SQLNET.INBOUND_CONNECT_TIMEOUT=60

Make the tnsnames entry on the server with dedicated as server like

orcl10203=
 (DESCRIPTION=
  (ADDRESS=(HOST=testnet.idc.oracle.com)(PORT=1521)(PROTOCOL=TCP))
  (CONNECT_DATA=
   (SERVER=DEDICATED)
  (SERVICE_NAME=orcl10203)
  )
 )

Now open a sqlplus session and enter wrong username or password:

[oracle@testnet admin]$ sqlplus system/jkkdsf@orcl10203

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Mar 19 17:51:35 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Keep the sqlplus session open for 60 seconds and in the alert log after 60th second , you will notice ORA-3136

$tail -f alert_orcl10203.log
Thu Mar 19 17:52:36 2009
WARNING: inbound connection timed out (ORA-3136)

In the server sqlnet tracing you would see following :

[19-MAR-2009 17:51:35:897] nspsend: 33 4F 52 41 2D 30 31 30 |3ORA-010|
[19-MAR-2009 17:51:35:897] nspsend: 31 37 3A 20 69 6E 76 61 |17:.inva|
[19-MAR-2009 17:51:35:897] nspsend: 6C 69 64 20 75 73 65 72 |lid.user|
[19-MAR-2009 17:51:35:897] nspsend: 6E 61 6D 65 2F 70 61 73 |name/pas|
[19-MAR-2009 17:51:35:897] nspsend: 73 77 6F 72 64 3B 20 6C |sword;.l|
[19-MAR-2009 17:51:35:897] nspsend: 6F 67 6F 6E 20 64 65 6E |ogon.den|
[19-MAR-2009 17:51:35:897] nspsend: 69 65 64 0A |ied. |
[19-MAR-2009 17:51:35:897] nspsend: 156 bytes to transport
[19-MAR-2009 17:51:35:897] nspsend: normal exit
[19-MAR-2009 17:51:35:897] nsdofls: exit (0)
[19-MAR-2009 17:51:35:897] nsdo: nsctxrnk=0
[19-MAR-2009 17:51:35:897] nsdo: normal exit
[19-MAR-2009 17:51:35:897] nsdo: entry
[19-MAR-2009 17:51:35:897] nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3
[19-MAR-2009 17:51:35:897] nsdo: rank=64, nsctxrnk=0
[19-MAR-2009 17:51:35:897] nsdo: nsctx: state=8, flg=0x400c, mvd=0
[19-MAR-2009 17:51:35:897] nsdo: gtn=156, gtc=156, ptn=10, ptc=2011
[19-MAR-2009 17:51:35:897] nsdo: switching to application buffer
[19-MAR-2009 17:51:35:897] nsrdr: entry
[19-MAR-2009 17:51:35:897] nsrdr: recving a packet
[19-MAR-2009 17:51:35:897] nsprecv: entry
[19-MAR-2009 17:51:35:897] nsprecv: reading from transport...
[19-MAR-2009 17:51:35:897] nttrd: entry
o At this point the server expects information from the client
o Since for one minute no update came from client, the server got closed
[19-MAR-2009 17:52:36:865] ntt2err: entry
[19-MAR-2009 17:52:36:865] ntt2err: soc 1660 error - operation=5, ntresnt[0]=522, ntresnt[1]=4, ntresnt[2]=0
[19-MAR-2009 17:52:36:865] ntt2err: exit
[19-MAR-2009 17:52:36:865] ntt2err: entry
[19-MAR-2009 17:52:36:865] ntt2err: soc 1660 error - operation=5, ntresnt[0]=530, ntresnt[1]=38, ntresnt[2]=0
[19-MAR-2009 17:52:36:865] ntt2err: exit

So, when next time such a warning message WARNING: inbound connection timed out (ORA-3136) appears on the alert log, you must ensure by checking with clients that its not a case like above.

On my 11g RAC 11.2.0.3 (Node1) alert log showed the following:

***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
    TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
    Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.3.0 - Production
    Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.3.0 - Production
  Time: 20-FEB-2012 08:58:54
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
   
TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=171.56.20.101)(PORT=1668))
WARNING: inbound connection timed out (ORA-3136)

Ref: 793259.1