ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 监控sqlldr运行脚本

监控sqlldr运行脚本

原创 Linux操作系统 作者:myownstars 时间:2011-05-13 15:49:20 0 删除 编辑

本来已经部署了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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-695222/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3164661