Idle Oracle connections give error 'ORA-03114: not connected to ORACLE'

Question

We have a Node.js web application that connects to an Oracle DB instance, The problem is after some inactivity, connections of the database is turned to read-only mode. It means SELECT operations work but INSERT and UPDATE transactions encounter this error:

"Error: ORA-03114: not connected to ORACLE"

This problem solves after restarting the application. We use the last version of knex(0.20.1) and node-oracledb(4.1.0) library to connect to the database.

Answer 1

The error means that something (probably a firewall) has expired a connection. You should track down the cause and eliminate it. There may be work arounds such as configuring the Oracle Net layer to send occasional pings across the network to stop idle connections from being terminated, see https://oracle.github.io/node-oracledb/doc/api.html#connectionha

Both queries and DMLs will be equally affected on the connection that gives the error - all will fail. I suspect you are using a different (new) connection for the query.

If you are using 19c client libraries (which, by the way, connect to Oracle DB 11.2 or later), then your connection string could use Easy Connect syntax like:

"mydbmachine.example.com/orclpdb1?expire_time=2"

This will perform a keep alive operation on idle connections, sending probes every two minutes. The general recommendation is to set the period to just less than half the time that connections will be terminated (e.g. by a firewall). See the tech article Oracle Database 19c Easy Connect Plus Configurable Database Connection Syntax.

Other syntaxes can be used in older versions, or in tnsnames.ora files; check the doc.