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
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
cd /data/dump_dir
find /data/dump_dir/partition* -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 "5 days ago" +%Y%m%d`
OWNER=TOPUP
RECORDTAB=TAB_PARTITION_MANTAIN_RECORD
TABNAME=`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
cd /data/dump_dir
find /data/dump_dir/partition* -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 "5 days ago" +%Y%m%d`
OWNER=TOPUP
RECORDTAB=TAB_PARTITION_MANTAIN_RECORD
TABNAME=`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
do
#echo $tablename
#done
#done
TAB_A=$OWNER.$tablename:$PARTNAME
TAB_B=$tablename$PARTNAME
TAB_B=$tablename$PARTNAME
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
/u01/product/oracle/11.2.0/db_1/bin/exp 'system/"system#test"' file=/data/dump_dir/$TAB_B.dmp log=/data/dump_dir/$TAB_B.log tables=$TAB_A
/u01/product/oracle/11.2.0/db_1/bin/exp 'system/"system#test"' file=/data/dump_dir/$TAB_B.dmp log=/data/dump_dir/$TAB_B.log tables=$TAB_A
flag=successfully
sflag=`tail -1 /data/dump_dir/$TAB_B.log | awk '{print $3}'`
#createdate=`date +%Y%m%d`
createdate=`date -d "5 days ago" +%Y%m%d`
sflag=`tail -1 /data/dump_dir/$TAB_B.log | awk '{print $3}'`
#createdate=`date +%Y%m%d`
createdate=`date -d "5 days ago" +%Y%m%d`
if [ "$sflag" = "$flag" ];then
/u01/product/oracle/11.2.0/db_1/bin/sqlplus /nolog <connect / as sysdba;
insert into $OWNER.$RECORDTAB values('$createdate','$tablename','$PARTNAME','1');
commit;
exit;
EOF
else
/u01/product/oracle/11.2.0/db_1/bin/sqlplus /nolog <connect / as sysdba;
insert into $OWNER.$RECORDTAB values('$createdate','$tablename','$PARTNAME','1');
commit;
exit;
EOF
else
/u01/product/oracle/11.2.0/db_1/bin/sqlplus /nolog <connect / as sysdba;
insert into $OWNER.$RECORDTAB values('$createdate','$tablename','$PARTNAME',0);
commit;
exit;
EOF
insert into $OWNER.$RECORDTAB values('$createdate','$tablename','$PARTNAME',0);
commit;
exit;
EOF
fi;
done
2.3 导出状态记录到记录表
CREATE TABLE TAB_PARTITION_MANTAIN_RECORD
(
CREATEDATE VARCHAR2(100 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;
(
CREATEDATE VARCHAR2(100 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博客 ” ,链接:http://blog.itpub.net/25198367/viewspace-735909/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25198367/viewspace-735909/