All the Perl that's Practical to Extract and Report

Cleanly logging out of Oracle in an emergency

jdavidb on 2007-07-26T20:07:25

I've got a set of programs that explicitly secure a certain type of lock in Oracle and then perform a certain process. Sometimes these programs die unexpectedly. When they do, the client process dies, but there is a server process within the Oracle instance itself that stays up because it is still holding that lock. The server process is apparently incapable of detecting that the client has died. It won't detect it until something I'm not sure of happens ... something that won't happen because the client is now not around to make it happen.

So, I need to catch every possible signal that might make these client processes die and have them attempt to release that lock if at all possible before dying.

Oh, and I have to do it in Java.

The reason I'm here writing about it is I was going to ask if anyone knew how to do this. (The reason I'm here and not on a Java site is because Perl programmers are smarter than Java programmers, and of course because I am always here and don't seem to derive enough benefit to going elsewhere to justify it. :) ) Then I realized noone will know what the locking mechanism is that I am talking about. Then I realized that the problem is that I don't understand these locks, and that if I did the rest of it would be a simple exercise in looking up Java and PL/SQL syntax. In trying to explain my question above, I actually outlined the solution. All this has done is serve to focus my thoughts. :)

So I guess now I just need to go read about it. Thanks, guys. You're great. :)

Glad to be of help

grantm on 2007-07-26T21:30:40

Where should I send the bill?

You may need to do more work than that

btilly on 2007-07-27T01:59:25

One of the signals that can kill the Java program is a kill -9. Which can't be caught. Ever.

If your sysadmins are being happy with kill -9, then what you may need to do is write a monitoring process which is told what each Java process is doing, and will tell Oracle to clean up after them when they disappear without properly cleaning up.

Another possibility is that the bug is in Oracle. Unlikely I know, but back in the Oracle 8 series I ran across a query that caused an internal Oracle process to try to follow a null pointer, which left the process talking to that in a hung state. (The bug hit with a correlated subquery that sometimes returned no rows. We experienced it as, "This query logs you out!" While playing with it we ran out of connections on the server. This was, of course, a test server.)

In any case, good luck.

Re:You may need to do more work than that

jdavidb on 2007-07-27T12:35:23

Yeah, that's why I said "catch all signals possible." :)

Part of the issue is actually that I myself and one of our monitoring groups have to kill this every so often, although I definitely don't kill with 9 unless something refuses to go away with something weaker.

I'm not sure a second process could tell Oracle to release these locks, because it wouldn't own them. So I'm not sure the monitoring process idea will work. It might be possible, however, for an audit process to run on the server side and kill hung processes. But the DBAs would have to handle that.

Re:You may need to do more work than that

btilly on 2007-07-27T15:14:32

You'll have to work with a DBA on how to do it, but it is possible for a second process with sufficient privileges to kill someone else's login on the Oracle side. When that login is killed, all locks that it held are released.

This does take a lot of work to set up though. I wouldn't go there unless you really have to. But be aware that it is an option.