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

ORA-03113: end-of-file on communication channel after long inactivity in ASP.Net app

提问by Greg Hurlman

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:

我在 IIS5 上有一个负载平衡的（不使用会话状态）ASP.Net 2.0 应用程序运行回单个 Oracle 10g 服务器，使用版本 10.1.0.301 的 ODAC/ODP.Net 驱动程序。在长时间不活动（几个小时）之后，应用程序似乎是随机的，将抛出 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()

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

例外：ORA-03113：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 行为) 在 Oracle.DataAccess.Client.OracleCommand.System.Data.IDbCommand.ExecuteReader()

...堆栈的 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 中以确保正确的连接关闭，并且整个过程都包含在 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 部门核实，防火墙未设置为终止这些服务器之间的连接。

采纳答案by mathewbutler

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:

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

1. A network issue - faulty connection, or firewall issue
2. The server process on the database that is servicing you died unexpectedly.

1. 网络问题 - 连接错误或防火墙问题
2. 为您提供服务的数据库上的服务器进程意外死亡。

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 中搜索SQLNET.EXPIRE_TIME. 这是一个 sqlnet.ora 参数，它将以可配置的时间间隔定期发送网络数据包，即：设置此项将使防火墙相信连接是有效的。

For 1) (network) speak to your network admin (connection could be unreliable)

对于 1) ( network) 与您的网络管理员交谈（连接可能不可靠）

For 2) Check the alert.logfor errors. If the server process failed there will be an error message. Also 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.comwith a suitable Customer Service Identifier (CSI)

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

回答by Christian13467

Add Validate Connection=trueto your connection string.

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

Look at this blogto 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.

使用验证连接，实际连接在 Open() 方法中进行验证。

回答by hamishmcn

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)

检查是否有防火墙在一段时间后终止连接（这是我们遇到类似问题的原因）

回答by Mathias Stanley

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 ARCHIVELOGtry to change into NOARCHIVELOG

如果它ARCHIVELOG试图改变成NOARCHIVELOG

by using sqlplus

通过使用 sqlplus

• startup mount
• alter database noarchivelog;
• alter database open;

• 启动挂载
• 更改数据库 noarchivelog；
• 更改数据库打开；

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 area 可能你的flashrecovery area已满->然后确认你的flashrecovery area有空间你可以把你的数据库改成 ARCHIVELOG

回答by Rajesh

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.

更正空间问题后，此特定错误消息消失了。

回答by Ken Wren

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 分钟。

回答by Brad Bruce

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 将挂起！！！！你不会得到一个错误条件来管理，只是一个完整的挂起！修复它的唯一方法是关闭连接池。一旦我们这样做了，所有的问题都迎刃而解。

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 为我们处理它呢？？？？