5、expdp

```bash
#!/bin/bash

source /home/oracle/.bash_profile

###############  1. 10.235.23.138 逻辑备份   ###################
WORK_DIR=/backup/partitiontable

SHELL_DIR=/home/oracle/backup/wlzy_gsm/table
LOG="${SHELL_DIR}/xunjian_partition_table.txt"
ROW="${SHELL_DIR}/xunjian_rows.txt"

if [ $# -ne 0 ];then
    t_day=$1
else
    t_day=`date -d "1 day ago" +"%Y%m%d"`
fi

cd ${WORK_DIR}
if [ ! -d $t_day ]; then
    mkdir $t_day
fi

#业务数据备份
cd ${SHELL_DIR}
while read line
do
    #t_nums=$(expr $t_nums + 1)

#表名
    t_table=`echo $line |awk -F'|' '{print $1}' `
    #表类型:1、分区表;2、静态配置表;3、普通表;
    t_type=`echo $line |awk -F'|' '{print $2}' `
    #查询条件
    t_query=`echo $line |awk -F'|' '{print $3}' `
    ################   备份数据       ###################
    if [ "$t_type" = 1 ];
    then
        echo "分区表备份：${t_table}"
        expdp wlzy_gsm/Lc_jcgx0@HXWDB3 dumpfile=${t_table}.dmp directory=expdp_subtable tables=${t_table}:P_1D_${t_day} compression=data_only CONTENT=DATA_ONLY logfile=${t_table}.log
    elif [ "$t_type" = 2 ];
    then
        echo "静态表备份：${t_table}"
        expdp wlzy_gsm/Lc_jcgx0@HXWDB3 dumpfile=${t_table}.dmp directory=expdp_subtable tables=${t_table} compression=data_only CONTENT=DATA_ONLY logfile=${t_table}.log
    else
        echo "普通表备份：${t_table}"
        expdp wlzy_gsm/Lc_jcgx0@HXWDB3 dumpfile=${t_table}.dmp directory=expdp_subtable tables=${t_table} compression=data_only CONTENT=DATA_ONLY query="'where ${t_query}=''${t_day}'''" logfile=${t_table}.log
    fi

mv ${WORK_DIR}/${t_table}* ${WORK_DIR}/${t_day}
    sleep 5
done < tablelist.txt

###############  2. 巡检   ###################
echo "------------------ ${t_day}:start ------------------" >${LOG}
#需要备份表个数
t_nums=$(cat ${SHELL_DIR}/tablelist.txt | wc -l)
#备份成功表个数
suc_nums=$(cat  ${WORK_DIR}/${t_day}/*.log |grep "successfully completed"| wc -l)

if [ $t_nums -ne $suc_nums ]; then
    err_info="ERROR:sjwdb:需要备份表个数:$t_nums,备份成功表个数:$suc_nums,备份失败 please check"
    echo $err_info >>${LOG}
else
    err_info="OK:sjwdb:需要备份表个数:$t_nums,备份成功表个数:$suc_nums,备份成功"
    echo $err_info >>${LOG}
fi

#表备份条数记录
cat  ${WORK_DIR}/${t_day}/*.log |grep rows|awk -F ' ' '{print $4"|"$7"|table"}'  >${ROW}
#异常数据备份记录
cat  ${WORK_DIR}/${t_day}/*.log |grep "could not be exported or imported"|awk -F ' ' '{print $3"|ERROR:was not found or could not be exported or imported|table"}'  >>${ROW}

sed -i "s/$/|${err_info}|${t_day}/g" ${ROW}

###############  3. 巡检记录入库便于日常核查   ###################
cd ${SHELL_DIR}
rm -f xunjian_rows.ctl ;
cat > ${SHELL_DIR}/xunjian_rows.ctl <<END
OPTIONS (skip=0)
load data 
CHARACTERSET AL32UTF8
INFILE '${ROW}'
append into table xunjian_rows
FIELDS TERMINATED BY '|'
trailing nullcols 
(table_desc,
row_nums,
unit,
err_info,
day_key
)
END

sqlldr userid=wlzy_gsm/Lc_jcgx0@HXWDB3 control=${SHELL_DIR}/xunjian_rows.ctl errors=1000000 rows=50000 bindsize=20971520 readsize=20971520
```

FYF

导航1234

最近发表

友情链接