7

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.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
Amir
  • 341
  • 1
  • 5
  • 16
  • 1
    I see you've added a bounty. If you want a better answer than the one I've given, then you should consider adding much more detail about how you are getting, using and releasing connections. And report back on how the Oracle Net options I linked to have affected the behavior. – Christopher Jones Jan 02 '20 at 08:00
  • Thanks @ChristopherJones , we are testing it. – feridok Jan 04 '20 at 18:51

2 Answers2

5

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.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
0

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.

silent wolf
  • 1