Oracle 18.104.22.168 EE on Solaris 10
I have an environment where a Pentaho instance is loading data into the Oracle database. The load action opens a database connection, reads some data and then takes a long time (60-90 minutes) to process the data and then attempts to write the data to the database using the same connection.
We are seeing a timeout error: "Io exception: Connection timed out" coming out of Pentaho after about 60 minutes.
I have the following settings in SQLNET.ORA:
The firewall settings have been verified to not include anything that would terminate a connection after 60 minutes.
I would like to learn more about the disconnection that seems to be happening from the database side.
What auditing option/command/setting can be sued to gather more detail about when the database senses a disconnected or idle process and hence terminates the connection?
Thanks very much!!
please check this documentation from oracle: http://docs.oracle.com/cd/B19306_01/network.102/b14213/sqlnet.htm
recommended value is 10
can you try it and update us if it works fine with you...
Thanks for the quick response.
I have set the parameter to 120 minutes due to the way the application needs to connect to the database. It holds the connection open on purpose and I need it to be 120 minutes to cover the gap in processing time.
I'm looking to find out more details about when and why the database connection is being dropped by the database if indeed it is being dropped from the database side at all.
I was hoping there was an database audit command but I haven't found it yet.
Thanks for your response!
As noted above the application holds the database connection open while it does some SELECTs and some processing and then tries to write the data back out to the database but by that time it appears the connection has been open too long and the application gets a 'Connection closed' error message.
I've raised the SQLNET.EXPIRE_TIME to 120 minutes and the error occurs before that much time has expired so I'm assuming that parameter is not the cause.
The profile for the user has the IDLE_TIME set to UNLIMITED so I ruled out that as well.
The network folks say there is no firewall parm set to cancel a connection.
I'm looking for the AUDIT command or whatever setting I can change in the database to increase the audit trail detail to try and identify who is breaking the connection and why.
Is there a database tracing parm I can set to see all the detail about the connections and especially when the database terminates one?
Thanks very much!