Re: Connection issue while connecting to Oracle database.

[David Browne]

Hello everyone,

We have developed a windows forms application using C# and access
Oracle8i database using web services developed using asp.net. The
version of the Oracle database we use is 8.1.7. Both the web server
and db are on windows 2000 servers.

We can connect to the database and work on the application, but after
3-4 hours the application looses connection to the database and this
happens even if no one uses the application also. I can start working
again just by opening and saving the web.config file. (this probably
is resetting the connection pool).

The errors we get are

ORA-03113: end-of-file on communication channel
and
ORA-03114: not connected to ORACLE

I have seen many threads posted earlier with same or similar issues.
Could any of you get a solution to this?

Your help is greatly appreciated as the application is developed and
runs, but cannot be used.

There may be a number of causes of this.
But they all have the same solution.

The root problem is that when connections sit in the connection pool for a
long time, they may break. In your case this may be caused by a firewall
closing your connection, or your Oracle Server going down for reboot, or an
intermediate switch going down, etc.

In general over a span of hours some connections can be broken. To account
for this you should validate the connection after retrieving it from the
connection pool. Then if you get ORA-03114 or ORA-03113, you can just open
a new connection. To validate your connection, send a cheap command back to
the server. I recommend this one:

alter session set NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss'

Which kills two birds, because you don't have to use Oracle's silly default
date format.

David

David

 

[Nanda Kumar Majeti]

Hi David,

Thanks for you reply and I will be trying your suggestion. But, from
your message i understand that you think there could be a network issue.
But, the Oracle server was not rebooted (the connection loss happens at
midnight, over the weekend when there is no one working) and there is no
firewall between these two servers.
These two servers are in a workgroup (and not yet under a domain). Could
this cause any problem?

Thanks again for your help
nandu

 

[Nanda Kumar Majeti]

Hi David,

Let me give you some more information that might be helpful.

We have installed Oracle client on the web server to connect and access
Oracle server. The .net web application uses the client to access the
database. There is one other J2EE based website hosted on the same web
server with IBM websphere application server that is using the same
database and runs properly. This website connects directly to the oracle
server without using the client. This, ofcourse uses a different
connection pool altogether.

Thanks
nandu

 

[Robert]

Hi,

Which provider you are using to connect Oracle
Database.Pls explain your more clearly.

 

[Nanda Kumar Majeti]

Hi,

We are using OLEDB provider (for Microsoft) to connect to the database.
The web server and the database are on two different physical machines.
Oracle 8i client (version 8.1.7) is installed on the web server and a
local named service created on the client is used to connect to the
database.

One more finding - The error does not come and the application runs well
if we disable connection pooling (pooling = false in the
connectionstring). So, we feel that this issue is related to connection
pool. As David mentioned in his response, the pool seems to be getting
expired.

thanks
nandu

 

[Robert Raj]

Hi Nandu,

Any progress on your issue....did you fix the problem.I am also running
with the same problem.If you fix the problem kindly let me know how it is
fixed.


Thanks
Robert

 

[Nanda Kumar Majeti]

Hi Robert,

We investigated some more, but still don't have a solution. We realized
that the issue is related to the connection pooling and this error does
not come if the connection pooling is disabled.
Our middle-tier code and the database are on two different servers and
this error is not reproduced if the middle-tier is on the db server
(both on the same server).

Our current assumption is that the issue is related to Oracle and we are
planning to contact Oracle support.

Sorry for not being of much help.

nandu