Check for long transactions

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.