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:
Checked:
Aditional information:
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:
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:
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; } }
After disabling pooling (Pooling=False), as @bdn02 suggested, I could confirm that it worked. However I think it should affect the performance and I was concerned about publishing this code into production without any pooling (I thought the standard values were ok).
So I tried many configurations and looks like somehow (it is not very clear) the pool management of oracle was raising an ORA-12570 error and, after a period of time, the sessions are closed and the application worked again.
To find the best configuration with pooling enabled I created a test application to start 50 threads (each one doing 1 test each 50ms), and decreased the default pool values until the error stoped. This way I was able to get an optimal configuration, stable, without any errors.
Obviously it does not applies to every server, but this is my final connection string configuration:
Pooling=true;Min Pool Size=1;Connection Lifetime=180;Max Pool Size=50;Incr Pool Size=5
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With