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
使用以下的命令来确认该脚本是可以执行的：
$ chmod 744 ckinstance.ksh $ ls -l ckinstance.ksh -rwxr--r-- 1 oracle dba 657 Mar 5 22:59 ckinstance.ksh*
以下是实例可用性的报表：
$ 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
检查Oracle监听器的可用性
以下有一个类似的脚本检查Oracle监听器。如果监听器停了，该脚本将会重新启动监听器：
####################################################################### ## 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
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
分析表和索引（以得到更好的性能）
以下我将展示如果传送参数到一个脚本中：
#################################################################### ## 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 enter instance name as the second parameter!" exit 0 fi
##################################################################### ## 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 INVALIDLBAILEY 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
监视用户和事务（死锁等）
以下的脚本在死锁发生的时候发送一个警告e-mail：
################################################################### ## 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
