0

I am using devart dotConnect for Oracle with Oracle 12c database. My application has an OracleDependency object instanced to read notifications when I got a table changed.

It is working fine for Oracle 12C. Recently, I have created a new instance using Oracle 18c. I realized that the notifications were delaying to arrive in my application, sometimes it was getting missed.

I tried to dig deep and find the issue. I found out that the table USER_CHANGE_NOTIFICATION_REGS

SELECT * from USER_CHANGE_NOTIFICATION_REGS

was showing old notifications registered. It seems that it was not cleared when I have stopped and started my web application from IIS.

I have tried to clean manually those notifications using DBMS_CHANGE_NOTIFICATION.DEREGISTER, but it doesn't work because was created using a different session. My application only starts to work again after a few hours when that list clean by itself.

I wonder if there are some new parameters/settings in Oracle 18c to define some kind of timeout. Should I change something in my application to clear the notifications registered before stopping it?

Those records in the image below are old. I suspect is delaying to send new notifications because it is trying to call the callback function (IP:Port) from that old notifications registered. When that list gets cleared (I have no idea how it clear by itself), then the application works again.

Image:

https://i.stack.imgur.com/kAlNl.png

Abra
  • 19,142
  • 7
  • 29
  • 41
Leonardo Willrich
  • 3
  • 2

2 Answers2

0

Try decreasing your value of command.Notification.Timeout.

If this doesn't help, please do:

1) send us a small complete test project for reproducing with the corresponding DDL/DML script

2) tell us exact versions of your Oracle Server 18c and Oracle Client

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Unfortunately, I have to keep the timeout = 0 to keep the dependency alive for the whole life cycle of my application. In my test application, I have set 120 seconds and it was cleaning after 2 minutes. But, how I said before, it is not suitable for my application. Fortunately, I found the problem, it is related to the windows firewall. Just to let you know, I am using Oracle at AWS using RDS service, version 18.7.0.0. Oracle Client version 18.5.0.0 64 bits. – Leonardo Willrich Oct 29 '19 at 20:38
0

Resolved! The problem was the Windows Firewall. I did a test using Oracle 12c and I got the same issue. Then, I just turn off the windows firewall and the change notification created previously was being deleted from DBA_Change_Notification_Regs table.

Probably another application/service is responsible to do it and was being blocked by the firewall. I wonder which application or which port number it uses to do it.

Leonardo Willrich
  • 3
  • 2