一个从oracle使用spool导出数据到kadb的脚本

1. dump_data.sh调用sql_dump.sh导出数据

2. load_data.sh将导出的数据加载至KADB

1. dump_data.sh

#!/bin/bash
begin_time=$(date +%Y%m%d -d '-1 day')
end_time=$(date +%Y%m%d)
echo "数据导出日期:"$begin_time
echo "数据导出日期:"$begin_time >> .//log/dump_data_$begin_time.log
echo "数据导出终止日期:"$end_time
echo "数据导出终止日期:"$end_time >> .//log/dump_data_$begin_time.log
while read LINE
do
  echo "表名:"$LINE
  echo "表名:"$LINE >> .//log/dump_data_$begin_time.log
  echo "执行参数: 表名 导出类型 开始时间 结束时间: "$LINE $begin_time $end_time
  echo "执行参数: 表名 导出类型 开始时间 结束时间: "$LINE $begin_time $end_time >> .//log/dump_data_$begin_time.log
  echo "./sql_dump.sh $LINE $begin_time $end_time"
  echo [`date +%Y-%m-%d_%H:%M:%S`]"执行导出操作:./sql_dump.sh $LINE $begin_time $end_time"
  echo [`date +%Y-%m-%d_%H:%M:%S`]"执行导出操作:./sql_dump.sh $LINE $begin_time $end_time" >> .//log/dump_data_$begin_time.log
  while [[ 1==1 ]]
  do
    echo '检查当前export进程数......'
    echo '检查当前export进程数......' >> .//log/dump_data_$begin_time.log
    sleep 2 
    ips=`ps -ef | grep -v grep| grep sql_dump | wc -l`
    echo 当前export进程数: $ips
    echo 当前export进程数: $ips >> .//log/dump_data_$begin_time.log
    if [ ${ips} -ge 4 ];then
      sleep 2 
    else
      #nohup sh ./sql_dump.sh $LINE $begin_time $end_time > .//"${LINE%% *}_`date +%Y%m%d`".log 2>&1 &
      nohup sh ./sql_dump.sh $LINE $begin_time $end_time >> .//log/dump_data_$begin_time.log 2>&1 &
      break;
    fi
  done
done < table.lst
 

2. sql_dump.sh

test=$1
flag=$2
begin_time=$3
end_time=$4
line=`wc -l $test.sql | cut -d ' ' -f 1`
export line
#echo 表名:$test 导出类型:$flag 列数:$line
echo 表名:$test 导出类型:$flag 列数:$line >> .//log/dump_data_$begin_time.log
if [ $flag = 'E' ];then
#  echo 当前表导出类型:$flag
  #echo 当前表导出类型:$flag >> .//log/"${test}_$begin_time".log
  awk -v line="$line" 'BEGIN{sql="select "}{if(NR<line){if($2~/char/){sql='sql'"replace("$1",chr(10),'\'''\'')\n""||'\'''\''||"}else{sql='sql'$1"||'\'''\''||"}}else{if($2~/char/){sql='sql'"replace("$1",chr(10),'\'''\'')"}else{sql='sql'$1}}}END{print 'sql'" from ""'$test'"" where fd_ie_flag=""'\''""'$flag'""'\''"" and fd_cus_release_time >= to_date(""'\''""'$begin_time'""'\'','\''yyyymmdd'\'')"" and fd_cus_release_time < to_date(""'\''""'$end_time'""'\'','\''yyyymmdd'\'')"";"}' ${test}.sql > ${test}_E.out
  cat ${test}_E.out
#  echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test开始导出数据
  echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test开始导出数据 >> .//log/dump_data_$begin_time.log
sqlplus -s / as sysdba << EOF 
set trimspool on;
set heading off;
set term off;
set feedback off;
set echo off;
SET SQLPROMPT "";
SET NEWPAGE NONE;
spool /home/oracle/${test}_E_${begin_time}.txt
@ ${test}_E.out
spool off;
set SQLPROMPT "SQL>";
exit;
EOF
touch ${test}_E_${begin_time}.done
#  echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test导出数据完成
  echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test导出数据完成 >> .//log/dump_data_$begin_time.log
  chmod 777 /home/oracle/${test}_E_${begin_time}.txt
elif [ $flag = 'I' ];then
  echo 当前表导出类型:$flag
#  echo 当前表导出类型:$flag >> .//"${test}_$begin_time".log
  awk -v line="$line" 'BEGIN{sql="select "}{if(NR<line){if($2~/char/){sql='sql'"replace("$1",chr(10),'\'''\'')\n""||'\'''\''||"}else{sql='sql'$1"||'\'''\''||"}}else{if($2~/char/){sql='sql'"replace("$1",chr(10),'\'''\'')"}else{sql='sql'$1}}}END{print 'sql'" from ""'$test'"" where fd_ie_flag=""'\''""'$flag'""'\''"" and fd_cus_clear_time >= to_date(""'\''""'$begin_time'""'\'','\''yyyymmdd'\'')"" and fd_cus_clear_time < to_date(""'\''""'$end_time'""'\'','\''yyyymmdd'\'')"";"}' ${test}.sql > ${test}_I.out
  cat ${test}_I.out
#  echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test开始导出数据
  echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test开始导出数据 >> .//log/dump_data_$begin_time.log
sqlplus -s / as sysdba << EOF 
set trimspool on;
set heading off;
set term off;
set feedback off;
set echo off;
SET SQLPROMPT "";
SET NEWPAGE NONE;
spool /home/oracle/${test}_I_${begin_time}.txt
@ ${test}_I.out
spool off;
set SQLPROMPT "SQL>";
exit;
EOF
touch ${test}_I_${begin_time}.done
#  echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test导出数据完成
  echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test导出数据完成 >> .//log/dump_data_$begin_time.log
  chmod 777 /home/oracle/${test}_I_${begin_time}.txt
else
  echo 当前表导出类型:$flag
  echo 当前表导出类型:$flag >> .//"${test}_$begin_time".log
  awk -v line="$line" 'BEGIN{sql="select "}{if(NR<line){if($2~/char/){sql='sql'"replace("$1",chr(10),'\'''\'')\n""||'\'''\''||"}else{sql='sql'$1"||'\'''\''||"}}else{if($2~/char/){sql='sql'"replace("$1",chr(10),'\'''\'')"}else{sql='sql'$1}}}END{print 'sql'" from ""'$test'"" where fd_cus_release_time >= to_date(""'\''""'$begin_time'""'\'','\''yyyymmdd'\'')"" and fd_cus_release_time < to_date(""'\''""'$end_time'""'\'','\''yyyymmdd'\'')"";"}' ${test}.sql > ${test}.out
  cat ${test}.out
#  echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test开始导出数据
  echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test开始导出数据 .//log/dump_data_$begin_time.log
sqlplus -s / as sysdba << EOF 
set trimspool on;
set heading off;
set term off;
set feedback off;
set echo off;
SET SQLPROMPT "";
SET NEWPAGE NONE;
spool /home/oracle/${test}_${begin_time}.txt
@ $test.out
spool off;
set SQLPROMPT "SQL>";
exit;
EOF
touch ${test}_${begin_time}.done
#  echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test导出数据完成
  echo [`date +%Y-%m-%d_%H:%M:%s`]..表$test导出数据完成 .//log/dump_data_$begin_time.log
  chmod 777 /home/oracle/${test}_${begin_time}.txt
fi
 

3. load_data.sh

#!/bin/bash
begin_time=$(date +%Y%m%d -d '-1 day')
end_time=$(date +%Y%m%d)
echo 导入开始日期: $begin_time
echo 导入结束日期: $end_time
WORKPATH=`dirname $0`

get_port()
{
  kbport=$1
  while [ $kbport -le 50000 ]   
  do
    /usr/sbin/lsof -i:$kbport > /dev/null
    if [ $? -eq 1 ]
    then
      echo $kbport
      break
    else
      let kbport+=2
    fi
  done
}

while read LINE
do
  #echo 表名: $LINE
  type=${LINE##* }
  if [ $type = 'O' ];then
    table_name=${LINE%% *}
  else
    table_name=${LINE%% *}_$type
  fi
  echo 待导入表: $table_name
  KB_PORT=`get_port 40000`        #获取gpfdist可用的端口
  while true
  do
    /usr/sbin/lsof -i:${KB_PORT} 
    if [ $? -eq 1 ]
    then
      if [ ! -d $WORKPATH/${KB_PORT} ]; 
      then
        mkdir $WORKPATH/${KB_PORT}
        if [ $? -ne 0 ];then
          echo $KB_PORT端口已经被使用
          let KB_PORT+=2
          KB_PORT=`get_port $KB_PORT`
          continue;
        fi
        if [[ "$table_name" =~ _I$ ]];then 
          table_name_r=${table_name%_*}
          echo $table_name_r
          sed -e "s#THISISDATADIRECTORY#\./${table_name}_${begin_time}.txt#g;s#zhuyongzhuyong#${table_name_r}#g;s/KBPORT/${KB_PORT}/g;s/EXECSQLSTAT/delete from ${table_name_r} where fd_ie_flag='I' and fd_cus_clear_time >= to_date('${begin_time}','yyyymmdd') and fd_cus_clear_time < to_date('${end_time}','yyyymmdd');/g" z.mod > $table_name.yml
          cat $table_name.yml
          break;
        elif [[ "$table_name" =~ _E$ ]]; then
          table_name_r=${table_name%_*}
          echo $table_name_r
          sed -e "s#THISISDATADIRECTORY#\./${table_name}_${begin_time}.txt#g;s#zhuyongzhuyong#${table_name_r}#g;s/KBPORT/${KB_PORT}/g;s/EXECSQLSTAT/delete from ${table_name_r} where fd_ie_flag='E' and fd_cus_release_time >= to_date('${begin_time}','yyyymmdd') and fd_cus_release_time < to_date('${end_time}','yyyymmdd');/g" z.mod > $table_name.yml
          cat $table_name.yml
          break;
        else
          echo $table_name
          sed -e "s#THISISDATADIRECTORY#\./${table_name}_${begin_time}.txt#g;s#zhuyongzhuyong#${table_name}#g;s/KBPORT/${KB_PORT}/g;s/EXECSQLSTAT/delete from ${table_name} where fd_cus_release_time >= to_date('${begin_time}','yyyymmdd') and fd_cus_release_time < to_date('${end_time}','yyyymmdd');/g" z.mod > $table_name.yml
          cat $table_name.yml
          break;
        fi
      else
        let KB_PORT+=2
         KB_PORT=`get_port $KB_PORT`
      fi 
    else
      sleep $(($RANDOM%5))
      let KB_PORT+=2      
      KB_PORT=`get_port $KB_PORT`  
    fi  
  done
  while true
  do
    if [ -f ${table_name}_${begin_time}.done ];then
      ips=`ps -ef | grep -v grep| grep sql_dump | wc -l`
      if [ ${ips} -ge 4 ];then
        sleep 2
      else
        echo "begin load"
        #echo "`date "+%Y-%m-%d_%H:%M:%S"`开始装载表:${table_name}"
        #nohup gpload -f $table_name.yml > ./log/gpload_$table_name_`date +%Y%m%d`.log &
        #grep "errors = [^0]\|failed\|ERROR" ./log/gpload_${table_name}_`date +%Y%m%d`.log > /dev/null    #检查gpload是否成功
        #if [ $? -eq 0 ]; then
    #  echo "GPLOAD ERROR!"
    #  echo $table_name >> ./log/error_`date +%Y%m%d`.lst
     #fi
        #echo "`date "+%Y-%m-%d_%H:%M:%S"`结束装载表:${table_name}"
        #ROWSLOAD=`cat $WORKPATH/load_log/gpload_${table_name}_$(date +%Y%m%d).log | grep "rows Inserted" | cut -d "=" -f 2 | sed 's/^ //'`    #获取装载数据行数
    #echo $ROWSLOAD
    #echo "`date "+%Y-%m-%d_%H:%M:%S"`本次装载数据${ROWSLOAD}行"
        rm -rf $WORKPATH/${KB_PORT}
        break;
      fi
    else
      echo ${table_name}_${begin_time}.txt have not done 
      sleep 10
      continue;
    fi
  done
done < table.lst