Pages

Monday, May 20, 2019

Fatal NI connect error 12170. Inbound connection timed out (ORA-3136)

============================
General
============================
Why error ORA-3136 is coming in Oracle alert.log?

============================
Reference
============================
Per Oracle Technote #793259.1

ORA-1017 May Cause ORA-3136 WARNING : Inbound Connection Timed Out in Alert Log (Doc ID 793259.1)
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)

To reproduce it:
inside sqlnet.ora parameter on the server add below parameter
SQLNET.INBOUND_CONNECT_TIMEOUT=60

Now open a SQL*Plus session and enter wrong username or password:

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


============================
Example
============================

Edit sqlnet.ora, to override the 1 minute default:
SQLNET.INBOUND_CONNECT_TIMEOUT=120

my_user@my_server:~>% date
Mon May 20 08:01:49 GMT 2019
iu@my_server:~>% sqlplus system/xxx@orainst

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 20 08:01:54 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

Enter user-name:

<keep the sqlplus session open>
After 2 minutes, check inside alert.log


/software/oracle/diag/rdbms/orainst/orainst/trace>% less alert_orainst.log
Mon May 20 08:03:54 2019

********************************************************************
Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.4.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 20-MAY-2019 08:03: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=4444.3333.222.111)(PORT=40131))
WARNING: inbound connection timed out (ORA-3136)



Inside lsnr_orainst.log
20-MAY-2019 08:01:54 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orainst)(CID=(PROGRAM=sqlplus)(HOST=my_server)(USER=iu))) * (ADDRESS=(PROTOCOL=tcp)(HOST=444.333.222.1111)(PORT=40131)) * establish * orainst * 0

============================
Conclusion
============================
Exactly as per Oracle technote...:

Connection was made at 08:01:54 with wrong credentials
sqlplus session was kept open for 2 minutes
After 2 minutes, there is ORA-3136 error in alert.log

No comments:

Post a Comment