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 on page 3

• Check listener availability on page 4
• Check alert log files for error messages on page 4
• Clean up old log files before log destination gets filled on page 5
• Analyze tables and indexes for better performance on page 5
• Check tablespace usage on page 6
• Find out invalid objects on page 6
• Monitor users and transactions on page 7

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:

• List available instances on a server:

Generated by Clearspace on 2010-06-29-06:00

1
Top DBA Shell Scripts for Monitoring the Database

$ 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

• 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

• Find out file system usage for Oracle archive destination:

$ df -k | grep oraarch /dev/vx/dsk/proddg/oraarch 71123968 4754872 65850768 7% /u09/oraarch

• List number of lines in the alert.log file:

$ cat alert.log | wc -l 2984

• List all Oracle error messages from the alert.log file:

$ grep ORA- alert.log ORA-00600: internal error code, arguments: [kcrrrfswda.1], [], [], [], [], [] ORA-00600: internal error code, arguments:
[1881], [25860496], [25857716], []

CRONTAB Basics

A crontab file is comprised of six fields:

Minute 0-59

Hour 0-23

Day of month 1-31

Month 1 - 12

Day of Week 0 - 6, with 0 = Sunday

Unix Command or Shell Scripts

• To edit a crontab file, type:

Generated by Clearspace on 2010-06-29-06:00

2
Top DBA Shell Scripts for Monitoring the Database

Crontab -e

• To view a crontab file, type:

Crontab -l
0 4**5 /dba/admin/analyze_table.ksh 30 3 * * 3,6 /dba/admin/hotbackup.ksh /dev/null 2>&1

In the example above, the first entry shows that a script to analyze a table runs every Friday
at 4:00 a.m. The second entry shows that a script to perform a hot backup runs every
Wednesday and Saturday at 3:00 a.m.

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:Yoradb2:/u01/app/oracle/product/8.1.7:Yoradb3:/u01/app/oracle/product/8.1.7:Noradb4:/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/oratabecho "`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"
fidone

Generated by Clearspace on 2010-06-29-06:00

3
Top DBA Shell Scripts for Monitoring the Database

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.kshMon Mar 4 10:44:12 PST 2002

Oracle Database(s) Status for DBHOST server:Oracle Instance - oradb1: UpOracle Instance - oradb2: UpOracle Instance - oradb3:
DownOracle Instance - oradb4: Up

Check Oracle Listener's Availability

A similar script checks for the Oracle listener. If the listener is down, the script will restart
the listener:

######################################################################### cklsnr.sh
##########################################################################!/bin/
kshDBALIST="primary.dba@company.com,another.dba@company.com";export DBALISTcd /var/opt/oraclerm -f lsnr.exist ps -ef |
grep mylsnr | grep -v grep > lsnr.existif [ -s lsnr.exist ]then echo else echo "Alert" | mailx -s "Listener 'mylsnr' on `hostname` is down"
$DBALIST TNS_ADMIN=/var/opt/oracle; export TNS_ADMIN ORACLE_SID=db1; 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 mylsnrfi

Check Alert Logs (ORA-XXXXX)

Some of the environment variables used by each script can be put into one profile:

######################################################################### oracle.profile
#########################################################################EDITOR=vi;export EDITOR ORACLE_BASE=/
u01/app/oracle; exportORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/8.1.7; exportORACLE_HOME LD_LIBRARY_PATH=
$ORACLE_HOME/lib; exportLD_LIBRARY_PATH TNS_ADMIN=/var/opt/oracle;exportTNS_ADMIN NLS_LANG=american;
exportNLS_LANG NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; exportNLS_DATE_FORMAT ORATAB=/var/opt/oracle/
oratab;exportORATAB PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/
opt/bin:.; exportPATH DBALIST="primary.dba@company.com,another.dba@company.com";exportDBALIST

The following script first calls oracle.profile to set up all the environment variables. The
script also sends the DBA a warning e-mail if it finds any Oracle errors:

###################################################################### ckalertlog.sh
#######################################################################!/bin/ksh. /etc/oracle.profilefor 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

Generated by Clearspace on 2010-06-29-06:00

4
Top DBA Shell Scripts for Monitoring the Database

[ `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.logdone

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 archFilesystem kbytes used avail capacity Mounted on/dev/vx/dsk/proddg/archive 71123968 30210248
40594232 43% /u08/archive

######################################################################### clean_arch.ksh
##########################################################################!/bin/kshdf -k | grep arch >
dfk.resultarchive_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 rmanfi

Analyze Tables and Indexes (for Better Performance)

Below, I have shown an example on how to pass parameters to a script:

###################################################################### analyze_table.sh
###################################################################### #!/bin/ksh #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 enterinstance name
as the second parameter!" exit 0 fi

To execute the script with parameters, type:

$ analyze_table.sh manager oradb1

The first part of script generates a file analyze.sql, which contains the syntax for analyzing
table. The second part of script analyzes all the tables:

####################################################################### analyze_table.sh
#######################################################################sqlplus -s <<!oracle/$1@$2set heading
offset feed offset pagesize 200set linesize 100spool analyze_table.sqlselect 'ANALYZE TABLE ' || owner || '.' || segment_name ||
' ESTIMATE STATISTICS SAMPLE 10 PERCENT;'from dba_segmentswhere segment_type = 'TABLE'and owner not in ('SYS',
'SYSTEM');spool offexit!sqlplus -s <<!oracle/$1@$2@./analyze_table.sqlexit!

Generated by Clearspace on 2010-06-29-06:00

5
Top DBA Shell Scripts for Monitoring the Database

Here is an example of analyze.sql:

$ cat analyze.sqlANALYZE TABLE HIRWIN.JANUSAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT;ANALYZE TABLE

HIRWIN.JANUSER_PROFILE ESTIMATE STATISTICS SAMPLE 10 PERCENT;ANALYZE TABLE APPSSYS.HIST_SYSTEM_ACTIVITY
ESTIMATE STATISTICS SAMPLE 10 PERCENT;ANALYZE TABLE HTOMEH.QUEST_IM_VERSION ESTIMATE STATISTICS
SAMPLE 10 PERCENT;ANALYZE TABLE JSTENZEL.HIST_SYS_ACT_0615 ESTIMATE STATISTICS SAMPLE 10
PERCENT;ANALYZE TABLE JSTENZEL.HISTORY_SYSTEM_0614 ESTIMATE STATISTICS SAMPLE 10 PERCENT;ANALYZE TABLE
JSTENZEL.CALC_SUMMARY3 ESTIMATE STATISTICS SAMPLE 10 PERCENT;ANALYZE TABLE IMON.QUEST_IM_LOCK_TREE
ESTIMATE STATISTICS SAMPLE 10 PERCENT;ANALYZE TABLE APPSSYS.HIST_USAGE_SUMMARY ESTIMATE STATISTICS
SAMPLE 10 PERCENT;ANALYZE TABLE PATROL.P$LOCKCONFLICTTX ESTIMATE STATISTICS SAMPLE 10 PERCENT;

Check Tablespace Usage

This scripts checks for tablespace usage. If tablespace is 10 percent free, it will send an
alert e-mail.

####################################################################### ck_tbsp.sh
########################################################################!/bin/kshsqlplus -s <<!oracle/$1@$2set
feed offset linesize 100set pagesize 200spool tablespace.alertSELECT F.TABLESPACE_NAME, TO_CHAR ((T.TOTAL_SPACE -
F.FREE_SPACE),'999,999') "USED (MB)", TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)", TO_CHAR (T.TOTAL_SPACE,
'999,999') "TOTAL (MB)", TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREEFROM
( SELECT TABLESPACE_NAME, ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V\$PARAMETER WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F, ( SELECT TABLESPACE_NAME, ROUND
(SUM (BYTES/1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) TWHERE
F.TABLESPACE_NAME = T.TABLESPACE_NAMEAND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;spool offexit!if [ `cat
tablespace.alert|wc -l` -gt 0 ]then cat tablespace.alert -l tablespace.alert > tablespace.tmp mailx -s "TABLESPACE ALERT for
${2}" $DBALIST < tablespace.tmpfi

An example of the alert mail output is as follows:

TABLESPACE_NAME USED (MB) FREE (MB) TOTAL (MB) PER_FREE ------------------- --------- ----------- -------------------
------------------SYSTEM 2,047 203 2,250 9 %STBS01 302 25 327 8
% STBS02 241 11 252 4 % STBS03 233 19 252 8%

Find Out Invalid Database Objects

The following finds out invalid database objects:

##################################################################### ## invalid_object_alert.sh ##
##################################################################### #!/bin/ksh . /etc/oracle.profile sqlplus

Generated by Clearspace on 2010-06-29-06:00

6
Top DBA Shell Scripts for Monitoring the Database

-s <<! oracle/$1@$2 set feed off set heading off column object_name format a30 spool invalid_object.alert SELECT
OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS = 'INVALID' ORDER BY OWNER,
OBJECT_TYPE, OBJECT_NAME; spool off exit ! if [ `cat invalid_object.alert|wc -l` -gt 0 ] then mailx -s "INVALID OBJECTS for
${2}" $DBALIST < invalid_object.alert fi$ cat invalid_object.alert

OWNER OBJECT_NAME OBJECT_TYPE STATUS----------------------------------------------------------------------HTOMEH

DBMS_SHARED_POOL PACKAGE BODY INVALIDHTOMEH X_$KCBFWAIT VIEW
INVALIDIMON IW_MON PACKAGE INVALIDIMON IW_MON PACKAGE BODY
INVALIDIMON IW_ARCHIVED_LOG VIEW INVALIDIMON IW_FILESTAT VIEW
INVALIDIMON IW_SQL_FULL_TEXT VIEW INVALIDIMON IW_SYSTEM_EVENT1 VIEW
INVALIDIMON IW_SYSTEM_EVENT_CAT VIEW INVALIDLBAILEY CHECK_TABLESPACE_USAGE
PROCEDURE INVALIDPATROL P$AUTO_EXTEND_TBSP VIEW INVALIDSYS
DBMS_CRYPTO_TOOLKIT PACKAGE INVALIDSYS DBMS_CRYPTO_TOOLKIT PACKAGE BODY
INVALIDSYS UPGRADE_SYSTEM_TYPES_TO_816 PROCEDURE INVALIDSYS AQ$_DEQUEUE_HISTORY_T
TYPE INVALIDSYS HS_CLASS_CAPS VIEW INVALID SYS HS_CLASS_DD
VIEW INVALID

Monitor Users and Transactions (Dead Locks, et al)

This script sends out an alert e-mail if dead lock occurs:

##################################################################### deadlock_alert.sh
######################################################################!/bin/ksh. /etc/oracle.profilesqlplus -s <<!
oracle/$1@$2set feed offset heading offspool deadlock.alertSELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,
DECODE(REQUEST, 0, 'NO','YES' ) WAITERFROM V$LOCK WHERE REQUEST > 0 OR BLOCK > 0 ORDER BY block DESC;
spool offexit!if [ `cat deadlock.alert|wc -l` -gt 0 ]then mailx -s "DEADLOCK ALERT for ${2}" $DBALIST < deadlock.alertfi

Conclusion

0,20,40 7-17 * * 1-5 /dba/scripts/ckinstance.sh > /dev/null 2>&10,20,40 7-17 * * 1-5 /dba/scripts/cklsnr.sh > /dev/null 2>&10,20,40
7-17 * * 1-5 /dba/scripts/ckalertlog.sh > /dev/null 2>&130 * * * 0-6 /dba/scripts/clean_arch.sh > /dev/null 2>&1* 5 * * 1,3 /
dba/scripts/analyze_table.sh > /dev/null 2>&1* 5 * * 0-6 /dba/scripts/ck_tbsp.sh > /dev/null 2>&1* 5 * * 0-6 /dba/scripts/
invalid_object_alert.sh > /dev/null 2>&10,20,40 7-17 * * 1-5 /dba/scripts/deadlock_alert.sh > /dev/null 2>&1

Now my DBA friends, you can have more uninterrupted sleep at night. You may also have
time for more important things such as performance tuning.

References
• Unix in a Nutshell, O'Reilly & Associates, Inc.;
• “Using  Oracle9i Application Server to Build Your Web-Based Database Monitoring Tool,” 
Daniel T. Liu; Select Magazine - November 2001 Volume 8, No. 1;
• “Net8:  A Step-by-Step Setup of Oracle Names Server,” Daniel T. Liu; Oracle  Open World
2000, Paper#271.

Generated by Clearspace on 2010-06-29-06:00

7
Top DBA Shell Scripts for Monitoring the Database

I would also like to acknowledge the assistance of Johnny Wedekind of ADP, Ann Collins,
Larry Bailey, Husam Tomeh and Archana Sharma of FARES.

--

Daniel Liu is a senior Oracle Database Administrator at First American Real Estate
Solutions in Anaheim, CA, and co-author of Oracle Database 10g New Features. His
expertise includes Oracle database administration, performance tuning, Oracle networking,
and Oracle Application Server. As an Oracle Certified Professional, he taught Oracle
certified DBA classes and IOUG University Seminar. Daniel has published articles with
DBAzine, Oracle Internals, and SELECT Journal. Daniel holds a Master of Science degree
in computer science from Northern Illinois University.

All companies and product names are trademarks or registered trademarks of the
respective owners. Please report errors in this article to the author. Neither FARES nor the
author warrants that this document is error-free.

Generated by Clearspace on 2010-06-29-06:00

8