Below script will be useful to gather ASM DG space usage details from all the servers and send a mail.
file1: db_serv_list.csv will have all IP/host names of the environment(you have to list all hosts or IPs in this file)
file2: check_ora_ASM_space.sh will be copied to all target server every time when ever you want to run the report and get the output by login to ASM instance.
file3: ora_ASM_space_report.sh this is the main script you suppose to run, this will gather out put from all the servers to file ora_ASM_space_report.log
ora_ASM_space_report.sh
==========================
#!/bin/bash
rm /opt/oracle/logs/ora_ASM_space_report.log
SERV_LIST=`egrep -v "#" /u01/scripts/db_serv_list.csv`
for i in $SERV_LIST; do
scp /opt/oracle/scripts/check_ora_ASM_space.sh $i:/tmp
ssh $i "sh /tmp/check_ora_ASM_space.sh" >> /u01/logs/ora_ASM_space_report.log
done
export PATH=/usr/sbin:$PATH
awk ' BEGIN {
print "From: goldalerts@symphonyretailai.com"
print "To: example1@oracmp.com example2@oracmp.com"
print "MIME-Version: 1.0"
print "Content-Type: text/html"
print "Subject: Your Subject"
print "<html><body><table border=1 BORDERCOLORDARK=BLUE cellspacing=1 cellpadding=5>"
print "<tr bgcolor=navy>"
print "<td><b>Hostname</b></td>";
print "<td><b>DG_NAME</b></td>";
print "<td><b>PCT_USED</b></td>";
print "<td><b>Avail_GB</b></td>";
print "</tr>"
} {
print "<tr>"
print "<td>"$1"</td>";
print "<td>"$2"</td>";
print "<td>"$3"</td>";
print "<td>"$4"</td>";
print "</tr>"
} END {
print "</table></body></html>"
} ' /u01/logs/ora_ASM_space_report.log | sendmail -t
check_ora_ASM_space.sh
========================
#!/bin/bash
export ORACLE_SID=`egrep -i ":Y|:N" /etc/oratab | cut -d":" -f1 | grep -v "\#" | grep -v "\*"|grep ASM`
if [ -z "$ORACLE_SID" ]
then
exit;
else
export ORACLE_HOME=`egrep -i ":Y|:N" /etc/oratab |grep $ORACLE_SID| cut -d":" -f2 | grep -v "\#" | grep -v "\*"`
export PATH=$ORACLE_HOME/bin:$PATH
DG_SPACE=`sqlplus -s "/ as sysdba" <<EOF
set heading off feedback off verify off
SELECT name group_name,total_mb/1024,(total_mb-free_mb)/1024 Avail_GB,ROUND((1- (free_mb / total_mb))*100, 2) pct_used FROM v\\$asm_diskgroup ORDER BY name;
exit
EOF`
echo `hostname` $DG_SPACE
fi
file1: db_serv_list.csv will have all IP/host names of the environment(you have to list all hosts or IPs in this file)
file2: check_ora_ASM_space.sh will be copied to all target server every time when ever you want to run the report and get the output by login to ASM instance.
file3: ora_ASM_space_report.sh this is the main script you suppose to run, this will gather out put from all the servers to file ora_ASM_space_report.log
ora_ASM_space_report.sh
==========================
#!/bin/bash
rm /opt/oracle/logs/ora_ASM_space_report.log
SERV_LIST=`egrep -v "#" /u01/scripts/db_serv_list.csv`
for i in $SERV_LIST; do
scp /opt/oracle/scripts/check_ora_ASM_space.sh $i:/tmp
ssh $i "sh /tmp/check_ora_ASM_space.sh" >> /u01/logs/ora_ASM_space_report.log
done
export PATH=/usr/sbin:$PATH
awk ' BEGIN {
print "From: goldalerts@symphonyretailai.com"
print "To: example1@oracmp.com example2@oracmp.com"
print "MIME-Version: 1.0"
print "Content-Type: text/html"
print "Subject: Your Subject"
print "<html><body><table border=1 BORDERCOLORDARK=BLUE cellspacing=1 cellpadding=5>"
print "<tr bgcolor=navy>"
print "<td><b>Hostname</b></td>";
print "<td><b>DG_NAME</b></td>";
print "<td><b>PCT_USED</b></td>";
print "<td><b>Avail_GB</b></td>";
print "</tr>"
} {
print "<tr>"
print "<td>"$1"</td>";
print "<td>"$2"</td>";
print "<td>"$3"</td>";
print "<td>"$4"</td>";
print "</tr>"
} END {
print "</table></body></html>"
} ' /u01/logs/ora_ASM_space_report.log | sendmail -t
check_ora_ASM_space.sh
========================
#!/bin/bash
export ORACLE_SID=`egrep -i ":Y|:N" /etc/oratab | cut -d":" -f1 | grep -v "\#" | grep -v "\*"|grep ASM`
if [ -z "$ORACLE_SID" ]
then
exit;
else
export ORACLE_HOME=`egrep -i ":Y|:N" /etc/oratab |grep $ORACLE_SID| cut -d":" -f2 | grep -v "\#" | grep -v "\*"`
export PATH=$ORACLE_HOME/bin:$PATH
DG_SPACE=`sqlplus -s "/ as sysdba" <<EOF
set heading off feedback off verify off
SELECT name group_name,total_mb/1024,(total_mb-free_mb)/1024 Avail_GB,ROUND((1- (free_mb / total_mb))*100, 2) pct_used FROM v\\$asm_diskgroup ORDER BY name;
exit
EOF`
echo `hostname` $DG_SPACE
fi
