expdp ---> impdb

性能优化： 除了设置合理　tempspace  &&  undospace  

导出导入  PARALLEL 和 CPU 核数保持一致

 

一  导出

1.1  expdp  

rq=` date +"%Y%m%d" `
CMD_STR="expdp \'/ as sysdba \' directory=back CLUSTER=N PARALLEL=2 JOB_NAME=cwbase111_table_exp_${rq} dumpfile=127.0.0.1_cwbase111_${rq}_%U.dmp logfile=127.0.0.1_cwbase1_table_exp_$rq.log TABLES=cwbase111.FBJSS,cwbase111.FBJSSKD"
su - oracle -c "$CMD_STR"

  

二  导入

2.1  停止oracle 后台 impdb 任务
source ~/.bash_profile

owner_name=$1
job_name=$2

echo '-------------------- oracle_stop_datapump_job.sh begin ----------------------------'
echo 'owner_name：' $owner_name ',job_name:' $job_name

sqlplus -S "/as sysdba" <<EOF
set head off
set feedback off
set time off
set timing off
# set echo off

DROP TABLE $owner_name.$job_name;
EXEC DBMS_DATAPUMP.STOP_JOB(DBMS_DATAPUMP.ATTACH('$job_name','$owner_name'));

COMMIT;
exit
EOF
echo '-------------------- oracle_stop_datapump_job.sh end ------------------------------'

 

2.2   impdb  

rq=` date +%Y%m%d --date="-1 day" `

job_name_pre=CWBASE111_12_imp

if [ -n "$1" ]
then
echo "传入日期为：$1"
rq=$1
else
echo "传入日期为: 空，用默认日期：${rq}"

fi
echo "运行日期： $rq"

owner_name=SYS
job_name=${job_name_pre}_${rq}

#查询进程，kill 当前jar/java程序
pid=`ps -ef|grep $job_name_pre|grep -v grep|grep -v kill|awk '{print $2}'`
if [ ${pid} ]; then
kill -9 $pid
echo "Stop ${pid} Finished"
fi

sh /ftp/ssp/oracle_stop_datapump_job.sh ${owner_name} ${job_name}
echo "oracle_stop_datapump_job.sh 传入owner_name: ${owner_name}，传入job_name：${job_name}"
echo "oracle执行有缓存时间，120秒后台执行"
sleep 120

impdp 'userid="/ as sysdba"' EXCLUDE=FUNCTION,PROCEDURE,PACKAGE,CONSTRAINT,REF_CONSTRAINT,GRANT,INDEX,TRIGGER,STATISTICS directory=IMP_FTP_FMIS_ZBJTBB    JOB_NAME=${job_name} table_exists_action=REPLACE remap_schema=cwbase1111:CWBASE1111_12 remap_tablespace=CWDATA111:LBS_CWBASE111  CLUSTER=N PARALLEL=2 dumpfile=127.0.0.1_cwbase1_${rq}_%U.dmp logfile=127.0.0.1_cwbase1_table_imp_${rq}.log

 

2.3   schedule

rq=20230215

echo $(date +%F%n%T) "ssp_batch begin "
sh /ftp/ssp_batch.sh ${rq}
echo $(date +%F%n%T) "ssp_batch finished "