##############################################################
## ckinstance.ksh ## ################################################################### ORATAB=/var/opt/oracle/oratab echo `date` echo Oracle Database(s) Status `hostname` : 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
#################################################################### ## 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 ${SID} ORACLE ALERT ERRORS $DBALIST < alert.err fi rm -f alert.err rm -f alert_work.log done
清除旧的归档文件 以下的脚本将会在log文件达到90%容量的时候清空旧的归档文件:
$ 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
分析表和索引(以得到更好的性能) 以下我将展示如果传送参数到一个脚本中:
#################################################################### ## 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
#!/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 --------------------------------------------
##!/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