RE: Korn Shell Sqlplus
According to Oracle Support, in 8.1.6, this can cause processes and locks of the KILLed sessions to hang. And we've seen it. Even after 24 hours, the processes are still listed in V$SESSION, but they have no corresponding entry in V$PROCESS. At this point, the only fix is to bounce the instance. Yes, I don't like killing the processes server-side, either, but in this case we don't have a choice. Also, I haven't been able to find a Metaclink article on when this is supposedly fixed. It doesn't matter because our 3rd-party software vendor won't support anything but 8.1.6.0.0. sigh Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Stephane Faroult [mailto:[EMAIL PROTECTED]] Sent: Monday, May 20, 2002 3:26 PM To: Multiple recipients of list ORACLE-L Subject: Re: Korn Shell Sqlplus Killing Oracle server processes is definitely bad manners. Especially if you are using MTS. Even if you are not, the proper way to kill an Oracle process is to use ALTER SYSTEM KILL SESSION. I think that you should use the DBMS_APPLICATION_INFO package to set something like 'Hey! Shoot me!' in your CLIENT_INFO column in V$PROCESS - it would make it easier to another process to log as DBA, spot the process and bang. Another thing that you might want to explore are profiles - you can define and set a profile with limits to suit your needs, and let Oracle do the dirty work. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Korn Shell Sqlplus
Stephane Faroult wrote: Killing Oracle server processes is definitely bad manners. Especially if you are using MTS. Even if you are not, the proper way to kill an Oracle process is to use ALTER SYSTEM KILL SESSION. I think that you should use the DBMS_APPLICATION_INFO package to set something like 'Hey! Shoot me!' in your CLIENT_INFO column in V$PROCESS - it would make it easier to another process to log as DBA, spot the process and bang. - Stephane, We will certainly disagree on that. I contend that you should *always* kill the process first. The reason being that using 'ALTER SYSTEM KILL SESSION' has a nasty habit of hanging sessions that are holding locks. I've seen it happen on all versions from 7.0.16 - 8.1.6, on DG/UX, Windows NT 4.0 and Solaris 2.5 - 2.6. It has become my habit to always kill the process ( if dedicated server ) or NT thread first. If the session still appears in v$session, use ALTER SYSTEM KILL SESSION to finish it off. Works every time. Doing it Oracle's way has necessitated too many database bounces for my tastes. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Korn Shell Sqlplus
I must agree with Jared on this practice. By following this practice, you never bounce database to just get rid of hanging sessions holding locks as some listers are complaining about that they had to bounce database to get rid of hanging sessions becuase they killed process on database level first. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 21 May 2002 11:47:10 -0800 Stephane Faroult wrote: Killing Oracle server processes is definitely bad manners. Especially if you are using MTS. Even if you are not, the proper way to kill an Oracle process is to use ALTER SYSTEM KILL SESSION. I think that you should use the DBMS_APPLICATION_INFO package to set something like 'Hey! Shoot me!' in your CLIENT_INFO column in V$PROCESS - it would make it easier to another process to log as DBA, spot the process and bang. - Stephane, We will certainly disagree on that. I contend that you should *always* kill the process first. The reason being that using 'ALTER SYSTEM KILL SESSION' has a nasty habit of hanging sessions that are holding locks. I've seen it happen on all versions from 7.0.16 - 8.1.6, on DG/UX, Windows NT 4.0 and Solaris 2.5 - 2.6. It has become my habit to always kill the process ( if dedicated server ) or NT thread first. If the session still appears in v$session, use ALTER SYSTEM KILL SESSION to finish it off. Works every time. Doing it Oracle's way has necessitated too many database bounces for my tastes. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MOHAMMAD RAFIQ _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Korn Shell Sqlplus
Michael - I have noticed the same issue you describe - ie. after the sqlplus process terminates, the shadow process continues to completion (assuming there is a COMMIT in the script). We are also on Compaq Tru64, but Oracle 8.1.6. I have only noticed it, not had a reason to dig in and investigate. I suspect that the shadow process is getting reparented to root. You might consider changing your kill to a kill -9. Or locate the shadow process and kill it, maybe first. Just some ideas, hopefully someone has more direct experience. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, May 20, 2002 2:55 PM To: Multiple recipients of list ORACLE-L Maybe one of you has some inspiration. Environment: Compaq Tru64 5.1; Oracle 8.1.7.3 The concept is for a parent Korn shell to spawn a sqlplus child in background (which will validate table structure via a PL/SQL Block) then wait for a fixed time period before stopping/killing the sqlplus child. After the sqlplus child is stopped some follow-up processing is needed to bookmark where the sqlplus processing has stopped. My quick test script so far look like: #!/bin/ksh echo Starting val_asc, spawning at $(date) val_asc.out sqlplus x/ @val_asc3.pls val_asc3.log valpid=$! sleep 240 kill -KILL %1 echo Leaving val_asc at $(date) val_asc.out exit When the sqlplus line runs it starts 2 processes: oragdv 169035 167185 0.0 10:00:20 pts/10:00.18 sqlplus oragdv 169652 169035 0.0 10:00:22 ?? 0:00.13 oracleGDV (DESCRIPTION=(LOCAL=YES). . . The problem is when the kill command executes, only the first of these 2 processes is killed immediately. The 2nd (with the PPID at 1) continues processing until the PL/SQL Block completes (including database updates) before it terminates. Here's the question. Is there a straightforward way to terminate that 2nd process before starting post processing? Any suggestions would be appreciated as I have been bashing my head against the proverbial wall for several days now ;) I have used the $! parameter to id the sqlplus PID and have been fiddling with ps awk to try and obtain the 2nd process's PID but have yet to be successful Michael Hand Polaroid Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Korn Shell Sqlplus
Hand, Michael T wrote: Maybe one of you has some inspiration. Environment: Compaq Tru64 5.1; Oracle 8.1.7.3 The concept is for a parent Korn shell to spawn a sqlplus child in background (which will validate table structure via a PL/SQL Block) then wait for a fixed time period before stopping/killing the sqlplus child. After the sqlplus child is stopped some follow-up processing is needed to bookmark where the sqlplus processing has stopped. My quick test script so far look like: #!/bin/ksh echo Starting val_asc, spawning at $(date) val_asc.out sqlplus x/ @val_asc3.pls val_asc3.log valpid=$! sleep 240 kill -KILL %1 echo Leaving val_asc at $(date) val_asc.out exit When the sqlplus line runs it starts 2 processes: oragdv 169035 167185 0.0 10:00:20 pts/10:00.18 sqlplus oragdv 169652 169035 0.0 10:00:22 ?? 0:00.13 oracleGDV (DESCRIPTION=(LOCAL=YES). . . The problem is when the kill command executes, only the first of these 2 processes is killed immediately. The 2nd (with the PPID at 1) continues processing until the PL/SQL Block completes (including database updates) before it terminates. Here's the question. Is there a straightforward way to terminate that 2nd process before starting post processing? Any suggestions would be appreciated as I have been bashing my head against the proverbial wall for several days now ;) I have used the $! parameter to id the sqlplus PID and have been fiddling with ps awk to try and obtain the 2nd process's PID but have yet to be successful Michael Hand Polaroid Corp Michael, Killing Oracle server processes is definitely bad manners. Especially if you are using MTS. Even if you are not, the proper way to kill an Oracle process is to use ALTER SYSTEM KILL SESSION. I think that you should use the DBMS_APPLICATION_INFO package to set something like 'Hey! Shoot me!' in your CLIENT_INFO column in V$PROCESS - it would make it easier to another process to log as DBA, spot the process and bang. Another thing that you might want to explore are profiles - you can define and set a profile with limits to suit your needs, and let Oracle do the dirty work. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
