Top DBA Shell Scripts for Monitoring the Database
by Daniel T. Liu
Introduction
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
Here are some examples of how the DBA uses these commands:
<wbr></wbr>
-
List available instances on a server:
$ ps -ef | grep smon
oracle 21832 1 0 Feb 24 ? 19:05 ora_smon_oradb1
oracle 898 1 0 Feb 15 ? 0:00 ora_smon_oradb2
dliu 25199 19038 0 10:48:57 pts/6 0:00 grep smon
oracle 27798 1 0 05:43:54 ? 0:00 ora_smon_oradb3
oracle 28781 1 0 Mar 03 ? 0:01 ora_smon_oradb4
<wbr></wbr>
-
List available listeners on a server:
$ ps -ef | grep listener | grep -v grep
oracle 23879 1 0 Feb 24 ? 33:36 /8.1.7/bin/tnslsnr listener_db1 -inherit
oracle 27939 1 0 05:44:02 ? 0:00 /8.1.7/bin/tnslsnr listener_db2 -inherit
oracle 23536 1 0 Feb 12 ? 4:19 /8.1.7/bin/tnslsnr listener_db3 -inherit
oracle 28891 1 0 Mar 03 ? 0:01 /8.1.7/bin/tnslsnr listener_db4 -inherit
<wbr></wbr>
-
Find out file system usage for Oracle archive destination:
$ df -k | grep oraarch
/dev/vx/dsk/proddg/oraarch 71123968 4754872 65850768 7% /u09/oraarch
<wbr></wbr>
-
List number of lines in the alert.log file:
$ cat ale
