none
Missing Records for Application in Which BizTalk Polling from Oracle DB. RRS feed

  • Question

  • Hi 

    The application is developed in BizTalk 2016 to poll the records from Oracle and process. Some times the application is unable to receive the records from Oracle DB. 

    The Oracle procedure is defined to pick the first 20 records, Update these records with Unique value , status updated from 0 to 1 and then commit. Then as a 2nd step in the Procedure pick the 20 records based on the unique value as updated above and return it to BizTalk.

    This is working most of the time correctly but sometimes it executing the Procedure at Oracle end but BizTalk not received any rows. 

    I came across the post which mention the similar scenario and mentioned this as a product bug in BizTalk 2010

    https://support.microsoft.com/en-us/help/2481676/fix-a-wcf-based-sql-adapter-may-lock-sql-server-resources-when-the-pol

    My Query here is 

    1. Is it still not fixed in BizTalk 2016? Is there any update on this?

    2. Is it valid for WCF_OracleDB Adapter or WCF_Custom Adapter with Oracle Binding?

    Thanks,

    Manish

    Friday, June 29, 2018 7:00 AM

All replies

  • Hi Manish,

    Current version of BizTalk Adapter Pack is 3.X and per my understanding this issue is fixed in older version of BizTalk Adapter Pack and must fixed here also(Refer Link#1 for list of CUs for BizTalk Adapter Pack for different versions).

    The issue you mentioned mostly occurs because of DTC, can you check "UseAmbientTransaction" (Refer Link#2 for more info) property of the Adapter and set to True if it is not.



    Link#1 https://support.microsoft.com/en-in/help/2555976/service-pack-and-cumulative-update-list-for-biztalk-server

    Link#2 https://social.technet.microsoft.com/wiki/contents/articles/3470.typed-polling-with-wcf-sql-adapter-best-practices-and-troubleshooting-tips.aspx

    Hope this Helps!!!!

    Regards,


    Note: Please Mark As Answered if you satisfy with Reply.
    Monday, July 2, 2018 12:40 PM
  • The referenced article and fix is related to SQL Server, not Oracle.  Meaning, it does not apply to Oracle in any way.

    If the mark and return is all handled within the Stored Procedure, then it seems more likely the problem is within Oracle.  This is a fairly common pattern and is otherwise unknown to BizTalk.

    I would focus on the SP code to find the cause and resolution.

    Monday, July 2, 2018 2:19 PM
  • Thanks Vikas. 

    This issue occurs very intermittently and difficult to reproduce the same. I am still not sure if it has been fixed in release 3 of adapter pack. There are no certain steps to follow to get the same error.

    In my case,  UseAmbientTransaction was set to false. It using Oracle connection in which the URI is more than 39 chars long and if it is more than 39 chars UseAmbientTransaction can't be set to true. 

    Regards,

    Manish

    Wednesday, July 4, 2018 1:30 AM
  • Thanks Johns.

    For me it not seems the issue with Oracle, There is additional logging step added in Oracle side which confirms the Stored procedure calls successfully which is mentioned in PollingStatement. Please go through the link which explains the exactly same issue in detail and the probable solution 

    #Link:- https://blog.thoughtstuff.co.uk/2011/11/biztalk-wcf-custom-adapter-sqlbinding-silently-consuming-messages/

    Note:- I still have to test the solution before confirming it working solution. 

    Wednesday, July 4, 2018 1:39 AM
  • it sounds unusual, what is the unique value you are committing? and how your second step also know those unique values? or it is just picking based on the status flag.

    What is the polling interval you have set?

    Wednesday, July 4, 2018 2:56 AM
  • Yes. Its unusual and very intermittent. Also I am not the first facing this issue. In last couple of days I found lots of blogs and articles. 

    To answer the queries, the unique value committed is GUID for a batch. Once updated this is being received within BizTalk with ID(Primary Key) and BizTalk Updates the record back to to 2 based on the ID. It is just picking the records based on unique GUID assigned to a batch(say 10 records with status =0). 

    polling interval set is 30 sec. and polling from 2 BizTalk servers.

    Regards,

    Manish

    Wednesday, July 4, 2018 10:21 AM
  • That link is specifically related to the sqlBinding, nothing to do with Oracle.

    I've used this pattern many times myself.  By far, the most likely culprit is the Stored Procedure code.

    Wednesday, July 4, 2018 2:05 PM
  • I assume you are maintaining the uniqueness by GUID. The other thing you can do is Host Clustering

    This will definitely avoid any concurrency related underlying issues if exist.

    http://kentweare.blogspot.com/2009/04/clustering-biztalk-hosts.html
    Wednesday, July 4, 2018 4:15 PM
  • polling interval set is 30 sec. and polling from 2 BizTalk servers.

    2 or more BizTalk Server isn't a problem, provided the SP code correctly implements transactions internally.

    This could be as simple as a BEGIN...END TRAN around the UPDATE where the GUID is applied.

    Wednesday, July 4, 2018 11:00 PM
  • In my opinion it can't be viewed as SQLbinding or OracleDBbinding. Its the issue with Polling from BizTalk irregardless of the Database type. To be more precise, The issue with WCF-Custom adapter in case of polling the records from any DB.

    Regards,

    Manish


    Thursday, July 5, 2018 12:00 AM
  • Earlier I also doubt on the SP, handling transaction whether its returning any rows to BizTalk or not. At the same time questions raised for BizTalk implementation to show where is BizTalk logs for its calls the SP and as polling is configured on adapter there is noway i can log each calls which triggers SP.

    After that In SP, added commit statement and logging additional steps at the start of the SP and after commit writing the selected records in another table.

    Thursday, July 5, 2018 12:12 AM
  • I suggest you do the host clustering while you figure out the issue. I think it may resolve by doing so.
    Thursday, July 5, 2018 5:38 AM
  • Well...that's just not how it works.  The WCF-Custom Adapter is just a wrapper and is oblivious to what clients or protocols are used by the various bindings.

    The actual work is done by the oracleBinding->Oracle Client->Oracle DB stack.  The gap is definitely somewhere in there.  To remind, the above articles and fixes are specifically for the sqlBinding, they do not apply to any other BizTalk, WCF or Oracle code.

    By the way, you never specified exactly what's happening.  Are records getting lost or is it just that no records are returned?

    Thursday, July 5, 2018 1:43 PM
  • Hi Johns,

    Well. I understand the sqlbinding and OracleDBbinding uses ADO and ORAClient respectivelly and both are different   but when you compare the properties on the adapter settings like polling interval, transactions, timeouts etc Under bindings TAB are same. Also at the moment  i am not sure the fix is applicable to for Oracle or not but looks like the issue is same.

    To Recap , whats happening here is step wise

    1. BizTalk polls Oracle DB using the Stored Procedure. This call is from 2 BT server and polling interval is 30 sec.

    2. SP updates the Timestamp = Systemtimestamp, status= From 0 to 1 and assigns unique value to one batch. This batch size is of max 20 based on how many records exists with status =0. When SP executes, that confirms there are records that has unique value and status =1.

    3. Next step in Same SP, The records are fetched based on unique value and a record set and returned back to BizTalk. For further processing. 

    Whats is the issue:- The issue is when the SP executed and the status made to one. At the same timestamp there is no entry in Tracked message event of BizTalk admin console. And this issue occurs vary rarely. In some posts its mentioned as memory leak issue with BizTalk WCF Adapters  (SQLBindings). 

    http://geekswithblogs.net/paulp/archive/2010/05/17/139876.aspx

    As solution mentioned in the above link I also have done the settings changes in the Adapter configuration and retesting it. 

    At this moment It's too early to comment this fix worked for OracleDBBindings. 

    Regards,

    Manish

    Friday, July 6, 2018 1:37 AM
  • The properties were named similarly for consistency, but there is no relationship or connection between them.  You have to accept that those fixes have nothing do to with the Oracle stack.  They are all SQL Server related and SQL Server only.

    Is the unique value assigned within a transaction?  Is it a GUID?

    Maybe don't update the status>1 until the results are actually returned.

    You also didn't answer an important question, are records lost?

    Friday, July 6, 2018 1:20 PM
  • Hi 

    I just have applied the same fix to see in case i can reproduce it. For the queries raised

    1:- Is the unique value assigned within a transaction?  Is it a GUID?

    A:- Yes it is in the transaction. Its in same Stored Proc and all the SQL statements are within the begin and end transaction . Yes the unique value is GUID. 

    2:- Maybe don't update the status>1 until the results are actually returned.

    A:- You mean this is to be updated from BizTalk? May be using postPollStatement. 

    3:- You also didn't answer an important question, are records lost?

    A:- Stored procedure executed, but Records not revived by BizTalk  as seen in tracked message event. Yes, Records are lost in between Oracle and BizTalk.

    Regards,

    Manish

    Monday, July 9, 2018 1:18 AM
  • On #2, in the SP, do the Select where Unique ID, then set the Status.

    Maybe even check rowcount to make sure records were returned before setting the status.

    Again, those updates are for SQL Server only.

    Monday, July 9, 2018 6:09 PM
  • Thanks John. Appreciate your quick response and valuable suggestions.

    Within SP , there has been added additional steps (only in Test and UAT). this step actually fetch the records after commit statement in SP and then inserts each records in a temporary log table. 

    Currently the proposed fix applied yesterday in UAT. In last 24 hrs, In Production environment(No Fix implemented yet), found 4 records failed to be received in BizTalk but nothing in UAT after applying the fix to set the receiveTimeOut to Max(24.20:31:23.6470000).

    Could you please share some more points, why do you think that the update applies only to SQLBindings not to the OracleDBbindings?  Even the post describes the similar issues.

     

    Regards,

    Manish

    Tuesday, July 10, 2018 12:36 AM
  • Well, because they're not.  I don't know what else to tell you.

    They are for the SQL Server stack only.  What give you the impression thy apply to Oracle?  They all referrer specifically to SQL Server.  No mention of Oracle anywhere.

    I don't think the Timout setting is having any effect.  

    Tuesday, July 10, 2018 11:23 AM
  • Hi Johns,

    There is a logic through which i claimed it's applicable for OracleDBBinding along with SQLBinding Type. Also can't stop trying with OracleDBbinding because it's not mentioned in any of the post. 

    The logic here is The property "receive timeout" is a part of WCF-Custom and it's not specific to any binding type. Also it is grouped as StandardBindingElement in the adapter configuration 

    Standard Bindings Settings

    Let's wait for the test outcome. I am trying to reproduce the record with failure which is very intermittent. 

    Will update soon with the end result. 

    Thanks,

    Manish


    • Edited by Manishkumar.ce Wednesday, July 11, 2018 5:03 AM Incorrect image uploded
    Wednesday, July 11, 2018 4:56 AM
  • Those values are defined by a standard WCF Interface, IDefaultCommunicationTimeouts, which is implemented by many different bindings, including basicHttp which also has nothing to do with Oracle.

    Please, you have to accept those updates are irrelevant.  You time is better focused on the actual implementation.

    Wednesday, July 11, 2018 12:22 PM
  • Thanks Johns. Please go through the links once again and the below one which says it's issue with BizTalk adapter. 

    https://social.msdn.microsoft.com/Forums/en-US/83e76be7-cea5-4164-89be-8726cd4c58c6/biztalk-2009-sql-wcf-adapter-losing-messages-aka-typedpolling-not-working?forum=biztalkgeneral 

    As you don't have anything in support it is only applies for SQL and not for Oracle but here the fix seems to be working in my case as i haven't observed any issue after reset the receive timeout to its max (24.20:31:23.6470000) in UAT. 

    https://blog.thoughtstuff.co.uk/2011/11/biztalk-wcf-custom-adapter-sqlbinding-silently-consuming-messages/ 

    Regards,

    Manish

    Thursday, July 12, 2018 12:50 AM
  • Again, don't know what else to tell you.  Those fixes apply to SQL Server, note, Oracle is not mentioned anywhere in the release notes or KB articles.  That's just how it works.

    Hate saying this, but you're wasting you time focusing on those.  They don't apply to Oracle just as they don't apply to HTTP or SFTP or any other Adapter.

    If changing the timeout helps, that indicates an underlying problem, a lock or some such.  You didn't mention if you changed the timeout in isolation.  You mentioned other code changes as well.

    Thursday, July 12, 2018 3:47 PM
  • Thanks Johns. 

    After applying the changes to set the receive timeout to its max (24.20:31:23.6470000). The missing records issue got resolved. Since last 2 days there are no pending records in Oracle DB. I have used the solution#1 suggested in below post for OracleDBBinding. 

    https://blog.thoughtstuff.co.uk/2011/11/biztalk-wcf-custom-adapter-sqlbinding-silently-consuming-messages/comment-page-1/#comment-1563465

    With this fix, the few points to highlight here are

    1. This is applicable to sqlbinding and OracleDBbinding. 

    2. The cumulative update fix is not a part of BizTalk 2016. This issue still exists in BizTalk 2016 and for OracleDBbinding.

    Thanks,

    Manish

    Tuesday, July 17, 2018 2:51 AM
  • Again, please understand, that is simply not correct.  You need to identify the problem on the Oracle side.

    Those updates do not apply to your situation.  Not at all.  They are completely irrelevant and ineffective.  In fact, the installer shouldn't even update them as the versions packaged in the BizTalk 2010 CU are lower than what is shipped with BizTalk Server 2016.  Meaning, you may have run the update, but no updates were actually done.

    To remind, neither the above article nor the update make any mention of Oracle.  Meaning, they do not apply to Oracle.  Please compare the assembly version numbers between the working and non-working servers.

    From too much experience with this, the Timeout setting has merely masked the actual problem.  This is unfortunately very common.


    Tuesday, July 17, 2018 3:50 PM
  • Thanks Johns. May be Timeout setting just to mask the actual problem but increasing the Timeout setting in BizTalk resolved the issue of missing records. 
    Thursday, July 19, 2018 6:54 AM
  • For information I have exactly the same problem (Biztalk 2016 - CU4)
    The Oracle procedure is well executed, the statuses updated, but no message in Biztalk.
    This happened 3 times in 15 days.

    Friday, July 27, 2018 12:07 PM
  • Thanks for sharing. In BizTalk CU4, again the issue described as it is only with WCF-SQL. 

    For me it was daily 1 -3  records prior to change the ReceiveTimeouts value. After changing, No missing records :) . 

    Tuesday, July 31, 2018 1:14 AM
  • I have discussed this issue with Microsoft support team and they shared the below documentation link for settings the Oracle Database Adapter binding property. It's mentioned that Receivetimeout to be set to 24 days (24.20:31:23.6470000). Refer the below link and screen shot

    https://docs.microsoft.com/en-us/biztalk/adapters-and-accelerators/adapter-oracle-database/read-about-the-oracle-database-adapter-binding-properties. 


    Thanks,

    Manish


    Friday, June 14, 2019 6:35 AM
  • Well, did the resolve the issue?  Probably not because the gap is still somewhere in the Oracle stack.

    Increasing a client timeout value is also quite common and not a fix for anything.  We do this because there's usually no practical reason to enforce a client timeout.


    Friday, June 14, 2019 12:03 PM
  • Hi 

    Obviously, Its resolved the issue. I am not sure, for what reason you don't have a believe on the recommended solution. 

    Do you have any alternate ? If yes, please share. If no, accept this as a solution. 

    Microsoft support team collected the trace and suggested this. And it is working. 


    Friday, June 14, 2019 12:46 PM
  • Awesome!  But now you see the problem was within Oracle all along and...sadly, probably still is.  Increasing timeouts is always a stopgap measure.

    The SP seems to be taking an unreasonable about of time to complete so you sill need to focus on the SP code to find out why it's taking so long, then either fix it finally or prove that execution time is legitimate.

    The problem was never BizTalk.

    Friday, June 14, 2019 12:59 PM
  • I never mentioned the problem was with Oracle or Oracle SP. And i have neither changed the oracle settings and nor the logic in SP. 

    Also there is no problem with BizTalk. The issue is with WCF. and the recommended settings for WCF adapter resolves the issue.

    Please refer the links above specially the below one to understand the basic root cause.

    http://geekswithblogs.net/paulp/archive/2010/05/17/139876.aspx

    Thanks,

    Manish

    Friday, June 14, 2019 1:13 PM
  • But that issue was fixed in BizTalk Server 2009.  If this was an actual problem it would be much, much more common.

    I'm saying the problem is somewhere in the Oracle stack because, well, that's where problems like this usually are.  BizTalk has distinct ability to surface gaps in other systems, Oracle, SAP, SalesForce.

    You've maybe accommodated the issue which might be fine.

    Friday, June 14, 2019 1:59 PM