Error message in Alert.log file frequently:
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
Time: 26-APR-2013 01:02:43
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=10.47.111.12)(PORT=49753))
WARNING: inbound connection timed out (ORA-3136)
Tns error struct:
Time: 26-APR-2013 01:02:43
ns main err code: 12535
Tracing not turned on.
Tns error struct:
TNS-12535: TNS:operation timed out
ns main err code: 12535
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=10.47.111.12)(PORT=49765))
WARNING: inbound connection timed out (ORA-3136)
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.47.111.12)(PORT=49776))
WARNING: inbound connection timed out (ORA-3136)
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
Time: 26-APR-2013 01:02:43
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=10.47.111.12)(PORT=49753))
WARNING: inbound connection timed out (ORA-3136)
Tns error struct:
Time: 26-APR-2013 01:02:43
ns main err code: 12535
Tracing not turned on.
Tns error struct:
TNS-12535: TNS:operation timed out
ns main err code: 12535
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=10.47.111.12)(PORT=49765))
WARNING: inbound connection timed out (ORA-3136)
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.47.111.12)(PORT=49776))
WARNING: inbound connection timed out (ORA-3136)
Solution One:
If you see the error message you have pasted. you will get the details from where the connection is coming from....
From your error message above:
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.47.111.12)(PORT=49753))
WARNING: inbound connection timed out (ORA-3136)
Usually it means that client connecting is taking long time to connect to the database server and hence the time out error.
Check with the application team, which server it is? Is it a application server or user connection coming to the database. Why it is taking too long to connect.
You can also take help from system admin to identify the user using the IP address.
Set the following parameter in sqlnet.ora and see if this helps you to overcome the issue.
To specify the time, in seconds, for a client to connect with the database server
and provide the necessary authentication information.
SQLNET.INBOUND_CONNECT_TIMEOUT=240
In 10gR2 and above, the default setting for both these parameters is 60 seconds.
From your error message above:
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.47.111.12)(PORT=49753))
WARNING: inbound connection timed out (ORA-3136)
Usually it means that client connecting is taking long time to connect to the database server and hence the time out error.
Check with the application team, which server it is? Is it a application server or user connection coming to the database. Why it is taking too long to connect.
You can also take help from system admin to identify the user using the IP address.
Set the following parameter in sqlnet.ora and see if this helps you to overcome the issue.
To specify the time, in seconds, for a client to connect with the database server
and provide the necessary authentication information.
SQLNET.INBOUND_CONNECT_TIMEOUT=240
In 10gR2 and above, the default setting for both these parameters is 60 seconds.
Solution Two:
- Search the corresponding text version of the listener log located on the database server for the corresponding client connection referenced by the Client address details referenced in the alert log message.
For the message incident below you would search the listener log for the 'Client address' string:
(ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092))
The search of the listener log should find the most recent connection before the time reference displayed in the alert log message, e.g. '22-JAN-2011 21:48:23'.
-Corresponding listener log entry:
22-JAN-2011 21:20:12 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMN11264.us.oracle.com)(CID=(PROGRAM=D:\app\mcassady\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=mcassady-lap)(USER=mca
ssady))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092)) * establish * AMN11264.us.oracle.com * 0
- Alert log entry:
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
Time: 22-JAN-2011 21:48:23
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092))
------------
Note the time of the client corresponding client connection(s) in the listener log. Here you may find a particular client, set of clients or particular applications that are improperly disconnecting causing the timeout errors to be raised and recorder in the database alert log.
You may choose to revert from the new Automatic Diagnostic Repository (ADR) method to prevent the Oracle Net diagnostic details from being written to the alert log(s) by setting the following Oracle Net configuration parameters:
To revert to Oracle Net Server tracing/logging, set following parameter in the server's sqlnet.ora :
DIAG_ADR_ENABLED = OFF
Also, to back out the ADR diag for the Listener component, set following parameter in the server's listener.ora:
DIAG_ADR_ENABLED_<listenername> = OFF
- Where the <listenername> would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file. For example, if the listener name is 'LISTENER', the parameter would read:
DIAG_ADR_ENABLED_LISTENER = OFF
-Reload or restart the TNS Listener for the parameter change to take effect.
For the message incident below you would search the listener log for the 'Client address' string:
(ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092))
The search of the listener log should find the most recent connection before the time reference displayed in the alert log message, e.g. '22-JAN-2011 21:48:23'.
-Corresponding listener log entry:
22-JAN-2011 21:20:12 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMN11264.us.oracle.com)(CID=(PROGRAM=D:\app\mcassady\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=mcassady-lap)(USER=mca
ssady))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092)) * establish * AMN11264.us.oracle.com * 0
- Alert log entry:
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
Time: 22-JAN-2011 21:48:23
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092))
------------
Note the time of the client corresponding client connection(s) in the listener log. Here you may find a particular client, set of clients or particular applications that are improperly disconnecting causing the timeout errors to be raised and recorder in the database alert log.
You may choose to revert from the new Automatic Diagnostic Repository (ADR) method to prevent the Oracle Net diagnostic details from being written to the alert log(s) by setting the following Oracle Net configuration parameters:
To revert to Oracle Net Server tracing/logging, set following parameter in the server's sqlnet.ora :
DIAG_ADR_ENABLED = OFF
Also, to back out the ADR diag for the Listener component, set following parameter in the server's listener.ora:
DIAG_ADR_ENABLED_<listenername> = OFF
- Where the <listenername> would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file. For example, if the listener name is 'LISTENER', the parameter would read:
DIAG_ADR_ENABLED_LISTENER = OFF
-Reload or restart the TNS Listener for the parameter change to take effect.
You didn't given any solution.
ReplyDelete