This article focuses on the DBA's daily responsibilities for monitoring Oracle databases and provides tips and techniques on how DBAs can turn their manual, reactive monitoring activities into a set of proactive shell scripts. The article first reviews some commonly used Unix commands by DBAs. It explains the Unix Cron jobs that are used as part of the scheduling mechanism to execute DBA scripts. The article covers eight important scripts for monitoring Oracle database:
+
Check instance availability
+
Check listener availability
+
Check alert log files for error messages
+
Clean up old log files before log destination gets filled
+
Analyze tables and indexes for better performance
+
Check tablespace usage
+
Find out invalid objects
+
Monitor users and transactions
UNIX Basics for the DBA
Basic UNIX Command
The following is a list of commonly used Unix command:
+
ps - Show process
+
grep - Search files for text patterns
+
mailx - Read or send mail
+
cat - Join files or display them
+
cut - Select columns for display
+
awk - Pattern-matching language
+
df - Show free disk space
Top DBA Shell Scripts for Monitoring the Database
The eight shell scripts provided below cover 90 percent of a DBA's daily monitoring activities. You will need to modify the UNIX environment variables as appropriate.
Check Oracle Instance Availability
The oratab file lists all the databases on a server:
$ cat /var/opt/oracle/oratab
###################################################################
## /var/opt/oracle/oratab ##
###################################################################
oradb1:/u01/app/oracle/product/8.1.7:Y
oradb2:/u01/app/oracle/product/8.1.7:Y
oradb3:/u01/app/oracle/product/8.1.7:N
oradb4:/u01/app/oracle/product/8.1.7:Y
The following script checks all the databases listed in the oratab file, and finds out the status (up or down) of databases:
###################################################################
## ckinstance.ksh ##
###################################################################
ORATAB=/var/opt/oracle/oratab
echo "`date` "
echo "Oracle Database(s) Status `hostname` :\n"
db=`egrep -i ":Y|:N" $ORATAB | cut -d":" -f1 | grep -v "\#" | grep -v "\*"`
pslist="`ps -ef | grep pmon`"
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
Use the following to make sure the script is executable:
$ chmod 744 ckinstance.ksh
$ ls -l ckinstance.ksh
-rwxr--r-- 1 oracle dba 657 Mar 5 22:59 ckinstance.ksh*
Here is an instance availability report:
$ ckinstance.ksh
Mon Mar 4 10:44:12 PST 2002
Oracle Database(s) Status for DBHOST server:
Oracle Instance - oradb1: Up
Oracle Instance - oradb2: Up
Oracle Instance - oradb3: Down
Oracle Instance - oradb4: Up
Full article...
