ORA-03113：ASP.Net应用程序长时间不活动后，通信通道上的文件结束

I've got a load-balanced (not using Session state) ASP.Net 2.0 app on IIS5 running back to a single Oracle 10g server, using version 10.1.0.301 of the ODAC/ODP.Net drivers. After a long period of inactivity (a few hours), the application, seemingly randomly, will throw an Oracle exception:

我使用版本10.1.0.301的ODAC / ODP.Net驱动程序在IIS5上运行负载平衡(不使用会话状态)ASP.Net 2.0应用程序,运行回单个Oracle 10g服务器。在长时间不活动(几个小时)之后,应用程序看似随机,将抛出Oracle异常:

Exception: ORA-03113: end-of-file on communication channel at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.DataAccess.Client.OracleCommand.System.Data.IDbCommand.ExecuteReader()

例外:ORA-03113:Oracle.DataAccess.Client上Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode,OracleConnection conn,IntPtr opsErrCtx,OpoSqlValCtx * pOpoSqlValCtx,Object src,String procedure)的通信通道上的文件结束Oracle.DataAccess.Client.OracleCommand.System.Data.IDbCommand.ExecuteReader()中的.OracleCommand.ExecuteReader(Boolean requery,Boolean fillRequest,CommandBehavior behavior)

...Oracle portion of the stack ends here...

...堆栈的Oracle部分在这里结束......

We are creating new connections on every request, have the open & close wrapped in a try/catch/finally to ensure proper connection closure, and the whole thing is wrapped in a using (OracleConnection yadayada) {...} block. This problem does not appear linked to the restart of the ASP.Net application after being spun down for inactivity.

我们正在为每个请求创建新的连接,在try / catch / finally中包含open和close以确保正确的连接关闭,并且整个事务都包含在using(OracleConnection yadayada){...}块中。此问题似乎与在停用不活动后重新启动ASP.Net应用程序相关联。

We have yet to reproduce the problem ourselves. Thoughts, prayers, help?

我们还没有自己重现这个问题。思想,祈祷,帮助?

More: Checked with IT, the firewall isn't set to kill connections between those servers.

更多:通过IT检查,防火墙未设置为终止这些服务器之间的连接。

7 个解决方案

#1

ORA-03113: end-of-file on communication channel

ORA-03113:通信信道上的文件结束

Is the database letting you know that the network connection is no more. This could be because:

数据库是否让您知道网络连接不再存在。这可能是因为:

A network issue - faulty connection, or firewall issue

网络问题 - 连接错误或防火墙问题

The server process on the database that is servicing you died unexpectedly.

服务于您的数据库上的服务器进程意外死亡。

For 1) (firewall) search tahiti.oracle.com for SQLNET.EXPIRE_TIME. This is a sqlnet.ora parameter that will regularly send a network packet at a configurable interval ie: setting this will make the firewall believe that the connection is live.

对于1)(防火墙)搜索tahiti.oracle.com for SQLNET.EXPIRE_TIME。这是一个sqlnet.ora参数,它将定期以可配置的间隔发送网络数据包,即:设置这将使防火墙认为连接是活动的。

For 1) (network) speak to your network admin

1)(网络)与您的网络管理员联系

For 2) Check the alert.log for errors, if the server process failed there will be an error message here and a trace file will have been written to enable support to identify the issue. The error message will reference the trace file.

对于2)检查alert.log是否存在错误,如果服务器进程失败,则此处将显示错误消息,并且将编写跟踪文件以启用支持以识别问题。错误消息将引用跟踪文件。

Support issues can be raised at metalink.oracle.com with a suitable Customer Service Identifier (CSI)

可以使用合适的客户服务标识符(CSI)在metalink.oracle.com上提出支持问题

#2

Add Validate Connection=true to your connection string.

将Validate Connection = true添加到连接字符串。

Look at this blog to find more about.

查看此博客以了解更多信息。

DETAILS: After OracleConnection.Close() the real database connection does not terminate. The connection object is put back in connection pool. The use of connection pool is implicit by ODP.NET. If you create a new connection you get one of the pool. If this connection is "yet open" the OracleConnection.Open() method does not really creates a new connection. If the real connection is broken (for any reason) you get a failure on first select, update, insert or delete.

详细信息:在OracleConnection.Close()之后,真正的数据库连接不会终止。连接对象将放回连接池中。 ODP.NET隐含了连接池的使用。如果您创建新连接,则会获得其中一个池。如果此连接“尚未打开”,则OracleConnection.Open()方法实际上不会创建新连接。如果真正的连接中断(由于任何原因),您在首次选择,更新,插入或删除时会出现故障。

With Validate Connection the real connection is validated in Open() method.

使用Validate Connection,可以在Open()方法中验证实际连接。

#3

Check that there isn't a firewall that is ending the connection after certain period of time (this was the cause of a similar problem we had)

检查在一段时间后没有防火墙结束连接(这是我们遇到类似问题的原因)

#4

end-of-file on communication channel:

沟通渠道上的文件结尾:

One of the course of this error is due to database fail to write the log when its in the stage of opening;

此错误的过程之一是由于数据库在打开阶段时无法写入日志;

Solution check the database if its running in ARCHIVELOG or NOARCHIVELOG

解决方案检查数据库是否在ARCHIVELOG或NOARCHIVELOG中运行

to check use

检查使用

select log_mode from v$database;

if its on ARCHIVELOG try to change into NOARCHIVELOG

如果它在ARCHIVELOG上尝试更改为NOARCHIVELOG

by using sqlplus

通过使用sqlplus

startup mount
alter database noarchivelog;

alter database noarchivelog;

alter database open;

alter database open;

if it works for this

如果它适用于此

Then you can adjust your flashrecovery area its possibly that your flashrecovery area is full -> then after confirm that your flashrecovery area has the space you can alter your database into the ARCHIVELOG

然后你可以调整你的flashrecovery区域可能你的flashrecovery区域已满 - >然后在确认你的flashrecovery区域有空间后你可以将数据库改成ARCHIVELOG

#5

This error message can be thrown in the application logs when the actual issue is that the oracle database server ran out of space.

当实际问题是oracle数据库服务器空间不足时,可以在应用程序日志中抛出此错误消息。

After correcting the space issue, this particular error message disappeared.

纠正空间问题后,此特定错误消息消失。

#6

You could try this registry hack:

你可以尝试这个注册表黑客:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"DeadGWDetectDefault"=dword:00000001
"KeepAliveTime"=dword:00120000

If it works, just keep increasing the KeepAliveTime. It is currently set for 2 minutes.

如果它工作,只需继续增加KeepAliveTime。它目前设置为2分钟。

#7

The article previously mentioned is good. http://forums.oracle.com/forums/thread.jspa?threadID=191750 (as far as it goes)

前面提到的文章很好。 http://forums.oracle.com/forums/thread.jspa?threadID=191750(就目前而言)

If this is not something that runs frequently (don't do it on your home page), you can turn off connection pooling.

如果这不是经常运行的东西(不要在主页上执行),则可以关闭连接池。

There is one other "gotcha" that is not mentioned in the article. If the first thing you try to do with the connection is call a stored procedure, ODP will HANG!!!! You will not get back an error condition to manage, just a full bore HANG! The only way to fix it is to turn OFF connection pooling. Once we did that, all issues went away.

文章中没有提到另外一个“问题”。如果您尝试连接的第一件事是调用存储过程,ODP将会挂起!你不会得到一个错误的条件来管理,只是一个完整的HANG!解决此问题的唯一方法是关闭连接池。一旦我们这样做,所有问题就消失了。

Pooling is good in some situations, but at the cost of increased complexity around the first statement of every connection.

在某些情况下,池化是很好的,但是以每个连接的第一个语句的复杂性为代价。

If the error handling approach is so good, why don't they make it an option for ODP to handle it for us????

如果错误处理方法非常好,为什么它们不能让ODP为我们处理它?

#1