Below script monitors and send mail if TBS is reached 80%. this is generic script works for all the databases in one server.
#!/bin/bash
OUTPUT_LOC=/u01/oracle/admin/output
SCRIPT_LOC=/u01/oracle/admin/scripts
DB_LIST=`egrep -i ":Y|:N" /etc/oratab | cut -d":" -f1 | grep -v "\#" | grep -v "\*"|grep -v ASM`
for DB in $DB_LIST ; do
echo "HOST: `hostname` DB Name: $DB" > $OUTPUT_LOC/tbs_mon.out
export ORACLE_SID=$DB
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
TBS=`sqlplus -s "/ as sysdba" <<EOF
set heading off feedback off verify off
select tablespace_name from dba_tablespaces where contents != 'UNDO';
exit;
EOF`
for i in $TBS;
do
if [ ! -z "$i" ]
then
TBS_SPACE=`sqlplus -s "/ as sysdba" <<EOF
set pagesize 200
set linesize 121
set heading off feedback off verify off
SELECT tablespace_name,
ROUND(SUM(max_gb) - (SUM(total_gb)-SUM(free_gb))) FREE_SPACE_GB,
ROUND(SUM(max_gb)) MAX_SZ_GB, ROUND((SUM(total_gb)-SUM(free_gb))/SUM(max_gb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024/1024 FREE_GB,
0 TOTAL_GB, 0 MAX_GB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_GB,
SUM(bytes)/1024/1024/1024 TOTAL_GB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024/1024 MAX_GB
FROM dba_data_files
GROUP BY tablespace_name) where tablespace_name='$i'
GROUP BY tablespace_name;
exit;
EOF`
TBS_NAME="`echo $TBS_SPACE|awk '{ print $1}'`"
PCT_FULL="`echo $TBS_SPACE|awk '{ print $4}'`"
if [ ! -z $PCT_FULL ]
then
if [ $PCT_FULL -ge 80 ]
then
echo "TBS:" $i " PCT_FULL: " $PCT_FULL >>$OUTPUT_LOC/tbs_mon.out
fi
fi
fi
done
CHECK_FILE=`grep -i PCT_FULL $OUTPUT_LOC/tbs_mon.out`
if [ ! -z "$CHECK_FILE" ]
then
cat -v $OUTPUT_LOC/tbs_mon.out | mail -r "sender@company.com" -s "`hostname`:oracle TBS utilization in critical" "whoever@company.com whoever2@company.com"
fi
done
trace files deletion script for oracle
House keeping script to delete trace files and xml files older than 5 days.
It reads database names from oratab and delete trace files from all the databases listed in /etc/oratab.
#!/bin/bash
DB_LIST=`egrep -i ":Y|:N" /etc/oratab | cut -d":" -f1 | grep -v "\#" | grep -v "\*"`
for i in $DB_LIST ; do
export ORACLE_SID=$i
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
TRACE_LOC=`sqlplus -s "/ as sysdba" <<EOF
set heading off feedback off verify off
select VALUE from v\\$diag_info where NAME='Diag Trace';
exit
EOF`
find $TRACE_LOC -name '*.trc' -mtime +5 -exec rm -f {} \;
find $TRACE_LOC -name '*.trm' -mtime +5 -exec rm -f {} \;
ALXML_LOC=`sqlplus -s "/ as sysdba" <<EOF
set heading off feedback off verify off
select VALUE from v\\$diag_info where NAME='Diag Alert';
exit
EOF`
find $ALXML_LOC -name 'log_*' -mtime +5 -exec rm -f {} \;
done
Oracle Listener log rotation script- Generic
Listener log rotation script(generic)- it works for what ever listeners currently running on the server.
#!/bin/bash
##Listener log rotation##
LSNR_NAME=`ps -ef|grep tnslsnr|grep -v grep|awk '{print $9}'`
for i in $LSNR_NAME; do
ORACLE_HOME=`ps -ef|grep tnslsnr|grep -v grep|awk '{print $8}'| rev | cut -d'/' -f3- | rev`
export PATH=$ORACLE_HOME/bin:$PATH
LSNR_LOG_LOC=`lsnrctl status ${LSNR_NAME} | grep "Listener Log File" | awk '{ print $4 }'|rev|cut -d '/' -f2-|rev`
find $LSNR_LOG_LOC -name 'log_*' -mtime +5 -exec rm -f {} \;
cd ../trace
LSNR_LOG_FILE=`ls -lrt *.log|awk '{print $9}'`
find -name '*.gz' -mtime +10 -exec rm -f {} \;
tar -cvzf $LSNR_LOG_FILE.`date '+%d%m%Y%H%M%S'`.tar.gz $LSNR_LOG_FILE
cat /dev/null > $LSNR_LOG_FILE
done
(or)
#!/bin/bash
##Listener log rotation##
LSNR_NAME=`ps -ef|grep tnslsnr|grep -v grep|awk '{print $9}'`
for i in $LSNR_NAME; do
ORACLE_HOME=`ps -ef|grep $i|grep -v grep|awk '{print $8}'| rev | cut -d'/' -f3- | rev`
export PATH=$ORACLE_HOME/bin:$PATH
LSNR_LOG_LOC=`lsnrctl <<-EOF | grep trc_directory | awk '{ print $6 }'
set displaymode normal
set current_listener ${i}
show trc_directory
EOF`
LSNR_XML_LOC=`lsnrctl <<-EOF | grep log_directory | awk '{ print $6 }'
set displaymode normal
set current_listener ${i}
show log_directory
EOF`
find $LSNR_XML_LOC -name 'log_*' -mtime +5 -exec rm -f {} \;
LSNR_LOG_FILE=`ls -lrt $LSNR_LOG_LOC/*.log|awk '{print $9}'`
find $LSNR_LOG_LOC -name '*.gz' -mtime +10 -exec rm -f {} \;
tar -cvzf $LSNR_LOG_FILE.`date '+%d%m%Y%H%M%S'`.tar.gz $LSNR_LOG_FILE
cat /dev/null > $LSNR_LOG_FILE
done
Script for ASM usage report from all the servers
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
shell script to monitor ASM DG space to send alert
Script to monitor ASM DG space. it will send alert if space is reached to 90% full and also if its full 80% with less than 100 GB space. schedule it to run for every 15min
--> 90% full --- send alert
--> 80% and avail space less than 100GB -- send alert
#!/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:/usr/sbin:$PATH
DG_LIST=`sqlplus -s "/ as sysdba" <<EOF
set heading off feedback off verify off
SELECT name from v\\$asm_diskgroup ORDER BY name;
exit
EOF`
for i in $DG_LIST; do
DG_SPACE=`sqlplus -s "/ as sysdba" <<EOF
set heading off feedback off verify off
SELECT name group_name,total_mb/1024,ROUND((free_mb)/1024) Avail_GB,ROUND((1- (free_mb / total_mb))*100) pct_used FROM v\\$asm_diskgroup where name='$i';
exit
EOF`
DG_NAME="`echo $DG_SPACE|awk '{ print $1}'`"
PCT="`echo $DG_SPACE|awk '{ print $4}'`"
AVL_GB="`echo $DG_SPACE|awk '{ print $3}'`"
if [ $PCT -ge 80 ] && [ $PCT -lt 90 ]
then
if [ $AVL_GB -lt 100 ]
then
echo `hostname` $DG_SPACE | mail -r "sender@company.com" -s "`hostname`:GOLD-oracle ASM-DG $DG_NAME utilization in critical" "yourmail@company.com"
fi
else if [ $PCT -ge 90 ]
then
echo `hostname` $DG_SPACE | mail -r "sender@company.com" -s "`hostname`:GOLD-oracle ASM-DG $DG_NAME utilization in critical" "yourmail@company.com"
fi
fi
done
fi
