CODEWITHSUNDEEP
node.jsoracle-databaseknex.jsnode-oracledbora-03114
Amir
Amir

Reputation: 351

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

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.

Upvotes: 8

Views: 14151

Answers (2)

silent wolf
silent wolf

Reputation: 1

I have faced same problem and found the solution. Add environment variables (system variables) ORACLE_HOME=installation path in my case F:\app\krushna\product\11.2.0\dbhome_1 and ORACLE_SID= orcl or xe . Which ever you have. It worked for me.

Upvotes: 0

Christopher Jones
Christopher Jones

Reputation: 10721

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.

Upvotes: 6

Related Questions