shell如何定时导入mysql数据到oracle_Powershell

1、业务场景

a系统负责开单，使用mysql数据库，b系统需要开单数据，使用oracle

2、实现思路

开单数据不可能来一条转移一条，只能定时任务复制一个时间段的数据

考虑业务上的故障可能需要补数，脚本分为两个sh,一个跑定时任务，每5分钟传入时间段给另一个脚本执行复制

先由a系统编写录单信息sql，联合查询出b系统需要的信息，从mysql导出文件，再通过ctl文件用sqlldr导入b系统oracle数据库

3、参考案例

1.mysql的录单信息查询 ,tu_doc_info.sql

select 
a.logistics_id as doc_id,
b.order_id as po_mark,
a.created_dot_code as send_comp,
...
from dot_logistics a 
left join dot_order b on a.logistics_id = b.logistics_id
left join dot_logistics_package c on a.logistics_id = c.logistics_id
left join ky_ydserver.gs d on a.created_dot_code = d.bm
where a.create_time between '#1' and '#2';

2.mysql导出语法测试,imp5m_wd.sh

#!/bin/sh
# ./imp5m_wd.sh "2013-10-21 00:00:00" "2013-10-21 00:04:59"
mysqlhost=127.0.0.1
mysqluser=root
mysqlpwd=root
#替换sql文件中的时间区间
sed  -e "s!#1!$1!g" -e "s!#2!$2!g" tu_doc_info.sql >tu_doc_info_2.sql
#获取sql语句内容
expsql=$(cat tu_doc_info_2.sql)
loc_ts_file=output.txt
# -n不要表头  -d 选择数据库
mysql -n -s -r --default-character-set=utf8 -h${mysqlhost} -p3306 -u${mysqluser} -p${mysqlpwd} -d ydky_dot -e "${expsql}" > $loc_ts_file

3.oracle控制文件 tu_doc_info.ctl

load data
characterset utf8
infile #1 
append into table tu_doc_info#2
fields terminated by x'09'
trailing nullcols
(doc_id,po_mark,send_comp,first_cent,recv_comp,last_cent,obj_name,pack_typ,obj_typ,obj_wei,cash_wei,
obj_vol,obj_qty,prod_typ,srvc_level,send_typ,pay_typ,deliv_amt,pres_amt,pres_exp,up_exp,oth_exp,
tot_amt,cust_name,send_prov,send_city,send_ctry,cust_comp,send_addr,send_mob_tel,sign_msg,send_tel,
cust_recv_name,cust_recv_comp,recv_prov,recv_city,recv_ctry,recv_addr,recv_mob_tel,deliv_msg,
recv_tel,doc_cmt,order_time date 'yyyy-mm-dd hh24:mi:ss',entry_man,alt_1,alt_2,alt_3,alt_4,alt_5,alt_6,alt_7,doc_src,po_typ,loan_amt,obj_sn)

4.oracle 导入测试 imp5m_wd.sh

#!/bin/sh
# ./imp5m_wd.sh ./output.txt 2
loc_user=root
loc_pass=root
#修改ctl的导入文件和分表的后缀
sed  -e "s!#1!'$1'!g" -e "s!#2!$2!g" tu_doc_info.ctl > tu_doc_info_2.ctl
sqlldr userid=$loc_user/$loc_pass@127.0.0.1/orcl control=tu_doc_info_2.ctl

5.整合导出和导入

传入查询时间，执行成功返回0

#!/bin/sh
# ./imp5m_wd.sh "2013-10-21 00:00:00" "2013-10-21 00:04:59"
source /home/oracle/.bash_profile
# 1-1 para
tsk_prx=$1
tsk_beg=$2
# 1-2 gen
tsk_cur_dat=$(date +%s -d "$tsk_beg")
tsk_cur=${tsk_beg//[- :]/}
tsk_day=${tsk_cur:0:8}
tsk_min=${tsk_cur:8:4}
tsk_mon=${tsk_day:4:2}
tsk_flg=`printf %02d $((10#$tsk_mon % 3 + 1))`
tsk_prt=${tsk_day:6:2}
# 1-3 cfg db
mysqlhost=10.19.205.73
mysqluser=ggs
mysqlpwd=**************

loc_user=ggs
loc_pass=**********
# 1-4 cfg path
loc_base=/u02/dab/shelltest
loc_ts_ord=${loc_base}/wdorder
loc_ts_imp=${loc_ts_ord}/${tsk_day}
loc_data_path=${loc_ts_imp}/${tsk_day}_${tsk_min}
loc_ts_file=${loc_ts_imp}/${tsk_day}_${tsk_min}.txt

# 1-5 check
if [ ! -d "$loc_ts_imp" ]; then
    mkdir "$loc_ts_imp"
fi
cd ${loc_base}

# update expsql and expfrommysql
sed  -e "s!#1!$tsk_prx!g" -e "s!#2!$tsk_beg!g" ${loc_base}/tu_doc_info.sql > ${loc_data_path}.sql
expsql=$(cat ${loc_data_path}.sql)
mysql -n -s -r --default-character-set=utf8 -h${mysqlhost} -p3306 -u${mysqluser} -p${mysqlpwd} -d ydky_dot -e "${expsql}" > $loc_ts_file
if [ "$?" != "0" ]; then
    exit 128
fi
if [ ! -e "$loc_ts_file" ]; then
	echo "not exists $loc_ts_file" 
    exit 127
fi
# 1-5 gen script and import
sed  -e "s!#1!'$loc_ts_file'!g" -e "s!#2!$tsk_flg!g" ${loc_base}/tu_doc_info.ctl > ${loc_data_path}.ctl
echo "sqlldr userid=$loc_user/$loc_pass@10.19.205.59/orcl control=${loc_data_path}.ctl"
sqlldr userid=$loc_user/$loc_pass@10.19.205.59/orcl control=${loc_data_path}.ctl
if [ "$?" != "0" ]; then
    exit 128
fi
exit 0

6,编写定时任务的imp5m_wd_call.sh，每5分钟执行一次

用一个updata.txt标识执行时间，防止脚本维护期间数据遗漏

#!/bin/sh
# config
range=9
delay=2
loc_work=/u02/dab/shelltest
loc_data_dta=${loc_work}/wdorder/
cd ${loc_work}
while [ 1 ]
do
        # 1 get time from file
        timepre_t=`head -1 imp5m_wdorder_call_update.txt`
        timepre_s=$(date +%s -d "$timepre_t")
        timepre=$(date +%y%m%d%h%m%s -d "$timepre_t")
        # 2 get cur work time (need change range->300 min->3)
        timecur_s=$(($timepre_s + $range))
        timecur_t=$(date '+%y-%m-%d %h:%m:%s' -d "1970-01-01 utc $timecur_s seconds");
        timecur=$(date +%y%m%d%h%m%s -d "$timecur_t")
        # 3 get next time
        timenxt_s=$(($timepre_s + $range + 1))
        timenxt_t=$(date '+%y-%m-%d %h:%m:%s' -d "1970-01-01 utc $timenxt_s seconds");
        # 4 get now sys time
        timenow_t=$(date '+%y-%m-%d %h:%m:%s' -d '-1 min')
        timenow_s=$(date +%s -d "$timenow_t")
        # 5 if have task
        if [ $timecur_s -lt $timenow_s ]; then
                # 5-1 hour
                hourpre=`expr substr $timepre 9 2`
                # 5-2 do by day
                echo "!!!begin<$(date)> [${timepre_t}, ${timecur_t}]"
                ./imp5m_wd.sh "${timepre_t}" "${timecur_t}"
                if [ "$?" == "0" ]; then
                    echo "$timenxt_t" > imp5m_wdorder_call_update.txt
                    echo "$timenxt_t" > ${loc_data_dta}/imp5m_wdorder_call_update.txt
                    sleep 1
                    echo "!!!done <$(date)>done"
                else
                    echo "   error for wait"
                    sleep $delay
                fi
        else
                # 4-8 delay
                echo "!!!sleep<$(date)>"
                sleep $delay
        fi
done