Pages

Wednesday, September 6, 2017

Troubleshoot operation timed out errors.

Below is based upon excellent comment from Ask Tom
Database connection closed on port

========================
General
========================
Application recieve time out errors.
When checking Oracle alert.log, there are following messages
Step 1. - check alert.log

less alert.log

***********************************************************************
Archived Log entry 160083 added for thread 1 sequence 160094 ID 0x36162634 dest 1:
Wed Sep 06 05:12:16 2017
***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 11.1.0.7.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
  Time: 06-SEP-2017 05:12:16
  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: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=111.222.555.200)(PORT=43680))
Wed Sep 06 05:32:39 2017
Thread 1 advanced to log sequence 160096 (LGWR switch)

Step 2. - get listener log details
Search for server 111.222.555.200 in listener logs
 
oracle@my_server:/software/oracle/diag/rdbms/igt/igt/trace>% lsnrctl status LSNR_IGT

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 06-SEP-2017 06:34:43

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora_server)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     lsnr_igt
Version                   TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date                18-JUL-2016 10:52:34
Uptime                    414 days 19 hr. 42 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /software/oracle/111/network/admin/listener.ora
Listener Log File         /software/oracle/diag/tnslsnr/my_server/lsnr_igt/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=100.000.555.125)(PORT=1521)))
Services Summary...
Service "igt" has 1 instance(s).
  Instance "igt", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


 
Step 3. - in listener log, look to  IP 10.128.156.200:
less /software/oracle/diag/tnslsnr/my_server/lsnr_igt/alert/log.xml


<msg time='2017-09-06T04:16:39.191+00:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='my_server'
 host_addr='10.0.156.126'>
 <txt>06-SEP-2017 04:16:39 * (CONNECT_DATA=(SID=igt)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=iu))) * (ADDRESS=(PROTOCOL=tcp)(HOST=111.222.555.200)(PORT=45413)) * establish * igt * 0
 </txt>
</msg>

========================
Additional info, per "Ask Tom" post:
========================
Basically it means that there is a network issue between client and the server. 
It could be client itself, or a firewall timeout, or a timeout on some network hardware between the two devices. 

These time out related messages are mostly informational in nature. 
The messages indicate the specified client connection (identified by the 'Client address:' details) has experienced a time out.  
The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limits after a client has abnormally terminated the database connection.

The 'nt secondary err code' translates to underlying network transport timeouts for the following Operating Systems:

For the Solaris system: nt secondary err code: 145:
ETIMEDOUT 145 /* Connection timed out */

For the Linux operating system: nt secondary err code: 110
ETIMEDOUT 110 Connection timed out

For the HP-UX system: nt secondary err code: 238:
ETIMEDOUT 238 /* Connection timed out */

For AIX: nt secondary err code: 78:

No comments:

Post a Comment