输入文件 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 " " >> $LOGFILE rm -f $TMPFILE done grep -i "Oracle Session ID/Serial# :" $LOGFILE > $TMP3 echo "Long Running Tx. Monitoring " > $TMP1 echo "Summary Info. " >> $TMP1 echo "--------------" >> $TMP1 cat $TMP3 >> $TMP1 echo " " >> $TMP1 echo "Suggestion:" >> $TMP1 echo "--------------" >> $TMP1 echo "1. For pcc - kill the session by running "kill_blocker script" " >> $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. " >> $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] " >> $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掉或者提交。