none
OracleDB Adapter BizTalk 2010 - Notifications - ORA-29970: Specified registration id does not exist RRS feed

  • Question

  • I am trying to get Oracle notifications working with BizTalk 2010. When I start the receive port I get the following error:

        "ORA-29970: Specified registration id does not exist"

    Now I don't have experience with using Oracle so I'm floundering somewhat. It feels like a security issue but could be an installation problem. I'm hoping I've missed something simple :-). I've searched the forum and can't find any mention of error code 29970.

    Everything is set up on a single machine.
    * Oracle Database version 11g R2: 11.2.0.1.0
    * Oracle Client: 11.1.0.7
    * Oracle Data Access Components 32 bit: 11.2.0.3.20

    These should be okay according to the compatibility matrix: http://social.technet.microsoft.com/wiki/contents/articles/17631.biztalk-server-supported-line-of-business-lob-and-enterprise-systems.aspx#Oracle_Database

    Notes:
    * Local Oracle database (no firewall issues)
    * Credentials option is "User account" with Oracle user and password specified
    * The user in Oracle has CHANGE NOTIFICATION system privilege
    * User has all roles and all system privileges except ADMINISTER RESOURCE MANAGER
    * InboundOperationType is set to Notification
    * Notification statement: SELECT REQUEST_ID, REQUEST_TIME FROM xxxx.REQUEST WHERE REQUEST_STATUS = 'P'
    * Notify listener on start is true
    * Safe typing is false
    * BizTalk compatibility is true
    * Notification port is -1
    * Ambient transaction is false
    * Both system notification tables in the database are empty (DBA_CHANGE_NOTIFICATION_REGS and USER_CHANGE_NOTIFICATION_REGS)

    If I set up polling on the same table with the data available statement the same as the notification statement it works fine. This tells me the database connection and queries are correct

    I've looked at all of these Oracle threads but none seem to help.
    https://community.oracle.com/community/developer/search.jspa?q=ORA-29970

    Has anyone successfully set up Notifications and is able to give me some clues?



    Monday, October 10, 2016 4:58 AM

Answers

  • My very strong recommendation, don't even try to use Notifications, with Oracle or SQL Server.

    I, and others, have observed that this feature is very fragile and the notifications will stop with no warning or error.  The only way to restart is restart the Receive Location.

    • Proposed as answer by Angie Xu Monday, October 24, 2016 3:18 AM
    • Marked as answer by Simon Clendon Thursday, October 27, 2016 8:43 PM
    Monday, October 10, 2016 11:18 AM

All replies

  • Hi,

    Change notifications are not really recommended mainly because the notifications can be lost if the recipient is not available etc ! and polling is suitable for critical scenarios, SEE https://msdn.microsoft.com/en-us/library/dd788443.aspx.

    However in your case , looks like the notification is not even registered as the tables DBA_CHANGE_NOTIFICATION_REGS  are empty. All your steps seem right, the only thing that MAY be an issue is that one of your columns, called request time, may be a column datetime datatype.  According to https://docs.oracle.com/cd/B28359_01/win.111/b28375/featChange.htm

    "Query-based notifications are supported only when all the following are true:

    1. The Oracle database version is at least 11.1.

    2. The select list contains no other column data types other than VARCHAR2 and NUMBER.

    3. The COMPATIBLE initialization parameter of the database is set to at least 11.0.0 and Automatic Undo Management (AUM) is enabled (the default). "

    Are there any errors in the event log?


    • Edited by lanax Monday, October 10, 2016 10:16 AM
    Monday, October 10, 2016 10:16 AM
  • My very strong recommendation, don't even try to use Notifications, with Oracle or SQL Server.

    I, and others, have observed that this feature is very fragile and the notifications will stop with no warning or error.  The only way to restart is restart the Receive Location.

    • Proposed as answer by Angie Xu Monday, October 24, 2016 3:18 AM
    • Marked as answer by Simon Clendon Thursday, October 27, 2016 8:43 PM
    Monday, October 10, 2016 11:18 AM
  • Thanks for your reply, lanax.

    Good spotting on the data types. I removed REQUEST_TIME from the notification statement but received the same error as before. Here is the full table definition. I have a sneaky feeling that the CLOB might be interfering but it really shouldn't.

    CREATE TABLE xxx.REQUEST
    (
      REQUEST_ID      VARCHAR2(12 BYTE)             NOT NULL,
      REQUEST_TIME    DATE,
      REQUEST_STATUS  CHAR(1 BYTE),
      RESULT_CODE     VARCHAR2(12 BYTE),
      MESSAGE_XML     CLOB
    )

    I think I will need to use polling which at least I know works. Not the best pattern, sadly. What I wanted to do was have the process triggered by a web service call into BizTalk but that was removed from scope for various reasons.

    FYI, here is the event log entry:

    The Messaging Engine failed to add a receive location "XXXNotification Oracle" with URL "oracledb://localhost:1521/xxxxxx.xxxxxx.co.nz/Dedicated" to the adapter "WCF-Custom". Reason: "Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-29970: Specified registration id does not exist ---> Oracle.DataAccess.Client.OracleException: ORA-29970: Specified registration id does not exist
       at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
       at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
       at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
       at Microsoft.Adapters.OracleCommon.OracleCommonUtils.ExecuteNonQuery(OracleCommand command, OracleCommonExecutionHelper executionHelper)
       --- End of inner exception stack trace ---
       at Microsoft.Adapters.OracleCommon.OracleCommonUtils.ExecuteNonQuery(OracleCommand command, OracleCommonExecutionHelper executionHelper)
       at Microsoft.Adapters.OracleDB.OracleDBInboundContract.StartListener(String[] actions, TimeSpan timeout)
       at Microsoft.ServiceModel.Channels.Common.Channels.AdapterChannelListener`1.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at System.ServiceModel.Dispatcher.ChannelDispatcher.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at System.ServiceModel.ServiceHostBase.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiveEndpoint.Enable()
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiveEndpoint..ctor(BizTalkEndpointContext endpointContext, IBTTransportProxy transportProxy, ControlledTermination control)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiver`2.AddReceiveEndpoint(String url, IPropertyBag adapterConfig, IPropertyBag bizTalkConfig)".

    Monday, October 10, 2016 10:40 PM
  • Thanks for your feedback. It's a shame it isn't more reliable. I will likely use polling even though it's not the best approach but at least it will work :-).
    Monday, October 10, 2016 10:41 PM