Shell同步数据到oracle数据库
某数据库有几张表更新，本地数据库增量更新数据，用脚本定时执行实现。
由于无blob/clob字段,使用sqlldr导入数据:查询目的数据库,以"|"分割导出所有数据,保存在./data/tablename.new
比较 tablename.new和tablename.old，将 tablename.new有,而tablename.old没有的数据更新至目的数据库，并添加至tablename.old
./data/tablename.ctl存放sqlldr控制文件
./log 存放相关日志
执行时发现bash中的while创建了子shell，不能保存变量的值，解决办法：
1、使用其他shell，如：ksh
2、形如 while read line ;do ... ;done < file
代码如下：
1 #!/usr/bin/ksh 2 3 DEBUG="false" 4 if [ $DEBUG = "true" ];then 5 orig_dir="." 6 else 7 cd 8 . ./.profile 9 orig_dir="${HOME}" 10 fi 11 v_date=`date +%Y%m%d%H%M%S` 12 ############################################################################### 13 # 日志文件，大于20M时压缩半数行 14 cd $orig_dir 15 log="$orig_dir/log/alpha.log" 16 [ ! -d $orig_dir/log ] && mkdir $orig_dir/log 17 [ ! -d $orig_dir/cfg ] && mkdir $orig_dir/cfg 18 [ ! -d $orig_dir/data ] && mkdir $orig_dir/data 19 if [ -e $log ];then 20 log_size=`ls -l $log | awk '{printf("%d",$5)}'` 21 if [ -n "$log_size" -a $log_size -gt 20971520 ];then 22 half_line=`awk 'END{printf("%d",NR/2)}' $log` 23 perl -ni -e "print if(\$.>$half_line)" $log 24 fi 25 fi 26 ############################################################################### 27 # 写日志 28 wlog() { 29 echo "`date +%Y-%m-%d' '%H:%M:%S`: $*">>$log 30 } 31 ############################################################################### 32 # 函数:get_col_type 获取列、类型 33 # 传递参数:用户名 密码 数据库名 表所有者 表名 34 get_col_type() { 35 db_user=$1 36 db_pass=$2 37 db_name=$3 38 tb_own=$4 39 tb_name=$5 40 tmp=./cfg/${tb_name}.tmp 41 tb_name="${tb_own}.${tb_name}" 42 sqlplus ${db_user}/${db_pass}@${db_name}<<EOF 43 set heading off feedback off pagesize 0 verify off echo off 44 spool $tmp 45 desc ${tb_name}; 46 spool off 47 quit 48 EOF 49 } 50 ############################################################################### 51 # 函数:make_ctl 生成控制文件 52 # 传递参数:目的表名 53 make_ctl() { 54 d_name=$1 55 ctl_file=./data/${d_name}.ctl 56 echo "load data" > $ctl_file 57 echo "infile './data/${d_name}.unl'" >> $ctl_file 58 echo "append into table ${d_name}" >> $ctl_file 59 echo "fields terminated by '|'" >> $ctl_file 60 echo "(" >> $ctl_file 61 cat ./cfg/${d_name}.ini|grep -v '^[ \t]*$'|grep -v '^#'|while read column col_type 62 do 63 case $col_type in 64 NUMBER|VARCHAR|VARCHAR2) 65 echo "$column," >> $ctl_file ;; 66 DATE) 67 echo "$column \"to_date(:$column,'yyyymmddhh24miss')\"," >> $ctl_file ;; 68 *) 69 wlog "未识别的类型:$col_type" ;; 70 esac 71 done 72 echo ")" >> $ctl_file 73 # 替换末尾的,)为) 74 perl -pi -e 'undef $/;s/,\r?\n\)/\n\)/' $ctl_file 75 } 76 77 ############################################################################### 78 # 函数:select_sql 查询数据 79 # 传递参数:用户名 密码 数据库名 数据文件名 表所有者 查询语句 表名 80 # select CALL_ID||'|'||to_char(END_TIME,'yyyymmddhh24miss')||'|'||CALLER||'|'||SERVICE_NO||'|'||CALL_PURPOSE from tablename; 81 select_sql() { 82 db_user=$1 83 db_pass=$2 84 db_name=$3 85 data_file=$4 86 tb_own=$5 87 sql_line=$6 88 tb_name=$7 89 sql_line="$sql_line from ${tb_own}.${tb_name}" 90 wlog "查询sql:$sql_line" 91 # set closep '|' 92 sqlplus -S /nolog<<EOF 93 set heading off feedback off newpage none pagesize 0 echo off termout off trimout on trimspool on linesize 800 94 conn ${db_user}/${db_pass}@${db_name} 95 spool ${data_file} 96 $sql_line; 97 spool off 98 quit 99 EOF 100 } 101 102 ############################################################################### 103 # 函数:handle 处理数据 104 # 传递参数:配置文件 105 handle() { 106 table_name=$1 107 cat $table_name|grep -v '^#'|while read suser sname dname 108 do 109 # 如果dname为空,默认使用sname 110 [ -z "$dname" ] && dname=$sname 111 sname=`echo $sname | tr 'A-Z' 'a-z'` 112 dname=`echo $dname | tr 'A-Z' 'a-z'` 113 114 [ -e ./log/${dname}.log ] && : > ./log/${dname}.log 115 [ -e ./log/${dname}.bad ] && : > ./log/${dname}.bad 116 [ -e ./log/${dname}.unl ] && : > ./log/${dname}.unl 117 118 if [ ! -e ./cfg/${dname}.ini ];then 119 wlog "不存在./cfg/${dname}.ini,开始获取${dname}列配置..." 120 get_col_type dest_db_user dest_db_pass DBNAME dest_db_user $dname 121 cat ./cfg/${dname}.tmp|grep -v "^SQL>" |grep -v " Name " |grep -v " -------" | tr -d '\015'|awk '{gsub(/[0-9]?\([0-9]+\)/,"",$0);print $1,$NF}' > ./cfg/${dname}.ini 122 fi 123 wlog "根据./cfg/${dname}.ini拼接查询sql..." 124 wlog "${dname}列配置为:" 125 line="select" 126 cat ./cfg/${dname}.ini|grep -v '^[ \t]*$'|grep -v '^#'|while read column col_type 127 do 128 wlog "$column $col_type" 129 case $col_type in 130 NUMBER|VARCHAR|VARCHAR2) 131 line="${line} $column;" ;; 132 DATE) 133 line="${line} to_char($column,'yyyymmddhh24miss');" ;; 134 *) 135 wlog "未识别的类型:$col_type" ;; 136 esac 137 done 138 [ $DEBUG = "true" ] && wlog "line:$line" 139 # 去掉最后一个分号，替换所有分号为| 140 line=${line%;*} 141 line=${line//,/"||'|'||"} 142 [ $DEBUG = "true" ] && wlog "line:$line" 143 144 # 不存在$sname.old时创建$sname.old 145 [ ! -e ./data/${sname}.old ] && touch ./data/${sname}.old 146 147 if [ ! -s ./data/${sname}.old ];then 148 wlog "./data/${sname}.old为空，从目的表获取数据..." 149 select_sql dest_db_user dest_db_pass DBNAME ./data/${sname}.old dest_db_user "$line" $dname 150 wlog "目的表数据获取成功,从源表获取数据..." 151 fi 152 153 select_sql source_db_user source_db_pass SDBNAME ./data/${sname}.new $suser "$line" $sname 154 # 对比文件，选出在new中但不在old中的行 155 if [ -s ./data/${sname}.old ];then 156 awk 'NR==FNR{if($0 !~ /^[ \t]*$/)arr[$0]+=1;next}{if($0 in arr || $0 ~ /^[ \t]*$/);else print;}' ./data/${sname}.old ./data/${sname}.new > ./data/${dname}.unl 157 else 158 cp ./data/${sname}.new ./data/${dname}.unl 159 fi 160 data_lines=`awk 'END{print NR}' ./data/${dname}.unl` 161 if [ ! -e ./data/${dname}.ctl ];then 162 wlog "不存在./data/${dname}.ctl,开始生成./data/${dname}.ctl " 163 make_ctl ${dname} 164 fi 165 166 if [ $data_lines -eq 0 ];then 167 wlog "本次无数据需要更新" 168 else 169 wlog "本次需要更新${data_lines}条数据" 170 wlog "执行sqlldr..." 171 sqlldr dest_db_user/dest_db_pass@DBNAME control=./data/${dname}.ctl direct=true log=./log/${dname}.log bad=./log/${dname}.bad 172 succ_cnt=`grep successfully ./log/${dname}.log | awk '{print $1}'` 173 bad_cnt=0 174 [ -e ./log/${dname}.bad ] && bad_cnt=`wc -l ./log/${dname}.bad | awk '{print $1}'` 175 wlog "成功更新:${succ_cnt}条数据,${bad_cnt}条失败" 176 fi 177 178 # 执行完毕，将${dname}.unl添加至${sname}.old 179 cat ./data/${dname}.unl >> ./data/${sname}.old 180 done 181 } 182 ############################################################################### 183 # main 184 main() { 185 wlog "+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++" 186 wlog "开始执行" 187 handle $1 188 wlog "执行完毕" 189 wlog "+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++" 190 } 191 192 if [ $# -ne 1 ];then 193 echo "Usage $0 file" 194 exit 1 195 else 196 main $1 197 fi
