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: <Srini.Chavali_at_Cummins.com>
Date: Tue, 15 Jan 2002 13:01:24 -0800
Message-ID: <F001.003F0853.20020115123522@fatcity.com> 







To clean up the sniped sessions faster, you can also use the IMMEDIATE
clause (new with 8i, I believe) in the ALTER SYSTEM KILL SESSION command.
See docs for details.


HTH



Srini Chavali


Oracle DBA


Cummins Inc






Sunil_Nookala_at_Dell.com_at_fatcity.com on 01/15/2002 02:05:23 PM



Please respond to ORACLE-L_at_fatcity.com



Sent by:  root_at_fatcity.com




To:   Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:





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).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Srini.Chavali_at_Cummins.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 - 15:01:24 CST












Original text of this message













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