USER_CHANGE_NOTIFICATION_REGS callback not return to node.js

[alpertandogan]

DB machine IP:192.168.160.1 I use Oracle Standard Edition Release 12.2.0.1 (final full patches) on linux OEL x64 7.5 final full update .

App machine IP:192.168.160.2 I use node.js v10.14.0 on linux x64 OEL 7.5 final update node oracledb 3.0.1 Oracle instclient 12.2 x64

node TESTRUN.js

const options = {
  callback : myCallback,
  sql: "SELECT NFIELD FROM NTABLE WHERE NFIELD < :bv",
  binds: { bv : 100 },
  timeout : 60, // Stop after 60 seconds
  // SUBSCR_QOS_QUERY: generate notifications when rows with k > 100 are changed
  // SUBSCR_QOS_ROWIDS: Return ROWIDs in the notification message
  qos : oracledb.SUBSCR_QOS_QUERY | oracledb.SUBSCR_QOS_ROWIDS
};
async function runTest() {
  let conn;
  try {
    conn = await oracledb.getConnection(dbConfig);
    await conn.subscribe('mysub', options);

---

When i start node TESTRUN.js in oracle i see record below in table USER_CHANGE_NOTIFICATION_REGS callback net8://(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.60.2)(PORT=38791))?PR=0

But when i make update on table NFIELD there is not return to node.js and record on USER_CHANGE_NOTIFICATION_REGS disappired.

[anthony-tuininga]

A few notes for you:

• you have timeout: 60 which implies that after 60 seconds the subscription will terminate normally
• you are using query notification with binds so updating NFIELD with a value greater than or equal to 100 will be ignored
• there are also network configurations that prevent notifications from being sent at all

[alpertandogan]

1. I increase timeout . Yes it waits but when i update or delete to table NFIELD oracle delete records in USER_CHANGE_NOTIFICATION_REGS and also create trc file in diag area ... JIT

2.. for NFIELD check where condition correct

3. Also I check network ACL for callback server from DB server using sql select utl_http.request('..:.') from dual return value. (But i m not sure JAva use this ACL or not)

---

2018-12-01T11:04:30.560609+03:00 SESSION ID:(628.25282) 2018-12-01T11:04:30.560633+03:00 CLIENT ID:() 2018-12-01T11:04:30.560639+03:00 SERVICE NAME:(SYS$BACKGROUND) 2018-12-01T11:04:30.560644+03:00 MODULE NAME:(MMON_SLAVE) 2018-12-01T11:04:30.560649+03:00 ACTION NAME:(JAVAVM JIT slave action) 2018-12-01T11:04:30.560654+03:00 CLIENT DRIVER:() 2018-12-01T11:04:30.560660+03:00 JIT starting 2018-12-01T11:04:30.560680+03:00 JIT running *** 2018-12-01T11:06:15.762991+03:00 JIT ending

[anthony-tuininga]

I presume you did a commit on the update you did in another session. No notification takes place until after the transaction is committed. Assuming that is the case, however, I'll have to ask internally about how to debug the reason for why you aren't getting notifications and get back to you.

[alpertandogan]

With other terminal session sqlplus connect to database insert new row to NFIELD and COMMIT while node.js working on APP server (...160.2) and waiting subscribed CQN.

(This meens Notification appired from Oracle DB to Node js with all opened connections in Node js and subscribed and wait to CQN )

With this transacations record in USER_CHANGE_NOTIFICATION_REGS deleted from internal background jobs from Oracle DB. I thing with this internal background operation comes from Oracle using JAVA and try to connect (APP server )using callback value in USER_CHANGE_NOTIFICATION_REGS .. But this internal procedure or java crash and system generate trc file in diag area of DB (...160.1) server.

I check there is no firewall all ACL problem (only PLSQL ?Java? ) with in DB server to APP server connections.

[cjbj]

Did you find what the DB crash is related to?

Why are you mentioning Java - what is its relationship to node-oracledb?

[alpertandogan]

Hi

I try to install new db and nodejs on same machine try to simulate working CQN.. But my real platfrom separate DB and App servers (nodejs , Apache 24 behind Treafik balancer) .

Java ? Because backend on Oracle when try to connect to "callback from Oracle" use Java in ORACLE Trace file when Crash it i see (JIT starting JIT running JIT ending )
I thing this problem comes from Java call inside Oracle for callback to Nodejs (url:port) for security reason (ACL i try but Java dont use ACL i thing)

[alpertandogan]

I install Oracle 18.3 on OEL 7.6 nodejs (final) oracledb (final) try to simulate your example . It works well with in the same machine.

So my problem is when Oracle DB and App server is diffrent (with in the same switch no firewall or NAT isues) Oracle DB inside (No acl problem ) didnot callback node App server. Oracle DB crash within JIT start JIT run JIT end trace logs.

I thing there must be setup in ORACLE DB for JAVA internal for security like ACL

[cjbj]

If you are seeing a DB crash, you should work through 'official' Oracle Support channels to get an explanation.

The network setup required for the CQN subscription to create a connection from the DB back to Node.js is a more specialist topic, and probably also best worked through with Support or in a DB forum where DBAs hang out.

[alpertandogan]

I use second (oracle db server and node app in same server) link to master database server solve this problem but its not a good way. I continue search solution for correct way.

links are mention about the same problem of ORACLE DB..

http://www.ahsanscorner.com/content/2015-09-18-1148-debugging-change-query-notificationdatabase-change-notification-feature-adf-shared-am-vos-autorefresh-property

https://stackoverflow.com/questions/36610463/cx-oracle-cqn-callback-usage-across-a-router

[cjbj]

After checking with our CQN development team, I still recommend you contact Oracle Support who can give you various commands to trace what might be happening.

[cjbj]

Closing - no activity.