ORA-1017,ORA-3136

ORA-1017 May Cause ORA-3136 WARNING : Inbound Connection Timed Out in Alert Log [ID 793259.1]

Applies to:

Oracle Net Services - Version 10.1.0.2.0 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 30-SEP-2011.

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.
For other reasons you need to check :
Note 465043.1 - Troubleshooting ORA - 3136 WARNING Inbound Connection Timed Out

Fix

The reason for the ORA-3136 error being thrown in the alert log is when a client fails to complete the authentication process in the time allowed by INBOUND_CONNECT_TIMEOUT.  Very often, the 3136 error is actually the result of an ORA-01017 or "invalid username /password".  When this error is returned to a client and, while at the same prompt, if incorrect credentials are supplied again OR no other attempt is made in under the 1 minute threshold (default), the instance will throw the message in the alert.log:   WARNING: inbound connection timed out (ORA-3136)

Lets try to reproduce it.

Place the following sqlnet.ora parameter on the server.  This would be the sqlnet.ora file that is referenced by the listener.  Again, 60 seconds is the default setting.

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)
  )
 )

Note: Ensure that tnsping orcl10203 works well and the same value set in tnsnames.ora is reflected in the output.

Now open a SQL*Plus 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 SQL*Plus 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

If this error persists or is frequent, it may be necessary to enable event tracing in order to capture more information.

SQL>alter system set events '3136 trace name errorstack level 3';

Once this is set, the next occurrence of the ORA-3136 should result in some trace data being dumped to USER_DUMP_DEST or BACKGROUND_DUMP_DEST.
The actual location of these directories can be found by issuing:
SQL>show parameter dump_dest;

Here's an example of the trace data for an ORA-3136 event that was thrown because of an ORA-1017:
d:\app\hostname\diag\rdbms\instance_name\instance_name\trace\orcl_ora_4604.trc
This is excerpted:

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-03136: inbound connection timed out

Further down in the event trace we see the ORA-01017:

DF2D500 00000000 00000000 00000000 00000000  [................]
DF2D510 00000000 074B3FBA 0DF2D69D 2D41524F  [.....?K.....ORA-]
DF2D520 31303130 69203A37 00000000 0CE5DCBC  [01017: i........]
DF2D530 00000000 2F656D61 00000003 00000000  [....ame/........]

We can also see the originating client address:

13105470 FFFFFFFF FFFFFFFF FFFFFFFF 754164FF  [.............dAu]
13105480 6E656874 61636974 20646574 203A7962  [thenticated by: ]
13105490 41544144 45534142 6C43203B 746E6569  [DATABASE; Client]
131054A0 64646120 73736572 4128203A 45524444  [ address: (ADDRE]
131054B0 283D5353 544F5250 4C4F434F 7063743D  [SS=(PROTOCOL=tcp]
131054C0 4F482829 313D5453 35312E30 37312E39  [)(HOST=10.100.200]
131054D0 32322E36 50282937 3D54524F 34333734  [.300)(PORT=4734]
131054E0 14FF2929 6E616D6A 73726564 5C73752D  [))..JOHN_DOE\]

 Turn event tracing off using:

SQL>alter system set events '3136 trace name errorstack level 0';

 See the following additional documents on this error and troubleshooting INBOUND CONNECT TIMEOUT issues:
Note 465043.1   Troubleshooting ORA-3136: WARNING Inbound Connection Timed Out
Note  3136.1   Connections that Used to Work in Oracle 10.1 Now Intermittently Fail with ORA-3113 or ORA-3136 from 10.2 Onwards