Using Continuous Query Notification (CQN)

[mihaimyh]

I am having hard times trying to make use of Continuous Query Notification (CQN) with a PDB inside an Oracle 19c database. Basically, I am not receiving any events. I've asked our DBA, and he pointed me in a direction to set up an ACL first, as this is the one that might block our app CQN registration.

I can not even make it work with Oracle 19c PDB running in docker and my app running on the same docker host.

Are there any other docs I can refer to? The ones I saw so far just explain about the grants the user should have and how to write the C# code to make use of the cqn notification, but nothing about firewalls, ACLs and other pre-requirements.

Thanks.

[alexkeh]

For ODP.NET CQN, the user should only need the CHANGE NOTIFICATION privilege to use CQN. To make it easier to verify whether CQN is working between Oracle DB and ODP.NET, I recommend enabling Client Initiated CQN (CICQN).

Traditional CQN relies on out of band messages to communicate changes from DB to client. That generally worked great in the on-premises world as the client and DB rarely had network restrictions between them. Of course, that has changed with cloud, increasing security concerns, and containers.

Traditional CQN relies on using a client port to get its notifications communicated. Thus, this client port needs to be open. Firewalls have to allow these messages to pass. The DB server needs to be allowed to send these messages out. The specific steps is different depending on each deployment's specific setup.

CICQN doesn't have this issue because it uses in band messages. ODP.NET maintains a constant connection back to the DB separate from the ones the app is using. This connection will remain connected as long as the app is running, even if the pool size goes to zero. This makes the configuration not dependent on specific network, container, and firewall setup other than letting regular DB connections through.

To use CICQN, use ODP.NET 21c or higher and a DB 19c RU released in the last couple of years. There was an initial problem with the DB implementation that was patched in one of the 19c RUs. In ODP.NET, set

OracleConfiguration.UseClientInitiatedCQN=true;

If you want to stay with traditional CQN, you can troubleshoot by first checking whether CQN registrations are being recorded by the DB and whether they are firing. Here are some tips to troubleshoot in that area.

[mihaimyh]

@alexkeh Thanks for the details, do you know if CICQN works with "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.7.0.0.0".

I've tested it with "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0" running in docker and I am still not getting notifications, although I can see the registration in dba_change_notification_regs view:

Here is my C# code:

using Oracle.ManagedDataAccess.Client;

using System.Data;

bool _isNotified = false;
string connectionString = "DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB1)));USER ID=DLM_RO; Password=pass123;";
string queryString = "select * from DLM.CQN_DEMO";
OracleConfiguration.UseClientInitiatedCQN = true;
//OracleConfiguration.DbNotificationPort = 18001;
//OracleConfiguration.DbNotificationAddress = "host.docker.internal";

DataSet ds = new();
OracleConnection con = new(connectionString);
try
{
    OracleCommand cmd = new(queryString, con);
    await con.OpenAsync();
    cmd.AddRowid = true;
    OracleDependency dep = new(cmd);
    cmd.Notification.IsNotifiedOnce = false;
    dep.OnChange += new OnChangeEventHandler(dep_OnChange);
    OracleDataAdapter da = new(cmd)
    {
        MissingSchemaAction = MissingSchemaAction.AddWithKey
    };
    da.Fill(ds, "CQN_DEMO");
}
catch (Exception e)
{
    await Console.Error.WriteLineAsync(e.Message);
    await con.CloseAsync();
}

while (_isNotified == false)
{
    await Task.Delay(TimeSpan.FromSeconds(100));
}

void dep_OnChange(object sender, OracleNotificationEventArgs eventArgs)
{
    Console.Out.WriteLine("Notification received");
    _isNotified = true;
}

I am using Oracle.ManagedDataAccess.Core 3.21.120

[alexkeh]

I don't remember which 19c RU CICQN was fully patched. 19.3 is definitely too early. 19.7 is one year later. To be safe, I would use 19.18 or later. Those patches would be all released in the last year.

The fix was made in Oracle DB 21c, which means it was patched sometime between when 19c came out and 21c came out.

[alexkeh]

You can also turn on ODP.NET tracing to see if ODP.NET is receiving messages back from the DB when a change occurs. If ODP.NET is receiving the messages, then it's a problem on the ODP.NET side. If ODP.NET is not receiving the messages, then the issue may be the DB or how the CQN event can be triggered.

OracleConfiguration.TraceFileLocation = @"C:\traces";
OracleConfiguration.TraceLevel = 7;

If you're not sure how to read the ODP.NET trace file, you can post it here or send it to us: dotnet_us (at) oracle.com.

[mihaimyh]

I've attached the trace.

WORKERSERVICEDEMO.EXE_PID_5556_DATE_2023_12_17_TIME_07_21_40_222190.zip

[alexkeh]

@mihaimyh From the trace, CICQN has been disabled.

2023-12-17 07:21:40.504592 TID:1 (PRI) (SVC) CAN use secure CQN for dependency:b183db3d-2a8f-412c-a247-cf16f61770ef = False

I inquired with my dev team. It appears ODP.NET CICQN connections remain disabled for all 19c DB in the code. I thought that we had enabled it for later DB RUs, but that was not correct.

You'll have to use traditional CQN. I would recommend working with Oracle Support on this as they can conduct a joint debugging session to get traditional CQN working. The biggest challenge is understanding your organization's network and security setup between client and server and configuring the out of band networking so that the DB messages to the ODP.NET can pass through.