数据同步脚本(linux)
--从文本导入数据库的sh脚本（ems_syn_info.sh）
ORACLE_BASE=/usr/oracle10g/app/oracle
export ORACLE_BASE
ORACLE_HOME=/usr/oracle10g/app/oracle/product/10.2.0/db_1
export ORACLE_HOME
ORACLE_SID=orcl
export ORACLE_SID
PATH=$PATH:/usr/bin:/usr/sbin:$ORACLE_HOME/bin
export PATH
#sqlldr control file baseDir
ctlDir=/var/ftp/cnpl_data_exchange/ctl/
cd /var/ftp/cnpl_data_exchange/data
#get current date format yyyyMMdd
curdate=`date '+%Y%m%d'`
#get current date ago 1 days
#curdate=`date -d '1 days ago' +'%Y%m%d'`
#modify summary log file
Log="$curdate"_emssyn_data_import.log
echo -----------------$curdate ---------------- >> $Log
date "+%Y-%m-%d %H:%M:%S" >> $Log
echo -----------------DataImport Job begin at ---------------- >> $Log
#EMS_sys_info
#check the file
if [ -e "i_ems183_"$curdate".txt" ]; then
sqlldr userid=cp_cd/cd@cnpl_cm data=i_ems183_"$curdate".txt log="$curdate"_mailInfosyn.log bad="$curdate"_mailInfosyn.bad control="$ctlDir"ems_mail_info.ctl
find /var/ftp/cnpl_data_exchange/curData -name i_ems183_"$curdate".txt -exec rm {} \;
fi
echo -----------------DataImport Job end at ---------------- >> $Log
date "+%Y-%m-%d %H:%M:%S" >> $Log
echo -----------------Collect log and bad report Job begin ---------------- >> $Log
echo imp END
cp *.log ../logs
echo log END
cp *_mailInfosyn.bad ../bad
cp i_ems183_"$curdate".txt ../hisData
#cp i_ems183_"$curdate".txt ../curData
find . -name i_ems183_"$curdate".txt -exec rm {} \;
find . -name *_mailInfosyn.bad -exec rm {} \;
echo -----------------clean file Job end ---------------- >> $Log
cp *.log ../logs
find . -name "*.log" -exec rm {} \;
我在写这个脚本时参照的是下面这个脚本( common_Data.sh)：
ORACLE_BASE=/usr/oracle10g/app/oracle
export ORACLE_BASE
ORACLE_HOME=/usr/oracle10g/app/oracle/product/10.2.0/db_1
export ORACLE_HOME
ORACLE_SID=orcl
export ORACLE_SID
PATH=$PATH:/usr/bin:/usr/sbin:$ORACLE_HOME/bin
export PATH
ctlDir=/var/ftp/cnpl_data_exchange/ctl/
#FtpLog=/var/ftp/uploads/cnpl_data_exchange/logs/ftp.log
cd /var/ftp/cnpl_data_exchange/data
# modify date
#curdate=`date '+%Y%m%d'`
curdate=`date -d '1 days ago' +'%Y%m%d'`
#curdate="20100408"
#cat ftp.txt | ftp -n
#modify summary log file
Log="$curdate"_data_import.log
date "+%Y-%m-%d %H:%M:%S" >> $Log
echo -----------------DataImport Job begin at ---------------- >> $Log
# PRODUCTION
# check the file
#if [ -e "i_"$curdate"_02107_00.dat" ]; then
# sqlldr userid=cp_cc/cc@cnpl_cc data=i_"$curdate"_02107_00.dat log="$curdate"_02107_00.log bad="$curdate"_02107_00.bad control="$ctlDir"02107_00_production.ctl
# find /var/ftp/cnpl_data_exchange/curData -name *_02107_00.dat -exec rm {} \;
#fi
#CITY
if [ -e "i_"$curdate"_05101_00.dat" ];then
echo dateFile name:i_"$curdate"_05101_00.dat >> $Log
sqlldr userid=cp_cc/cc@cnpl_cc data=i_"$curdate"_05101_00.dat log="$curdat
e"_05101_00.log bad="$curdat"e_05101_00.bad control="$ctlDir"05101_00_city.ctl
find /var/ftp/cnpl_data_exchange/curData -name "*_05101_00.dat" -exec rm {} \;
fi
#COUNTRY
if [ -e "i_"$curdate"_05103_00.dat" ];then
echo dateFile name:i_"$curdate"_05103_00.dat >> $Log
sqlldr userid=cp_cc/cc@cnpl_cc data=i_"$curdate"_05103_00.dat log="$curdate"_05103_00.log bad="$curdate"_05103_00.bad control="$ctlDir"05103_00_nation.ctl
find /var/ftp/cnpl_data_exchange/curData -name *_05103_00.dat -exec rm {} \;
fi
#PROVINCE
if [ -e "i_"$curdate"_05105_00.dat" ];then
echo dateFile name:i_"$curdate"_05105_00.dat >> $Log
sqlldr userid=cp_cc/cc@cnpl_cc data=i_"$curdate"_05105_00.dat log="$curdate"_05105_00.log bad="$curdate"_05105_00.bad control="$ctlDir"05105_00_province.ctl
find /var/ftp/cnpl_data_exchange/curData -name *_05105_00.dat -exec rm {} \;
fi
#COUNTY
if [ -e "i_"$curdate"_05106_00.dat" ];then
echo dateFile name:i_"$curdate"_05106_00.dat >> $Log
sqlldr userid=cp_cc/cc@cnpl_cc data=i_"$curdate"_05106_00.dat log="$curdate"_05106_00.log bad="$curdate"_05106_00.bad control="$ctlDir"05106_00_county.ctl
find /var/ftp/cnpl_data_exchange/curData -name *_05106_00.dat -exec rm {} \;
fi
#ORGANIZATION
if [ -e "i_"$curdate"_06107_00.dat" ]; then
echo dateFile name:i_"$curdate"_06107_00.dat >> $Log
sqlldr userid=cp_cc/cc@cnpl_cc data=i_"$curdate"_06107_00.dat log="$curdate"_06107_00.log bad="$curdate"_06107_00.bad control="$ctlDir"06107_00_organization.ctl
find /var/ftp/cnpl_data_exchange/curData -name *_06107_00.dat -exec rm {} \;
fi
echo -----------------DataImport Job end at ---------------- >> $Log
echo -----------------Collect log and bad report Job begin ---------------- >> $Log
cp *.log ../logs
cp *.bad ../bad
cp i_"$curdate"*.dat ../hisData
cp i_"$curdate"*.dat ../curData
echo -----------------Collect log and bad report Job end ---------------- >> $Log
echo -----------------Clean File Job begin ---------------- >> $Log
#find . -name "i*.dat" -exec rm {} \;
#find . -name "*.bad" -exec rm {} \;
echo -----------------clean file Job end ---------------- >> $Log
cp *.log ../logs
find . -name "*.log" -exec rm {} \;
#exit
---数据库数据控制脚本(ems_mail_info.ctl)：
OPTIONS (rows=128,errors=-1)
load data
characterset zhs16cgb231280
append
into table TBL_EMS_MAIL_INFO_SYN
fields terminated by X'09'
trailing nullcols
(
MAIL_NUM "trim(:MAIL_NUM)",
ORDERCODE "trim(:ORDERCODE)",
SENDER_DEPT_NAME "trim(:SENDER_DEPT_NAME)",
SENDER_ADDR "trim(:SENDER_ADDR)",
SENDER_NAME "trim(:SENDER_NAME)",
SENDER_HOME_AREA_NAME "trim(:SENDER_HOME_AREA_NAME)",
SENDER_POST_CODE "trim(:SENDER_POST_CODE)",
SENDER_CONTACT_PHONE "trim(:SENDER_CONTACT_PHONE)
",
RCVER_DEPT_NAME "trim(:RCVER_DEPT_NAME)",
RCVER_ADDR "trim(:RCVER_ADDR)",
RCVER_NAME "trim(:RCVER_NAME)",
RCVER_CONTACT_PHONE "trim(:RCVER_CONTACT_PHONE)",
ACTUAL_WEIGHT,
LENGTH,
PROD_CODE "trim(:PROD_CODE)",
PAY_SIDE_CODE "trim(:PAY_SIDE_CODE)",
BASIC_FEE,
SHOULD_PAY_TOTAL_FEE,
ACTUAL_TOTAL_FEE,
STATE "trim(:STATE)"
)
同样也是参照下面这个文件写的(02107_00_production.ctl)：
load data
characterset zhs16cgb231280
replace
into table CP_POSTAL_PRODUCTION_EXCHANGE
fields terminated by X'09'
trailing nullcols
(
PROD_CODE,
PROD_NAME,
PROD_DESC,
ESTAB_ORG_CODE,
ESTAB_PERSON,
ESTAB_DATE DATE "YYYY-MM-DD",
AUDIT_ORG_CODE,
PROD_APPLY_DATE DATE "YYYY-MM-DD",
PROD_FINISH_DATE "to_date(to_char(sysdate,'YYYY-MM-DD'),'YYYY-MM-DD')"
)
---写的一个定时任务：
[root@zgyzcc10 ctl]# crontab -l
0 1 * * * /var/ftp/cnpl_data_exchange/bin/common_Data.sh
30 1 * * * /var/ftp/cnpl_data_exchange/bin/custData_load.sh
0 2 * * * /var/ftp/cnpl_data_exchange/bin/custData_to_business.sh
0 4 * * * /var/ftp/cnpl_data_exchange/bin/EMS_SYN_INFO.sh
*/5 * * * * /usr/bin/php /var/www/cacti/poller.php > /dev/null 2>&1
[root@zgyzcc10 ctl]# crontab -e
进行任务编辑。