ORA-17002 IP hostname timeout DISTRIBUTED_LOCK_TIMEOUT

Intermittent failures to connect JDBC/thin to a RAC database with error:

IOException: The network adapter could not establish the connection. ( ORA-17002)

Connection string is

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=<IP ADDRESS NODE 1>)(PORT=<PORT>))(ADDRESS=(PROTO =TCP)(HOST=<IP ADDRESS NODE 2>)(PORT=<PORT>)))(CONNECT_DATA=(SERVICE_NAME=<SERVICE NAME>)))

Connection to individual nodes always works:

jdbc:oracle:thin:@<IP ADDRESS NODE 1>:<PORT>:<SID1>
Or this:
jdbc:oracle:thin:@<IP ADDRESS NODE 2>:<PORT>:<SID2>
 

CHANGES

CAUSE

When switching to JDBC/OCI it was found that the following occurs:

Intermittent  TNS-12545 "Connect failed because host or object does not exist"

Even though the client's connection is specified using numeric IP addresses the client connection involves an exchange of information between the RAC nodes,  This exchange requires 100% reliable hostname to IP resolution. 

If the DNS is not 100% reliable then this can be done by ensuring the hosts files contain the name resolution information.

This issue is intermittent and may not reproduce when performing trivial connections with SQL*Plus probably due to low usage.  The issue will more likely happen with a production JDBC/thin application in a period of high usage.

 

SOLUTION

To implement the solution, please execute the following steps:

Modify /etc/hosts file to include hostname resolution.

<IP ADDRESS NODE 1> <HOSTNAME NODE 1>
<IP ADDRESS NODE 2> <HOSTNAME NODE 2>

 For details, please refer to the following document:

Document 220970.1 RAC: Frequently Asked Questions
What should I do to make my Oracle RAC deployment highly available?
[...] Oracle recommends that the hostname, vip, and interconnect are defined in the /etc/hosts file on all nodes in the cluster.

CAUSE

Low value for timeout properties.
 
In the databases sqlnet.ora files, the following is set:
SQLNET.INBOUND_CONNECT_TIMEOUT = 3

SQLNET.INBOUND_CONNECT_TIMEOUT parameter specifies the time, in seconds, for a client to connect with the database server and provide the necessary authentication information. If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection.
Reference:
Database Net Services Reference
SQLNET.INBOUND_CONNECT_TIMEOUT


According to the error:

SQLState : 08006
 Message : IO Error: send failed
 Connection reset by peer
 Error Code : ORA-17002

JDBC is responding to a net break exception i.e. sendReset() and the database connection cut off (Connection reset by peer). Since such a low value of 3 is set for SQLNET.INBOUND_CONNECT_TIMEOUT, there is not enough time for the connection authentication before the database terminates the connection.----3s 不够 原因呢？
 

SOLUTION

1. Edit the sqlnet.ora file for the target database.
2. Increase the SQLNET.INBOUND_CONNECT_TIMEOUT from 3 to 120:

SQLNET.INBOUND_CONNECT_TIMEOUT=120

SYMPTOMS

Upon startup of Service Object Architecture (SOA) Managed Server, the following error is seen:
 

Resource manager is unavailable. ORA-17002 SQLErr (0): OracleError = 17002;

CHANGES

CAUSE

The Database has an F5 Load Balancer in front of it with a default 300 second timeout.  The Load Balancer in front of the database is closing the connection

F5硬件负载均衡器
 

SOLUTION

It is necessary to adjust the Load Balancer timeout so the following is observed.

Database Distributed lock timeout > F5 timeout > XA timeout > JTA timeout.

Then the connection will be managed by Weblogic in manner that is predictable.

Please review Transaction Timeout Management in OIM (Doc ID 2045167.1)

分布式超时

Distributed transactions

Distributed transactions are composed of individual transactions that must be managed together. This functionality is built upon the open XA standard for distributed transactions (XA is part of the X/Open standard and is not specific to Java.)
An XA transaction involves a coordinating transaction manager, with one or more databases (or other resources, like JMS) all involved in a single global transaction.
Non-XA transactions have no transaction coordinator, and a single resource is doing all its transaction work itself (this is sometimes called local transactions).
The Transaction Manager coordinates all of this through a protocol called Two Phase Commit (2PC). This protocol also has to be supported by the individual resources.
In terms of datasources, an XA datasource is a data source that can participate in an XA global transaction. A non-XA datasource generally can't participate in a global transaction.

Timeouts

Three different types of timeouts are involved in an OIM deployment. 

1. JTA Timeout (Java Transaction API)
   
   WebLogic Server defines a global transaction timeout that is used for all XA transactions in the domain.
   This is controled by 3 parameters available in WLS console, at the domain level (Configuration/JTA tab, basic + advanced sections) :
   "Completion Timeout" expressed in seconds. Default value 0, which sets the value to approximately twice the default "Timeout Seconds" value with a maximum value of 120 seconds. This value provides backward compatibility for prior releases without this setting. If the specified value is -1, the maximum value supported by this attribute is used. If the specified value exceeds the value set for abandon-timeout-seconds, the value of abandon-timeout-seconds is used.
   "Timeout Seconds" Default Value 30. Specifies the maximum amount of time, in seconds, an active transaction is allowed to be in the first phase of a two-phase commit transaction. If the specified amount of time expires, the transaction is automatically rolled back.
   "Abandon Timeout Seconds" Default Value 86400 (24h)
   
   For OIM, "Completion Timeout" is set to 0. This sets the JTA timeout to 60 seconds.
   This parameter may need to be increased in overloaded environment.
   
    
2. XA Timeout
   
   XA timeout can be altered at the datasource level. To achieve this, in WLS console, at the JDBC datasource level (Configuration/Transaction tab) :
   1) set "Set XA Transaction Timeout"
   2) set "XA Transaction Timeout" (in seconds). If set to zero, the XA Timeout for this datasource will be set to the global transaction timeout (JTA timeout)
    

       3. DISTRIBUTED_LOCK_TIMEOUT

           This Database parameter specifies the amount of time (in seconds) for distributed transactions to wait for locked resources.

           It is accessible with :         

select value from v$parameter where upper(name) = 'DISTRIBUTED_LOCK_TIMEOUT'

       It can be changed as such, depending if pfile or spfile is used :  

pfile:
add/change distributed_lock_timeout parameter in init.ora with proper value

spfile:
alter system set distributed_lock_timeout=&value scope=spfile;

  
           In both cases, restart the instance after change.
 

Order of the 3 timeouts

The following relation should be maintained :

DISTRIBUTED_LOCK_TIMEOUT > XA Transaction Timeout > WebLogic Server JTA timeout

This ensures that transactions will not be aborted by the database, while still being considered valid at the JDBC driver or Transaction Manager.

Restart the DB, and the techstack

NOTE: Also check under JDBC Connection Pool and add to the property box:

ORACLE.NET.READ_TIMEOUT=300000
ORACLE.JDBC.ReadTimeout=300000

<connection-reserve-timeout-seconds>10</connection-reserve-timeout-seconds>
<inactive-connection-timeout-seconds>0</inactive-connection-timeout-seconds>
<statement-timeout>-1</statement-timeout>