If I were you, I would install a native Oracle client on the Windows machine and use this Oracle client to connect to the DBMS, not the Oracle Wire driver. In general I wouldn't use an ODBC driver for Oracle, DB2, or Sybase.
The driver may report this kind of errors depending on the timeout setting and the usability of the connection. The "connection dead" error can appear by itself if the driver attempts to communicate with the server and discovers that the socket has been closed. If the driver determines that the connection is no longer valid, then it will report that the network operation has timed out and also that the connection is dead.
The driver determines whether a connection is valid by doing the following:
The driver sends an interrupt to the server to determine whether the connection is valid. The interrupt is used to cancel the current request.
If any of the following are true, then the connection is considered valid and usable:
- The server replies with a cancel acknowledgment
- The server replies with a valid response to the original request.
- If the server has not replied, a simple database operation is performed. The operation is to open and close a cursor.
Otherwise, the connection is no longer usable and the socket is closed.
I found the same information in the below KB.
But in the resolution they mentioned about following:
- Query Timeout to -1
- Failover Mode
- AlternateServers: To same connection if we are not using any alternate server
- Failover Mode to 2
I looked for those settings in Informatica cloud connection properties of “OracleProdConnection” but those won’t exist over there. Since I am .Net side and novice on “Informatica” side, do you know how can we change the above settings? If yes can you provide me the steps to do so.