input file mon_tx.sql cap_tx.sql output file input parameter: Db_name,minutes output parameter: Flow program:if。 for if[ $#-ne 2]; ThenEcho"Not enough Parameters"ElseDBNAME= $Tx_max= $Fiexport Root_dir=/home/Oracleexport Script_dir=$ROOT _dir/utility/Macroexport Logfile_dir=$ROOT _dir/utility/Logexport Tmp_dir=$ROOT _dir/utility/Tmpexport Trace_dir=$ROOT _dir/utility/Traceexport LOGIN=oper/stat_4102.$SCRIPT _dir/Macro.envexport Oracle_sid=Statdbexport PATH=$PATH:.:/ usr/local/Binexport Oraenv_ask=NO. Oraenv>/dev/Nullexport Oraenv_ask=Ovo_dir=/Tmpemail_lst=$ADMIN _dir/Email.lst#email_lst= $ADMIN _dir/email.tstEXT="' 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.logovosmy= $OVO _dir/mon_tx_smy.logsname=yidbmonerr_fg="N"rm-f $OVOLOGtouch $OVOLOGchmod 777 $OVOLOGchmod 777 $OVOSMYif ["${dbname}" = "Epprod" ]; Then Sql_script=${script_dir}/mon_tx_epprod.sqlelse sql_script=${script_dir}/mon_tx.sqlfi$oracle_home/bin/ Sqlplus ${login} <<! @${sql_script} ${dbname} $TMP 1 ${tx_max}!sed/^$/d $TMP 1 > $TMP 2mv $TMP 2 $TMP 1 #lncnt = ' Wc-l ${TMP1} | Cut-f 1-d" "' #if ["${lncnt}"-eq 0]; thenif [!-S ${TMP1}]; Then rm-f $TMP 1 $TMP 2 exitfi rm-f $TMP 3logfile= $TRACE _dir/mon_tx.${dbname}_${ext} rm-f ${logfile}lncnt=0 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 $DBNAM E $pid cat $TMPFILE >> $LOGFILE echo"\ n">> $LOGFILE rm-f $TMPFILE done Grep-i"Oracle Session id/serial#: "$LOGFILE > $TMP 3Echo"Long Running Tx. monitoring \ n">$TMP 1Echo"Summary Info.">>$TMP 1Echo"--------------">>$TMP 1Cat$TMP 3>>$TMP 1Echo"\ n">>$TMP 1Echo"Suggestion:">>$TMP 1Echo"--------------">>$TMP 1Echo"1. For Pcc-kill the session by running \"Kill_blocker script\"\ n">>$TMP 1Echo"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 whether the pending process can be terminiated or not.\n">>$TMP 1Echo"3. For Easyport-please call DBA-Support."Echo"Terminate/kill a session-syntax:/macro/runjob kill_blocker [dbname] [SID] [serial#]">>$TMP 1Echo"Check blocker-syntax:/macro/runjob check_blocker [dbname]\n">>$TMP 1Cat$LOGFILE>>$TMP 1MV$TMP 1 $LOGFILE#echo "Information:transaction have been running more than" ${tx_max} "mins." >> $LOGFILE#echo "Impact:check whether there is a performance degradation, or receive any user complaint" >> $LOGFILE /c1>#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 forInameinch' Cat$EMAIL _lst` Do/bin/mailx-s"Long Running Tx. Found in ${dbname}; Total: ${lncnt}" $iname<$LOGFILEDonerm- F $TMP 1 $TMP 2## OVO MSGif["${ERR_FG}"!="N" ]; Then Echo"critical Long Running Tx. Found in"${dbname}"; Check Email">$OVOLOGEcho"critical ${lncnt} Long Running Tx. Found in"${dbname}" ; "${dt}>>$OVOSMYFichmod777$OVOLOGchmod777$OVOSMY
Check for long transactions