检查超过30 秒上长事务

 

 

输入文件  mon_tx.sql cap_tx.sql
输出文件   TMP1,TMP2,TMP3,TMPFILE
输入参数： db_name,minutes
输出参数：  OVOLOG,OVOSMY,sname,err_fg,LOGFILE,lncnt

it will run in every 10 seconds.

流程序：4if 。1个for 。1个while

-s file　　　　　文件大小非0时为真

if [ $# -ne 2 ]; then
        echo "Not Enough Parameters"
else
        DBNAME=$1
        TX_MAX=$2
fi

--config env 
export ROOT_DIR=/home/oracle
export SCRIPT_DIR=$ROOT_DIR/utility/macro
export LOGFILE_DIR=$ROOT_DIR/utility/log
export TMP_DIR=$ROOT_DIR/utility/tmp
export TRACE_DIR=$ROOT_DIR/utility/trace
export LOGIN=oper/stat_4102

. $SCRIPT_DIR/macro.env

export ORACLE_SID=statdb
export PATH=$PATH:.:/usr/local/bin

export ORAENV_ASK=NO

. oraenv >/dev/null
export ORAENV_ASK=

OVO_DIR=/tmp

EMAIL_LST=$ADMIN_DIR/email.lst
#EMAIL_LST=$ADMIN_DIR/email.tst

EXT="`date '+%y%m%d%H%M'`"
dt="`date '+%y/%m/%d %H:%M'`"

TMP1=$TMP_DIR/mon_tx1.${DBNAME}_${EXT}
TMP2=$TMP_DIR/mon_tx2.${DBNAME}_${EXT}
TMP3=$TMP_DIR/mon_tx3.${DBNAME}_${EXT}


OVOLOG=$OVO_DIR/mon_tx.log
OVOSMY=$OVO_DIR/mon_tx_smy.log

sname=yidbmon
err_fg="N"

rm -f $OVOLOG
touch $OVOLOG

chmod 777 $OVOLOG
chmod 777 $OVOSMY

if [ "${DBNAME}" = "epprod" ]; then
  SQL_SCRIPT=${SCRIPT_DIR}/mon_tx_epprod.sql
else
  SQL_SCRIPT=${SCRIPT_DIR}/mon_tx.sql
fi

---first get data from sql whre session hold 30 second lock;

$ORACLE_HOME/bin/sqlplus ${LOGIN} <<! 
@${SQL_SCRIPT} ${DBNAME} $TMP1 ${TX_MAX} 
!

--- 

sed /^$/d $TMP1 > $TMP2
mv $TMP2 $TMP1 

#lncnt=`wc -l ${TMP1} | cut -f 1 -d" "`

#if [ "${lncnt}" -eq 0 ]; then
if [ ! -s ${TMP1} ]; then
        rm -f $TMP1 $TMP2
        exit

fi
 

rm -f $TMP3

LOGFILE=$TRACE_DIR/mon_tx.${DBNAME}_${EXT} 

rm -f ${LOGFILE}

lncnt=0

----print the lock session info in log 

 cat ${TMP1} | while read pid
 do
 
   lncnt=`expr ${lncnt} + 1`
    err_fg="Y"
    TMPFILE=$TRACE_DIR/blkinfo.$pid
 
    $ORACLE_HOME/bin/sqlplus -s ${LOGIN} @${SCRIPT_DIR}/cap_tx.sql $TMPFILE $DBNAME $pid
    cat $TMPFILE >> $LOGFILE
    echo "\n" >> $LOGFILE
 
    rm -f $TMPFILE
 done


 grep -i "Oracle Session ID/Serial#      :"  $LOGFILE > $TMP3

 echo "Long Running Tx. Monitoring \n" > $TMP1


 echo "Summary Info. " >> $TMP1
 echo "--------------" >> $TMP1

 cat $TMP3 >> $TMP1

 echo "\n" >> $TMP1
 echo "Suggestion:" >> $TMP1
 echo "--------------" >> $TMP1
 
 echo "1. For pcc - kill the session by running \"kill_blocker script\"\n" >> $TMP1

 echo "2. For nGenpr,gdpr - run "check_blocker" script 3 times. If same session id appears, using "kill_blocker" script to kill it. Otherwise, call nGen/gdpr support and ask wheth
er the pending process can be terminiated or not.\n" >> $TMP1 

 echo "3. For EasyPort - Please call DBA to support."

 echo "Terminate / kill a session - Syntax: /macro/runjob kill_blocker [dbname] [sid] [serial#]" >> $TMP1

 echo "Check Blocker              - Syntax: /macro/runjob check_blocker [dbname]\n" >> $TMP1


 cat $LOGFILE >> $TMP1
 mv $TMP1 $LOGFILE

#  echo "Information: Transaction has been running more than "${TX_MAX} "mins." >> $LOGFILE
#  echo "Impact: Check whether there is any performance degradation, or receive any user complaint " >> $LOGFILE
#  echo "Action: If yes, then check blocker/kill blocker. If problem persists, then call DBA. Otherwise, just be alerted, and inform dba during office hours." >> $LOGFILE

 for iname in `cat $EMAIL_LST`
       do
        /bin/mailx -s "Long Running Tx. Found in ${DBNAME}; Total : ${lncnt} " $iname < $LOGFILE
 done


rm -f $TMP1 $TMP2

## OVO Msg

if [ "${err_fg}" != "N" ]; then
  echo "critical Long Running Tx. found in "${DBNAME}"; Check Email" > $OVOLOG
  echo "critical ${lncnt} Long Running Tx. found in "${DBNAME}" ; "${dt} >> $OVOSMY
fi

chmod 777 $OVOLOG
chmod 777 $OVOSMY

 

 

--mon_tx_epprod.sql   (check include TX and lmode =6 )

rem insert into mon_transaction_info
rem ( dbname, sid,serial#, tx_start_time,runtime);
rem dbname txmax
set feedback off pagesize 0 verify off  
spool &2 
--select s.sid, s.username, t.start_time
select p.spid
from v$session@&1 s, v$transaction@&1 t, v$process@&1 p
where s.taddr = t.addr
and sysdate - to_date(start_time,'MM/DD/YY HH24:MI:SS') >=  &3/1440
and  s.sid not in (select sid
                   from   v$lock@&1
                   where  (type = 'TX' and lmode=6)
                   or     (type = 'TM')
                   group by sid
                   having count(*) = 1)               
and s.paddr = p.addr; 
spool off
exit

 

1.检查是否session是active的。

2.如果不是active 的，那么联系应用

这个事务没有commit,导致事务一直在v$lock 里。

 你看下能否将该事务信息kill掉或者提交。