Re: Oracle processes running wild

From: Mark Bole <makbo_at_pacbell.net>
Date: Thu, 26 Feb 2004 03:39:56 GMT
Message-ID: <gUd%b.17283$Fz.3768@newssvr29.news.prodigy.com>

Daniel Morgan wrote:

> Dennis Bieling wrote:
> 

>> Hi!
>>
>> I have the following problem. On one of our database instances (9ir2)
>> there are 3 oracle processes running wild for 2 days now, using each
>> 100% CPU performance, so there is only one more left for real business
>> . I identified the 3 sessions
>> that are responsible for these prozesses. After asking the user
>> who is the owner of these sessions, he told me that he doesn't know
>> about the sessions and that probably a statement was aborted.
>> When I looked at the SQL that belongs to the sessions, there was no
>> statement associated with any of these sessions. Therefore I decided
>> to kill those sessions. They are now marked as "killed", but the
>> operating system processes are still active and using still
>> 3 of the 4 CPUs. What can I do to get rid of them without stopping the
>> database? Is it save to kill these oracle processes?
>>
>> Best regards
>> Dennis

> 
> 
> I know others have advised differently ... but I have never hesitated
> to apply a small thermonuclear device, kill -9, and never once had a
> problem.
> 

Under any flavor of Unix that supports Oracle, "kill -9" (or the symbolic equivalent signal) for client or server (in the Oracle sense,

   MTS or dedicated) processes is very low risk, as indicated.

Try an "alter system kill session..." from within the RDBMS first, if you can. (Sounds like the OP already tried this). Can't hurt, and might be all you have to do in many situations. The OEM stand-alone client under Windows is a very convenient way to do this.

Even killing the core RDBMS processes under Unix is no worse than a "shutdown abort". To re-start the instance, you might have to remove shared memory "ipcrm ..." and lock file ($ORACLE_HOME/dbs/LK*), which in turn may require some reading and understanding of Unix man pages to perform correctly. Some people just reboot after this drastic action to (usually) accomplish the same thing, albeit the slow and easy way. (And I'm not even dealing with RAC here, which might have other issues unique to a clustered environment).

Here is 1-liner to try, logged in as user 'oracle', if you find the need to quickly, forcefully kill all Oracle processes running under Unix without rebooting the system:

ps -ef | grep oracle | grep -v grep | awk '{print $2}' | xargs kill -9

Test it first, of course, by leaving off one or more of the trailing pieces of the pipeline.

Windows has a "tlist" and "kill" command also (and Task Manager), but I haven't used them as often so can't vouch for their robustness.

If you have to resort to killing Unix processes more often than rarely, you should find the underlying cause of the problem.

--Mark Bole Received on Wed Feb 25 2004 - 21:39:56 CST