Author: Richard Seroter

I’ve spent the better part of this week wrestling with Oracle connectivity issues, and figured I’d share a few things I’ve discovered.

A recent BizTalk application deployment included an orchestration that does a simple update to an Oracle table. Instead of using the Oracle adapter, I used .NET component and the objects in the System.Data.OracleClient .NET framework namespace. As usual, everything worked fine in the development and test environments.

Upon moving to production, all of a sudden I was seeing the following error with some frequency:

Logging failure … System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
…

Yowza. The most common reason for this occuring is failing to properly close/dispose a database connection. After scouring the code, I was positive that this wasn’t the case. After a bit of research, I came across the following two Microsoft .NET Framework hotfixes:

• [.NET Framework 1.1] – FIX: A pooled connection is not disposed by Microsoft .NET Managed Provider for Oracle when an exception occurs
• [.NET Framework 2.0] – FIX: The connection is not discarded from the connection pool when you use the .NET Framework Data Provider for Oracle in the .NET Framework 2.0 to connect to a data source and an exception occurs

So in a nutshell, bad database connections are by default, returned to the connection pool. Nice. I went ahead and applied this hotfix in production, but still saw intermittent (but less frequent) occurences of the error above.

Next, I decided to turn on the SQL/Oracle performance counters so that I could actually see the pooling going on. There are a few counters that are “off” by default (including NumberOfActiveConnections and NumberOfFreeConnections) and require a flag in the application configuration file. To add these counters, go to the BTSNTSvc.exe.config file, and add the following section …

<system.diagnostics>
    <switches>
      <add name="ConnectionPoolPerformanceCounterDetail"
           value="4"/>
    </switches>
  </system.diagnostics>

Now, on my BizTalk server, I can add performance counters for the .NET Data Provider for Oracle and see exactly what’s going on.

For my error above, the most important counter to initially review is NumberofReclaimedConnections which indicates how many database connections were cleaned up by the .NET Garbage Collector and not closed properly. If this number was greater than 0, or increasing over time, then clearly I’d have a connection leak problem. In my case, even under intense load, this value stayed at 0.

When reviewing the NumberOfFreeConnections counter, I noticed that this was usually 0. Because my database connection string didn’t include any pooling details, I wasn’t sure how many connections the pool allocated automatically. As desperation set in, I decided to tweak my connection string to explicitly set pooling conditions (new part in bold):

User Id=useracct1;Password=secretpassword;
   Data Source=prod_system.company.com;
   Pooling=yes;Max Pool Size=100;Min Pool Size=5;

Once I did this, my counters looked like my picture above, with a minimum of 5 connections available in the pool. As I type this (2 days after applying this “fix”), the problem has yet to resurface. I’m not declaring victory yet since it’s too small of a sample size.

However, given the grief that this has caused me, I’m tempted to switch from the System.Data.OracleClient to the System.Data.Odbc objects where I’ve had previous success and never seen this error in production. My other choice is give up my dream of using the API altogether and use the BizTalk Oracle adapter instead. Thoughts?

To add insult to my week of Oracle connectivity hell, I’ve noticed that the Oracle adapter for a DIFFERENT application has been spitting this message out with greater occasion …

Failed to send notification : System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Naturally the message in the Event Log doesn’t tell me which send/receive port this is associated with because that would make troubleshooting less exciting. Anyone else see this rascal when using the Microsoft Biztalk Adapters for Enterprise Applications? I’ve also see it on occasion with my .NET code solution.

All of this is the reason I missed the Los Angeles BizTalk Server 2006 R2 launch event this week. I’m still bitter. However, I’m told that bets were made at the event as to whether I’d blog more or less while out on paternity leave in a week or two, so it’s nice to know they were thinking of me! Stay tuned.

Technorati Tags: BizTalk, Oracle

Yesterday a co-worker of mine was having issues serializing an auto-generated BizTalk schema into a .NET object. We found an obscure fix that solved the problem.

In Darren’s Professional BizTalk Server 2006 book, he’s a proponent of working with serializable classes (instead of messages) where possible. In our case, my buddy Prashant was doing some mass Oracle table updates using data retrieved from the BizTalk Siebel adapter. Instead of having countless “Oracle Insert” messages, we discussed simply turning the Siebel messages into .NET objects and using a helper class to do one big transactional insert.

So, he took the Siebel adapter schemas, ran them through xsd.exe, and ended up with a nice .NET object representing all the nodes in the schema. However, upon doing the XLANGMessage “RetrieveAs” operation, he got a gnarly error (actual type names removed) stating:

Cannot use XLANGMessage.RetrieveAs to convert message part part with type [SampleNamespace].[TypeName]+QueryEx2Response to type QueryEx2Response.”
…
Exception type: InvalidCastException
Source: Microsoft.XLANGs.Engine
Target Site: System.Object RetrieveAs(System.Type)
…
Unable to generate a temporary class (result=1).
error CS0030:
Cannot convert type ‘Customer_Complaint_Case_BCResultRecord[]’ to
‘Customer_Complaint_Case_BCResultRecord’
error CS0029:
Cannot implicitly convert type
‘Customer_Complaint_Case_BCResultRecord’ to
‘Customer_Complaint_Case_BCResultRecord[]’

Ouch. Well from reading that, clearly there looks like a problem serializing that “BCResultRecord” array. After doing a quick web search, I came across a newsgroup post discussing the same serialization problem we hit. The solution? Add a temporary “attribute” to the unbounded item to force the xsd.exe tool to properly deal with array types. So, before the change, my offending piece of the Siebel-generated XSD looked like this:

<xsd:complexType name="Customer_Complaint_Case_BCResultRecordSet">
    <xsd:sequence>
      <xsd:element minOccurs="0" maxOccurs="unbounded" 
	  name="Customer_Complaint_Case_BCResultRecord" 
	  type="BizObj:Customer_Complaint_Case_BCResultRecord" />
    </xsd:sequence>
  </xsd:complexType>
  

When running xsd.exe, the generated type looked like this …

public partial class QueryEx2Response {
    
    private Customer_Complaint_Case_BCResultRecord[][] 
	    Customer_Complaint_Case_BCResultRecordSetField;
    
    [System.Xml.Serialization.XmlArrayItemAttribute
	(typeof(Customer_Complaint_Case_BCResultRecord),
	 Namespace="http://schemas.microsoft.com/Business_Objects",
	  IsNullable=false)]
    public Customer_Complaint_Case_BCResultRecord[][] 
                   Customer_Complaint_Case_BCResultRecordSet {
        get {
         return this.Customer_Complaint_Case_BCResultRecordSetField;
        }
        set {
         this.Customer_Complaint_Case_BCResultRecordSetField = value;
        }
    }
}

Here’s where the problem was. So, I *temporarily* tweaked the schema to add the temporary attribute …

<xsd:complexType name="Customer_Complaint_Case_BCResultRecordSet">
    <xsd:sequence>
      <xsd:element minOccurs="0" maxOccurs="unbounded" 
	  name="Customer_Complaint_Case_BCResultRecord" 
	  type="BizObj:Customer_Complaint_Case_BCResultRecord" />
    </xsd:sequence>
    <xsd:attribute name="temp" type="xsd:string" />
  </xsd:complexType>
  

NOW, after re-running xsd.exe, my generated type looked like this …

public partial class QueryEx2Response {
    
    private Customer_Complaint_Case_BCResultRecordSet[] 
	Customer_Complaint_Case_BCResultRecordSetField;
    
    [System.Xml.Serialization.XmlElementAttribute
	("Customer_Complaint_Case_BCResultRecordSet")]
    public Customer_Complaint_Case_BCResultRecordSet[] 
                  Customer_Complaint_Case_BCResultRecordSet {
        get {
         return this.Customer_Complaint_Case_BCResultRecordSetField;
        }
        set {
         this.Customer_Complaint_Case_BCResultRecordSetField = value;
        }
    }
}

You can see how the generated class now recognizes the “BCResultRecordSet” object as an array, vs. using a double-array of type “BCResultRecord.” Also, the metadata about accessor changed from being a XmlArrayItemAttribute to a XmlElementAttribute. Once this change was made, everything worked perfectly.

I was able to successfully switch the schema back to it’s original form (sans “temporary attribute”), and the serialization still worked fine. The key was adding that temporary attribute for the creation of the serializable class only. You don’t need to keep this temporarily attribute in the schema after that.

I suspect that this situation would arise for many of the auto-generated schemas from the BizTalk adapters (Siebel, Oracle, Peoplesoft, SQL Server etc). It’s quite nice to deal with these messages as pure .NET objects, but watch out for tricky serialization issues.

Technorati Tags: BizTalk