Sometime when the Source SQL statement perform very poorly and taking long time, you might experience that Oracle Database Session disappear when querying for v$session table. This usually happen in the Firewall environments.
When there is Firewall between the Informatica and Source Database, you need to make sure that database server is sending probe packets to the client in order for Firewall not to kill the connections due to idle time. Check the expire_time parameter in the SQLNet.ora file on the Database server. Oracle recommended value is 10 min. Default value is 0. When the value of expire_time parameter is set to 10 min, it means that Database server will send the probe packets every 1o min, and Firewall will not kill the client connections in case SQL queries are running very slow and taking hours just to prepare the SQL execution.
If even setting the expire_time in the SQLnet.ora file causing DB session to disappear, check the tcp_keeplive_time parameter on the client machine. Default value for this parameter is 2 hours (7200 seconds) on Linux and Solaris.
$ cat /proc/sys/net/ipv4/tcp_keepalive_time
Set this parameter to less than the firewall timeout value. If the firewall timeout value is 30 min, below statement set tcp_keepalive_time to 15 min on Linux. Use the ndd -set on Solaris to set the same parameter.
$echo 900 > /proc/sys/net/ipv4/tcp_keepalive_time
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment