狐狸情 发表于 2018-8-24 13:33:28

Top DBA Shell Scripts for Monitoring the Database

Introduction
　　This article focuses on the DBA's daily responsibilities for monitoringOracle databases and provides tips and techniques on how DBAs can turn theirmanual, reactive monitoring activities into a set of proactive shell scripts.The article first reviews some commonly used Unix commands by DBAs. It explainsthe Unix Cron jobs that are used as part of the scheduling mechanism to executeDBA scripts. The article covers eight important scripts for monitoring Oracledatabase:



[*]　　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:



[*]　　List available instances on a server:


　　$ ps -ef | grep smon
　　oracle 21832   10   Feb 24 ?       19:05 ora_smon_oradb1
　　oracle   898   10   Feb 15 ?      0:00 ora_smon_oradb2
　　dliu 25199 190380 10:48:57 pts/6    0:00 grep smon
　　oracle 27798   10 05:43:54 ?      0:00 ora_smon_oradb3
　　oracle 28781   10   Mar 03 ?      0:01 ora_smon_oradb4



[*]　　List available listeners on a server:


　　$ ps -ef | grep listener | grep -v grep
　　oracle 23879    10   Feb 24 ?33:36 /8.1.7/bin/tnslsnr listener_db1 -inherit
　　oracle 27939    10 05:44:02 ?0:00/8.1.7/bin/tnslsnr listener_db2 -inherit
　　oracle 23536    10   Feb 12 ?4:19/8.1.7/bin/tnslsnr listener_db3 -inherit
　　oracle 28891    10   Mar 03 ?0:01/8.1.7/bin/tnslsnr listener_db4 -inherit



[*]　　Find out file system usage for Oracle archivedestination:


　　$ df -k | grep oraarch
　　/dev/vx/dsk/proddg/oraarch 71123968 4754872 658507687%/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: , [], [], [], [], []
　　ORA-00600: internal error code, arguments: , , , []
CRONTAB Basics
　　A crontab file is comprised of six fields:
Minute0-59       Hour0-23       Day of month1-31       Month1 - 12       Day of Week0 - 6, with 0 = Sunday       Unix Command or Shell Scripts



[*]　　To edit a crontab file, type:


　　Crontab -e



[*]　　To view a crontab file, type:


　　Crontab -l
　　04 * * 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 tableruns every Friday at 4:00 a.m. The second entry shows that a script to perform ahot 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 dailymonitoring activities. You will need to modify the UNIX environment variables asappropriate.
　　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, andfinds 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 Mar5 22:59 ckinstance.ksh*
　　Here is an instance availability report:
　　$ ckinstance.ksh
　　Mon Mar4 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
Check Oracle Listener's Availability
　　A similar script checks for the Oracle listener. If the listener is down, thescript 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
Check Alert Logs (ORA-XXXXX)
　　Some of the environment variables used by each script can be put into oneprofile:
　　#######################################################################
　　## oracle.profile ##
　　#######################################################################
　　EDITOR=vi;export EDITOR ORACLE_BASE=/u01/app/oracle; export
　　ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/8.1.7; export
　　ORACLE_HOME LD_LIBRARY_PATH=$ORACLE_HOME/lib; export
　　LD_LIBRARY_PATH TNS_ADMIN=/var/opt/oracle;export
　　TNS_ADMIN NLS_LANG=american; export
　　NLS_LANG NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; export
　　NLS_DATE_FORMAT ORATAB=/var/opt/oracle/oratab;export
　　ORATAB PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/
　　sbin:/usr/openwin/bin:/opt/bin:.; export
　　PATH DBALIST="primary.dba@company.com,another.dba@company.com";export
　　DBALIST
　　The following script first calls oracle.profile to set up all the environmentvariables. The script also sends the DBA a warning e-mail if it finds any Oracleerrors:
　　####################################################################
　　## ckalertlog.sh                                                ##
　　####################################################################
　　#!/bin/ksh
　　. /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 &quot;${SID} ORACLE ALERT ERRORS&quot; $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 systemreaches 90-percent capacity:
　　$ df -k | grep arch
　　Filesystem                kbytes   used   avail    capacityMounted 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&quot; &quot;'{ print $6 }' dfk.result`
　　archive_capacity=`awk-F&quot; &quot;'{ print $5 }' dfk.result`
　　if [[ $archive_capacity > 90% ] ]
　　then
　　echo &quot;Filesystem ${archive_filesystem} is ${archive_capacity} filled&quot;
　　# 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)
　　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 (($# /dev/null 2>&1
　　0,20,40 7-17 * * 1-5 /dba/scripts/cklsnr.sh > /dev/null 2>&1
　　0,20,40 7-17 * * 1-5 /dba/scripts/ckalertlog.sh > /dev/null 2>&1
　　30         * * * 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>&1
　　0,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 mayalso have time for more important things such as performance tuning.

页: [1]
查看完整版本: Top DBA Shell Scripts for Monitoring the Database