Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: sniped sessions

RE: sniped sessions

From: david hill <david.hill_at_lechateau.ca>
Date: Tue, 15 Jan 2002 12:49:32 -0800
Message-ID: <F001.003F073A.20020115112024@fatcity.com> 






here is a little script i wrote


simpliefied a little


it first kills the session within oracle then generates the unix commands to
kill the session


so there can never a mistake about killing the wrong pid
and i also insert into an oracle table all killed sessions so you can query
them a litlle easier

-----------kill_sniped_session.ksh-------------------




#!/bin/sh

#CREATE TABLE killed_sessions

#     (

#       SID       NUMBER,


#       SERIAL#   NUMBER,


#       SPID     VARCHAR2(9),


#       USERNAME  VARCHAR2(30),


#       TIMESTAMP DATE


#     ) tablespace DBA                               

#       STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0); 



tmpfile=/tmp/kill_sniped.$$


su - superman -c "sqlplus -s /@mer << 'EOF' 


    whenever sqlerror exit 1


    whenever oserror  exit 1


    set serveroutput on


    set feedback off 


    spool $tmpfile


DECLARE


        Stmt_Str        VARCHAR2(200);
        v_Sid           v\$session.sid%TYPE;
        v_Serial        v\$session.serial#%TYPE;
        v_Username      v\$session.username%TYPE;
        v_spid          v\$process.spid%TYPE;
        CURSOR c_list IS
        select a.SID, a.SERIAL#, a.USERNAME, b.SPID
        from v\$session a, v\$process b
        where a.paddr = b.addr
        and a.STATUS = 'SNIPED';




BEGIN



    FOR user in c_list


    LOOP


        v_sid := user.sid;
        v_serial := user.serial#;
        v_username := user.username;
        v_spid := user.spid;
        stmt_str := 'alter system kill session ''' || v_sid || ',' ||
v_serial || '''';
        insert into sys.killed_sessions values(v_sid, v_serial, v_spid,
v_username, sysdate);
        DBMS_OUTPUT.PUT_LINE('kill -9 '|| v_spid);
        Execute Immediate(stmt_str);




    END LOOP;



END;



/


EOF" >>/dev/null 2>&1



cat $tmpfile |while read line ; do


    cmd=$line


    eval $cmd


done



rm -f $tmpfile





Thanks 


David Hill


DBA



Le Chateau Stores




-----Original Message-----


Sent: Tuesday, January 15, 2002 10:06 AM
To: Multiple recipients of list ORACLE-L




Attached are the scripts we use to kill sniped sessions.  This is part of a
series of monitoring scripts we developed for our Oracle databases.  The
scripts are scheduled through Cron and run every 15 minutes.  A logonid or
an email address is passed to the scripts for either paging or email.  In
this case an accum of sniped sessions is maintained on a 'flat' file just so
we can go back and check to see how many sniped sessions are being killed.
I will attach a zip file.  If that doesn't get through I will also list the
scripts below.



Ron Smith


Kerr-McGee Corp



SNIPED.SH




#! /bin/sh

#          DBA MONITORING SCRIPTS 


# ******************************************************************

#

#     Author:         Ron Smith

#     Date:           06/18/98

#     Funtion:        Checks for sessions that have been "Sniped".

#

# ******************************************************************

#

#                     CHANGE HISTORY


#

#     DATE        WHO             Reason for Change




#     03/03/00    Ron Smith       New Prog




#

# ******************************************************************

#

#                     FUNCTION





#

#     This script calls sniped.sql.

#     The function of this script is to report sessions that have

#     been "sniped" by Oracle through the use of resource limits.

#

#     If the id of the DBA is a Zid, a page will be sent.  If the 

#     id of the DBA is an email address (determined by looking for

#     an "@" ) , an EMAIL will be sent.

#

# ******************************************************************

#

#                     PREREQUISITES


#

#     The OPS$ORACLE user must exist in the instance.  This can be 

#     created by running the opsuer.sql script in SQLPLUS while

#     logged on as SYSTEM. 

#

#     The cdmonitoring script must exist in the home/oracle

#     directory.

#

# ******************************************************************

#

#                     RUN SYNTAX


#      

#     sniped.sh (sid) (oncall dba)       

#      

#      

# ******************************************************************




# cd to the monitoring script directory



. $HOME/cdmonitoring.sh



ORACLE_SID=$1



export ORACLE_SID


DBA=$2



export DBA


echo $DBA


ATCNT=`echo $DBA | grep @ |  wc -l`


export ATCNT



ORACLE_HOME=`grep "^$ORACLE_SID:" /etc/oratab | head -1 | cut -d: -f2`
export ORACLE_HOME


PATH=$ORACLE_HOME/bin:/usr/local/bin:$PATH:.
export PATH



# Delete the old list file if it exists




if [ -e sniped_$ORACLE_SID.lst ]


        then rm sniped_$ORACLE_SID.lst


fi



# Delete the old error file if it exists




if [ -e sniped_$ORACLE_SID.err ]


        then rm sniped_$ORACLE_SID.err


fi



# Delete the old kill file if it exists




if [ -e sniped_kill_$ORACLE_SID.sh ]


        then rm sniped_kill_$ORACLE_SID.sh
fi



# If sending to EMAIL address, run sql with headings on else run with

headings off



if [ "$ATCNT" -gt "0" ]

        then
        sqlplus / @sniped.sql on  
        else 
        sqlplus / @sniped.sql off  




fi



# If there is anything in the lst file then kill the user processes and send

a message



if [ -s sniped_$ORACLE_SID.lst ]


        then cat sniped_$ORACLE_SID.lst >> sniped_accum.lst



        sqlplus / @sniped2.sql


        if [ -s sniped_kill_$ORACLE_SID.sh ]
           then chmod +x sniped_kill_$ORACLE_SID.sh;
                cat sniped_kill_$ORACLE_SID.sh >> sniped_kill_accum.lst
                ./sniped_kill_$ORACLE_SID.sh;
        fi

        echo "-DBA- Sniped sessions killed " > sniped_$ORACLE_SID.err
        echo "SID=" $ORACLE_SID " " >> sniped_$ORACLE_SID.err
        cat sniped_$ORACLE_SID.lst >> sniped_$ORACLE_SID.err

        if [ "$ATCNT" -gt "0" ]
                then
                echo "email sent"
                elm -s "-DBA- $ORACLE_SID Sniped sessions exist" $DBA <
sniped_$ORACLE_SID.err
        else
                LC=`cat sniped_$ORACLE_SID.lst | sed -e 's/  */ /g' | wc -c`
                echo $LC
                if [ "$LC" -gt "160" ]
                        then echo "Sniped sessions killed. Check
sniped_$ORACLE_SID.lst" >> sniped_$ORACLE_SID.err
                        else
                        cat sniped_$ORACLE_SID.lst >> sniped_$ORACLE_SID.err
                fi
                echo "page sent"
                pager $DBA "`cat sniped_$ORACLE_SID.err`"
        fi




fi




SNIPED.SQL


set pause off


SET ECHO off


set verify off


set feedback off


set linesize 132


set heading &1


col "User Name"  format a10;


alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';
spool sniped_$ORACLE_SID.lst



select s.username "User Name",

        s.osuser "OS User",
        s.status "Status",
        s.logon_time "Connect Time",
        p.spid, p.pid, si.sid




from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
where s.username is not null and 

        si.sid(+)=s.sid
        and p.addr(+)=s.paddr
        and  status = 'SNIPED';





spool off;


exit;



SNIPED2.SQL


set pause off


SET ECHO off


set verify off


set feedback off


set linesize 132


set heading off



spool sniped_kill_$ORACLE_SID.sh;



select 'kill -9 ' || p.spid

        from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
        where s.username is not null and
        si.sid(+)=s.sid
        and p.addr(+)=s.paddr
        and  status = 'SNIPED';




spool off;


exit;




-----Original Message-----


Sent: Monday, January 14, 2002 4:10 PM


To: Multiple recipients of list ORACLE-L




Dear List,



I have "init.ora" resource_limit = true, and idle_time set in profile. 
When idle_time exceeds, the session status becomes 'sniped' in v$session and
never gets cleaned up.


I manually kill these threads using orakill utility.



I appreciate, if someone can help me with a script to clean up these
sessions automatically(8.1.7 on NT)??



Sunil Nookala


DBA



Dell Computer Corp.


Austin, TX 78738

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Sunil_Nookala_at_Dell.com

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: ListGuru_at_fatcity.com (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: david hill
  INET: david.hill_at_lechateau.ca

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: ListGuru_at_fatcity.com (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).


Received on Tue Jan 15 2002 - 14:49:32 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US