===below is Script for AWR Analysis i.e. awr_analysis.sql===
set "db file sequential read" "db file scattered read" "log file sync" "enq: TX - row lock" "log file parallel write" "RMAN backup" "log buffer space" "db file parallel read" "direct path read" "Net message from dblink" "Net more data from dblink" "enq: HW - contention" "log file sequential read" "gc buffer busy acquire" "gc current block 2-way"
FILELIST='awrrpt*.html awr*.html'
echo "Provide output file name without any extension (default - summary_out) : "
read FNAME
if [ "${FNAME}" == "" ] ; then
FNAME="summary_out.html"
else
FNAME=$FNAME".html"
fi
TMPFILE=TMP_FILE
TMPFILE01=TMP_FILE01
TMPFILE02=TMP_FILE02
TMPFILE03=TMP_FILE03
TMPFILE04=TMP_FILE04
TMPFILE05=TMP_FILE05
TEMP_FILE=TEMP_FILE
echo '<HTML><HEAD><TITLE>AWR Analysis</TITLE><style type="text/css">
body.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:black; background:White;}
pre.awr {font:8pt Courier;color:black; background:White;}
h1.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
h2.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:Black;background-color:White;margin-top:3pt; margin-bottom:0pt;}
h3.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;}
li.awr {font: 8pt Arial,Helvetica,Geneva,sans-serif; color:black; background:White;}
th.awrnobg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:black; background:White;padding-left:4px; padding-right:4px;padding-bottom:2px}
th.awrbg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px}
td.awrnc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;}
td.awrc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;}
a.awr {font:bold 8pt Arial,Helvetica,sans-serif;color:#663300; vertical-align:top;margin-top:0pt; margin-bottom:0pt;}
</style><body>' > ${FNAME}
##### Print Wait Events heading
WAIT_EVENT_HEADING=`echo "<table border=0 class="tdiff"><tr><th class="awrbg" scope="col">File Name</th><th class="awrbg" scope="col">Snap Time</th>
<th class="awrbg" scope="col">Event</th><th class="awrbg" scope="col">Waits</th>
<th class="awrbg" scope="col">%Time-outs</th><th class="awrbg" cope="col">Total Wait Time (s)</th><th class="awrbg" cope="col">Avg wait (ms)</th>
<th class="awrbg" cope="col">Waits /txn </th><th class="awrbg" cope="col">% DB time</th></tr>"`
##### Print load profile heading
LOAD_PROFILE_HEADING=`echo "<table border=0 class="tdiff"><tr><th class="awrbg" scope="col">DB Name</th><th class="awrbg" scope="col">Instance Name</th>
<th class="awrbg" scope="col">File Name</th><th class="awrbg" scope="col">Snap Begin Time</th><th class="awrbg" scope="col">Snap End Time</th>
<th class="awrbg" scope="col">Elapsed Time</th><th class="awrbg" scope="col">DB Time</th>
<th class="awrbg" scope="col">Redo Size</th><th class="awrbg" scope="col">Logical Reads</th><th class="awrbg" scope="col">Hard Parses</th>
<th class="awrbg" scope="col">Parses</th><th class="awrbg" cope="col">Transactions</th><th class="awrbg" cope="col">Rollbacks</th><th class="awrbg" cope="col">Executes</th>
<th class="awrbg" scope="col">DB CPU (s)</th><th class="awrbg" cope="col">Buffer Cache</th><th class="awrbg" cope="col">Shared Pool</th></tr>"`
##### Print Datafile Read Write Ratio Heading
DATAFILE_RW_RAIO_HEADING=`echo "<table border=0 class="tdiff"><tr><th class="awrbg" scope="col">File Name</th><th class="awrbg" scope="col">Snap Time</th>
<th class="awrbg" scope="col">Type</th><th class="awrbg" scope="col">Reads: Data</th><th class="awrbg" scope="col">Reqs per sec</th>
<th class="awrbg" scope="col">Data per sec</th><th class="awrbg" cope="col">Writes: Data</th><th class="awrbg" cope="col">Reqs per sec</th>
<th class="awrbg" cope="col">Data per sec</th><th class="awrbg" cope="col">Small Read</th><th class="awrbg" cope="col">Large Read</th></tr>"`
FILECOUNT=`ls -1 $FILELIST | wc -l`
echo $FILECOUNT Files to be processed.
FILE_RUN_COUNT=0
for x in $FILELIST
do
awk '/AWR Report for/,/Top 5 Timed Foreground Events/' $x > ${TEMP_FILE}
BEGIN_SNAP=`cat ${TEMP_FILE} | grep "Begin Snap:" | awk -F\> '{print $7}' | awk -F \< '{print $1}'`
END_SNAP=`cat ${TEMP_FILE} | grep "End Snap:" | awk -F\> '{print $7}' | awk -F \< '{print $1}'`
ELAPSED_TIME=`cat ${TEMP_FILE} | grep -i "Elapsed" | awk -F\> '{print $7}' | awk -F \< '{print $1}'`
SNAPTIME_APPEND="<TR><td scope="row" class='awrc'> $x <td scope="row" class='awrc'>${BEGIN_SNAP}"
DBNAME=`grep "AWR Report for" ${TEMP_FILE} | awk -F\, '{print $1}' | awk '{print $NF}'`
INSTNAME=`grep "AWR Report for" ${TEMP_FILE} | awk -F\, '{print $2}' | awk '{print $NF}'`
DBTIME=`grep "DB Time:" ${TEMP_FILE} | awk -F\> '{print $7}' | awk -F \< '{print $1}'`
DBCPU=`grep "DB CPU(s):" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
BUFFER_CACHE=`grep "Buffer Cache:" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
SHARED_POOL=`grep "Shared Pool Size:" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
REDO_SIZE=`grep -i "redo size" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
LOGICAL_READS=`grep -i "logical read" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
HARD_PARSES=`grep -i "hard parses" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
PARSES=`grep -i "parses" ${TEMP_FILE} | grep -vi hard | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
TRANSACTIONS=`grep -i "transactions" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
ROLLBACKS=`grep -i "rollbacks" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
EXECUTES=`grep -i "executes" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
echo "<tr><td scope="row" class='awrc'>" $DBNAME "</td><td scope="row" class='awrc'>" $INSTNAME >> ${TMPFILE}
echo "</td><td scope="row" class='awrc'>" $x "</td><td scope="row" class='awrc'>" $BEGIN_SNAP "</td><td scope="row" class='awrc'>" $END_SNAP >> ${TMPFILE}
echo "</td><td scope="row" class='awrc'>" $ELAPSED_TIME "</td><td align="right" class='awrc'>" $DBTIME "</td><td align="right" class='awrc'>" $REDO_SIZE >> ${TMPFILE}
echo "</td><td align="right" class='awrc'>" $LOGICAL_READS "</td><td align="right" class='awrc'>" $HARD_PARSES "</td><td align="right" class='awrc'>" $PARSES >> ${TMPFILE}
echo "</td><td align="right" class='awrc'>" $TRANSACTIONS "</td><td align="right" class='awrc'>" $ROLLBACKS "</td><td align="right" class='awrc'>" $EXECUTES >> ${TMPFILE}
echo "</td><td align="right" class='awrc'>" $DBCPU"</td><td align="right" class='awrc'>" $BUFFER_CACHE"</td><td align="right" class='awrc'>" $SHARED_POOL"</td></tr>" >> ${TMPFILE}
awk '/Foreground Wait Events/,/Wait Event Histogram/' $x | awk '/<table/,/<\/table/' > ${TEMP_FILE}
for i
do
cat ${TEMP_FILE} | grep "$i" | grep -v User | awk -v orig="<tr>" -v repl="$SNAPTIME_APPEND" '{sub(orig, repl)}1' | head -1 >> ${TMPFILE01}
done
awk '/IOStat by Filetype summary/,/IOStat by Function\/Filetype summary/' $x | awk '/<table/,/<\/table/' | grep "Data File" | awk -v orig="<tr>" -v repl="$SNAPTIME_APPEND" '{sub(orig, repl)}1' >> ${TMPFILE02}
awk '/IOStat by Filetype summary/,/IOStat by Function\/Filetype summary/' $x | awk '/<table/,/<\/table/' | grep "TOTAL:" | awk -v orig="<tr>" -v repl="$SNAPTIME_APPEND" '{sub(orig, repl)}1' >> ${TMPFILE03}
echo '<h3 class=awr>File: ' $x ' Snap Begin Time : ' $BEGIN_SNAP>> ${TMPFILE04};
awk '/Top 5 Timed Foreground Events/,/<\/table/' $x | awk '/<table/,/<\/table/' >> ${TMPFILE04};
echo '<h3 class=awr>File: ' $x ' Snap Begin Time : ' $BEGIN_SNAP>> ${TMPFILE05};
awk '/SQL ordered by Gets/,/SQL ordered by Reads/' $x | awk '/<table/,/<\/table/' >> ${TMPFILE05};
FILE_RUN_COUNT=$(expr "$FILE_RUN_COUNT" + 1)
echo "File $x processed. $FILE_RUN_COUNT file(s) out of $FILECOUNT is done."
done
###### Merging all temp files and writing to main file
echo "Preparing output file..."
echo '<h2 class=awr>Load Profile (values per second)</h2>' >> ${FNAME}
echo ${LOAD_PROFILE_HEADING} >> ${FNAME}
cat ${TMPFILE} >>${FNAME}
echo "</table>" >> ${FNAME}
for i
do
echo '<hr><h2 class=awr>Event :' "${i}" '</h2>' >> ${FNAME}
echo ${WAIT_EVENT_HEADING} >> ${FNAME}
cat ${TMPFILE01} | grep "$i" | sort | awk -v orig="<tr>" -v repl="$SNAPTIME_APPEND" '{sub(orig, repl)}1' >> ${FNAME}
echo "</table>" >> ${FNAME}
done
echo '<hr><h2 class=awr>Data File Read Write Ratio</h2>' >>${FNAME}
echo ${DATAFILE_RW_RAIO_HEADING} >> ${FNAME}
cat ${TMPFILE02} >>${FNAME}
echo "</table>" >> ${FNAME}
echo '<hr><h2 class=awr>Total Read Write Ratio</h2>' >>${FNAME}
echo ${DATAFILE_RW_RAIO_HEADING} >> ${FNAME}
cat ${TMPFILE03} >>${FNAME}
echo "</table>" >> ${FNAME}
echo '<hr><h2 class=awr>Top 5 Wait Events</h2>' >>${FNAME}
cat ${TMPFILE04} >>${FNAME}
echo '<hr><h2 class=awr>Top Buffer Gets SQLs</h2>' >>${FNAME}
cat ${TMPFILE05} >>${FNAME}
## /bin/cp new_awr.html /home/ora12c/
rm ${TMPFILE}
rm ${TMPFILE01}
rm ${TMPFILE02}
rm ${TMPFILE03}
rm ${TMPFILE04}
rm ${TMPFILE05}
rm ${TEMP_FILE}
################ Other Unique parameters to grep ################
echo "Searching other unique parameters"
set "rollback changes - undo records applied" "physical read total IO requests" "physical write total IO requests"
for i
do
OTHER_HEADING=""
echo $i
echo "<hr><h2 class=awr>$i</h2>" >>${FNAME}
for x in $FILELIST
do
if [ "$OTHER_HEADING" == "" ]
then
OTHER_HEADING=`sed -n '1,/'"$i"'/p' $x | grep -iE "<Th|<Table" | tail -1 | awk -v orig="<tr>" -v repl="<th class=awrbg>File Name</th><th class=awrbg>Snap Time</th>" '{sub(orig, repl)}1'`
## The below syntax works in Linux only.
## OTHER_HEADING=`grep -B100000 "$i" $x | grep -iE "<th|<table" | tail -1 | awk -v orig="<tr>" -v repl="<th class=awrbg>File Name</th><th class=awrbg>Snap Time</th>" '{sub(orig, repl)}1'`
echo $OTHER_HEADING >> ${FNAME}
fi
BEGIN_SNAP=`cat $x | grep "Begin Snap:" | awk -F\> '{print $7}' | awk -F \< '{print $1}'`
SNAPTIME_APPEND="<TR><td scope="row" class='awrc'> $x <td scope="row" class='awrc'>${BEGIN_SNAP}"
grep "$i" $x | awk -v orig="<tr>" -v repl="$SNAPTIME_APPEND" '{sub(orig, repl)}1' >> ${FNAME}
done
echo '</table>' >> ${FNAME}
done
#################################################################
echo "Analysis completed. Report is in : $FNAME file."
echo "Provide output file name without any extension (default - summary_out) : "
read FNAME
if [ "${FNAME}" == "" ] ; then
FNAME="summary_out.html"
else
FNAME=$FNAME".html"
fi
TMPFILE=TMP_FILE
TMPFILE01=TMP_FILE01
TMPFILE02=TMP_FILE02
TMPFILE03=TMP_FILE03
TMPFILE04=TMP_FILE04
TMPFILE05=TMP_FILE05
TEMP_FILE=TEMP_FILE
echo '<HTML><HEAD><TITLE>AWR Analysis</TITLE><style type="text/css">
body.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:black; background:White;}
pre.awr {font:8pt Courier;color:black; background:White;}
h1.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
h2.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:Black;background-color:White;margin-top:3pt; margin-bottom:0pt;}
h3.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;}
li.awr {font: 8pt Arial,Helvetica,Geneva,sans-serif; color:black; background:White;}
th.awrnobg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:black; background:White;padding-left:4px; padding-right:4px;padding-bottom:2px}
th.awrbg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px}
td.awrnc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;}
td.awrc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;}
a.awr {font:bold 8pt Arial,Helvetica,sans-serif;color:#663300; vertical-align:top;margin-top:0pt; margin-bottom:0pt;}
</style><body>' > ${FNAME}
##### Print Wait Events heading
WAIT_EVENT_HEADING=`echo "<table border=0 class="tdiff"><tr><th class="awrbg" scope="col">File Name</th><th class="awrbg" scope="col">Snap Time</th>
<th class="awrbg" scope="col">Event</th><th class="awrbg" scope="col">Waits</th>
<th class="awrbg" scope="col">%Time-outs</th><th class="awrbg" cope="col">Total Wait Time (s)</th><th class="awrbg" cope="col">Avg wait (ms)</th>
<th class="awrbg" cope="col">Waits /txn </th><th class="awrbg" cope="col">% DB time</th></tr>"`
##### Print load profile heading
LOAD_PROFILE_HEADING=`echo "<table border=0 class="tdiff"><tr><th class="awrbg" scope="col">DB Name</th><th class="awrbg" scope="col">Instance Name</th>
<th class="awrbg" scope="col">File Name</th><th class="awrbg" scope="col">Snap Begin Time</th><th class="awrbg" scope="col">Snap End Time</th>
<th class="awrbg" scope="col">Elapsed Time</th><th class="awrbg" scope="col">DB Time</th>
<th class="awrbg" scope="col">Redo Size</th><th class="awrbg" scope="col">Logical Reads</th><th class="awrbg" scope="col">Hard Parses</th>
<th class="awrbg" scope="col">Parses</th><th class="awrbg" cope="col">Transactions</th><th class="awrbg" cope="col">Rollbacks</th><th class="awrbg" cope="col">Executes</th>
<th class="awrbg" scope="col">DB CPU (s)</th><th class="awrbg" cope="col">Buffer Cache</th><th class="awrbg" cope="col">Shared Pool</th></tr>"`
##### Print Datafile Read Write Ratio Heading
DATAFILE_RW_RAIO_HEADING=`echo "<table border=0 class="tdiff"><tr><th class="awrbg" scope="col">File Name</th><th class="awrbg" scope="col">Snap Time</th>
<th class="awrbg" scope="col">Type</th><th class="awrbg" scope="col">Reads: Data</th><th class="awrbg" scope="col">Reqs per sec</th>
<th class="awrbg" scope="col">Data per sec</th><th class="awrbg" cope="col">Writes: Data</th><th class="awrbg" cope="col">Reqs per sec</th>
<th class="awrbg" cope="col">Data per sec</th><th class="awrbg" cope="col">Small Read</th><th class="awrbg" cope="col">Large Read</th></tr>"`
FILECOUNT=`ls -1 $FILELIST | wc -l`
echo $FILECOUNT Files to be processed.
FILE_RUN_COUNT=0
for x in $FILELIST
do
awk '/AWR Report for/,/Top 5 Timed Foreground Events/' $x > ${TEMP_FILE}
BEGIN_SNAP=`cat ${TEMP_FILE} | grep "Begin Snap:" | awk -F\> '{print $7}' | awk -F \< '{print $1}'`
END_SNAP=`cat ${TEMP_FILE} | grep "End Snap:" | awk -F\> '{print $7}' | awk -F \< '{print $1}'`
ELAPSED_TIME=`cat ${TEMP_FILE} | grep -i "Elapsed" | awk -F\> '{print $7}' | awk -F \< '{print $1}'`
SNAPTIME_APPEND="<TR><td scope="row" class='awrc'> $x <td scope="row" class='awrc'>${BEGIN_SNAP}"
DBNAME=`grep "AWR Report for" ${TEMP_FILE} | awk -F\, '{print $1}' | awk '{print $NF}'`
INSTNAME=`grep "AWR Report for" ${TEMP_FILE} | awk -F\, '{print $2}' | awk '{print $NF}'`
DBTIME=`grep "DB Time:" ${TEMP_FILE} | awk -F\> '{print $7}' | awk -F \< '{print $1}'`
DBCPU=`grep "DB CPU(s):" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
BUFFER_CACHE=`grep "Buffer Cache:" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
SHARED_POOL=`grep "Shared Pool Size:" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
REDO_SIZE=`grep -i "redo size" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
LOGICAL_READS=`grep -i "logical read" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
HARD_PARSES=`grep -i "hard parses" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
PARSES=`grep -i "parses" ${TEMP_FILE} | grep -vi hard | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
TRANSACTIONS=`grep -i "transactions" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
ROLLBACKS=`grep -i "rollbacks" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
EXECUTES=`grep -i "executes" ${TEMP_FILE} | awk -F\> '{print $5}' | awk -F \< '{print $1}'`
echo "<tr><td scope="row" class='awrc'>" $DBNAME "</td><td scope="row" class='awrc'>" $INSTNAME >> ${TMPFILE}
echo "</td><td scope="row" class='awrc'>" $x "</td><td scope="row" class='awrc'>" $BEGIN_SNAP "</td><td scope="row" class='awrc'>" $END_SNAP >> ${TMPFILE}
echo "</td><td scope="row" class='awrc'>" $ELAPSED_TIME "</td><td align="right" class='awrc'>" $DBTIME "</td><td align="right" class='awrc'>" $REDO_SIZE >> ${TMPFILE}
echo "</td><td align="right" class='awrc'>" $LOGICAL_READS "</td><td align="right" class='awrc'>" $HARD_PARSES "</td><td align="right" class='awrc'>" $PARSES >> ${TMPFILE}
echo "</td><td align="right" class='awrc'>" $TRANSACTIONS "</td><td align="right" class='awrc'>" $ROLLBACKS "</td><td align="right" class='awrc'>" $EXECUTES >> ${TMPFILE}
echo "</td><td align="right" class='awrc'>" $DBCPU"</td><td align="right" class='awrc'>" $BUFFER_CACHE"</td><td align="right" class='awrc'>" $SHARED_POOL"</td></tr>" >> ${TMPFILE}
awk '/Foreground Wait Events/,/Wait Event Histogram/' $x | awk '/<table/,/<\/table/' > ${TEMP_FILE}
for i
do
cat ${TEMP_FILE} | grep "$i" | grep -v User | awk -v orig="<tr>" -v repl="$SNAPTIME_APPEND" '{sub(orig, repl)}1' | head -1 >> ${TMPFILE01}
done
awk '/IOStat by Filetype summary/,/IOStat by Function\/Filetype summary/' $x | awk '/<table/,/<\/table/' | grep "Data File" | awk -v orig="<tr>" -v repl="$SNAPTIME_APPEND" '{sub(orig, repl)}1' >> ${TMPFILE02}
awk '/IOStat by Filetype summary/,/IOStat by Function\/Filetype summary/' $x | awk '/<table/,/<\/table/' | grep "TOTAL:" | awk -v orig="<tr>" -v repl="$SNAPTIME_APPEND" '{sub(orig, repl)}1' >> ${TMPFILE03}
echo '<h3 class=awr>File: ' $x ' Snap Begin Time : ' $BEGIN_SNAP>> ${TMPFILE04};
awk '/Top 5 Timed Foreground Events/,/<\/table/' $x | awk '/<table/,/<\/table/' >> ${TMPFILE04};
echo '<h3 class=awr>File: ' $x ' Snap Begin Time : ' $BEGIN_SNAP>> ${TMPFILE05};
awk '/SQL ordered by Gets/,/SQL ordered by Reads/' $x | awk '/<table/,/<\/table/' >> ${TMPFILE05};
FILE_RUN_COUNT=$(expr "$FILE_RUN_COUNT" + 1)
echo "File $x processed. $FILE_RUN_COUNT file(s) out of $FILECOUNT is done."
done
###### Merging all temp files and writing to main file
echo "Preparing output file..."
echo '<h2 class=awr>Load Profile (values per second)</h2>' >> ${FNAME}
echo ${LOAD_PROFILE_HEADING} >> ${FNAME}
cat ${TMPFILE} >>${FNAME}
echo "</table>" >> ${FNAME}
for i
do
echo '<hr><h2 class=awr>Event :' "${i}" '</h2>' >> ${FNAME}
echo ${WAIT_EVENT_HEADING} >> ${FNAME}
cat ${TMPFILE01} | grep "$i" | sort | awk -v orig="<tr>" -v repl="$SNAPTIME_APPEND" '{sub(orig, repl)}1' >> ${FNAME}
echo "</table>" >> ${FNAME}
done
echo '<hr><h2 class=awr>Data File Read Write Ratio</h2>' >>${FNAME}
echo ${DATAFILE_RW_RAIO_HEADING} >> ${FNAME}
cat ${TMPFILE02} >>${FNAME}
echo "</table>" >> ${FNAME}
echo '<hr><h2 class=awr>Total Read Write Ratio</h2>' >>${FNAME}
echo ${DATAFILE_RW_RAIO_HEADING} >> ${FNAME}
cat ${TMPFILE03} >>${FNAME}
echo "</table>" >> ${FNAME}
echo '<hr><h2 class=awr>Top 5 Wait Events</h2>' >>${FNAME}
cat ${TMPFILE04} >>${FNAME}
echo '<hr><h2 class=awr>Top Buffer Gets SQLs</h2>' >>${FNAME}
cat ${TMPFILE05} >>${FNAME}
## /bin/cp new_awr.html /home/ora12c/
rm ${TMPFILE}
rm ${TMPFILE01}
rm ${TMPFILE02}
rm ${TMPFILE03}
rm ${TMPFILE04}
rm ${TMPFILE05}
rm ${TEMP_FILE}
################ Other Unique parameters to grep ################
echo "Searching other unique parameters"
set "rollback changes - undo records applied" "physical read total IO requests" "physical write total IO requests"
for i
do
OTHER_HEADING=""
echo $i
echo "<hr><h2 class=awr>$i</h2>" >>${FNAME}
for x in $FILELIST
do
if [ "$OTHER_HEADING" == "" ]
then
OTHER_HEADING=`sed -n '1,/'"$i"'/p' $x | grep -iE "<Th|<Table" | tail -1 | awk -v orig="<tr>" -v repl="<th class=awrbg>File Name</th><th class=awrbg>Snap Time</th>" '{sub(orig, repl)}1'`
## The below syntax works in Linux only.
## OTHER_HEADING=`grep -B100000 "$i" $x | grep -iE "<th|<table" | tail -1 | awk -v orig="<tr>" -v repl="<th class=awrbg>File Name</th><th class=awrbg>Snap Time</th>" '{sub(orig, repl)}1'`
echo $OTHER_HEADING >> ${FNAME}
fi
BEGIN_SNAP=`cat $x | grep "Begin Snap:" | awk -F\> '{print $7}' | awk -F \< '{print $1}'`
SNAPTIME_APPEND="<TR><td scope="row" class='awrc'> $x <td scope="row" class='awrc'>${BEGIN_SNAP}"
grep "$i" $x | awk -v orig="<tr>" -v repl="$SNAPTIME_APPEND" '{sub(orig, repl)}1' >> ${FNAME}
done
echo '</table>' >> ${FNAME}
done
#################################################################
echo "Analysis completed. Report is in : $FNAME file."
======END====================
total 7472
-rw-r--r-- 1 oracle dba 10565 Jul 23 09:37 awr_analysis.sh
$ sh awr_analysis.sh
Provide output file name without any extension (default - summary_out) :
14 Files to be processed.
File awrrpt_1_219_220.html processed. 1 file(s) out of 14 is done.
File awrrpt_1_220_221.html processed. 2 file(s) out of 14 is done.
File awrrpt_1_221_222.html processed. 3 file(s) out of 14 is done.
File awrrpt_1_222_223.html processed. 4 file(s) out of 14 is done.
File awrrpt_1_223_224.html processed. 5 file(s) out of 14 is done.
File awrrpt_1_224_225.html processed. 6 file(s) out of 14 is done.
File awrrpt_1_225_226.html processed. 7 file(s) out of 14 is done.
File awrrpt_1_219_220.html processed. 8 file(s) out of 14 is done.
File awrrpt_1_220_221.html processed. 9 file(s) out of 14 is done.
File awrrpt_1_221_222.html processed. 10 file(s) out of 14 is done.
File awrrpt_1_222_223.html processed. 11 file(s) out of 14 is done.
File awrrpt_1_223_224.html processed. 12 file(s) out of 14 is done.
File awrrpt_1_224_225.html processed. 13 file(s) out of 14 is done.
File awrrpt_1_225_226.html processed. 14 file(s) out of 14 is done.
Preparing output file...
Searching other unique parameters
rollback changes - undo records applied
physical read total IO requests
physical write total IO requests
Analysis completed. Report is in : summary_out.html file.
$
$
$ ls -lrt
total 7608
-rw-r--r-- 1 oracle dba 10565 Jul 23 09:37 awr_analysis.sh
-rw-r--r-- 1 oracle dba 152576 Jul 23 09:37 summary_out.html
Output:
No comments:
Post a Comment