監控sqlldr執行指令碼

本來已經部署了sqlldr指令碼 每小時定時匯入 指令碼如下
--文字檔案每小時產生一次，格式為yyyymmddhh，每次sqlldr前將其重新命名為固定名字source_file.csv，載入結束後還原
[root justin]# more sqlldr.sh
#!/bin/bash

ORACLE_HOME=/data/oracle/product/10205/db1
export ORACLE_HOME

folder=/var/www/data/

#get the past time value
date1=`date --date='1 hour ago'  +%Y%m%d%H`
date2=`date --date='15 day ago'  +%Y%m%d%H`

#rename the file generated one hour ago to source_file.csv, which would be called by control.ctl
mv "$folder""$date1"  "$folder"source_file.csv

#call sqlldr, with control as control.ctl
$ORACLE_HOME/bin/./sqlldr userid=****/*** control="$folder"control.ctl direct=false readsize=900000000 bindsize=900000000 rows=2000   log="$folder""$date1"-51
#rename bad file if generated
if [ -f "$folder"source_file.bad ]; then
 mv "$folder"source_file.bad "$folder""$date1"-51.bad
fi

#roll back the rename operation
mv "$folder"source_file.csv "$folder""$date1"

#if the file generated by 15 days ago still exist, then drop them
if [ -f "$folder""$date2" ]; then
  rm "$folder""$date2"
fi
if [ -f "$folder""$date2"-51.log ]; then
  rm "$folder""$date2"-51.log
fi
if [ -f "$folder""$date2"-51.bad ]; then
  rm "$folder""$date2"-51.bad
fi

偏偏最近修改指令碼，到時sqlldr失效，又沒有留意觀察，導致半個月的資料沒有匯入；於是寫了以下指令碼監控
--每個檔案使用sqlldr載入後，都會產生一個log檔案，透過檢視log檔案是否存在判斷sqlldr是否執行過；然後再透過判斷log檔案中的記錄數檢視是否成功載入
#!/bin/bash

folder=/var/www/data/
cd $folder
cat /dev/null > monitor_sqlldr.log
echo `ifconfig eth0 | grep 'inet' | sed 's/^.*addr://g' | sed 's/Bcast.*$//g' ` >> monitor_sqlldr.log
#get the past time value
end=`date --date='2 hour ago' +%Y%m%d%H`
start=`date --date='25 hour ago'  +%Y%m%d%H`

for (( i=$start; i<=$end; i=i+1 ))
do
  if [ -f $i ]; then
    file=$i"-51.log"
    if [ -f $file ] && [ "`wc -l < $file`" -gt "73" ]; then
      echo "$i has been successfully load into oracle" >> monitor_sqlldr.log
      cat $file | grep "Rows not loaded due to data errors" >> monitor_sqlldr.log
      echo "" >> monitor_sqlldr.log
    else
      echo "$i not loaded into oracle" >> monitor_sqlldr.log
      echo "" >> monitor_sqlldr.log
    fi
  fi

done

`/usr/bin/sendEmail -s mail.****.com -f justin\@****.com -t justin\@****.com -u 'the summary of today sqlldr job' -o message-file=/var/www/data/monitor_sqlldr.log`

新增至crontab，每日的1點執行，得到昨天的sqlldr工作情況，郵件內容如下
10.1.0.13
2011051214 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051215 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051216 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051217 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051218 has been successfully load into oracle

2011051219 has been successfully load into oracle

2011051220 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051221 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051222 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051223 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051300 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051301 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051302 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051303 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051304 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051305 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051306 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051307 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051308 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051309 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051310 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051311 has been successfully load into oracle
  0 Rows not loaded due to data errors.

2011051312 has been successfully load into oracle
  3 Rows not loaded due to data errors.

2011051313 has been successfully load into oracle
  0 Rows not loaded due to data errors.