General
============================
Why error ORA-3136 is coming in Oracle alert.log?
============================
Reference
============================
Per Oracle Technote #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...:
sqlplus session was kept open for 2 minutes
After 2 minutes, there is ORA-3136 error in alert.log
No comments:
Post a Comment