Issue: While running ETL, one of the task fails with below error :-
READER_1_1_1> RR_4049 SQL Query issued to database : (Mon Oct 01 17:17:40 2012)
READER_1_1_1> RR_4050 First row returned from database to reader : (Mon Oct 01 19:49:46 2012)
WRITER_1_*_1> WRT_8167 Start loading table [W_ORA_PARTY_PER_TMP] at: Mon Oct 01 19:49:45 2012
WRITER_1_*_1> CMN_1761 Timestamp Event: [Mon Oct 01 19:49:46 2012]
WRITER_1_*_1> WRT_8229 Database errors occurred:
ORA-03114: not connected to ORACLE
Process ID: 29105
Session ID: 2047 Serial number: 3003
Analysis: After analysis there was no issue found with database or listener.
In this particular scenario the issue that seems to be happening was:-
a) A task in ETL is started and creates 2 sessions. One for our source system EBS and another for target system DWH.
b) SELECT is issued on source system.
c) DWH session truncates the TEMP table and then waits for SELECT query to respond with data.
d) SELECT being a complex query takes more than 2 hrs and when it fetches data and passes it to dwh, the DWH session is dropped.
The issue was with firewall settings. The tcp timeout limit was set to 1 hr and after one hr any ideal session was getting terminated by firewall rules.
Solution:Increase the tcp timeout setting in firewall and then try restarting the task in ETL.