Skip to main content

Shell Script for DB server Health Status



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 

#!/bin/bash
################################################################################################
#
# 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>&nbsp;</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> &nbsp;</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'>&nbsp;</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 :) 




Comments

  1. DB_health_status.sh: line 32: syntax error near unexpected token `$'in\r''
    'B_health_status.sh: line 32: `case $opt in

    Getting above error in linux

    ReplyDelete

Post a Comment

Popular posts from this blog

How to unmount and mount ACFS FILESYSTEM

How to unmount and mount ACFS FileSystem in Linux Unmounting ACFS File Systems ACFS file systems can be used by Oracle RAC for hosting software files for the database. It can also be used as a general purpose file system for non-database files. The ACFS file system is managed and administered by the Oracle Grid Infrastructure, So ACFS file systems will be impacted when shutting down the GI stack for patching GI homes. Shut down the processes (including non-Oracle processes) that use the files on ACFS, then unmount the ACFS file system. If the ACFS file system is used by Oracle database software, then perform Steps 1 and 2. 1.      Execute the following command to find the names of the CRS managed ACFS file system resource.  # crsctl stat res -w "TYPE = ora.acfs.type" -p | grep VOLUME 2.      Execute the following command to stop the CRS managed ACFS file system resource with the resource name found from Step 1. ...

How to perform GRID/RDBMS patching of DB systems deployed in OCI using dbcli command line utility

This procedure covers OS, Grid and RDBMS home patching for Oracle Cloud Infrastructure (OCI) DB systems using the dbcli utility. Run every step as root . Advantages of dbcli patching Simple command line, with no manual patch download or copy to the nodes. Patches are downloaded automatically if the service gateway can reach Oracle Object Storage and the YUM repos. Applied in a rolling fashion, so no downtime is needed. Disadvantages You can't choose the patch version; the latest available in OCI is applied. Rollback isn't possible from the console, and dbcli support is limited (see the note at the end). Steps 1. Backup Make sure a recent full backup exists before patching. 2. Blackout Create a blackout for both nodes (console or emctl ) to avoid unnecessary alerts. 3. Check patch availability # dbcli describe-component 4. Pre-checks Grid home: # dbcli update-server -p # dbcli describe-job -i <jobId> RDBMS home: # dbcli list-dbhomes...

To fix huge lag between primary and DR using 12c feature: 12c Recover from service

Scope If transport or apply lag is huge difference, then we can fix using 12c new feature “Recover from Service”. Prerequisites : Oracle Net connectivity is established between the physical standby database and the primary database. You can do this by adding an entry corresponding to the primary database in the tnsnames.ora file of the physical standby database. The password files on the primary database and the physical standby database are the same. The COMPATIBLE parameter in the initialization parameter file of the primary database and physical standby database is set to 12.0 or higher. Start RMAN and connect as target to the physical standby database. Detailed Steps: Step 1: Stop the MRP process in standby database. If dg broker: Edit database <dbname> set state=’APPLY-OFF’; Step 2:   Shut down the standby database and mount the same. Shut immediate;   (srvctl stop database –d <dbname>)  ...