search

laurenz / oracle_fdw

PostgreSQL Foreign Data Wrapper for Oracle
http://laurenz.github.io/oracle_fdw/
Other
496 stars 156 forks source link

ORA-03135: connection lost contact #693

Closed haleys-m closed 3 months ago

haleys-m commented 3 months ago

Hello Laurenz!

We are having issues with FDW. We have a process that extracts reports from an Oracle database that takes approximately 8 hours and in the early hours of the morning, the following error occurs:

ERROR: error committing transaction: OCITransCommit failed DETAIL: ORA-03135: connection lost contact Process ID: 15707 Session ID: 643 Serial number: 65391 CONTEXT: PL/pgSQL function update_daily() line 16 at COMMIT

We did a ping test between the networks and did not identify any problems. We set keep alive. We changed the SQLNet parameters too.

But the issue continues to occur. Could you please help us understand what could be causing this issue? We've already spoken to a network analyst as well.

Thanks

laurenz commented 3 months ago

Well, the error message is pretty clear: "connection lost contact" This is a problem between Oracle client and the network, and it is probably the fault of the network. There is not much I can do for you here. If there is a longer period with no network activity while the process is running, it may well be that an ill-configured network component drops the connection. Keepalive should prevent that, no idea why it doesn't. Pester your network analyst. The error is clearly from he network (unless the Oracle server hangs up; your Oracle DBA should be able to help you there).