Connection problem due to DB server restart

labate · Post by **labate** » Fri 08 Sep 2006 12:48

Hi,

During our integration tests, we noted a problem about the connection.

Suppose I invoke the web service that takes an id and returns a string value read from the db.
Then the Oracle DB has been shutdown and restarted.
If I invoke the web service again, I got an error that I cannot avoid even if I try again and again. Stopping the web site on IIS6 didn't change anything. I had to reboot the web server machine.

I translate en english, because the error is written in french. It is more or less (may not be the orginal english msg) :

Database error ---> Impossible to write the data on the transport connection. --> An existing connexion has been closed by a remote host.

Code: Select all

System.Web.Services.Protocols.SoapException: Erreur de la base de données ---> System.IO.IOException: Impossible d'écrire les données sur la connexion de transport. ---> System.Net.Sockets.SocketException: Une connexion existante a dû être fermée par l'hôte distant
   at System.Net.Sockets.Socket.Send(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
   at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
   --- --- ---
   at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
   at a.a.ai.a(Byte[] A_0, Int32 A_1)
   at a.a.be.d()
   at a.a.al.a()
   at a.a.al.b(Byte[] A_0, Int32 A_1, Int32 A_2)
   at a.a.al.b()
   at a.a.ad.a()
   at a.a.bf.a(Int32 A_0, ay A_1)
   at CoreLab.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
   at CoreLab.Common.DbCommandBase.b(CommandBehavior A_0)
   at CoreLab.Common.DbCommand.ExecuteReader()
   at CoreLab.Common.DbCommandBase.ExecuteNonQuery()
   at WSPolyrightEPFL.TWSPolyrightEPFL.GetSciper(String cardid)
   --- --- ---
   at WSPolyrightEPFL.TWSPolyrightEPFL.GetSciper(String cardid)

Here's my first thoughts:
When I closed the first connection I used, it has then been released to the connection pool.
Then the Oracle server has restarted and the connection in the connection pool was not valid anymore.
As the documentation said the TAF (failover) is not available when working in direct mode (that's my case), so the connection has not been recreated automatically. In could explain why I always get the same error, even if Oracle was up.

So when I instanciate a new connection (retrieve it from the pool), how could I test if it is valid or not?

I read in the MySQLDirect FAQ that a MySqlConnection.Ping() method exist to check if the connection is alive or not. There is no similar method for OraDirect, so have you any other solution (always working in direct mode)?

Many thanks.
Adriano

Alexey · Post by **Alexey** » Mon 11 Sep 2006 10:58

I can't reproduce such behaviour on a local machine with Oracle Database 10g Express Edition using direct mode. Could you provide me with your full configuration and detailed scenario on how to reproduce the error?

labate · Post by **labate** » Mon 11 Sep 2006 12:28

Alexey wrote:I can't reproduce such behaviour on a local machine with Oracle Database 10g Express Edition using direct mode. Could you provide me with your full configuration and detailed scenario on how to reproduce the error?

Hi,

Thanks for answering.
My configuration is :

* Oracle 9i installed on a remote db server.
* Web service installed on a WS2003 with IIS6

I have 2 versions of the web service, one written in Delphi.NET in BDS 2005 (.NET 1.1), the other one in C# in VS2005 (.NET 2.0). They do exactly the same operations.

I have noted now that the problem occurs only in the Delphi version. Except the language, the other big difference between these two versions is the .NET Framework / ASP.NET version : v.1.1 for the Delphi code.
And in consequence, the CoreLab component too, because for the Delphi/BDS2005 version we used OraDirect .NET 3.50 for .NET Framework 1.x and for the C#/VS2005 I used OraDirect .NET 3.50 for .NET Framework 2.0.

The scenario is the following :

1) Oracle is up and running
2) I call the WS (Delphi version), it responds correctly
3) I go on the DB server and stop the Oracle windows service
that corresponds to the Oracle database instance I use
4) I call the WS (Delphi version), it displays the error (see previous msg)
5) I go on the DB server and start the Oracle windows service
6) I call the WS (Delphi version) any number of times, it
always displays the same error as if the Oracle DB was down

For the C#/VS2005 version (working in .NET/ASP.NET 2.0, CoreLab components for .NET 2.0) at the step #6, the web service responds correctly again.

I hope these new details are of any help.

Best regards,
Adriano Labate

labate · Post by **labate** » Mon 11 Sep 2006 12:36

Hi again,

I don't know if this could be of any help, but for the two versions, Delphi and C#, of the web service, the error message is not exacly the same.

I already put the error message for the Delphi version in my first msg.
Here is the error message for the C# code :

Code: Select all

CoreLab.Oracle.OracleException: Network error: ( Une connexion existante a dû être fermée par l'hôte distant )
   à CoreLab.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
   à CoreLab.Common.DbCommandBase.c(CommandBehavior A_0)
   à System.Data.Common.DbCommand.ExecuteReader()
   à CoreLab.Common.DbCommandBase.ExecuteNonQuery()
   à polyright.WSPolyrightEPFL.WSPolyrightEPFLService.GetSciper(String cardid)
   --- Fin de la trace de la pile d'exception interne ---

Best regards,
Adriano

Alexey · Post by **Alexey** » Tue 12 Sep 2006 16:38

.NET1.1 Framework seemingly is not the case, cause it works with C# web service (i checked this as well). So could you send me (AlexeyI at crlab dot com) your web service written in Delphi?