2. 基本思路

2.1 通过定制任务调用LINUX脚本实现分区表的定时导出并记录导出状态
crontab -l
30 10 * * * sh /data/run/exp_partition.sh 1>>/data/run/log/exp_partition.log 2>>/data/run/log/exp_partition.bad

2.2 LINUX脚本实现分区表的定时导出并记录导出状态，每天定时导出5天前的分区，若成功完成，则记录为1，否则记录为0;脚本代码如下：

#!/bin/sh

v_path=/data/dump_dir
cd $v_path
find $v_path/TAB* -mtime +15 -exec rm -f {} \;
ORACLE_HOME=/u01/product/oracle/11.2.0/db_1;export ORACLE_HOME
ORACLE_SID=szxddb;export ORACLE_SID
FILENAME=`date +%Y%m%d%H`
PARTNAME=P`date -d "3 days ago" +%Y%m%d`
OWNER=TOPUP
RECORDTAB=TAB_PARTITION_MANTAIN_RECORD
TABNAME=`$ORACLE_HOME/bin/sqlplus -silent "/ as sysdba" << END
set pagesize 0 feedback off verify off heading off echo off
select table_name from dba_part_tables where wner = '$OWNER';
exit;
END`
#echo $TABNAME

for tablename in $TABNAME
do

#echo $tablename
#done

TAB_A=$OWNER.$tablename:$PARTNAME
TAB_B=$tablename$PARTNAME

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
$ORACLE_HOME/bin/expdp 'system/"system#test"' directory=dump_dir dumpfile=$TAB_B.dmp logfile=$TAB_B.log tables=$TAB_A

v_type=expdp_partition
v_log=ok
flag=successfully
sflag=`tail -1 $v_path/$TAB_B.log | awk '{print $3}'`
v_err_1=`cat $v_path/$TAB_B.log | grep err`
v_err_2=`cat $v_path/$TAB_B.log | grep ora`
v_err_3=`cat $v_path/$TAB_B.log | grep ora`
createdate=`date +%Y%m%d`
#createdate=`date -d "4 days ago" +%Y%m%d`
err_1=${v_err_1:=ok}
err_2=${v_err_2:=ok}
err_3=${v_err_3:=ok}

if [ "$sflag" = "$flag" ]&&[ "$err_1" = "$v_log" ]&&[ "$err_2" = "$v_log" ]&&[ "$err_3" = "$v_log" ];then

$ORACLE_HOME/bin/sqlplus /nolog <connect / as sysdba;
insert into $OWNER.$RECORDTAB values('$createdate','$v_type','$tablename','$PARTNAME','1');
commit;
exit;
EOF

else

$ORACLE_HOME/bin/sqlplus /nolog <connect / as sysdba;
insert into $OWNER.$RECORDTAB values('$createdate','$v_type','$tablename','$PARTNAME',0);
commit;
exit;
EOF

fi;

done

2.3 导出状态记录到记录表

CREATE TABLE TAB_PARTITION_MANTAIN_RECORD
(
CREATEDATE     VARCHAR2(100 BYTE),
OPERATETYPE    VARCHAR2(50 BYTE),
TABLENAME      VARCHAR2(100 CHAR),
PARTITIONNAME VARCHAR2(50 BYTE),
FLAG           VARCHAR2(1 CHAR)
)
TABLESPACE BASE_DATA
PCTUSED    0
PCTFREE    20
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

ITPub博客

改进版ORACLE 分区表定期自动分区导出及删除导出分区(二)

最新文章