ODP .NET Oracle管理的数据访问随机ORA-12570错误

20jt8wwn  于 2023-02-11  发布在  Oracle
关注(0)|答案(7)|浏览(679)

我正在尝试从非托管版本迁移到Oracle.ManagedDataAcess,并收到随机数ORA-12570 TNS:数据包读取器故障。
我不知道为什么会出现这个错误,但一旦出现,每个后续请求都会在大约10 - 30分钟内出现同样的错误,然后它又会在10 - 30分钟内出现,依此类推。
因此,这是一段时间内的随机后续失败,然后是后续成功
已经尝试了很多东西,恢复:

    • 环境:**
  • Oracle. ManagedDataAcess版本12.1.2400(4.121.2.20150926)(nuget)(服务器上未安装可覆盖bin版本的gac引用)
  • Oracle服务器Oracle数据库12c企业版12.1.0.2.0版-64位生产
  • Windows 2012(Windows更新正常)
    • 已检查:**
  • 防火墙:这不是防火墙的问题
  • 机器错误:同样的问题发生在我的计算机、Azure WebApp和AWS EC2示例上
  • 干扰:没有运行嗅探器、透明代理等。
  • 加密:我不使用任何类型的加密(除非默认启用了我不知道的东西)
  • 连接字符串:相同的连接字符串在非托管版本中运行良好
    • 其他信息:**
  • 这是一个生产数据库,非常稳定
  • 应用程序编译为anycpu,IIS应用程序池限制为64位
  • 我每次测试完全相同的请求(只是一个restws,webapi的geturl的刷新),所以它与数据格式无关
    • 配置:**
    • 服务器sqlnet.ora**
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
    • 应用程序Web.配置**
<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>
    • 部分参考文献:**

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

    • 更新1**

在池发生变化后(正如我在这里描述的答案),我决定发布一个版本来做一些真正的测试。1天后,用户抱怨性能,我得到了另一个错误:值不能为空。参数名:字节数组
我把引用改回了非托管版本,一切都恢复正常,速度更快,没有字节数组错误,池管理更好。
所以我现在只是放弃了托管版本,也许我会在Oracle的下一个版本中再次尝试。
这里有一些关于这个新错误的参考,如你所见,看起来像是另一个bug(仍然没有任何答案)。
https://community.oracle.com/thread/3676588?start=0&tstart=0
EF + ODP.NET + CLOB = Value Cannot be Null - Parameter name: byteArray?
到目前为止,不使用的理由:

  • 池化管理错误
  • CLOB空/非空字节数组错误
  • 性能下降可能与池化错误有关
taor4pac

taor4pac1#

根据我的经验,类似的错误12570(reader vs writer)只有一个原因--你网络上的某个东西终止了空闲的tcp连接。通常这是一个防火墙/管理交换机。你说你已经排除了防火墙,但我不确定是怎么排除的。有可能是数据库本身终止了连接(dba脚本),但我记得那是一个不同的错误。
Ora-12571可能稍有不同,但既然您已经确定了问题是相同的,因为它是长期建立的池连接,我将继续讨论。
您可以做以下几件事:
1.在连接字符串中设置最小池大小= 0。这通常可以解决我的问题。它允许在应用空闲时关闭整个池。但也有一个很小的机会,如果你的流量波动剧烈,decr池大小可能需要增加,以便更快地关闭由疯狂的匆忙创建的连接。
1.在sqlnet. ora中设置Expire_Time。名称不明显,此设置发送一个探测包,这会导致满足任何tcp空闲监视。这里唯一的问题是我不完全确定如何使用托管提供程序设置sqlnet设置。我猜sqlnet.ora可以放在exe目录中,但我也看到一些指示,表明它可以在.config中以(参见类似的wallet_override示例here)因为您只在托管提供程序中获得此设置,所以我想知道您的非托管客户机sqlnet.ora是否已经具有此设置。
多年来我已经多次看到这种情况,第一次发生时我创建了一个实用程序,通过创建不同持续时间的连接,基本上可以执行二进制搜索来确定确切的超时时间。如果它始终停留在相同的终止时间,您可以猜测是某个地方的设置导致了这种情况。如果它不稳定,那么您可能有某种基础设施故障。
不幸的是,我把它创建成了一个c#表单应用程序,所以我把表单代码和设计器代码都粘贴到了下面:
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;
  }
}
ljo96ir5

ljo96ir52#

在禁用池化(Pooling=False)之后,正如@bdn02所建议的,我可以确认它工作了。但是我认为它会影响性能,我担心在没有任何池化的情况下将代码发布到生产环境中(我认为标准值是可以的)。
因此,我尝试了许多配置,看起来Oracle的池管理不知何故(不是很清楚)引发了一个ORA-12570错误,一段时间后,会话关闭,应用程序再次工作。
为了找到启用池的最佳配置,我创建了一个测试应用程序来启动50个线程(每个线程每50 ms执行一次测试),并降低默认池值,直到错误停止。这样我就能够获得一个最佳配置,稳定,没有任何错误。
显然,它并不适用于每个服务器,但这是我的最终连接字符串配置:

Pooling=true;Min Pool Size=1;Connection Lifetime=180;Max Pool Size=50;Incr Pool Size=5
slmsl1lt

slmsl1lt3#

我将提供一个powershell脚本,用于检查数据库连接。$baselogpath = "" $filesuffix = "_GetDBConnection" $dbuser ="" $dbpassword ="" $dbalias = ""; $command = new-Object Oracle.DataAccess.Client.OracleCommand($queryString, $connection); $connection.Open(); $count = $command.ExecuteScalar(); $connection.Close(); $message = "Records found: " + $count; $esito = "OK"; } Catch { $message = $_.Exception.Message; $esito = "KO"; } $now = Get-Date $filename = $baselogpath + $now.Year + $now.Month.Tostring("00") + $now.Day.Tostring("00") + $filesuffix + ".log" if (!(Test-Path($filename))) { $fileheader = "Time Esito, Elapsed, Message" $fileheader > $filename } $Time.Stop(); $Elapsed = $Time.Elapsed; $row = $now.Hour.toString("00") + ":" + $now.Minute.toString("00") + ":" + $now.Second.toString("00") + " " + $esito + "," + $Elapsed.Hours.toString("00") + ":" + $Elapsed.Minutes.toString("00") + ":" + $Elapsed.Seconds.toString("00") + "," + $message; $row >> $filename
您是否可以尝试使用Oracle托管dll的托管版本每隔一分钟调度此脚本?如果问题仅发生在Web应用程序上,或者与Oracle托管驱动程序相关,我可以理解。如果您希望进行高级测试,您可以调度此脚本的副本,该脚本使用oracle.dataaccess的非托管版本。
祝你好运

blmhpbnm

blmhpbnm4#

我在使用SQL Server Reporting Services 2016和ODAC 12c版本4时遇到了同样的间歇性错误:

Error:
An error has occurred during report processing. (rsProcessingAborted) 
Query execution failed for dataset 'TermPrompt'. (rsErrorExecutingCommand) 
ORA-12570: Network Session: Unexpected packet read error 
ORA-12570: Network Session: Unexpected packet read error 
ORA-12537: Network Session: End of file

将池参数Data Source="myOracleDB";Pooling="false"添加到SSRS中的Oracle数据源完全解决了该问题。
立即重新执行报告可以正常工作。
我意识到每次创建一个新连接而不是使用池会有潜在的性能问题,但是在Oracle修复这个问题之前,我不希望我的用户遇到这个错误。

6tqwzwtp

6tqwzwtp5#

我在应用程序异常中收到此错误。内部异常中没有更多有用的详细信息。更改池选项无法修复此问题,禁用池也无法修复此问题。启用跟踪后,跟踪文件中会显示另一个错误“ORA-12537 network session end of file(不传播到应用程序异常)。那个线程暗示oracle驱动程序的旧版本是罪魁祸首。我检查了一下,而且我用的是2014年的版本,升级到2017/12. 2 c/12. 2. 0. 1. 0版本解决了这个问题。

dphi5xsq

dphi5xsq6#

KeepAlive设置,可以防止连接丢失的池错误,如ORA-12571等。(而不是禁用池)
问题和解决方案:https://github.com/oracle/dotnet-db-samples/issues/58
在打开连接之前设置connection.KeepAlive = true;。您还需要具有不低于2.19.100的Oracle.ManagedDataAccess.Core版本(不了解非核心应用程序)。
我在生产版本中偶尔会遇到难以理解的池错误“Oracle错误ORA-12571远程主机强制关闭了现有连接”和“ORA-03135:连接失去联系”与.Net Core 3.0和设置帮助。

fruv7luv

fruv7luv7#

多年来我一直随机收到这个错误,因为我的连接在很多方面都丢失了。
我通过使用失败后重试方法解决了这个问题:

public string GetData(int retry = 3)
{
    string prop = string.Empty;
    try
    {
        var connectionString = "...";
        using (var conn = new OracleConnection(connectionString))
        {
            string sql = "SELECT NAME FROM USERS WHERE ID = 10";

            conn.Open();
            var cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            var dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                prop = dr["NAME"].ToString();
            }
        }

        return prop;
    }
    catch (OracleException ex)
    {
        //Retry on lost connection errors
        //In tests conducted, the following errors tend to occur when the connection is interrupted between the Open method and the ExecuteReader.
        if (ex.Number == 12570 || ex.Number == 03135 || ex.Number == 12571)
        {
            if (retry == 0)
                throw;

            return GetData(retry - 1);
        }

        throw;
    }
}

使用上面的示例方法,您可以执行以下操作:

  • 创建要使用的扩展方法,如ExecuteReaderWithRetry()和ExecuteNonQueryWithRetry(),并使用它们代替原始方法。
  • 创建一个类来封装此逻辑并调用它而不是Oracle类。
    如果使用实体框架,则可以配置执行策略,以便在发生错误(12571、12570、03135等)时重试。
    下面是实体框架核心中的一个示例:
public class RetryOnErrorStrategy : OracleRetryingExecutionStrategy
{
    private static readonly IList<int> ErrorNumbersToRetry = new List<int>()
    {
        12570, 12571, 03135 //lost connection errors
    };

    public SomeRetryStrategy(ExecutionStrategyDependencies dependencies)
        : base(dependencies, DefaultMaxRetryCount, DefaultMaxDelay, ErrorNumbersToRetry)
    {
    }
}

public class YourDbContext : DbContext
{
    public YourDbContext(string connectionString)
       : base(OracleDbContextOptionsExtensions.UseOracle(new DbContextOptionsBuilder(), connectionString, oracleOptions =>
       {
           oracleOptions.ExecutionStrategy(dependencies =>
               new RetryOnErrorStrategy(
               dependencies));
       }).Options)
    {

    }

    public DbSet<SomeEntity> SomeEntity { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
       ....
    }
    
    ....
}

参见:
Answer about execution extrategy
ORA 12570 on Azure

相关问题