Check Oracle Instance Availability
###################################################################
## ckinstance.sh ##
###################################################################
#!/bin/bash
ORATAB=/etc/oratab
echo "`date` "
echo -e "Oracle Database(s) Status `hostname` :\n---------------------------------------------"
db=`egrep -i ":Y|:N" $ORATAB | cut -d":" -f1 | grep -v "\#" | grep -v "\*"`
pslist="`ps -ef | grep pmon|grep -v grep`"
for i in $db;do
echo "$pslist" | grep "ora_pmon_$i" > /dev/null 2>&1
if (( $? )); then
echo "Oracle Instance - $i: Down"
else
echo "Oracle Instance - $i: Up"
fi
done
Check Oracle Listener’s Availability
#######################################################################
## cklsnr.sh ##
#######################################################################
#!/bin/sh
source /home/oracle/.bash_profile
DBALIST="primary.dba@company.com, another.dba@company.com";export DBALIST
cd $ORACLE_BASE
rm -f lsnr.exist
ps ux|grep LISTENER|grep -v grep > lsnr.exist
if [ -s lsnr.exist ]
then
echo
else
echo "Alert" | mailx -s "Listener 'LISTENER' on `hostname` is down" $DBALIST
TNS_ADMIN=/var/opt/oracle; export TNS_ADMIN
ORACLE_SID=lorcl19; export ORACLE_SID
ORAENV_ASK=NO; export ORAENV_ASK
PATH=$PATH:/bin:/usr/local/bin; export PATH
. oraenv
LD_LIBRARY_PATH=${ORACLE_HOME}/lib;export LD_LIBRARY_PATH
lsnrctl start LISTENER
fi
Check Alert Logs (ORA-XXXXX)
####################################################################
## ckalertlog.sh ##
####################################################################
#!/bin/sh
. /etc/oracle.profile
for SID in `cat $ORACLE_HOME/sidlist`
do
cd $ORACLE_BASE/admin/$SID/bdump
if [ -f alert_${SID}.log ]
then
mv alert_${SID}.log alert_work.log
touch alert_${SID}.log
cat alert_work.log >> alert_${SID}.hist
grep ORA- alert_work.log > alert.err
fi
if [ `cat alert.err|wc -l` -gt 0 ]
then
mailx -s "${SID} ORACLE ALERT ERRORS" $DBALIST < alert.err
fi
rm -f alert.err
rm -f alert_work.log
done
Clean Up Old Archived Logs
The following script cleans up old archive logs if the log file system reaches 90 percent capacity:
$ df -k | grep arch
Filesystem kbytes used avail capacity Mounted on
/dev/vx/dsk/proddg/archive 71123968 30210248 40594232 43% /u08/archive
#######################################################################
## clean_arch.ksh ##
#######################################################################
#!/bin/ksh
df -k | grep arch > dfk.result
archive_filesystem=`awk -F" " '{ print $6 }' dfk.result`
archive_capacity=`awk -F" " '{ print $5 }' dfk.result`
if [[ $archive_capacity > 90% ] ]
then
echo "Filesystem ${archive_filesystem} is ${archive_capacity} filled"
# try one of the following option depend on your need
find $archive_filesystem -type f -mtime +2 -exec rm -r {} \;
tar
rman
fi
Analyze Tables and Indexes (for Better Performance)
####################################################################
## analyze_table.sh ##
####################################################################
#!/bin/sh #
input parameter: 1: password # 2: SID if (($#<1)) then echo "Please enter
'oracle'
user password as the first parameter !" exit 0 fi if (($#<2)) then echo
"Please enter
instance name as the second parameter!" exit 0 fi
To execute the script with parameters, type:
$ analyze_table.sh manager oradb1