drop connection

windows 2000.8.1.6.

Our production database drops connection from 30minutes to 6 hours without any reason . One of our DBA
already tested out the connection in DMZ area and found out connection drops
periodically. We need to fix this issue as soon as possible. Following are the two error messages occurs when try to connect to the
outside server.
ORA-02068: following severe error from OPSWEB ORA-03113: end-of-file on
communication channel
Or
ORA-02068: following severe error from OPSWEB ORA-03114: not connected to
ORACLE

disrupts in N/W can cause this err

You going through a firewall? Have you configured SQLNET.EXPIRE_TIME?

yes i am . i already tried configuring the SQLNET.EXPIRE_TIME . conncetion still droped

You going through a firewall? Have you configured SQLNET.EXPIRE_TIME?

Anything interesting in the alert log, or listener log? Any trace files being generated?

I found a similar situation when going through a firewall.
The connection would disconnect only when idle for a certain amount of time. It was definitely not on the server or database side, but had to do with the firewall configs.

Once we moved the server inside the firewall the disconnects dissapeared.

No nothing at all. It's been going on for past several months now . I opened TAR with Oracle but they are insisting this is network issue and our net work team insisting oracle issue. I m figuring it out how should I resolve this.

Open a SQL session locally (on the server) see if that gets disconnected. If it does than you have ruled out most networking possibilities. If it does not than you have some proof to support your networking theory.

Also, if the times are random I doubt there is a procedure in place that causes this. Come to think of it I do not know of a way to terminate sessions after X time.

MH

who is your firewall vendor?

Mr.Hanky
sql sessions drops after 45 mins.i checked that already

Quote:

---

Originally posted by mehak
Mr.Hanky
sql sessions drops after 45 mins.i checked that already

---

Is it always after exactly 45 minutes? Is that 45 minutes since connection established, or 45 minutes after connection goes idle?

if session stays idle for 45 mins it drops exactally after 45 mins

Quote:

---

Originally posted by mehak
if session stays idle for 45 mins it drops exactally after 45 mins

---

That rules out a network problem. You did this locally, correct?

What algorhythms are out there that will kill idle sessions after XX minutes?

MH

I'm betting this is a firewall issue, unless of course the Oracle profile says to kill the connection after 45 idle minutes.

Quote:

---

Originally posted by Mr.Hanky
You did this locally, correct?

What algorhythms are out there that will kill idle sessions after XX minutes?

MH

---

Might ther be any algorithm developed to kill idle seession after 45 mins, but this reasoning for the above ora - err seems no relevence..

If Session is killed u get an Error "ORA-00028: your session has been killed"..but hez getting "ORA-02068"...so its suerly related to N/W issues..

Abhay.

Quote:

---

Originally posted by abhaysk
If Session is killed u get an Error "ORA-00028: your session has been killed"..but hez getting "ORA-02068"...so its suerly related to N/W issues..

---

It's not that simple, I think. The 2068 error refers to losing a connection needed for a database link.

Quote:

---

Originally posted by slimdave
It's not that simple, I think. The 2068 error refers to losing a connection needed for a database link.

---

I really meant the error ORA-03113 end-of-file on communication channel
and 2068 was jus in a hurry to copy paste from above/original post...

Well 03113 also states if any session being killed/disconnected immediate this error can be seen, but to my notice i never had Ora 03113 when session is even killed immediate..

Most likely cause is the N/W..for this kinda errors..

Abhay.

have you thought to involve the network admin and have them check the logs/events on the firewall and or the switch in front of the firewall?

Since when are Oracle errors and the exact cause of a problem an exact science?

:rolleyes:

MH

yeah i checked with Network team according to them problem is without question revolving around the Oracle software or its interaction with the Windows Operating System.

What evidence do the network guys have that it is Oracle?
If they have information which points towards Oracle then we should be told.

Actually now that I think about it, I'm not sure that logging in locally will bypass the network. Anyone?

MH

this is the answer from network admin.

The Oracle Software is transmitting frames with invalid TCP checksums, the firewall's job is to detect malformed or out of the ordinary TCP frames/streams. When the Firewall detects a 'bad' frame it's function is to drop the frame and/or terminated the TCP conversation. Oracle has conveniently ignored this problem every time it has been run by them, and frankly I want a valid explanation as to why their software transmits frames with bad headers, please...THAT is why you are being disconnected. There are *NO* underlying network issues here. I have proven this repeatedly, furthermore the thousands of users that make TCP connections through the DOT firewall to the DOT DMZ without a problem every day lends irrefutable support to my point.

You have a firewall log which shows that Oracle is sending bad packets?
Have you sent it to Oracle?

Quote:

---

Originally posted by mehak
this is the answer from network admin.

The Oracle Software is transmitting frames with invalid TCP checksums, the firewall's job is to detect malformed or out of the ordinary TCP frames/streams. When the Firewall detects a 'bad' frame it's function is to drop the frame and/or terminated the TCP conversation. Oracle has conveniently ignored this problem every time it has been run by them, and frankly I want a valid explanation as to why their software transmits frames with bad headers, please...THAT is why you are being disconnected. There are *NO* underlying network issues here. I have proven this repeatedly, furthermore the thousands of users that make TCP connections through the DOT firewall to the DOT DMZ without a problem every day lends irrefutable support to my point.

---

Your network people are a bit sparky, aren't they?

So does Oracle only start sending these malformed or "out-of-the-ordinary" TCP frames/streams after 45 minutes or something?

It sounds, reading between the lines, as if this is some kind of known issue ("Oracle has conveniently ignored this problem every time it has been run by them..."). Does the NA mean "run by them by me" or "run by them by the firewall manufacturer" or "run by them by the entire networking community"?

You see this is either something that is only happening on your installation, in which case Oracle should do something about it, or it happens on every oracle installation, but the firewall is configured to be too anal about what it rejects (compared to every other firewall in the world that Oracle passes TCP through).

Here ya go, use this to open a can of whoopass on your network admin

http://www.thetabletop.com/images/Can%20Opener.JPG

Perhaps developers are not the only people in need of frequent severe beatings?

I'll have to re-think my strategy.
And clean my gun collection.

:D

MH

Quote:

---

Originally posted by mehak

The Oracle Software is transmitting frames with invalid TCP checksums, the firewall's job is to detect malformed or out of the ordinary TCP frames/streams. When the Firewall detects a 'bad' frame it's function is to drop the frame and/or terminated the TCP conversation. Oracle has conveniently ignored this problem every time it has been run by them, and frankly I want a valid explanation as to why their software transmits frames with bad headers, please...THAT is why you are being disconnected. There are *NO* underlying network issues here. I have proven this repeatedly, furthermore the thousands of users that make TCP connections through the DOT firewall to the DOT DMZ without a problem every day lends irrefutable support to my point.

---

This guy definately needs a smackdown.

Save this E-mail, gather all your facts and then nail him to a cross. Unless of course he is correct, then you have some humble pie to eat.
My manager would slap me if I sent out an E-mail like this.

MH

There's a guy i work with who was in the habit of typing email sentences in upper case all the time -- i told him to cut it out, because it meant he was shouting, right? He replied (via email, cc'ed to the whole team) "Whenever I've come across it before it just indicated excitement". What a jerk.

I opened sql session on server and it never drop however the client session dropped exactly after 45 mins.
Does that mean there is some sort of time restriction on the network port which is killing the session in the specified amount of time?

Quote:

---

Originally posted by mehak
I opened sql session on server and it never drop however the client session dropped exactly after 45 mins.
Does that mean there is some sort of time restriction on the network port which is killing the session in the specified amount of time?

---

According to your network admin, the firewall is killing the connection, no?

Is there a firewall log that shows Oracle is sending bad packets?
Where is the evidence that Oracle sends bad packets?

Without this evidence the network guy's argument falls apart.

Without it his argument reduces to this:

The firewall closes connections that have been idle for 45 minutes.

Quote:

---

Originally posted by mehak
I opened sql session on server and it never drop however the client session dropped exactly after 45 mins.
Does that mean there is some sort of time restriction on the network port which is killing the session in the specified amount of time?

---

So you can suerly conclude its due N/W disc/dirupts ...

Quote:

---

Originally posted by Mr.Hanky
Since when are Oracle errors and the exact cause of a problem an exact science?

---

BTW, Mr Hanky i guess some times u can very well tell that Ora Err & cause an exact science ... Ain't we??

Abhay.

So you can suerly conclude its due N/W disc/dirupts ...

I wish i can conclude it to be a N/W issue ,but this is what there respond to my email from our network team about session not disconnecting on server but only on client side it drops
-
"Most likely the session timeout are a result of the checksum errors. We have nothing on our firewall that would equate to a timer to drop you after 45 minutes"

"Most likely the session timeout are a result of the checksum errors."

Do the network guys admit the firewall is closing the connections?

I ask again.
What evidence do they have that Oracle is sending bad packets?

N/W is not admitting the fact that it is due to firewall. About the bad packet, here is the oracle analysis which Is again denied by our network team , I don’t know what steps should I take to convince my boss this has nothing to do with oracle .

ANALYSIS
===============
Well the basic issue here is the firewall is dropping the connections and its logs show
Frame 1 checksum is CORRECT
Frame 2 checksum is CORRECT
Frame 3 checksum is INCORRECT
Frame 4 checksum is INCORRECT

The ORA-3113 and ORA-12571 are expected bahaviors and reported error messages if the connection is severed by the firewall software.
the question becomes what do those errors mean from the firewall software?

What do those Frame checksum errors mean?
Is it only oracle connections being cut?
If the Oracle connection is working properly inside the firewall then the issue would have to be addressed from the underneath networking layers to insure you dont have packet drops or packet corruption on the network layer.

ACTION PLAN
===============
1. Have the network admin onsite research the Frame checksum errors the firewall is producing.
2. Try the same application inside the firewall and see if the application works as expected then you further prove the reason the firewall detects issues is outside of oracle.

STATUS
=======
@cus


03-DEC-03 18:40:25 GMT

Email Update button has been pressed -- Sending email.

08-DEC-03 19:48:48 GMT


ANALYSIS
===========
The bad packet view would be valid assuming the Oracle software failed inside the firewall also.
From the sounds of it the firewall software is performing checksums on some level and the firewall is detecting issues.

In this case the Oracle software is not responsible for the checksum errors being detected. If they were in fact bad packets the users internal to the firewall would not work correctly.

I will go ahead and deactivate the issue for a two week period pending further need.
This issue will be due to the firewall but leaving the deactivated period in case further questions are needed from Oracle.

Quote:

---

Originally posted by Rickenbacker
I ask again.
What evidence do they have that Oracle is sending bad packets?

---

to add, eactly after 45 mins ...

Yes indeed, I forgot to include the crucial 45 minute interval.

Have the network guys contacted the firewall vendor about these errors?

Oracle do pose a good question:

"Is it only oracle connections being cut?"

Can you try setting up an idle session using telnet, or some other program from outside the firewall?

No They didn't contacted vendor .

Users from our intranet work with a web based interface, normally the software client is MS Internet Explorer.On the interface they have some screens that allow them to read, edit, and
insert data into tables located in the server outside the firewall.
From time to time they get the following two error messages when trying to
connect to the outside server
ORA-02068: following severe error from OPSWEB ORA-03113: end-of-file on
communication channel
Or
ORA-02068: following severe error from OPSWEB ORA-03114: not connected to
ORACLE

You need to nail this down once and for all.

If the network guys can't or wont offer any evidence that Oracle sends bad packets every 45 minutes then you'll need to go and get it yourself.

Let's just assume they are right.
In that case Oracle need to fix it.
Oracle will not even look at it unless you can show them some evidence.
The only way to get the evidence is to use a network/packet sniffer to catch the bad packets.

You can then relate the errors in the firewall log to the bad packets you capture on the network. But only if your network guys are right.

And if you don't capture any bad packets coming from oracle and the connection has been closed?