Database Server health Status Shell script
Shell script for DB Server Health Status:
Save the below code as DB_health_status.sh and provide execute privileges
################################################################################################
#
# PURPOSE: Database status report
# Srinivas.p
################################################################################################
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# SET ENVIRONMENT VARIABLES
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set -vx
if [[ $0 != "/"* ]]; then
MYPATH=`pwd`"/"`dirname $0`
else
MYPATH=`dirname $0`
fi
cd ${MYPATH}
script_dir=${MYPATH}
dat=$script_dir/dat
Today=`date +%Y%m%d_%H`
V1='NO FILE SYSTEM ALERTS'
V3='NO TABLESPACE ALERTS'
V2='NO ASM ALERTS'
Alt=90
cntt=0
DGALT=80
stdserv=0
dgs='FALSE'
email="NO"
while getopts "d:e:" opt ;
do
echo "$opt" $OPTARG
case $opt in
d) dgs="${OPTARG}" ;;
e) email="${OPTARG}" ;;
esac
done
log=$script_dir/log
server="$log/serv"
touch $server
server_list="$log/server_list"
if [ ${email} == "NO" ] ;
then
echo "Invalid email address provided"
exit;
fi
function dbn
{
sid8=`ps -ef | grep pmon | grep ASM |awk '{print $8 }'|cut -c 10-18`
export ORACLE_SID=$sid8
export ORAENV_ASK=NO
export PATH=$PATH:/usr/local/bin/:/usr/loca/sbin
. oraenv
if [ -f $server ]
then
rm -f $server
olsnodes > $server
if [ -s $server ]
then
rm -rf $log/dblist
ps -ef | grep ora_pmon | grep -v grep | grep -v ASM | grep -v MGMTDB | awk '{ print substr($8,10)}' | rev | cut -c 2- |rev > $log/dblist
stdserv=1
else
rm -rf $log/dblist
ps -ef | grep ora_pmon | grep -v grep | grep -v ASM | grep -v MGMTDB | awk '{ print substr($8,10)}' > $log/dblist
hostname > $server
fi
else
echo "file not exists "
fi
}
function db_status
{
echo '<htm><body><h1>DB Status : </h1><h3> '
sid8=`ps -ef | grep pmon | grep ASM |awk '{print $8 }'|cut -c 10-18`
export ORACLE_SID=$sid8
export ORAENV_ASK=NO
. oraenv
for dbname in `cat $log/dblist| tr -s '\n' ' '`
do
echo "DB Name : $dbname"
echo '<br>'
$ORACLE_HOME/bin/srvctl status database -d ${dbname}
echo '<br>'
done
echo '</h3><br>'
}
function service_status
{
echo "<htm><body><h1>DB Services Status : </h1><h4>"
sid9=`ps -ef | grep pmon | grep ASM |awk '{print $8 }'|cut -c 10-18`
export ORACLE_SID=$sid9
export ORAENV_ASK=NO
. oraenv
for dbname in `cat $log/dblist| tr -s '\n' ' '`
do
echo "<br>"
echo "DB : $dbname " | tr '[a-z]' '[A-Z]'
echo "<table border=1>"
echo "<th>SERVICE NAME </th> <th>Current Status </th> <th> Prefered Instance </th>"
$ORACLE_HOME/bin/srvctl status service -d $dbname | awk '{ print $2 }' > $log/service.log
for sname in `cat $log/service.log | tr -s '\n' ' '`
do
$ORACLE_HOME/bin/srvctl status service -d $dbname -s $sname | awk '{ print $7 }' | tr '[a-z]' '[A-Z]' > $log/svc1.log
$ORACLE_HOME/bin/srvctl config service -d $dbname -s $sname | grep Preferred | awk '{ print $3 }' | tr '[a-z]' '[A-Z]' > $log/svc2.log
sv1=`cat $log/svc1.log`
sv2=`cat $log/svc2.log`
if [[ $sv1 == $sv2 ]]; then
echo "<tr><td> $sname </td><td> $sv1 </td><td> $sv2 </td></tr>"
else
echo "<tr bgcolor=red><td> $sname </td><td> $sv1 </td><td> $sv2 </td></tr>"
fi
done
echo "</table>"
done
echo '</h4><br>'
}
function service_status_std
{
echo "<htm><body><h1>DB Services Status : </h1><h4>"
sid5=`ps -ef | grep pmon | grep ASM |awk '{print $8 }'|cut -c 10-18`
export ORACLE_SID=$sid5
export ORAENV_ASK=NO
. oraenv
for dbname in `cat $log/dblist| tr -s '\n' ' '`
do
echo "<br>"
echo "DB : $dbname " | tr '[a-z]' '[A-Z]'
echo "<table border=1>"
echo "<th>SERVICE NAME </th> <th>Current Status </th>"
$ORACLE_HOME/bin/srvctl status service -d $dbname | awk '{ print $2 }' > $log/service.log
for sname in `cat $log/service.log | tr -s '\n' ' '`
do
$ORACLE_HOME/bin/srvctl status service -d $dbname -s $sname | grep "is running" > $log/svc1.log
if [ -s $log/svc1.log ]; then
echo "<tr><td> $sname </td><td> "Running" </td></tr>"
else
echo "<tr bgcolor=red><td> $sname </td><td> "Not Running" </td></tr>"
fi
done
echo "</table>"
done
echo '</h4><br>'
}
function OSDF
{
# OS FILESYSTEM
for SERV_NAME in `cat $server | tr -s ',' ' ' `
do
ssh $SERV_NAME df -Pk | grep -vE '^Filesystem|tmpfs' | awk '{print $5}' | cut -d'%' -f1 > $log/fs.txt
for FLE in `cat $log/fs.txt | tr -s ',' ' ' `
do
if [ $FLE -gt $Alt ]; then
cntt=1
ssh $SERV_NAME df -Pk | grep -vE '^Filesystem|tmpfs' | awk '{print $5 " " $6}' | file_system_status
break
fi
done
if [ $cntt == 0 ] ; then
echo '<HTML><BODY>'
echo '<H3>Server Name :- '$SERV_NAME'</H3><H4><P align=center>FILE SYSTEM ALERTS</P></H4><TABLE BORDER=2 CELLSPACING=0 CELLPADDING=2 align=center>'
echo '<TR bgcolor=#CCCC99><TH>'$V1'</TH></TR>'
echo '</TABLE><p align=center><font color="#868A08" size="7" face="wingdings"> J </font></p><P align=center>Generated at '$(date '+%H:%M on %d-%b-%y')'</BODY></HTML>'
fi
done
}
function file_system_status
{
echo '<HTML><HEAD></HEAD><BODY>'
echo '<H3>Server Name :- '$SERV_NAME'</H3><H4><P align=center>FILE SYSTEM ALERTS</P></H4><TABLE BORDER=2 CELLSPACING=0 CELLPADDING=2 align=center>'
echo '<TR bgcolor=#CCCC99><TH>Filesystem</TH><TH>Percentage</TH>'
while read output;
do
usep=$(echo $output | awk '{print $1}' | cut -d'%' -f1)
partition=$(echo $output | awk '{print $2}')
if [ $usep -ge $Alt ]; then
echo '<TR bgcolor=#F7F7E7><TD>'$partition'</TD><TD ALIGN=RIGHT>'$usep'%</TD>'
fi
done
echo '</TABLE><P align=center>Generated at '$(date '+%H:%M on %d-%b-%y')'</BODY></HTML>'
}
function TBS
{
# Tablespace
ps -ef | grep ora_pmon | grep -v grep | grep -v ASM | awk '{ print substr($8,10)}' > $log/dbns.log
for dbname in `cat $log/dbns.log | tr -s '\n' ' '`
do
export ORACLE_SID=$dbname
export ORAENV_ASK=NO
. oraenv
$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' << EOF > $log/tbslog.log
@$dat/tbs.sql
EOF
if [ -s $log/tbslog.log ]
then
echo '<HTML><HEAD><TITLE>DATABASE HEALTH STATUS </TITLE></HEAD><BODY>'
echo '<H3>'$dbname'</H3><H4><P align=center>TABLESPACE ALERTS</P></H4><TABLE BORDER=2 CELLSPACING=0 CELLPADDING=2 align=center>'
echo '<TR bgcolor=#CCCC99><TH>DATABASE</TH><TH>TABLESPACE_NAME</TH><TH>CURRENT_SIZE(M)</TH><TH>MAX(M)</TH>'
echo '<TH>USED(M)</TH><TH>FREE(M)</TH><TH>CAPACITY</TH></TR>'
cat $log/tbslog.log | awk 'NR != 0 && NF==7'|sort| while read DBNAME TSNAME CURRENT MAX USED FREE USED_PCT
do
echo '<TR bgcolor=#F7F7E7><TD>'$DBNAME'</TD><TD>'$TSNAME'</TD><TD ALIGN=RIGHT>'$CURRENT'</TD>'
echo '<TD ALIGN=RIGHT>'$MAX'</TD><TD ALIGN=RIGHT>'$USED'</TD>'
echo '<TD ALIGN=RIGHT>'$FREE'</TD>'
echo '<TD><TABLE BORDER=0 CELLSPACING=1 CELLPADDING=0><TR>'
echo '<TD WIDTH='$((150*$USED/$MAX))' BGCOLOR="#868A08"></TD>'
echo '<TD WIDTH='$((150*$FREE/$MAX))' BGCOLOR="#B4B46E"></TD>'
echo '<TD><FONT SIZE=-1 COLOR=black> '$USED_PCT'</FONT></TD>'
echo '<TR></TABLE></TD></TR>'
echo '<br>'
done
echo '</TABLE><P align=left>Generated at '$(date '+%H:%M on %d-%b-%y')'</BODY></HTML>'
else
echo '<HTML><HEAD><TITLE>DATABASE HEALTH STATUS </TITLE></HEAD><BODY>'
echo '<H3>'$dbname'</H3><H4><P align=center>TABLESPACE ALERTS</P></H4><TABLE BORDER=2 CELLSPACING=0 CELLPADDING=2 align=center>'
echo '<TR bgcolor=#CCCC99><TH>'$V3'</TH></TR>'
echo '</TABLE><p align=center><font color="#868A08" size="7" face="wingdings"> J </font></p><P align=center>Generated at '$(date '+%H:%M on %d-%b-%y')'</BODY></HTML>'
fi
done
}
function ASM
{
sid1=`ps -ef | grep pmon | grep ASM |awk '{print $8 }'|cut -c 10-18`
export ORACLE_SID=$sid1
export ORAENV_ASK=NO
. oraenv
$ORACLE_HOME/bin/sqlplus -S '/ as sysasm' << EOF > $log/asmds.log
set linesize 200
set pagesize 50000
col free_pct format a8
set heading off
set feedback off;
col name for a15
col state for a15
select name,state,total_mb/1024,free_mb/1024,round((1- (free_mb/total_mb))*100, 2)"used_pct" from v\$asm_diskgroup;
EOF
cat $log/asmds.log | awk 'NR != 0 && NF == 5' | awk -v dgl=$DGALT '($5 >= dgl)' > $log/asmds1.log
if [ -s $log/asmds1.log ]
then
echo '<HTML><HEAD><TITLE>Database Health Status</TITLE></HEAD><BODY>'
echo '<H3>'`hostname`'</H3><H4><p align=center> ASM DISKGROUP ALERTS </p></H4><TABLE BORDER=2 CELLSPACING=0 CELLPADDING=2 align=center>'
echo '<TR bgcolor=#CCCC99><TH>NAME</TH><TH>STATE</TH>'
echo '<TH>TOTAL_GB</TH><TH>FREE_GB</TH><TH>USED_PERCENTAGE</TH></TR>'
cat $log/asmds.log | awk 'NR != 0 && NF == 5' | awk -v dgl=$DGALT '($5 >= dgl)' |sort|while read NAME STATE TOTAL_GB FREE_GB USED_PCT
do
echo '<TR bgcolor=#F7F7E7><TD>'$NAME'</TD><TD ALIGN=RIGHT>'$STATE'</TD>'
echo '<TD ALIGN=RIGHT>'$TOTAL_GB'</TD><TD ALIGN=RIGHT>'$FREE_GB'</TD>'
echo '<TD><TABLE BORDER=0 CELLSPACING=1 CELLPADDING=0><TR>'
echo '<TD ALIGN=RIGHT>'$USED_PCT'</TD>'
echo '<TR></TABLE></TD></TR>'
echo '<br>'
done
echo '</TABLE><P align=center>Generated at '$(date '+%H:%M on %d-%b-%y')'</BODY></HTML>'
else
echo '<HTML><HEAD><TITLE>Database Health Status</TITLE></HEAD><BODY>'
echo '<H3>'`hostname`'</H3><H4><P align=center> ASM DISKGROUP ALERTS</P></H4><TABLE BORDER=2 CELLSPACING=0 CELLPADDING=2 align=center>'
echo '<TR bgcolor=#CCCC99><TH>'$V2'</TH></TR>'
echo '</TABLE><p align=center><font color="#868A08" size="7" face="wingdings"> J </font></p><P align=center >Generated at '$(date '+%H:%M on %d-%b-%y')'</BODY></HTML>'
fi
}
function alert_db
{
echo '<br>'
echo '<h1> DB Alert Logs : </h1>'
for SERV_NAME in `cat $server | tr -s '\n' ' '`
do
echo '<html><body><h2>Server: ' $SERV_NAME '</h2>'
echo '<table BORDER=2 CELLSPACING=1 CELLPADDING=0 align=center>'
ssh $SERV_NAME ". ~/.profile>/dev/null;$ORACLE_HOME/bin/adrci exec=\"set homepath diag/rdbms;show homes;\"" |grep -v trace |grep -v dump |grep diag|grep -v MGMTDB | cut -d
: -f2 > $log/rdhomes
for adrci_home in `cat $log/rdhomes | tr -s '\n' ' '`
do
db_name=`basename "$adrci_home"`
ssh $SERV_NAME ". ~/.profile > /dev/null; $ORACLE_HOME/bin/adrci exec=\"set home ${adrci_home};show alert -term -p (message_text like '%ORA-%' or message_text like '%UNUSAB
LE%' or message_text like '%ERROR%' or message_text like '%was suspended due to%' or message_text like '%Corrupt block relative dba%') and originating_timestamp > systimest
amp-1;\""|cut -d : -f2 > $log/"${db_name}".log
num_errors=`grep -c -e 'TNS' -e 'ORA' -e 'Corrupt block relative dba' -e 'UNUSABLE' -e 'ERROR' -e 'was suspended due to' $log/"${db_name}".log`
file1=$log/"${db_name}".log
echo '<tr bgcolor=#CCCC99 ><td width="60%">'
echo '<B>'
echo 'Instance Name :' "$db_name"
echo '</B></td></tr>'
if [ $num_errors -gt 0 ]
then
echo '<tr bgcolor=#CCCC99><td>'
while IFS= read -r line;
do
echo "$line" '<br>'
done < "$file1"
echo '</td></tr>'
else
echo '<br>'
echo '<TR bgcolor=#CCCC99><TD> NO ALERTS FOR LAST 24 HRS !!!'
echo '<p align=left><font color="#868A08" size="5" face="wingdings"> J </font></p><P align=left>Generated at '$(date '+%H:%M on %d-%b-%y')'</td></tr></BODY></HTML>'
fi
done
echo '</table></BODY></HTML>'
done
}
function alert_asm
{
echo '<h1> ASM Alert Logs : </h1>'
for SERV_NAME in `cat $server | tr -s '\n' ' '`
do
echo '<html><body><h2>Server: ' $SERV_NAME '</h2>'
echo '<table BORDER=2 CELLSPACING=1 CELLPADDING=0 align=center>'
ssh $SERV_NAME ". ~/.profile>/dev/null;$ORACLE_HOME/bin/adrci exec=\"set homepath diag/asm;show homes;\"" | grep -v trace | grep -v dump| grep diag |grep +asm| cut -d : -f2
> $log/asmhomes
for adrci_home in `cat $log/asmhomes | tr -s '\n' ' '`
do
db_name=`basename "$adrci_home"`
ssh $SERV_NAME ". ~/.profile>/dev/null;$ORACLE_HOME/bin/adrci exec=\"set home ${adrci_home};show alert -term -p (message_text like 'ORA-%' or message_text like '%WARNING%'
or message_text like '%ERROR%') and originating_timestamp > systimestamp-1;\""|cut -d : -f2 > $log/"${db_name}".log
num_errors=`grep -c -e 'WARNING' -e 'ORA-' -e 'ERROR' $log/"${db_name}".log`
file1=$log/"${db_name}".log
echo '<tr bgcolor=#CCCC99 ><td width="60%">'
echo '<B>'
echo 'Instance Name :' "$db_name"
echo '</B></td></tr>'
if [ $num_errors -gt 0 ]
then
echo '<tr bgcolor=#CCCC99><td>'
while IFS= read -r line;
do
echo "$line" '<br>'
done < "$file1"
echo '</td></tr>'
else
echo '<br>'
echo '<TR bgcolor=#CCCC99><TD> NO ALERTS FOR LAST 24 HRS !!!'
echo '<p align=left><font color="#868A08" size="5" face="wingdings"> J </font></p><P align=left>Generated at '$(date '+%H:%M on %d-%b-%y')'</td></tr></BODY></HTML>'
fi
done
echo '</table></BODY></HTML>'
done
}
function cluster_status
{
sid3=`ps -ef | grep pmon | grep -v grep | grep ASM | awk '{ print substr($8,10)}'`
export ORACLE_SID=$sid3
export ORAENV_ASK=NO
. oraenv
$ORACLE_HOME/bin/crsctl stat res > $log/crsctl_status
awk 'NF > 0' $log/crsctl_status > $log/crsctl_status1
awk -F '=' '{print $2}' $log/crsctl_status1 > $log/crsctl_status2
echo '<HTML><HEAD><TITLE>Database Health Status </TITLE></HEAD><BODY>'
echo '<H3>'`hostname`'</H3><H4><P align=center>Cluster Status</P></H4><TABLE BORDER=2 CELLSPACING=0 CELLPADDING=2 align=center>'
echo '<TR bgcolor=#CCCC99><TH>Name</TH><TH>Type </TH><TH>Target</TH><TH>State</TH></TR>'
cat $log/crsctl_status2 | while read Name; read Type; read Target; read State
do
echo "$Target" | grep -q "OFFLINE"
if [ $? -eq 0 ] ;
then
echo '<TR bgcolor=red><TD>'$Name'</TD><TD>'$Type'</TD><TD ALIGN=RIGHT>'$Target'</TD>'
echo '<TD ALIGN=RIGHT>'$State'</TD>'
echo '<TD><TABLE BORDER=0 CELLSPACING=1 CELLPADDING=0><TR>'
echo '<TR></TABLE></TD></TR>'
else
echo '<TR bgcolor=#F7F7E7><TD>'$Name'</TD><TD>'$Type'</TD><TD ALIGN=RIGHT>'$Target'</TD>'
echo '<TD ALIGN=RIGHT>'$State'</TD>'
echo '<TD><TABLE BORDER=0 CELLSPACING=1 CELLPADDING=0><TR>'
echo '<TR></TABLE></TD></TR>'
fi
done
echo '</TABLE><P align=left>Generated at '$(date '+%H:%M on %d-%b-%y')'</BODY></HTML>'
}
function dg_status
{
ps -ef | grep ora_pmon | grep -v grep | grep -v ASM | awk '{ print substr($8,10)}' | grep -v MGMTDB > $log/dblist
echo '<htm><body><h1>DataGuard Status : </h1><h3> '
for dbname in `cat $log/dblist| tr -s '\n' ' '`
do
export ORACLE_SID=$dbname
export ORAENV_ASK=NO
. oraenv
echo "DB Name : $dbname"
echo '<br>'
dgmgrl -silent / "show configuration" > $log/dgstatus
for i in 7 8 9 10
do
cat $log/dgstatus | awk 'NR=='$i'{print $1}' > $log/dgst1
dg_name=`cat $log/dgst1 | awk '{print $1}'`
if [ -z "$dg_name" ]
then
break
else
dgmgrl -silent / "show database $dg_name"
echo '<br>'
fi
echo '<br>'
done
done
echo '</h3><br>'
}
function cleanup
{
rm -rf final.html
rm -rf final1.html
rm -rf final2.html
rm -rf Database_Health_Status.html
}
cleanup
dbn
cat header_note.html >> final.html
db_status > final1.html
while IFS= read -r line;
do
echo $line '<br>' >> final.html
done < final1.html
if [ $stdserv == 1 ]; then
service_status >> final.html
else
service_status_std >> final.html
fi
cluster_status >> final.html
OSDF >> final.html
TBS >> final.html
ASM >> final.html
alert_db >> final.html
alert_asm >> final.html
if [ $dgs == "TRUE" ]
then
dg_status > final2.html
while IFS= read -r line;
do
echo $line '<br>' >> final.html
done < final2.html
fi
cat footer_note.html >> final.html
cat final.html > Database_Health_Status.html
echo "Please find the attachment for details" | mailx -s "Database health Status" -a "Database_Health_Status.html" ${email}
======================================================================
Save below has header_note.html :
================================
<html>
<HEAD><TITLE>Database Health Status Report </TITLE></HEAD><div class=WordSection1>
<p class=MsoNormal><span style='mso-no-proof:yes'><o:p> </o:p></span></p>
<style type="text/css">
.html-marquee {height:40px;width:1200px;background-color:666600;font-family:Arial;font-size:22pt;color:FFCC00;font-weight:bold;border-width:1;border-style:solid;border-colo
r
:000099;}
</style>
<marquee class="html-marquee" behavior="alternate" scrollamount="1" >Delta DBA TEAM @ EPSILON </marquee><p style="font-family:arial,sans-serif;font-size:10px;"></p>
<p class=MsoNormal align=center style='text-align:center'><b style='mso-bidi-font-weight:normal'><span style='font-size:22.0pt;mso-bidi-font-size:11.0pt;line-height:115%;co
l
or:#948A54;mso-themecolor:background2;mso-themeshade:128'>Database Health Status Report <o:p></o:p></span></b></p>
</div>
</html>
===================================================================
Save below as Footer_note.html
========================
<html>
<body lang=EN-US link=blue vlink=purple style='tab-interval:.5in'>
<div class=WordSection1>
<p class=MsoNormal><span class=GramE><span style='color:blue'>Prepared for</span>
<span style='mso-no-proof:yes'>Delta OnCall DBA</span><span style='color:blue'> by</span> <b style='mso-bidi-font-weight:normal'><span
style='color:red'>Srinivas P</span></b>.</span> </p>
<p class=MsoNoSpacing><span class=GramE><span style='font-size:10.0pt'>Contact :-</span></span><span style='font-size:10.0pt'>
<body onload="setInterval('blinkIt()',500)">
<script type="text/javascript">
function blinkIt() {
if (!document.all) return;
else {
for(i=0;i<document.all.tags('blink').length;i++){
s=document.all.tags('blink')[i];
s.style.visibility=(s.style.visibility=='visible') ?'hidden':'visible';
}
}
}
</script>
<blink>
<a href="mailto:delta_dba@epsilon.com">delta_dba@epsilon.com</blink></body1></a> for questions</span>.</p>
<p class=MsoNormal style='margin-left:2.0in'> </p>
</div>
</body>
</html>
========================================================================
Create directories called "dat" and "log" under same location where the above files were created then create following files under "dat" directory.
create tbs.sql file as below, Also you can set threshold by changing the bold value below.
SET verify off
SET feedback off
SET linesize 150
SET pagesize 200
SET termout off
SET head off
trimspool OFF
COLUMN DB_NAME FORMAT a25
COLUMN TABLESPACE_NAME FORMAT a25
COLUMN "CURRENT_SIZE_MB" FORMAT 999999999
COLUMN "MAX_SIZE_MB" FORMAT 999999999
COLUMN "USED_SIZE_MB" FORMAT 999999999
COLUMN "FREE_MB" FORMAT 999999999
COLUMN "USED_PCT" FORMAT a10
column instance_name new_val db_name
select upper(instance_name) "instance_name" from v$instance;
column value new_val blksize
select value
from v$parameter
where name = 'db_block_size';
SET termout on
select '&&db_name' "DB_NAME",
a.tablespace_name "TABLESPACE_NAME",
SUM(a.bytes)/1024/1024 "CURRENT_SIZE_MB",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*&&blksize/1024/1024)) "MAX_SIZE_MB",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "USED_SIZE_MB",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*&&blksize/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "FREE_MB",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null
, A
.BYTES/1024/1024, b.maxextend*&&blksize/1024/1024))))||'%' "USED_PCT"
from
dba_data_files a,
sys.filext$ b,
(SELECT '&&db_name' "DB_NAME", d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE
c
where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
having round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend,
null, A.BYTES/1024/1024, b.maxextend*&&blksize/1024/1024))))> 70
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend
, null, A.BYTES/1024/1024, b.maxextend*&&blksize/1024/1024)))) desc;
exit
===================================================================
alert.sh
#!/bin/bash
echo '<html><body><h2>Server: ' $SERV_NAME '</h2>'
echo '<table BORDER=2 CELLSPACING=1 CELLPADDING=0 align=center>'
sid3=`ps -ef | grep pmon | grep ASM |awk '{print $8 }'|cut -c 10-18`
export ORACLE_SID=$sid3
export ORAENV_ASK=NO
. oraenv
"$ORACLE_HOME/bin/adrci exec=\"set homepath diag/rdbms;show homes;\"" | grep -v trace | grep -v dump | grep diag | grep -v MGMTDB | cut -d : -f2 > rdhomes
for adrci_home in `cat rdhomes | tr -s '\n' ' '`
do
db_name=`basename "$adrci_home"`
"$ORACLE_HOME/bin/adrci exec=\"set home ${adrci_home};show alert -term -p (message_text like '%ORA-%' or message_text like '%UNUSABLE%' or message_text like '%ERROR%' or me
ssage_text like '%was suspended due to%' or message_text like '%Corrupt block relative dba%') and originating_timestamp > systimestamp-1;\""|cut -d : -f2- > "${db_name}".
log
num_errors=`grep -c -e 'TNS' -e 'ORA' -e 'Corrupt block relative dba' -e 'UNUSABLE' -e 'ERROR' -e 'was suspended due to' "${db_name}".log`
file1="${db_name}".log
echo '<tr bgcolor=#CCCC99 ><td width="60%">'
echo '<B>'
echo 'Instance Name :' "$db_name"
echo '</B></td></tr>'
if [ $num_errors -gt 0 ]
then
echo '<tr bgcolor=#CCCC99><td>'
while IFS= read -r line;
do
echo "$line" '<br>'
done < "$file1"
echo '</td></tr>'
else
echo '<br>'
echo '<TR bgcolor=#CCCC99><TD> NO ALERTS FOR LAST 24 HRS !!!'
echo '<p align=left><font color="#868A08" size="5" face="wingdings"> J </font></p><P align=left>Generated at '$(date '+%H:%M on %d-%b-%y')'</td></tr></BODY></HTML>'
fi
done
echo '</table></BODY></HTML>'
==================================================================
How to execute the above Script:
Option 1 : If we have dgmgrl/datagaurd configured then run as below
sh DB_health_status.sh -d TRUE -e "email_address"
Option 2 : If we don't have datagaurd setup then run as below
sh DB_health_status.sh -e "email_address"
Note: If you need any support to setup this script, please do reach out to me at "srinivas.dba98@gmail.com". I am happy to help you :)
DB_health_status.sh: line 32: syntax error near unexpected token `$'in\r''
ReplyDelete'B_health_status.sh: line 32: `case $opt in
Getting above error in linux