ODP.NET Oracle.ManagedDataAcess随机ORA-12570错误 [英] ODP.NET Oracle.ManagedDataAcess random ORA-12570 errors

问题描述

我试图从非托管版本迁移到Oracle.ManagedDataAcess和接收偶合ORA-12570 TNS：包读取器失败。

我不知道为什么这个错误开始，但一旦启动后，每个后续请求给出了同样的错误约10-30分钟，然后重新将其适用于另一10-30分钟等

因此，它是一种随机的一段时间，然后随后的成功

随后的失败的

已经尝试过很多事情，恢复：

环境：

• Oracle.ManagedDataAcess版2400年12月1日（4.121.2.20150926）（的NuGet）（安装在服务器中没有GAC引用，可以覆盖斌版本）


• 甲骨文服务器Oracle数据库12c的企业版发布12.1.0.2.0 - 64生产


• 的Windows 2012（的Windows Update OK）

经过：

• 防火墙：它不是一个防火墙的问题


• 机错误：同样的问题也发生在我的机器，Azure的Web应用程序和AWS EC2实例


• 干扰上：没有嗅探器运行，透明代理等


• 加密：我没有使用任何类型的加密（除非有默认启用的东西，我不知道）


• 连接字符串：同样的
  
  ：连接字符串与非托管版本

aditional的信息工作完美

• 这是一个生产数据库，这是非常稳定


• 应用程序被编译到值为anycpu的IIS应用程序池被限制为64位


• 即时测试正是每一次（只是在休息的WS，的WebAPI的获取网址刷新）同样的要求，所以它是不相关的数据格式

配置：

服务器的sqlnet.ora

 
 SQLNET.AUTHENTICATION_SERVICES =（NTS）
 NAMES.DIRECTORY_PATH =（TNSNAMES，EZCONNECT）
 

应用程序的Web.config

  <&是connectionStrings GT; 
<添加名称=XXXX的connectionString =数据源=（DESCRIPTION =（ADDRESS_LIST =（ADDRESS =（PROTOCOL = TCP）（HOST = xxx.xxx.com）（PORT = 1521）））（CONNECT_DATA =（SERVER = DEDICATED）（SERVICE_NAME = XXX）））;用户ID = XXXXX;密码= XXXXX; /> 
< /&是connectionStrings GT; 
 
< configSections> 
<节名称=oracle.manageddataaccess.clientTYPE =OracleInternal.Common.ODPMSectionHandler，Oracle.ManagedDataAccess，版本= 4.121.2.0，文化=中性公钥= 89b483f429c47342/> 
< / configSections> 
 
< oracle.manageddataaccess.client> 
将;版本号=*> 
<数据源> 
<！ - <数据源别名为SampleDataSource描述=（DESCRIPTION =（ADDRESS =（PROTOCOL = TCP）（HOST =本地主机）（PORT = 1521））（CONNECT_DATA =（SERVICE_NAME = ORCL） ））/>  - > 
< /数据源> 
<设置> 
<设定名=SQLNET.AUTHENTICATION_SERVICESVALUE =NONE/> <！ -  NTS  - > 
<设定名=sqlnet.crypto_checksum_serverVALUE =拒绝/> 
<设定名=sqlnet.crypto_checksum_clientVALUE =拒绝/> 
<设定名=SQLNET.ENCRYPTION_SERVERVALUE =拒绝/> 
< /设置> 
< /版本> 
< /oracle.manageddataaccess.client> 
  

一些参考文献：

https://community.oracle.com/thread/3634263?start=0& TSTART = 0

的 ODP.net管理驱动程序会引发ORA-12570：网络会话：意外的数据包读取错误

管理的Oracle客户端Oracle高级安全选项

的在IIS ODP.NET错误：文件的ORA-12357网络会话结束

更新1

池更改之后（因为我形容这里的答案），我决定发布一个版本做一些真正的考验。 1天，用户抱怨的性能后，我得到了另一个错误：值不能为空。参数名：的字节数组

我改变了参考回到非托管版本，一切都很好再次，更快，没有ByteArray的错误，更好地统筹管理

所以我只是给的，现在的托管版本了，也许我会在Oracle下一个版本再试。

下面一些这个新的错误引用，你可以看到，看起来像另一个bug（仍然没有任何答案）。

的 https://community.oracle.com/thread/3676588?start=0&tstart=0

EF + ODP.NET + CLOB =值不能为空 - 参数名：的字节数组

到目前为止，理由不使用：

• 池管理漏洞


• CLOB空/不为空的ByteArray错误


• 性能下降可能与池错误

解决方案

在我的经验，类似的错误12570（读者VS作家），只有一个原因的错误 - 这是在网络上被终止空闲的TCP连接。通常，这是一个防火墙/管理的交换机。你说你已经排除了防火墙，但我不知道怎么样。这有可能是数据库本身终止连接（DBA脚本），但我记得，是一个不同的错误。

奥拉-12571可能会略有不同。但还是因为你已经确定了的问题是，它的成立多年的池连接，我会坚持下去一样。

有一对夫妇的事情可以做：

1. 设置最小连接字符串中的池大小= 0。这通常解决的事情对我来说。它可以让整个池被关闭了时，应用程序处于空闲状态。有一个小的机会，但如果你的流量波动剧烈，DECR池大小可能需要以更快地收出由狂奔创建的连接增加。


2. 设置的expire_time在SQLNET .ORA。通过它的名字并不明显，此设置发送一个数据包的概率，从而导致任何TCP空闲监测得到满足。这里唯一的问题是，我不完全知道如何与托管提供设置SQLNET设置。我猜的sqlnet.ora可以在exe文件目录，但我也看到一些迹象表明，它能够在形式的.config文件进行设置（看到类似的例子wallet_override的这里）因为你只得到这在管理供应商，我不知道你受管客户端SQLNET。 ORA已经有此设置。

我见过多年来很多次和第一次发生我创建了一个实用工具，基本上没有一个二进制搜索通过创建不同期限的连接，以确定确切的超时时间。如果始终在同一终止时间的土地，你可以猜测有这导致这一个设置的地方。如果它是不稳定的，那么你可能有某种基础设施片状的。

不幸的是我创建它作为一个C＃形式的应用程序，所以我贴两个表单代码和设计下面的代码：

Form1.cs的：

 使用系统; 
使用System.Collections.Generic; 
使用System.ComponentModel; 
使用System.Data这; 
使用System.Drawing中; 
使用System.Linq的; 
使用System.Text;使用System.Windows.Forms的
; 
使用Oracle.ManagedDataAccess.Client; 
 
命名空间TimeoutTest 
 {
公共部分Form1类：表格
 {
名单，LT; TestConnection>连接; 
的Int32 connectionCount; 
的Int32乘法器; 
的Int32 initialConnectionWait; 
 
 TestConnection controlConnection = NULL; 
 
公共Form1中（）
 {
的InitializeComponent（）; 
 
 
 
} 
 
私人无效BtStart_Click（对象发件人，EventArgs五）
 {
 
 connectionCount = Int32.Parse（InConnections.Text）; 
乘数= Int32.Parse（InMultiplier.Text）; 
 initialConnectionWait = Int32.Parse（InInitialWait.Text）; 
 
 DisplayMessage（启动控制connection\r\\\
）; 
 controlConnection =新TestConnection（）; 
 controlConnection.ErrorOccured + =新的EventHandler（controlConnection_ErrorOccured）; 
 controlConnection.IsControlConnection = TRUE; 
 controlConnection.StartTest（2）; 
 
连接=新的List< TestConnection>（）; 
 DisplayMessage（旋转起来{0}连接... \r\\\
，connectionCount）; 
的for（int i = 0，IDLETIME = initialConnectionWait; I< connectionCount;我++，IDLETIME * =乘数）
 {
 
 TestConnection连接=新TestConnection（）; 
 connection.Notified + =新TestConnection.NotificationEventHandler（connection_Notified）; 
 connection.ErrorOccured + =新的EventHandler（connection_ErrorOccured）; 
 connection.TestCompleted + =新的EventHandler（connection_TestCompleted）; 
 connection.StartTest（IDLETIME）; 
 
 connections.Add（连接）; 
} 
 DisplayMessage（）; 
} 
 
 
无效controlConnection_ErrorOccured（对象发件人，EventArgs五）
 {
 DisplayMessage（\r\\\
Co​​ntrol连接错误，中止！ ！）; 
 BtCancel_Click（这一点，EventArgs.Empty）; 
 
} 
 
无效connection_TestCompleted（对象发件人，EventArgs五）
 {
 TestConnection currentConnection =（TestConnection）发送; 
 
如果（currentConnection == connections.Last（））
 DisplayMessage（\r\\\
All测试完整的完成。）; 
 
} 
 
无效connection_ErrorOccured（对象发件人，EventArgs五）
 {
 //停止任何活动的连接。 
的foreach（在连接TestConnection TC）
 {
 tc.CompletionTimer.Enabled = FALSE; 
} 
 
 TestConnection currentConnection =（TestConnection）发送; 
的Int32 upperTime = currentConnection.IdleTime; 
的Int32 lowerTime = 0; 
 
的Int32指数= connections.IndexOf（currentConnection）; 
 //如果这不是第一个连接... 
如果（指数大于0）
 {
 //...then设置基于以前的较低的时间连接
 lowerTime =连接[索引1] .IdleTime; 
} 
 
 //得到下和上作为新的范围之间的差异上
的Int32范围= upperTime工作 -  lowerTime; 
 
 
 //划分射程超过连接数，以获得新的区间
的Int32间隔=范围/ this.connectionCount; 
 connections.Clear（）; 
 
 //如果间隔太小尽量减少连接数量
而（间隔2&放大器;&放大器; this.connectionCount→2）
 {
 this.connectionCount--; 
 DisplayMessage（\r\\\
Co​​nnections太高，当前的分辨率减少{0}的连接。this.connectionCount）; 
间隔=范围/ this.connectionCount; 
} 
 
 
 
如果（间隔2）
 {
 DisplayMessage（\r\\\
Resolution不能增加。完成）; 
} 
，否则
 {
 DisplayMessage（用最小\r\\\
Restarting测试：{0}，最大值{1}，分辨率{2}，lowerTime， upperTime，间隔）; 
 
 
 //创建一个新的连接
（INT I = connectionCount-1，IDLETIME = upperTime间隔; I> = 0;我 - ，idleTime- =间隔）
 {
 
 TestConnection连接=新TestConnection（）; 
 connection.Notified + =新TestConnection.NotificationEventHandler（connection_Notified）; 
 connection.ErrorOccured + =新的EventHandler（connection_ErrorOccured）; 
 connection.TestCompleted + =新的EventHandler（connection_TestCompleted）; 
 connection.StartTest（IDLETIME）; 
 
 connections.Insert（0，连接）; 
} 
 this.connectionCount = connections.Count; 
} 
 
} 
私人无效BtCancel_Click（对象发件人，EventArgs五）
 {
 //停止任何活动的连接。 
的foreach（在连接TestConnection TC）
 {
 tc.CompletionTimer.Enabled = FALSE; 
 tc.Command.Connection.Close（）; 
} 
 DisplayMessage（停止运行测试。）; 
} 
 
 
无效connection_Notified（对象o，Form1.TestConnection.NotificationEventArgs E）
 {
 DisplayMessage（e.Message）; 
} 
 
私人无效DisplayMessage（字符串消息）
 {
 DisplayMessage（{0}，邮件）; 
} 
私人无效DisplayMessage（字符串消息，params对象[]参数）
 {
 OutStatus.AppendText（的String.Format（消息，参数）+\r\ N）; 
} 
 
 
公共类TestConnection 
 {
公布尔IsControlConnection {搞定;组; } 
公众的OracleCommand命令{搞定;私人集; } 
公共定时器CompletionTimer {搞定;私人集; } 
公共字符串的ConnectionId {搞定;私人集; } 
公众的Int32 IDLETIME 
 {
得到
 {
返回CompletionTimer.Interval / 1000; 
} 
组
 {
 CompletionTimer.Interval =值* 1000; 
} 
} 
＃地区的事件和委托
公共事件的EventHandler ErrorOccured; 
公共事件的EventHandler TestCompleted; 
公共类NotificationEventArgs：EventArgs的
 {
公共NotificationEventArgs（字符串消息）
 {
 this.Message =消息; 
} 
公共字符串消息{搞定;组; } 
} 
 
公众委托无效NotificationEventHandler（对象o，NotificationEventArgs E）; 
 
公共事件NotificationEventHandler通知; 
 
私人无效通知（字符串消息）
 {
如果（公告！= NULL）
 {
通知（这一点，新NotificationEventArgs（消息）） ; 
} 
} 
公共无效通知（字符串格式，params对象[]参数）
 {
 this.Notify（的String.Format（格式参数））; 
} 
 
 
 
 #endregion 
 
公共TestConnection（）
 {
 CompletionTimer =新的Timer（） ; 
 CompletionTimer.Tick + =新的EventHandler（CompleteTest）; 
 
命令=新的OracleCommand（
选择'SADDR：'|| SADDR ||'-sid：'|| SID ||'-serial＃：'||序列号|| -audsid：'|| audsid ||'-paddr：'|| PADDR ||'-module：从GV $会话||模块，其中audsid = USERENV（'SESSIONID'））; 
 
 Command.Connection =新的OracleConnection（Configuration.OracleConnectionString）; 
} 
 
公共字符串StartTest（的Int32 IDLETIME）
 {
 Command.Connection.Open（）; 
的ConnectionId =（字符串）Command.ExecuteScalar（）; 
通知（用空闲时间开始测试= {0}，ID = {1}，IDLETIME，的ConnectionId）; 
 IDLETIME = IDLETIME; 
 CompletionTimer.Enabled = TRUE; 
回报率的ConnectionId; （！IsControlConnection）
} 
 
私人无效CompleteTest（对象发件人，EventArgs五）
 {
如果
 CompletionTimer.Enabled = FALSE; 
试
 {
 Command.ExecuteScalar（）; 
通知（测试与空闲时间连接完成= {0}，ID = {1}，IDLETIME，的ConnectionId）; 
如果（TestCompleted！= NULL）
 TestCompleted（这一点，EventArgs.Empty）; 
} 
赶上（OracleException前）
 {
如果（ex.Number == 12571）
 {
如果（ErrorOccured！= NULL）
 {
通知（关于用闲置时间连接Found错误= {0}，ID = {1}，IDLETIME，的ConnectionId）; 
 ErrorOccured（这一点，EventArgs.Empty）; {1}，\r\\\
：
} 
} 
，否则
 {
通知（关于与超时{0}，错误发生的连接未知错误{2}（IDLETIME）的ToString（），恩，的ConnectionId）; 
 
} 
} 
赶上（异常前）
 {
通知（关于与超时{0}，错误连接发生未知错误：{1 }，\r\\\
 {2}（IDLETIME）的ToString（），恩，的ConnectionId）; 
} 
终于
 {
如果
 Command.Connection.Close（）（IsControlConnection！）; 
} 
} 
} 
 
私人无效InConnections_TextChanged（对象发件人，EventArgs五）
 {
 Int32.TryParse（InConnections.Text，出connectionCount）; 
 Int32.TryParse（InMultiplier.Text，出乘数）; 
 Int32.TryParse（InInitialWait.Text，出initialConnectionWait）; 
 
 OutLongestConnection.Text =（Math.Pow（乘数，connectionCount-1）* initialConnectionWait）的ToString（）; 
} 
 
私人无效Form1_Load的（对象发件人，EventArgs五）
 {
 InConnections_TextChanged（这一点，EventArgs.Empty）; 
} 
 
} 
} 
  

Form1上。 designer.cs：

 命名空间TimeoutTest 
 {
部分Form1类
 {
 ///<总结> 
 ///必需的设计变量。 
 ///< /总结> 
私人System.ComponentModel.IContainer成分= NULL; 
 
 ///<总结>使用
 ///清理的任何资源。 
 ///< /总结> 
 ///< PARAM NAME =处理>真要是托管资源应释放;否则为false< /参数> 
保护覆盖无效的Dispose（BOOL处置）
 {
如果（处置和放大器;及（成分= NULL）！）
 {
 components.Dispose（）; 
} 
 base.Dispose（处置）; 
} 
 
 #region Windows窗体设计器生成的代码
 
 ///<总结> 
 ///设计器支持所需的方法 - 不要修改
 ///此方法的代码编辑器的内容。 
 ///< /总结> 
私人无效的Ini​​tializeComponent（）
 {
 this.BtStart =新System.Windows.Forms.Button（）; 
 this.OutStatus =新System.Windows.Forms.TextBox（）; 
 this.InConnections =新System.Windows.Forms.MaskedTextBox（）; 
 this.label1 =新System.Windows.Forms.Label（）; 
 this.label3 =新System.Windows.Forms.Label（）; 
 this.InInitialWait =新System.Windows.Forms.MaskedTextBox（）; 
 this.InMultiplier =新System.Windows.Forms.MaskedTextBox（）; 
 this.label2 =新System.Windows.Forms.Label（）; 
 this.BtCancel =新System.Windows.Forms.Button（）; 
 this.label4 =新System.Windows.Forms.Label（）; 
 this.OutLongestConnection =新System.Windows.Forms.Label（）; 
 this.SuspendLayout（）; 
 // 
 // BtStart 
 // 
 this.BtStart.Location =新System.Drawing.Point（13，394）; 
 this.BtStart.Name =BtStart; 
 this.BtStart.Size =新System.Drawing.Size（75，23）; 
 this.BtStart.TabIndex = 0; 
 this.BtStart.Text =开始; 
 this.BtStart.UseVisualStyleBackColor = TRUE; 
 this.BtStart.Click + =新System.EventHandler（this.BtStart_Click）; 
 // 
 // OutStatus 
 // 
 this.OutStatus.Location =新System.Drawing.Point（13，13）; 
 this.OutStatus.Multiline = TRUE; 
 this.OutStatus.Name =OutStatus; 
 this.OutStatus.ReadOnly = TRUE; 
 this.OutStatus.ScrollBars = System.Windows.Forms.ScrollBars.Both; 
 this.OutStatus.Size =新System.Drawing.Size（766，375）; 
 this.OutStatus.TabIndex = 1; 
 // 
 // InConnections 
 // 
 this.InConnections.Location =新System.Drawing.Point（180，397）; 
 this.InConnections.Mask =00; 
 this.InConnections.Name =InConnections; 
 this.InConnections.Size =新System.Drawing.Size（22，20）; 
 this.InConnections.TabIndex = 2; 
 this.InConnections.Text =10; 
 this.InConnections.TextChanged + =新System.EventHandler（this.InConnections_TextChanged）; 
 // 
 // LABEL1 
 // 
 this.label1.AutoSize = TRUE; 
 this.label1.Location =新System.Drawing.Point（108，400）; 
 this.label1.Name =LABEL1; 
 this.label1.Size =新System.Drawing.Size（66，13）; 
 this.label1.TabIndex = 3; 
 this.label1.Text =连接; 
 // 
 // LABEL3 
 // 
 this.label3.AutoSize = TRUE; 
 this.label3.Location =新System.Drawing.Point（289，399）; 
 this.label3.Name =LABEL3; 
 this.label3.Size =新System.Drawing.Size（113，13）; 
 this.label3.TabIndex = 5; 
 this.label3.Text =初始连接等待; 
 // 
 // InInitialWait 
 // 
 this.InInitialWait.Location =新System.Drawing.Point（408，396）; 
 this.InInitialWait.Mask =00; 
 this.InInitialWait.Name =InInitialWait; 
 this.InInitialWait.Size =新System.Drawing.Size（21，20）; 
 this.InInitialWait.TabIndex = 4; 
 this.InInitialWait.Text =60; 
 this.InInitialWait.TextChanged + =新System.EventHandler（this.InConnections_TextChanged）; 
 // 
 // InMultiplier 
 // 
 this.InMultiplier.Location =新System.Drawing.Point（262，396）; 
 this.InMultiplier.Mask =0; 
 this.InMultiplier.Name =InMultiplier; 
 this.InMultiplier.Size =新System.Drawing.Size（21，20）; 
 this.InMultiplier.TabIndex = 2; 
 this.InMultiplier.Text =2; 
 this.InMultiplier.TextChanged + =新System.EventHandler（this.InConnections_TextChanged）; 
 // 
 // LABEL2 
 // 
 this.label2.AutoSize = TRUE; 
 this.label2.Location =新System.Drawing.Point（208，400）; 
 this.label2.Name =LABEL2; 
 this.label2.Size =新System.Drawing.Size（48，13）; 
 this.label2.TabIndex = 3; 
 this.label2.Text =倍增器; 
 // 
 // BtCancel 
 // 
 this.BtCancel.Location =新System.Drawing.Point（13，436）; 
 this.BtCancel.Name =BtCancel; 
 this.BtCancel.Size =新System.Drawing.Size（75，23）; 
 this.BtCancel.TabIndex = 6; 
 this.BtCancel.Text =取消; 
 this.BtCancel.UseVisualStyleBackColor = TRUE; 
 this.BtCancel.Click + =新System.EventHandler（this.BtCancel_Click）; 
 // 
 // label4 
 // 
 this.label4.AutoSize = TRUE; 
 this.label4.Location =新System.Drawing.Point（451，399）; 
 this.label4.Name =label4; 
 this.label4.Size =新System.Drawing.Size（102，13）; 
 this.label4.TabIndex = 7; 
 this.label4.Text =最长连接; 
 // 
 // OutLongestConnection 
 // 
 this.OutLongestConnection.AutoSize = TRUE; 
 this.OutLongestConnection.Location =新System.Drawing.Point（559，399）; 
 this.OutLongestConnection.Name =OutLongestConnection; 
 this.OutLongestConnection.Size =新System.Drawing.Size（102，13）; 
 this.OutLongestConnection.TabIndex = 8; 
 this.OutLongestConnection.Text =最长连接; 
 // 
 // Form1中
 // 
 this.AutoScaleDimensions =新System.Drawing.SizeF（6F，13F）; 
 this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font; 
 this.ClientSize =新System.Drawing.Size（791，582）; 
 this.Controls.Add（this.OutLongestConnection）; 
 this.Controls.Add（this.label4）; 
 this.Controls.Add（this.BtCancel）; 
 this.Controls.Add（this.label3）; 
 this.Controls.Add（this.InInitialWait）; 
 this.Controls.Add（this.label2）; 
 this.Controls.Add（this.InMultiplier）; 
 this.Controls.Add（this.label1）; 
 this.Controls.Add（this.InConnections）; 
 this.Controls.Add（this.OutStatus）; 
 this.Controls.Add（this.BtStart）; 
 this.Name =Form1的; 
 this.Text =Form1的; 
 this.Load + =新System.EventHandler（this.Form1_Load）; 
 this.ResumeLayout（假）; 
 this.PerformLayout（）; 
 
} 
 
 #endregion 
 
私人System.Windows.Forms.Button BtStart; 
私人System.Windows.Forms.TextBox OutStatus; 
私人System.Windows.Forms.MaskedTextBox InConnections; 
私人System.Windows.Forms.Label LABEL1; 
私人System.Windows.Forms.Label LABEL3; 
私人System.Windows.Forms.MaskedTextBox InInitialWait; 
私人System.Windows.Forms.MaskedTextBox InMultiplier; 
私人System.Windows.Forms.Label LABEL2; 
私人System.Windows.Forms.Button BtCancel; 
私人System.Windows.Forms.Label label4; 
私人System.Windows.Forms.Label OutLongestConnection; 
} 
} 
  

I'm trying to migrate to Oracle.ManagedDataAcess from unmanaged version and receiving randoms ORA-12570 TNS:packet reader failure.

I don't know why this error starts, but once it starts, every subsequent request gives the same error for about 10-30 minutes, then it works again for another 10-30 minutes and so on.

So it is a random of subsequent failures for some time then subsequent success

Already tried a lot of things, to resume:

The environment:

• Oracle.ManagedDataAcess version 12.1.2400 (4.121.2.20150926) (nuget) (no gac reference installed on server that could override the bin version)
• Oracle Server Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
• Windows 2012 (Windows Update ok)

Checked:

• Firewall: It is not a firewall problem
• Machine error: The same problem happens on my machine, Azure WebApp and an AWS EC2 Instance
• Interference: There is no sniffer running, transparent proxy etc.
• Encryption: I don't use any kind of encryption (unless there is something enabled by default that I don't know)
• Connections string: The same connection string is working perfectly with the unmanaged version

Aditional information:

• This is a production database, it is very stable
• The application is compiled to anycpu, the IIS app pool is restricted to 64bits
• Im testing exactly the same request every time (just a refresh on a get url of a rest ws, webapi), so it is not related to data format

Configuration:

Server sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Application Web.config

<connectionStrings>
<add name="XXXX" connectionString="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxx)));User Id=xxxxx;Password=xxxxx;" />
</connectionStrings>

<configSections>
    <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</configSections>

<oracle.manageddataaccess.client>
    <version number="*">
      <dataSources>
        <!--<dataSource alias="SampleDataSource" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) " />-->
      </dataSources>
      <settings>
        <setting name="SQLNET.AUTHENTICATION_SERVICES" value="NONE"/> <!--NTS-->
        <setting name="sqlnet.crypto_checksum_server" value="rejected"/>
        <setting name="sqlnet.crypto_checksum_client" value="rejected"/>
        <setting name="SQLNET.ENCRYPTION_SERVER" value="rejected"/>
      </settings>
    </version>
</oracle.manageddataaccess.client>

Some references:

https://community.oracle.com/thread/3634263?start=0&tstart=0

ODP.net managed driver throws ORA-12570: Network Session: Unexpected packet read error

Managed Oracle Client with Oracle Advanced Security Options

ODP.NET error in IIS: ORA-12357 Network Session End of file

UPDATE 1

After pooling changed (as I described as an answer here), I decided to publish a version to do some real test. After 1 day and users complaining about performance I got another error: Value cannot be null. Parameter name: byteArray

I changed the reference back to the unmanaged version and everything was fine again, faster, without bytearray error, better pooling management.

So I'm just giving up of the managed version for now, maybe I will try again on Oracle next release.

Here some references about this new error, as you can see, looks like another bug (still without any answer).

https://community.oracle.com/thread/3676588?start=0&tstart=0

EF + ODP.NET + CLOB = Value Cannot be Null - Parameter name: byteArray?

So far, reasons to not use:

• Pooling management bug
• CLOB null/not null bytearray errors
• Performance degradation probably related to pooling bug

解决方案

In my experience with a similar error 12570 (reader vs writer) there's only one reason for this error - something on your network is terminating idle tcp connections. Typically this is a firewall/managed switch. You said you've already ruled out firewall but I'm not sure how. It's possible that the db itself is terminating the connections (dba script), but I recall that being a different error.

Ora-12571 might be slightly different. But still since you've identified that the issue is the same in that it's long established pool connections I'll keep going.

There's a couple of things you can do:

1. Set min pool size = 0 in the connection string. This generally fixes things for me. It allows the entire pool to be closed out when the app is idle. There is a small chance though that if your traffic swings violently, decr pool size might need to be increased in order to more quickly close out connections created by a mad rush.
2. Set Expire_Time in sqlnet.ora. Not obvious by it's name, this setting sends a prob packet, which causes any tcp idle monitoring to be satisfied. Only issue here is that I'm not entirely sure how to set sqlnet settings with the managed provider. I'm guessing that sqlnet.ora can go in the exe dir but I'm also seeing some indication that it can be set in the .config in the form of (see a similar wallet_override example here) Because you're only getting this in the managed provider, I wonder if your unmanaged client sqlnet.ora already has this setting.

I've seen this many times over the years and the first time it happened I created a utility that basically does a binary search to determine the exact timeout time by creating connections of varying durations. If it consistently lands on the same termination time, you can guess there's a setting somewhere that's causing this. If it's erratic, then you may have some sort of infrastructure flakiness.

Unfortunately I created it as a c# forms app, so I've pasted both the form code and designer code below:

Form1.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;

namespace TimeoutTest
{
  public partial class Form1 : Form
  {
    List<TestConnection> connections;
    Int32 connectionCount;
    Int32 multiplier;
    Int32 initialConnectionWait;

    TestConnection controlConnection = null;

    public Form1()
    {
      InitializeComponent();



    }

    private void BtStart_Click(object sender, EventArgs e)
    {

      connectionCount = Int32.Parse(InConnections.Text);
      multiplier = Int32.Parse(InMultiplier.Text);
      initialConnectionWait = Int32.Parse(InInitialWait.Text);

      DisplayMessage("Starting control connection\r\n");
      controlConnection = new TestConnection();
      controlConnection.ErrorOccured += new EventHandler(controlConnection_ErrorOccured);
      controlConnection.IsControlConnection = true;
      controlConnection.StartTest(2);

      connections = new List<TestConnection>();
      DisplayMessage("Spinning up {0} connections...\r\n", connectionCount);
      for (int i = 0, idleTime=initialConnectionWait; i < connectionCount; i++, idleTime*=multiplier)
      {

        TestConnection connection = new TestConnection();
        connection.Notified += new TestConnection.NotificationEventHandler(connection_Notified);
        connection.ErrorOccured += new EventHandler(connection_ErrorOccured);
        connection.TestCompleted += new EventHandler(connection_TestCompleted);
        connection.StartTest(idleTime);

        connections.Add(connection);
      }
      DisplayMessage("");
    }


    void controlConnection_ErrorOccured(object sender, EventArgs e)
    {
      DisplayMessage("\r\nControl connection error, aborting!!!");
      BtCancel_Click(this, EventArgs.Empty);

    }

    void connection_TestCompleted(object sender, EventArgs e)
    {
      TestConnection currentConnection = (TestConnection)sender;

      if (currentConnection == connections.Last())
        DisplayMessage("\r\nAll tests complete.  Done");

    }

    void connection_ErrorOccured(object sender, EventArgs e)
    {
      //stop any active connection.
      foreach(TestConnection tc in connections)
      {
        tc.CompletionTimer.Enabled=false;
      }

      TestConnection currentConnection = (TestConnection)sender;
      Int32 upperTime = currentConnection.IdleTime;
      Int32 lowerTime = 0;

      Int32 index = connections.IndexOf(currentConnection);
      //if this is not the first connection...
      if(index > 0)
      {
        //...then set the lower time based on the previous connection
        lowerTime = connections[index-1].IdleTime;
      }

      //get the difference between the lower and upper as the new range to work on
      Int32 range = upperTime - lowerTime;


      //divide the range over the number of connections to get the new interval
      Int32 interval = range / this.connectionCount;
      connections.Clear();

      //if the interval is too small try to reduce the number of connections
      while (interval < 2 && this.connectionCount > 2)
      {
        this.connectionCount--;
        DisplayMessage("\r\nConnections too high for current resolution.  Reducing to {0} connections.", this.connectionCount);
        interval = range / this.connectionCount;
      }



      if(interval < 2)
      {
        DisplayMessage("\r\nResolution cannot be increased.  Done.");
      }
      else
      {
        DisplayMessage("\r\nRestarting test with min:{0}, max{1}, resolution{2}.", lowerTime, upperTime, interval);


        //create the new connections
        for (int i = connectionCount-1, idleTime = upperTime-interval; i >= 0; i--, idleTime-=interval)
        {

          TestConnection connection = new TestConnection();
          connection.Notified += new TestConnection.NotificationEventHandler(connection_Notified);
          connection.ErrorOccured += new EventHandler(connection_ErrorOccured);
          connection.TestCompleted += new EventHandler(connection_TestCompleted);
          connection.StartTest(idleTime);

          connections.Insert(0,connection);
        }
        this.connectionCount = connections.Count;
      }

    }
    private void BtCancel_Click(object sender, EventArgs e)
    {
      //stop any active connection.
      foreach (TestConnection tc in connections)
      {
        tc.CompletionTimer.Enabled = false;
        tc.Command.Connection.Close();
      }
      DisplayMessage("Stopped running tests.");
    }


    void connection_Notified(object o, Form1.TestConnection.NotificationEventArgs e)
    {
      DisplayMessage(e.Message);
    }

    private void DisplayMessage(String message)
    {
      DisplayMessage("{0}", message);
    }
    private void DisplayMessage(String message, params Object[] args)
    {
      OutStatus.AppendText(String.Format(message, args) + "\r\n");
    }


    public class TestConnection
    {
      public Boolean IsControlConnection { get; set; }
      public OracleCommand Command { get; private set; }
      public Timer CompletionTimer { get; private set; }
      public String ConnectionId { get; private set; }
      public Int32 IdleTime
      {
        get
        {
          return CompletionTimer.Interval / 1000;
        }
        set
        {
          CompletionTimer.Interval = value * 1000;
        }
      }
      #region Events and Delegates
      public event EventHandler ErrorOccured;
      public event EventHandler TestCompleted;
      public class NotificationEventArgs : EventArgs
      {
        public NotificationEventArgs(String message)
        {
          this.Message = message;
        }
        public String Message { get; set; }
      }

      public delegate void NotificationEventHandler(object o, NotificationEventArgs e);

      public event NotificationEventHandler Notified;

      private void Notify(String message)
      {
        if (Notified != null)
        {
          Notified(this, new NotificationEventArgs(message));
        }
      }
      public void Notify(String format, params object[] args)
      {
        this.Notify(String.Format(format, args));
      }



      #endregion

      public TestConnection()
      {
        CompletionTimer = new Timer();
        CompletionTimer.Tick += new EventHandler(CompleteTest);

        Command = new OracleCommand(
          "select 'saddr:' || saddr || '-sid:' || sid || '-serial#:' || serial# || '-audsid:' || audsid || '-paddr:' || paddr || '-module:' || module  from gv$session where audsid=Userenv('SESSIONID')");

        Command.Connection = new OracleConnection(Configuration.OracleConnectionString);
      }

      public String StartTest(Int32 idleTime)
      {
        Command.Connection.Open();
        ConnectionId = (String)Command.ExecuteScalar();
        Notify("Started test with idle time={0}, id={1}.", idleTime, ConnectionId);
        IdleTime = idleTime;
        CompletionTimer.Enabled = true;
        return ConnectionId;
      }

      private void CompleteTest(object sender, EventArgs e)
      {
        if (!IsControlConnection)
          CompletionTimer.Enabled = false;
        try
        {
          Command.ExecuteScalar();
          Notify("Test complete on connection with idle time={0}, id={1}.", IdleTime, ConnectionId);
          if (TestCompleted != null)
            TestCompleted(this, EventArgs.Empty);
        }
        catch (OracleException ex)
        {
          if (ex.Number == 12571)
          {
            if (ErrorOccured != null)
            {
              Notify("Found error on connection with idle time={0}, id={1}.", IdleTime, ConnectionId);
              ErrorOccured(this, EventArgs.Empty);
            }
          }
          else
          {
            Notify("Unknown error occured on connection with timeout {0}, Error: {1}, \r\n{2}",(IdleTime).ToString(), ex, ConnectionId);

          }
        }
        catch (Exception ex)
        {
          Notify("Unknown error occured on connection with timeout {0}, Error: {1}, \r\n{2}", (IdleTime).ToString(), ex, ConnectionId);
        }
        finally
        {
          if(!IsControlConnection)
            Command.Connection.Close();
        }
      }
    }

    private void InConnections_TextChanged(object sender, EventArgs e)
    {
      Int32.TryParse(InConnections.Text,out connectionCount);
      Int32.TryParse(InMultiplier.Text,out multiplier);
      Int32.TryParse(InInitialWait.Text, out initialConnectionWait);

      OutLongestConnection.Text = (Math.Pow(multiplier,connectionCount-1) * initialConnectionWait).ToString();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
      InConnections_TextChanged(this, EventArgs.Empty);
    }

 }
}

Form1.designer.cs:

namespace TimeoutTest
{
  partial class Form1
  {
    /// <summary>
    /// Required designer variable.
    /// </summary>
    private System.ComponentModel.IContainer components = null;

    /// <summary>
    /// Clean up any resources being used.
    /// </summary>
    /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
    protected override void Dispose(bool disposing)
    {
      if (disposing && (components != null))
      {
        components.Dispose();
      }
      base.Dispose(disposing);
    }

    #region Windows Form Designer generated code

    /// <summary>
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// </summary>
    private void InitializeComponent()
    {
      this.BtStart = new System.Windows.Forms.Button();
      this.OutStatus = new System.Windows.Forms.TextBox();
      this.InConnections = new System.Windows.Forms.MaskedTextBox();
      this.label1 = new System.Windows.Forms.Label();
      this.label3 = new System.Windows.Forms.Label();
      this.InInitialWait = new System.Windows.Forms.MaskedTextBox();
      this.InMultiplier = new System.Windows.Forms.MaskedTextBox();
      this.label2 = new System.Windows.Forms.Label();
      this.BtCancel = new System.Windows.Forms.Button();
      this.label4 = new System.Windows.Forms.Label();
      this.OutLongestConnection = new System.Windows.Forms.Label();
      this.SuspendLayout();
      // 
      // BtStart
      // 
      this.BtStart.Location = new System.Drawing.Point(13, 394);
      this.BtStart.Name = "BtStart";
      this.BtStart.Size = new System.Drawing.Size(75, 23);
      this.BtStart.TabIndex = 0;
      this.BtStart.Text = "Start";
      this.BtStart.UseVisualStyleBackColor = true;
      this.BtStart.Click += new System.EventHandler(this.BtStart_Click);
      // 
      // OutStatus
      // 
      this.OutStatus.Location = new System.Drawing.Point(13, 13);
      this.OutStatus.Multiline = true;
      this.OutStatus.Name = "OutStatus";
      this.OutStatus.ReadOnly = true;
      this.OutStatus.ScrollBars = System.Windows.Forms.ScrollBars.Both;
      this.OutStatus.Size = new System.Drawing.Size(766, 375);
      this.OutStatus.TabIndex = 1;
      // 
      // InConnections
      // 
      this.InConnections.Location = new System.Drawing.Point(180, 397);
      this.InConnections.Mask = "00";
      this.InConnections.Name = "InConnections";
      this.InConnections.Size = new System.Drawing.Size(22, 20);
      this.InConnections.TabIndex = 2;
      this.InConnections.Text = "10";
      this.InConnections.TextChanged += new System.EventHandler(this.InConnections_TextChanged);
      // 
      // label1
      // 
      this.label1.AutoSize = true;
      this.label1.Location = new System.Drawing.Point(108, 400);
      this.label1.Name = "label1";
      this.label1.Size = new System.Drawing.Size(66, 13);
      this.label1.TabIndex = 3;
      this.label1.Text = "Connections";
      // 
      // label3
      // 
      this.label3.AutoSize = true;
      this.label3.Location = new System.Drawing.Point(289, 399);
      this.label3.Name = "label3";
      this.label3.Size = new System.Drawing.Size(113, 13);
      this.label3.TabIndex = 5;
      this.label3.Text = "Initial Connection Wait";
      // 
      // InInitialWait
      // 
      this.InInitialWait.Location = new System.Drawing.Point(408, 396);
      this.InInitialWait.Mask = "00";
      this.InInitialWait.Name = "InInitialWait";
      this.InInitialWait.Size = new System.Drawing.Size(21, 20);
      this.InInitialWait.TabIndex = 4;
      this.InInitialWait.Text = "60";
      this.InInitialWait.TextChanged += new System.EventHandler(this.InConnections_TextChanged);
      // 
      // InMultiplier
      // 
      this.InMultiplier.Location = new System.Drawing.Point(262, 396);
      this.InMultiplier.Mask = "0";
      this.InMultiplier.Name = "InMultiplier";
      this.InMultiplier.Size = new System.Drawing.Size(21, 20);
      this.InMultiplier.TabIndex = 2;
      this.InMultiplier.Text = "2";
      this.InMultiplier.TextChanged += new System.EventHandler(this.InConnections_TextChanged);
      // 
      // label2
      // 
      this.label2.AutoSize = true;
      this.label2.Location = new System.Drawing.Point(208, 400);
      this.label2.Name = "label2";
      this.label2.Size = new System.Drawing.Size(48, 13);
      this.label2.TabIndex = 3;
      this.label2.Text = "Multiplier";
      // 
      // BtCancel
      // 
      this.BtCancel.Location = new System.Drawing.Point(13, 436);
      this.BtCancel.Name = "BtCancel";
      this.BtCancel.Size = new System.Drawing.Size(75, 23);
      this.BtCancel.TabIndex = 6;
      this.BtCancel.Text = "Cancel";
      this.BtCancel.UseVisualStyleBackColor = true;
      this.BtCancel.Click += new System.EventHandler(this.BtCancel_Click);
      // 
      // label4
      // 
      this.label4.AutoSize = true;
      this.label4.Location = new System.Drawing.Point(451, 399);
      this.label4.Name = "label4";
      this.label4.Size = new System.Drawing.Size(102, 13);
      this.label4.TabIndex = 7;
      this.label4.Text = "Longest Connection";
      // 
      // OutLongestConnection
      // 
      this.OutLongestConnection.AutoSize = true;
      this.OutLongestConnection.Location = new System.Drawing.Point(559, 399);
      this.OutLongestConnection.Name = "OutLongestConnection";
      this.OutLongestConnection.Size = new System.Drawing.Size(102, 13);
      this.OutLongestConnection.TabIndex = 8;
      this.OutLongestConnection.Text = "Longest Connection";
      // 
      // Form1
      // 
      this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
      this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
      this.ClientSize = new System.Drawing.Size(791, 582);
      this.Controls.Add(this.OutLongestConnection);
      this.Controls.Add(this.label4);
      this.Controls.Add(this.BtCancel);
      this.Controls.Add(this.label3);
      this.Controls.Add(this.InInitialWait);
      this.Controls.Add(this.label2);
      this.Controls.Add(this.InMultiplier);
      this.Controls.Add(this.label1);
      this.Controls.Add(this.InConnections);
      this.Controls.Add(this.OutStatus);
      this.Controls.Add(this.BtStart);
      this.Name = "Form1";
      this.Text = "Form1";
      this.Load += new System.EventHandler(this.Form1_Load);
      this.ResumeLayout(false);
      this.PerformLayout();

    }

    #endregion

    private System.Windows.Forms.Button BtStart;
    private System.Windows.Forms.TextBox OutStatus;
    private System.Windows.Forms.MaskedTextBox InConnections;
    private System.Windows.Forms.Label label1;
    private System.Windows.Forms.Label label3;
    private System.Windows.Forms.MaskedTextBox InInitialWait;
    private System.Windows.Forms.MaskedTextBox InMultiplier;
    private System.Windows.Forms.Label label2;
    private System.Windows.Forms.Button BtCancel;
    private System.Windows.Forms.Label label4;
    private System.Windows.Forms.Label OutLongestConnection;
  }
}

这篇关于ODP.NET Oracle.ManagedDataAcess随机ORA-12570错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！