Can't set port for AQ Queue listener in Oracle.ManagedDataAccess.Core v23.4.0

[n-yates]

I'm using Oracle.ManagedDataAccess.Core v23.4.0 to process an AQ Queue in an Oracle 19c database. According to the Data Provider for .NET Developer's Guide, the port can be configured centrally. If it isn't specified then a valid and random port is used. I believe the static OracleConfiguration class's DbNotificationAddress and DbNotificationPort properties are used to configure this centrally. This doesn't work. When I set these fields an exception is thrown containing "ORA-50050: The notification listener is already started." This exception is thrown when an OracleAQMessageAvailableEventHandler is added to the OracleAQQueue.MessageAvailable event. When I don't set these fields then a random port is selected.

I debugged the code and found that there is an internal static property for the port that is set to -1. This property is set to the random port after the listener was started.

I want to be able to configure the database address and port for AQ Queue notifications so that it is known and we can set networking rules based on this known port. The random port will not do. Based on the available documentation and that v23.4.0 is fairly new, this seems like a bug.

Setting the DbNotificationPort based on configuration

if (!int.TryParse(configuration[$"{QueueOptions.SectionName}:{nameof(QueueOptions.DbNotificationPort)}"], out int dbNotificationPort))
{
    return serviceCollection;
}

OracleConfiguration.DbNotificationPort = dbNotificationPort;

Code to create the OracleAQQueue instance, add an OracleAQMessageAvailableEventHandler then listen.

...
var queue = _oracleFactory.CreateAqQueue(_options.CurrentValue.ConnectionString!, _options.CurrentValue.AqQueueName!);

try
{
  queue.Connection.Open();

  // Register the Queue listener
  queue.MessageAvailable += new OracleAQMessageAvailableEventHandler(MessageReceived);

  queue.Listen(null);
...

Stack Trace

System.TypeInitializationException
  HResult=0x80131534
  Message=The type initializer for 'Oracle.ManagedDataAccess.Client.OracleDependency' threw an exception.
  Source=Oracle.ManagedDataAccess
  StackTrace:
   at Oracle.ManagedDataAccess.Client.OracleDependency.GetNextClientRegistrationId() in Oracle.ManagedDataAccess.Client\OracleDependency.cs:line 697
   at OracleInternal.ServiceObjects.OracleNotificationManager.SendAQRegistrationInfo(OracleConnectionImpl connectionImpl, OracleDependencyImpl orclDependencyImpl, String[] subscriptionName, String listenerClientId, OracleNotificationRequest ntfnReq) in OracleInternal.ServiceObjects\OracleNotificationManager.cs:line 591
   at OracleInternal.ServiceObjects.OracleNotificationManager.RegisterForAQNotification(OracleConnectionImpl connectionImpl, OracleDependencyImpl orclDependencyImpl, String queueName, String[] notificationConsumers, OracleNotificationRequest ntfnReq, OracleAQQueue oracleAQQueue) in OracleInternal.ServiceObjects\OracleNotificationManager.cs:line 445
   at Oracle.ManagedDataAccess.Client.OracleAQQueue.SubscriptionRegister() in Oracle.ManagedDataAccess.Client\OracleAQQueue.cs:line 992
   at Oracle.ManagedDataAccess.Client.OracleAQQueue.add_MessageAvailable(OracleAQMessageAvailableEventHandler value) in Oracle.ManagedDataAccess.Client\OracleAQQueue.cs:line 270
   at Queue.WorkerService.AqQueue.QueueProcessor.<ExecuteAsync>d__7.MoveNext() in C:\repos\Queue.WorkerService\Queue.WorkerService\AqQueue\QueueProcessor.cs:line 43

  This exception was originally thrown at this call stack:
    Oracle.ManagedDataAccess.Client.OracleDependency.Port.set(int) in OracleDependency.cs
    Oracle.ManagedDataAccess.Client.OracleDependency.OracleDependency() in OracleDependency.cs

Inner Exception 1:
InvalidOperationException: ORA-50050: The notification listener is already started

https://docs.oracle.com/en/database/oracle/oracle-database/23/odpnt/AQQueueMessageAvailableEvent.html#GUID-37AD57F2-4121-4C63-877A-0C06B7AA79D1

https://docs.oracle.com/en/database/oracle/oracle-database/23/odpnt/InstallCoreConfiguration.html#GUID-24C963AE-F20B-44B5-800C-594CA06BD24B

[alexkeh]

Do you set the OracleConfiguration DbNotificationPort prior to opening your first connection? Typically, this error occurs after the first connection has already been opened and the port number is attempted to be set.

[n-yates]

Yes, I'm pulling the configuration then setting the OracleConfiguration.DbNotificationPort in an IServiceCollection extension method. I then have a BackgroundService which contains the code above that creates the OracleAQQueue instance, opens the connection, and adds the event handler.

public static IServiceCollection AddAqSubscriber(this IServiceCollection serviceCollection, IConfiguration configuration)
{
    ...
    // Configure DB Notifications
    if (!int.TryParse(configuration[$"{QueueOptions.SectionName}:{nameof(QueueOptions.DbNotificationPort)}"], out int dbNotificationPort))
    {
        return serviceCollection;
    }

    OracleConfiguration.DbNotificationPort = dbNotificationPort;

    return serviceCollection;
}

Program.cs

public class Program
{
    protected Program() { }

    public static void Main(string[] args)
    {
        CreateHostBuilder(args).Build().Run();
    }

    public static IHostBuilder CreateHostBuilder(string[] args)
    {
        return Host.CreateDefaultBuilder(args)
            .ConfigureAppConfiguration((hostBuilderContext, builder) =>
            {
                ...
            })
            .ConfigureLogging((hostBuilderContext, loggingBuilder) =>
            {
                ...
            })
            .ConfigureServices((hostBuilderContext, services) =>
            {
                services.AddAqSubscriber(hostBuilderContext.Configuration);
            });
    }
}

[alexkeh]

Can you generate an ODP.NET trace and share it to see why the listener is started prior to opening the first connection? Here's how to generate an ODP.NET trace file. You can choose your own directory for the trace file location.

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

Was this working before with an earlier ODP.NET Core version or 23.4.0 is the first time you are trying to use ODP.NET AQ in this way?

[n-yates]

This is a new application. I had used an older version for a proof of concept, but that only ran on my local machine. We didn't discover an issue until we deployed it then investigated when the app wasn't receiving notifications. I'll provide a trace shortly. Based on my debugging I think the RegisterForChangeNotification method in OracleNotificationManager should reference OracleDependency.Port instead of OracleDependencyImpl.m_portForListening.

[alexkeh]

I filed bug 36736236 to track this issue.

[alexkeh]

The dev team has a fix for the issue. It's very unlikely the fix will be part of 23.5 as that patch has been frozen. The fix will be scheduled for 23.6 then.

[n-yates]

Excellent. Based on the version history on nuget.org I suspect that is a couple months out. Does that seem reasonable?

[alexkeh]

23.5 is planned to be released by July. 23.6 would be 3 months after 23.5, around October.

There's a possibility the ODP.NET team will release an interim patch between 23.5 and 23.6 on NuGet. So, the fix may come sooner than October, but no sooner than July.

If you need a fix delivered before October, you can open an Oracle Support service request and request a one-off fix.

This bug has the unfortunate timing of being resolved right after the next quarterly patch finished accepting new fixes.

[n-yates]

Will this fix also allow us to configure the DbNotificationAddress property? I assume so, but want to confirm. Thanks.

[alexkeh]

I think you mean setting the port via DbNotificationPort. Yes, you will be able to set the port after the fix.