#!/bin/bash
Auth:Zz
Script:导出一个表的数据,ftp上传后删除,以应对磁盘空间不够的问题。
sysname=$1
date=$2
tablelist=`grep "^${sysname};" /etl/etl/prog/tablename.list | awk -F ";" '{print $2}' | awk 'gsub(/[[:blank:]]*/,"")'`
user=`grep "^${sysname}%=" /etl/etl/prog/tablename.list |awk -F "%=" '{print $2}' | awk 'gsub(/[[:blank:]]*/,"")'`
password=`grep "^${sysname}%=" /etl/etl/prog/tablename.list|awk -F "%=" '{print $3}' | awk -F 'gsub(/[[:blank:]]*/,"")'`
tns=`grep "^{sysname}%=" /etl/etl/prog/tablename.list | awk -F '{print $4}'|awk -F 'gsub(/[[:blank:]]*/,"")'`
ftp_ip=`grep "^ftp_ip%=" /etl/etl/prog/tablename.list |awk -F "%=" '{print $2}' |awk -F 'gsub(/[[:blank:]]*/,"")'`
ftp_password=`grep "^ftp_ip%=" /etl/etl/prog/tablename.list | awk -F "%=" '{print $3}' |awk -F 'gsub(/[[:blank:]]*/,"")'`
localdmp_path=`grep "^path%=" /etl/etl/prog/tablename.list | awk -F "%=" '{print $2}' | awk -F 'gsub(/[[:blank:]]*/,"")'`
ftpdmp_path=`grep "^path%=" /etl/etl/prog/tablename.list | awk -F "%={print $3}"|awk -F 'gsub(/[[:blank:]]*/,"")'`
if [ -n "$2" ] ###没有日期,导出全量表,有日期导出增量数据
then
for tablename in $tablelist
do
bdp_etl_date=`sqlplus -s ${user}/${password}@${tns}<<!
set heading off;
set feedback off;
set verify off;
select bdp_etl_date from ${tablename} where rownum =1;
!` ##dds表etl时间有bdp_etl_date和bdp_modify_date两个,要做判断
if [ ${bdp_etl_date} -ge "0" ] || [ -z "$bdp_etl_date" ]
then
dmpname=`echo $tablename|sed 's/\./_/g'`
exp ${user}/${password}@$tns file=${localdmp_ptah}/${dmpname}.dmp tables=${tablename} query="\where bdp_etl_date=${date}\"
ftp_flg_cmd="
open ${ftp_ip}\n
prompt off\n
verbose off\n
user ${ftp_user} ${ftp_password}\n
lcd ${loacldmp_path}\n
cd ${ftpdmp_path}\n
bin\n
put ${dmpname}.dmp
bye\n
"
ftp_log=`echo ${ftp_flg_cmd}|ftp -n -v` #上传dmp文件,并得到ftp日志
cd ${localdmp_path}
rm ${dmpname}.dmp
echo "rm ${dmpname}.dmp"
echo ${ftp_log}
else
dmpname=`echo $tablename|sed 's/\./_/g'`
exp ${user}/${password}@$tns file=${localdmp_ptah}/${dmpname}.dmp tables=${tablename} query="\where bdp_modify_date=${date}\"
ftp_flg_cmd="
open ${ftp_ip}\n
prompt off\n
verbose off\n
user ${ftp_user} ${ftp_password}\n
lcd ${loacldmp_path}\n
cd ${ftpdmp_path}\n
bin\n
put ${dmpname}.dmp
bye\n
"
ftp_log=`echo ${ftp_flg_cmd}|ftp -n -v`
cd ${localdmp_path}
rm ${dmpname}.dmp
echo "rm ${dmpname}.dmp"
echo ${ftp_log}
fi
done
else
for tablename in $tablelist
do
dmpname=`echo $tablename|sed 's/\./_/g'`
exp ${user}/${password}@$tns file=${localdmp_ptah}/${dmpname}.dmp tables=${tablename}
ftp_flg_cmd="
open ${ftp_ip}\n
prompt off\n
verbose off\n
user ${ftp_user} ${ftp_password}\n
lcd ${loacldmp_path}\n
cd ${ftpdmp_path}\n
prom\n
bin\n
put ${dmpname}.dmp
bye\n
"
ftp_log=`echo ${ftp_flg_cmd}|ftp -n -v`
cd ${localdmp_path}
rm ${dmpname}.dmp
echo "rm ${dmpname}.dmp"
echo ${ftp_log}
done
cd /etl/etl/prog
fi
#################################################
将ftp上传步骤写成函数可以使脚本更简洁
ftp_function(){
ftp_ip=$1
ftp_user=$2
ftp_password=$3
loacldmp_path=$4
ftpdmp_path=$5
dmpname=$6
ftp_flg_cmd="
open ${ftp_ip}\n
prompt off\n
verbose off\n
user ${ftp_user} ${ftp_password}\n
lcd ${loacldmp_path}\n
cd ${ftpdmp_path}\n
prom\n
bin\n
put ${dmpname}.dmp
bye\n
"
ftp_log=`echo ${ftp_flg_cmd}|ftp -n -v`
cd ${localdmp_path}
rm ${dmpname}.dmp
echo "rm ${dmpname}.dmp"
echo ${ftp_log}
}
###############################################
tablename.list
######导出的表清单
DDS;DDS.ADD_TABLETEST1
DDS;DDS.ADD_TABLETEST2
DDS_ALL;DDS.ALL_TABLETEST1
DDS_ALL;DDS.ALL_TABLETEST2
######数据库用户名和密码及TNS
DDS%=dds%=dds%=bdpdb
DDS_ALL%=dds%=dds%=bdpdb
######ftp_ip用户名和密码
ftp_ip%=168.5.16.189%=etl%=etl
######dmp导出本地路径和ftp上传路径
path%=/etl/data/receive%=/etl/data/receive/test