none
OracleDb adapter hangs up RRS feed

  • Question

  • I have the following scenario:

    • Multiple Request/Response schemas published as methods in WCF basic http two-way service under IIS
    • Two-way WCF BasicHttp Receive port/location (ReceiveLocationA) connected to the mentioned SVC published in IIS
    • OracleDb send/receive port to call stored procedures (SendPortA) in my Oracle DB
    • Inbound/outbound maps in SendPortA to map between WCF schemas and SP request/response schemas
    • Filters on SendPortA to subscribe to corresponding BTS.MessageType and SOAP.MethodName, i.e. requests coming from ReceiveLocationA

    Sample action mapping in SendPortA:

     

    <BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <Operation Name="Get_Subscription_Info" Action="http://Microsoft.LobServices.OracleDB/2007/03/OCRM/Package/DPKG_OCRM_GENERAL/GET_SUBSCRIPTION_INFO" />
      <Operation Name="Get_Products" Action="http://Microsoft.LobServices.OracleDB/2007/03/OCRM/Package/DPKG_OCRM_GENERAL/GET_PRODUCTS" />
      <Operation Name="GetContractInfo" Action="http://Microsoft.LobServices.OracleDB/2007/03/OCRM/Package/DPKG_OCRM_GENERAL/GET_CONTRACT_INFO" />
      <Operation Name="GetHistory" Action="http://Microsoft.LobServices.OracleDB/2007/03/OCRM/Package/DPKG_OCRM_GENERAL/GET_HISTORY" />
    </BtsActionMapping>
    

     

     

    As you can see it is a pretty simple scenario: I successfully call the published WCF service, it maps request to corresponding SP (stored proc) call, then maps SP response back to WCF service method response.

    IT WORKS FOR ME PRETTY GOOD, even for weeks, then at some point, due to temporary failures I get suspended messages (actually, timed out or unwanted calls to SendPortA), terminate them manually in BT admin console and all subsequent calls to my published WCF service go through to SendPortA and get stuck there with a long timeout. Of course, there is no sense to resume these, since WCF client also times out and I just can't send response from SendPortA back to service client (just can only terminate the SendPortA message instances)

    I just wonder why at some point without any exception traces in system's event log ALL the requests to SendPortA start to timeout (no matter I set port-level connection timeouts to 1, 10 or 20 minutes). Actually the SP execution take less than a second and right at the moment when all my WCF calls start to timeout in OracleDB adapter (SendPortA) I am able to call the same SPs and get results though an orchestration via DLL call in atomic scope.

    How can I see what's happening at adapter level and is there a way to somehow reset OracleDb adapter? Maybe I am speaking absurd things, but I really can't understand what's the matter with OracleDb adapter.

    Only server restart helps. I tried to iisreset, start/stop all instances and apps in BT - nothing else than system restart helps. It is a development server and I can't imagine such a "solution" when my app migrates to live BT cluster.

    Friday, February 3, 2012 6:44 AM

All replies

  • Just an addon. The only repeating event log error is the following (OcrmBillingSP is actually SendPortA mentioned above):

    A message sent to adapter "WCF-OracleDB" on send port "OcrmBillingSP" with URI "oracledb://test" is suspended. 
     Error details: Microsoft.ServiceModel.Channels.Common.ConnectionException: Connection request timed out ---> Oracle.DataAccess.Client.OracleException: Connection request timed out
       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, IntPtr opsErrCtx, Object src)
       at Oracle.DataAccess.Client.OracleConnection.Open()
       at Microsoft.Adapters.OracleCommon.OracleCommonConnectionWrapper..ctor(String connectionString, OracleCommonExecutionHelper executionHelper)
       at Microsoft.Adapters.OracleDB.OracleDBConnection.Microsoft.ServiceModel.Channels.Common.IConnection.Open(TimeSpan timeout)
       --- End of inner exception stack trace ---
    
    Server stack trace: 
       at Microsoft.Adapters.OracleDB.OracleDBConnection.Microsoft.ServiceModel.Channels.Common.IConnection.Open(TimeSpan timeout)
       at Microsoft.ServiceModel.Channels.Common.Design.ConnectionPool.GetConnection(Guid clientId, TimeSpan timeout)
       at Microsoft.ServiceModel.Channels.Common.Design.ConnectionPool.GetConnectionHandler[TConnectionHandler](Guid clientId, TimeSpan timeout, MetadataLookup metadataLookup, String& connectionId)
       at Microsoft.ServiceModel.Channels.Common.Channels.AdapterRequestChannel.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at System.ServiceModel.Channels.ServiceChannel.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open()
    
    Exception rethrown at [0]: 
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at System.ServiceModel.ICommunicationObject.Open()
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.GetChannel[TChannel](IBaseMessage bizTalkMessage, ChannelFactory`1& cachedFactory)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.SendMessage(IBaseMessage bizTalkMessage) 
     MessageId:  {3366595F-616E-4657-9ADD-5253FF75C085}
     InstanceID: {65F9894C-A361-49B9-A286-212E342BFE1F}


     

     

    Friday, February 3, 2012 6:52 AM
  • I am wondering if the error message is not actually the root cause - maybe the actual problem is something other than a timeout. Did your generated schema change around the time you started getting timeouts. Is it possible the database schema may have changed around the time the timeouts started occuring?

    My guess about your restart issue is that you are having trouble getting the order of restarted items in the correct order. I have noticed that orchestrations/schemas exposed as WCF can be quite painful from a restart perspective. Here is the order I think I used in a similar situation:

    1. Stop BizTalk applications
    2. Stop BizTalk host instances
    3. Deploy chanages or republish schemas
    4. iisreset
    5. Restart BizTalk applications

    You should not need to restart, it sounds like there is some other underlying problem.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Saturday, February 4, 2012 6:15 AM
    Moderator
  • Hi,

    A) Can you try out a simple ODP.NET code that uses the connection pool, and does the following in a loop:

    1. Open connection to oracle

    2. Perform simple operation

    3. return connection to pool.

    In all probability you should be able to reproduce the problem with this.

    B) When this problem occurs, are you able to use tnsping to ping the oracle server? You could also try running a simple program that connects to the database.

    C) You must have tested this already, but just for completeness, is there a network issue at the time when these connection requests start failing?

    Thanks,
    Manas


    -- Please mark as answered if this answers your question.
    Monday, February 6, 2012 7:53 AM
  • I am wondering if the error message is not actually the root cause - maybe the actual problem is something other than a timeout. Did your generated schema change around the time you started getting timeouts. Is it possible the database schema may have changed around the time the timeouts started occuring?

    My guess about your restart issue is that you are having trouble getting the order of restarted items in the correct order. I have noticed that orchestrations/schemas exposed as WCF can be quite painful from a restart perspective. Here is the order I think I used in a similar situation:

    1. Stop BizTalk applications
    2. Stop BizTalk host instances
    3. Deploy chanages or republish schemas
    4. iisreset
    5. Restart BizTalk applications

    You should not need to restart, it sounds like there is some other underlying problem.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline

    Schema is exactly what's needed. Now I am again close to the same hang-up situation. About 4 hours ago I have made a WCF call to the service and after 20 minutes (!) of silent execution the listening 2-way receive location have timed out (although the wcftestclient.exe gave timeout exception in a minute after the call) with the following event log error:

    A request-response for the "BasicHttpRLConfig" adapter at receive location "/SiebelCRMGW/SiebelCRMGW.svc" has timed 
    out before a response could be delivered.

    Yet, the Oracle send port is still running after 4 hours, although the session on the Oracle DB has ended 2 hours ago. Is it possible to control over the ODP.NET connection pool that Oracle-DB adapter uses?



    Monday, February 6, 2012 9:05 AM
  • Hi,

    When the problem occurs I am still able to call any SP at Oracle with .net code (using System.Data.OracleClient: OracleConnection and OracleCommand) - all without any delays or problems, but I never tried to use ODP.NET lib. Can you provide a simple quick link how to write the code you've mentioned in ODP.NET?

    So, the problem is not related to network issues.

    What's rather interesting, the problem mainly starts to occur when I call a stored procedure with many branches (depend on an input parameter value) and the delay for hours occur when just one specific branch in SP is executed. All other branches generate no delays or problems. But this specific branch executes in about 3 seconds when I manually call SP in an Oracle GUI client software and hangs ONLY when I call this SP via Oracle-DB adapter.

    Here is the content of the branch that never executes via Oracle-DB (hangs up adapter for further BizTalk queries, especially if I manually terminate the Active OcrmBillingSP port activity -- see picture above):

    	ELSIF (regexp_replace(P_NUMBER_LIST,'[a-zA-z]','') <> P_NUMBER_LIST) THEN                             -- Pattern
    	
      	  OPEN P_RESULT FOR
            SELECT phone_number   		  								PHONE_NUMBER,
                   number_status										STATUS,
                   brs_rst_code                                         RESERVATION_TYPE,
                   nvl(user_name,'')      								RESERVED_BY,
                   nvl(to_char(reserved_until,'MM/DD/YYYY'),'')      	RESERVED_UNTIL,
                   nvl(reserved_to,'')    								RESERVED_FOR,
                   'Comments'             								COMMENTS
              FROM brs_numbers
             WHERE 
               number_status in (1,2,5) 
               AND phone_number in (   WITH a AS (SELECT  P_NUMBER_LIST m from dual),
    									    b AS (SELECT distinct substr(regexp_replace(m,'\d+',''), level,1) l from a connect by level <= length(regexp_replace(m,'\d+',''))),
    								        c AS (SELECT lpad(level-1,(select count(*) from b),'0') n from dual	connect by level <= power(10,(select count(*) from b))),
    							  	        d AS (SELECT n from c, (select rownum i from b) group by n having count(distinct substr(n, i, 1)) = (select count(*) from b)),
    							  	        e AS (SELECT replace(wmsys.wm_concat(l),',') ll from b),
      					 		            f AS (SELECT regexp_substr(v_prefix,'[[:digit:]]{1,}', 1 , level) k FROM dual connect by level <= (length(v_prefix)+1)/3)
    		   						 SELECT concat(f.k,translate(a.m,e.ll,d.n)) s
    								   FROM a,d,e, f )  
            ORDER BY 1;
    

    This branch (SP call) if called through an Oracle GUI or .NET lasts only 3 seconds max and hangs up the BizTalk and produces long-running session at Oracle DB if called via OracleDB adapter. What is most absurd thing, it seems that the problem is produced by translate() above. Can't even imagine how can it affect OracleDb adapter!

    Monday, February 6, 2012 9:25 AM
  • UPDATE:

    At last, 4 hours and 20 minutes after making WCF (and Oracle-DB) call, the Active instance of WCF-OracleDB OcrmBillingSP send port have been suspended with the following error (absolutely no other related errors within all this period):

    A message sent to adapter "WCF-OracleDB" on send port "OcrmBillingSP" with URI "oracledb://test" is suspended. 
     Error details: Microsoft.ServiceModel.Channels.Common.XmlReaderGenerationException: ORA-04030: out of process memory when trying to allocate 80 bytes (kxs-heap-w,cursor work heap) ---> Oracle.DataAccess.Client.OracleException: ORA-04030: out of process memory when trying to allocate 80 bytes (kxs-heap-w,cursor work heap)
       at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
       at Oracle.DataAccess.Client.OracleDataReader.Read()
       at Oracle.DataAccess.Client.OracleDataReader.get_HasRows()
       at Microsoft.Adapters.OracleCommon.ProcedureResponseBodyWriter.WriteWeaklyTypedReader(XmlDictionaryWriter writer, OracleCommonDataReaderWrapper reader, ComplexQualifiedType cqt, MetadataLookup metadataLookup, OracleCommonExecutionHelper executionHelper, Boolean enableSafeTyping)
       at Microsoft.Adapters.OracleCommon.ProcedureResponseBodyWriter.WriteParameters(XmlDictionaryWriter writer)
       at Microsoft.Adapters.OracleCommon.ProcedureResponseBodyWriter.OnWriteBodyContents(XmlDictionaryWriter writer)
       --- End of inner exception stack trace ---
       at Microsoft.Adapters.OracleCommon.ProcedureResponseBodyWriter.OnWriteBodyContents(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.BodyWriter.WriteBodyContents(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.BodyWriterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.Message.WriteBodyContents(XmlDictionaryWriter writer)
       at Microsoft.Adapters.AdapterUtilities.AdapterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.Message.WriteBodyContents(XmlDictionaryWriter writer)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkMessageStream(Message wcfMessage, IAdapterConfigInboundMessageMarshalling config)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkMessage(IBaseMessageFactory messageFactory, IAdapterConfigInboundMessageMarshalling marshallingConfig, Message wcfMessage)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result) 
     MessageId:  {232F8621-0B9E-47EE-8526-BCF5EBD94DE4}
    

    Please suggest what this could be.

    Monday, February 6, 2012 9:47 AM
  • Hi Michael,

    Did you manage to solve this issue?

    I am experiencing similar timeout issues when connection pooling is turned on. It happens randomly and even when the pool is empty.

    Kind Regards,

    Raicho

    Wednesday, February 27, 2013 3:45 PM
  • Hi Michael,

    I am experiencing the same request time out issue in our production site. were you able to get any solution for this?

    regards,

    Pratik

    Tuesday, October 15, 2013 8:35 AM