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:
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...
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.
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.
This question is related to
oracle
oracle10g
odp.net
oracleexception
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)
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
to check use
select log_mode from v$database;
if its on ARCHIVELOG try to change into NOARCHIVELOG
by using sqlplus
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
The article previously mentioned is good. http://forums.oracle.com/forums/thread.jspa?threadID=191750 (as far as it goes)
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.
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????
Add Validate Connection=true to your connection string.
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.
With Validate Connection the real connection is validated in Open() method.
Add Validate Connection=true to your connection string.
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.
With Validate Connection the real connection is validated in Open() method.
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
to check use
select log_mode from v$database;
if its on ARCHIVELOG try to change into NOARCHIVELOG
by using sqlplus
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
The article previously mentioned is good. http://forums.oracle.com/forums/thread.jspa?threadID=191750 (as far as it goes)
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.
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????
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.
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)
The article previously mentioned is good. http://forums.oracle.com/forums/thread.jspa?threadID=191750 (as far as it goes)
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.
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????
This error message can be thrown in the application logs when the actual issue is that the oracle database server ran out of space.
After correcting the space issue, this particular error message disappeared.
The article previously mentioned is good. http://forums.oracle.com/forums/thread.jspa?threadID=191750 (as far as it goes)
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.
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????
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)
The article previously mentioned is good. http://forums.oracle.com/forums/thread.jspa?threadID=191750 (as far as it goes)
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.
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????
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)
This error message can be thrown in the application logs when the actual issue is that the oracle database server ran out of space.
After correcting the space issue, this particular error message disappeared.
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)
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.
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
to check use
select log_mode from v$database;
if its on ARCHIVELOG try to change into NOARCHIVELOG
by using sqlplus
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
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.
This error message can be thrown in the application logs when the actual issue is that the oracle database server ran out of space.
After correcting the space issue, this particular error message disappeared.
The article previously mentioned is good. http://forums.oracle.com/forums/thread.jspa?threadID=191750 (as far as it goes)
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.
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????
This error message can be thrown in the application logs when the actual issue is that the oracle database server ran out of space.
After correcting the space issue, this particular error message disappeared.
Add Validate Connection=true to your connection string.
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.
With Validate Connection the real connection is validated in Open() method.
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
to check use
select log_mode from v$database;
if its on ARCHIVELOG try to change into NOARCHIVELOG
by using sqlplus
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
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.
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)
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.
Source: Stackoverflow.com