By Rampant author Daniel Liu
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.
For complete Oracle shell scripts and examples, see the book
Oracle Shell Scripting
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.
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:
$ 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
$ 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
$ df -k | grep oraarch
/dev/vx/dsk/proddg/oraarch
71123968 4754872 65850768 7% /u09/oraarch
$ cat alert.log |
wc -l
2984
$ grep ORA-
alert.log
ORA-00600: internal error code, arguments: [kcrrrfswda.1], [],
[], [], [], []
ORA-00600: internal error code, arguments: [1881], [25860496],
[25857716], []
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.
Shell Scripts for Monitoring Oracle
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
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/ksh
DBALIST="primary.dba@company.com,another.dba@company.com";export
DBALIST
cd /var/opt/oracle
rm -f lsnr.exist
ps
-ef | grep mylsnr | grep -v grep > lsnr.exist
if [ -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 mylsnr
fi
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
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/ksh
sqlplus -s <<!
oracle/$1@$2
set feed off
set linesize 100
set pagesize 200
spool tablespace.alert
SELECT 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_FREE
FROM
(
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
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100))
< 10;
spool off
exit
!
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.tmp
fi
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 Invalid Database Objects
The following finds out invalid database objects:
#####################################################################
##
invalid_object_alert.sh ##
#####################################################################
#!/bin/ksh
. /etc/oracle.profile
sqlplus -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
INVALID
HTOMEH
X_$KCBFWAIT
VIEW
INVALID
IMON
IW_MON
PACKAGE
INVALID
IMON
IW_MON
PACKAGE BODY
INVALID
IMON
IW_ARCHIVED_LOG
VIEW
INVALID
IMON
IW_FILESTAT
VIEW
INVALID
IMON
IW_SQL_FULL_TEXT
VIEW
INVALID
IMON
IW_SYSTEM_EVENT1
VIEW
INVALID
IMON
IW_SYSTEM_EVENT_CAT
VIEW
INVALID
LBAILEY
CHECK_TABLESPACE_USAGE PROCEDURE
INVALID
PATROL
P$AUTO_EXTEND_TBSP
VIEW
INVALID
SYS
DBMS_CRYPTO_TOOLKIT
PACKAGE
INVALID
SYS
DBMS_CRYPTO_TOOLKIT
PACKAGE BODY
INVALID
SYS
UPGRADE_SYSTEM_TYPES_TO_816 PROCEDURE
INVALID
SYS
AQ$_DEQUEUE_HISTORY_T TYPE
INVALID
SYS
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.profile
sqlplus -s <<!
oracle/$1@$2
set feed off
set heading off
spool deadlock.alert
SELECT SID, DECODE(BLOCK, 0,
'NO', 'YES' ) BLOCKER,
DECODE(REQUEST, 0, 'NO','YES' ) WAITER
FROM V$LOCK
WHERE
REQUEST > 0 OR BLOCK > 0
ORDER BY block DESC;
spool off
exit
!
if [ `cat deadlock.alert|wc -l` -gt 0 ]
then
mailx -s "DEADLOCK ALERT
for ${2}" $DBALIST < deadlock.alert
fi
