This content has been marked as final. Show 20 replies
But the database can't be to blame because...I'm not going to rule the database out or the network connectivity to it. I think that the 2.1 SQL*Dev is just less affected by whatever is happening. On the 3.1 system for example, if I leave it idle for about 15 minutes, issuing a command hangs SQL*Dev hard (no response -- must kill with Task Manager). However, the 2.1 SQL*Dev is unaffected. I think that the hosting center has a firewall that is killing the connection and 2.1 simply recovers better from that. I may be way off base. Somewhere (think this forum) I found someone had encountered similar 'idle' behavior and used the SQL*Net EXPIRE_TIME setting to keep a connection from being seen as inactive long enough to be killed by the firewall. However, that won't help most of my problems because they aren't inactivity-related.
The network card I would rule out because I have zero problems connecting to the 'local' database that is in the same building. I would not be surprised to find out that ALL of this is something to do with their security -- either a firewall issue or some other security measure. However, if I go to them and say that their network is screwing up my SQL*Dev connection, I need to be able to do more than just point out that access to my local server works. There's several behaviors that I see from SQL*Dev 3.1 when accessing this database. Some I see all the time, others intermittently. The behavior I'm trying to track down is just the one that matched perfectly with that described in this thread.
Among the behaviors I've seen:
Trying to open the 'Tables' listing hangs for long periods (upwards of fifteen minutes before I kill the connection).
Trying to open a package body (not all of them, just some of them) hangs for long periods. (ditto kill data above)
Opening a package body completes, but the connection closes in the middle of pulling over the text and and only part of the package body is available in SQL*Dev.
I get 'connection reset' warnings numerous times throughout the day.
I'm trying to find the smoking gun that will lead me to my connection killer.
Well in this thread, others are seeing a similar 'firewall disconnect' to mine:
3.1 Prod : Reconnect does not work on tree item navigator
I'm about going to have to discard using SQL*Developer altogether. Unfortunately, I don't know that moving to TOAD, PL/SQL Developer or the like will resolve the issues I'm seeing. This morning when I pulled up a package body, the connection died in the middle of pulling over the code. I didn't realize this, made a change and compiled. Turns out half the code was missing, so I trashed the package. Had to go back to my QA server and pull the package body from it to repair.
I don't know that the problem is SQL*Developer, but it only shows up there. I don't know if the thread dump of SQL*Developer isn't showing where/why it's losing the connection or I'm missing it. Trying to come up with any direction to head next...
Woo Hoo! Was finally able to get something both reproducible and definitely non-SQL*Developerish. All of the most severe problems seemed to involve disconnects during significant data transfers from the server. I connected with SQL*Plus and did a SELECT * from my largest table. Got it to hang once (hard to do with SQL*Plus). On a second attempt I got it to give me an ORA-12592 -- bad packet error. Since then, I've been able to reproduce the 12592 at will. This in all likelihood is what is killing the SQL*Dev connections.
On a secondary note, I'll point out that this means SQL*Developer isn't really handling 12592 errors very well.
So you've made progress, but without any real solution, unfortunately. Typically a ORA-12592 TNS:bad packet means you have network issues. There have been very few bugs ever logged against either Oracle DB or Network Services for this kind of issue, and the documentation says it is not normally visible to the user. As far as I know, there is nothing at the SQL Developer layer that explicitly catches an ORA-12592.
There are also very few occurrences when searching across all the forum categories for all time. For example:
If you suspect the explicit Reconnect option in a Connection node's context menu in 3.1 is a problem, you could try reverting to 3.0.
I assume the ORA-12592 occurs in SQL*Plus on the Win7 machine. Does it also happen on the WinXP box?
Good luck in tracking down the point at which packets become malformed,
Now that I have an actual error message and have reproduced it in something as rock-solid as SQL*Plus, I've kicked the problem over to the network Administration team at the HP datacenter where I'm hosting the database. The only reason I haven't involved them before now is because some of the symptoms in SQL*Developer matched whose in this forum (i.e. this thread and others) and I couldn't definitively prove that the problem was on their end.
I don't really know why 2.1 is less susceptible to the problem than 3.1. All I can think of is that something must have changed with the way it handles unexpected network problems. The thread I linked to in a previous post was of people seeing firewall disconnects in 3.1 that they didn't see in earlier releases (which I've also experienced). I have to assume that sometimes 2.1 is able to recover. Dunno. Right now I'm just going to hold HPs feet to the fire for a while until they find a solution.
I wanted to post the resolution to this. The network team at the hosting center removed SQL Inspection from the firewall and this resolved the issue.