2

(Sorry for the long post, but this will help to give cleare picture)
DB: Oracle 12c
Host: Linux 6.10
Server Location: Chi and Boston

Recently we moved Oracle database to new server location in Boston which is on different network. We have multiple databases running on both servers.

Problem: ctrl + c disconnects users sqlplus session when they are connected across different server/network location.

Error: ORA-03135: connection lost contact.

Oracle client version are same at both locations.

Example: 1. User from Chi started sqlplus session and connects to database in Boston, does ctrl + c to kill current query but instead of killing query and returning sqlplus prompt his session gets disconnected and he has to login again.

  1. User from Boston started sqlplus session and connects to database in Chicago, does ctrl + c to kill query but instead of sqlplus prompt return his session gets disconnected and he has to login again.

If users are on same network connects to any database, ctrl + c doesn't disconnects session. (Chi -> Chi works fine, Bos -> Bos works fine).

Network team or Linux sysadmin doesn't see any problem at their end and don't want to spend time on investigation.

I somehow feels something between network is not interpreting ctrl + c which send SIGINT signal correctly. I'm no expert in network nor experienced similar behavior in past.

Does anybody knows what could be causing sessions to disconnects or any pointer?

homer
  • 423
  • 2
  • 11
  • 24
  • That's the behavior I know *for ages*; don't know why is that so, but - it is. – Littlefoot Dec 19 '18 at 17:27
  • @ChristianGibbons, removed c tag, thank you didn't realized that. – homer Dec 19 '18 at 17:27
  • 1
    @APC SQLcl Ctrl+c will send cancel message to DB and bring you back to SQL prompt. If you're impatient and hit Ctrl c again before we can respond to the first request, you'll bork out of the program back to your cmd/bash shell prompt – thatjeffsmith Dec 20 '18 at 11:14
  • @thatjeffsmith - thanks for the info. I'm going to play with `sqlcl` over the Winterval break, as I'm yet to get the chance at work :( – APC Dec 20 '18 at 11:16
  • It is described here: https://stackoverflow.com/a/21572816/836215 . – ibre5041 Dec 20 '18 at 14:22

1 Answers1

3

sqlplus.exe has basically always had a bug. It's been in the tool since at least around year 2000 (that was around version 3.x if I recall correctly). Run a query, while the output is scrolling by on the screen press Ctrl+C just once, if you catch the query at the wrong moment the entire tool exits. One of the biggest frustrations of this bug is that Oracle appears unwilling to even acknowledge the bug exists, let alone fix it. There are least two scenarios when the bug occurs, one of which is trivial to reproduce - run a query, time how long it takes, run it again and when you get close to the end - but while the data is still scrolling on the screen - press Ctrl C just once. It may take one or two tries to get the timing right but with little effort you can reproduce the bug. What happens in this scenario is easy to understand, the interaction with the database has stopped by the time you press Ctrl+C -- but if the screen is scrolling then how can you know you are near the end of the query? It is impossible in general to guarantee you never press ctrl+C during that window of mis-opportunity -- how anyone can say this is not a bug is beyond me.

There is at least one other situation when the bug occurs, but it is hard in general to reproduce - except that when it occurs, at that time it is easy to reproduce, so I suspect some kind of timing issue in the communications with the database. Run a query, imagine you realize your where clause is going to return vast amounts of data, so as it starts to display data you press Ctrl+C. Normally this is safe, but once in a while it is not safe - the tool will exit. When that happens restart sqlplus and re-run the same query, again press Ctrl+C once after it starts scrolling - once again the tool will exit. I have accidentally run this test numerous times. When this is happening you can do the same test over and over, for amusement I have killed my session at least half a dozen times in a row before I get back to work (by entering the correct where clause and not pressing Ctrl+C).

Anyway, the point is that it is extremely frustrating whenever anyone glibly answers that you should be careful to only press Ctrl C once. That is absolutely not any guarantee that you will be safe when running sqlplus.exe

Malcolm
  • 56
  • 3