#!/bin/bash

# by ray

# 2016-06-08

. ~/.bash_profile

export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

todayUtc=`date +%s000` #獲取毫秒的utc時間

tdate=`date +%F-%H%M%S` #獲取當前時間

days=185 #指定向前的天數，以獲取保留的分割槽數

truncdir=/tmp/truncpar

if [ ! -d ${truncdir} ];then

mkdir -p ${truncdir}

fi

#獲取所有分割槽表的函式

getParTableInfo(){

[ -e ${truncdir}/.partable.tmp ]&& rm -f ${truncdir}/.partable.tmp #檢查檔案是否存在，刪除存在的檔案

[ -e ${truncdir}/.partable.txt ]&& rm -f ${truncdir}/.partable.txt

#獲取所有分割槽表的表名和使用者名稱稱

sqlplus -s /nolog <<-RAY

conn $1/$2@$3

set termout off;

set echo off;

set feedback off;

set verify off;

set heading off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

spool ${truncdir}/.partable.tmp;

select DISTINCT A.TABLE_NAME from user_TAB_PARTITIONS A ;

spool off

RAY

grep "^[A-Z]" ${truncdir}/.partable.tmp > ${truncdir}/.partable.txt

[ -e ${truncdir}/.partable.tmp ]&& rm -f ${truncdir}/.partable.tmp

}

#獲取某個表的所有分割槽資訊

getPartitionInfo(){

[ -e ${truncdir}/.$1-$3-$4.tmp ]&& rm -f ${truncdir}/.$1-$3-$4.tmp

[ -e ${truncdir}/.$1-$3-$4.txt ]&& rm -f ${truncdir}/.$1-$3-$4.txt #檢查檔案是否存在，檔案的命名規則是oracleuser-tnsname-tablename.txt,例如kcpt-RACDB-th_vehicle_alarm.TXT使用者kcpt,tns連線字串為RACDB,表名為th_vehicle_alarm

sqlplus -s /nolog <<-RAY

conn $1/$2@$3

set termout off;

set echo off;

set feedback off;

set verify off;

set heading off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

spool ${truncdir}/.$1-$3-$4.tmp;

SELECT a.table_name,a.PARTITION_NAME,a.HIGH_VALUE

FROM dba_TAB_PARTITIONS A

WHERE A .table_name = '$4';

spool off

RAY

grep "^$4" ${truncdir}/.$1-$3-$4.tmp | sort -n -k 3 | awk '{print NR","$1","$2","$3}' > ${truncdir}/.$1-$3-$4.txt

[ -e ${truncdir}/.$1-$3-$4.tmp ]&& rm -f ${truncdir}/.$1-$3-$4.tmp

}

#函式：獲取備份命令

getExpdpParCommandAndTruncSql(){

#迴圈獲取表名

for i in `cat ${truncdir}/.partable.txt`

do

un=$1

up=$2

tn=$3

dn=$4

getPartitionInfo $1 $2 $3 $i

for j in `cat ${truncdir}/.$1-$3-$i.txt`

do

utc=`echo $j | awk -F ',' '{print $4}'`

if [[ ${utc} -ge ${todayUtc} ]];then

linenum=`echo $j | awk -F ',' '{print $1}'` #獲取表內highvalue值大於當前utc時間的分割槽的行號

linenum=$[$linenum-$days] #獲取表內highvalue值大於當前utc時間減去指定的天數的分割槽的行號

dumpnum=$[$linenum+2] #獲取備份的分割槽表的行號

if [[ ${dumpnum} -lt 1 || ${linenum} -lt 1 ]];then #當行號小於1時推出當前迴圈

continue

else

#獲取expdp命令，把命令寫入檔案

#sed -n "${dumpnum}p" ${truncdir}/.$1-$3-$i.txt | awk -F ',' '{print "expdp '${un}'/'${up}' directory=expdp network_link='${dn}' dumpfile='${tn}'-"$2"-"$3"-'${tdate}'.dmp logfile='${tn}'-"$2"-"$3"-'${tdate}'.log tables="$2":"$3}' >> ${truncdir}/ExpdpPar.cmd

sed -n "${dumpnum}p" ${truncdir}/.$1-$3-$i.txt | awk -F ',' '{print "expdp '${un}'/'${un}' directory=expdp network_link='${dn}' dumpfile='${tn}'-"$2"-"$3"-'${tdate}'.dmp logfile='${tn}'-"$2"-"$3"-'${tdate}'.log tables="$2":"$3}' >> ${truncdir}/ExpdpPar.cmd

#獲取truncate命令，把命令寫入檔案

sed -n "${linenum}p" ${truncdir}/.$1-$3-$i.txt | awk -F ',' '{print "alter table "$2" truncate partition "$3" update global indexes;"}' >> ${truncdir}/truncPar.sql

#expdpcommand=`sed -n "${dumpnum}p" ${truncdir}/.$1-$3-$i.txt | awk -F ',' '{print "expdp '${un}'/'${up}' director=expdp network_link='${dn}' dumpfile='${tn}'-"$2"-"$3"-'${tdate}'.dmp logfile='${tn}'-"$2"-"$3"-'${tdate}'.log tables="$2":"$3}'`

#echo ${expdpcommand} >> ${truncdir}/ExpdpPar.cmd

break

#rm -rf ${truncdir}/.$1-$3-$i.txt

fi

done

}

#函式:執行指定sql

execSQL(){

sqlplus /nolog <<-RAY

conn $1/$2@$3

@$4

RAY

}

#指令碼入口

#迴圈引數檔案的內容

for f in `cat $1`

do

[ -e ${truncdir}/ExpdpPar.cmd ]&& rm -f ${truncdir}/ExpdpPar.cmd

[ -e ${truncdir}/truncPar.sql ]&& rm -f ${truncdir}/truncPar.sql

#獲取oracle使用者，密碼和tns連線字串名稱，dblink名稱

ouname=`echo ${f} | awk -F ',' '{print $1}'`

oupass=`echo ${f} | awk -F ',' '{print $2}'`

tnsname=`echo ${f} | awk -F ',' '{print $3}'`

dblname=`echo ${f} | awk -F ',' '{print $4}'`

#獲取指定使用者的所有的分割槽表

getParTableInfo ${ouname} ${oupass} ${tnsname}

#獲取指定使用者的所有分割槽表指定分割槽的expdp語句和截斷分割槽語句

getExpdpParCommandAndTruncSql ${ouname} ${oupass} ${tnsname} ${dblname}

#執行備份

[ -e ${truncdir}/ExpdpPar.cmd ]&& bash ${truncdir}/ExpdpPar.cmd

#執行截斷分割槽

[ -e ${truncdir}/truncPar.sql ]&& execSQL ${ouname} ${oupass} ${tnsname} "${truncdir}/truncPar.sql"

[ -e ${truncdir}/ExpdpPar.cmd ]&& rm -f ${truncdir}/ExpdpPar.cmd

[ -e ${truncdir}/truncPar.sql ]&& rm -f ${truncdir}/truncPar.sql

done

###################################

#trunc_par.prm 引數檔案

#引數檔案，格式：使用者名稱，密碼，tns連線字串，dblink名稱

ora_name,ora_pass,RACDB,DB_BASIC

ora_name,ora_pass,RACDB,DB_STORAGE

ora_name,ora_pass,kcptdg2,kcpt103

###################################

#用法：./trunc_partition-data.sh /path/trunc_par.prm

自動備份、截斷分割槽表分割槽資料

相關文章