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: <Sunil_Nookala_at_Dell.com>
Date: Tue, 15 Jan 2002 11:52:25 -0800
Message-ID: <F001.003F069E.20020115110523@fatcity.com> 






appreciate you help guys..



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


Sent: Tuesday, January 15, 2002 9: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: 
  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).


Received on Tue Jan 15 2002 - 13:52:25 CST












Original text of this message













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