備份表空間並上傳磁帶庫的指令碼

#!/bin/sh

#應用Oracle使用者的環境變數
. /oracle/.profile

#指定程式安裝路徑
PDIR="/bkda2/expdump/bin/backup/month"

#刪除歷史資料程式路徑
DDIR="/bkda2/expdump/bin/drop_his"

[@more@]

#********************
# 程式初始化
#********************

#當前年
YY=`date +%Y`
#當前月
MM=`date +%m`

#切換到指令碼所在目錄
cd $PDIR

echo "nnn" >./log/backup.log 1>&1
echo "Initializing....n===================================" >>./log/backup.log 1>&1

#輸入需要備份的表空間名
#echo "Enter TablespaceName:c"
#read SPACE_NAME
SPACE_NAME=$1
#輸入執行壓縮的使用者名稱/密碼
#echo "Enter Username/Password:c"
#read USERPASS
USERPASS=$2

#判斷表空間名是否輸入
if [ "$SPACE_NAME" = "" ]; then
echo "n**Error!** You did not enter TablespaceName" >>./log/backup.log 1>&1
ps -ef | grep "backup.sh" | grep -v grep | awk '{print $2}' | xargs -i kill -9 {}
fi

#判斷表使用者名稱/密碼是否輸入
if [ "$SPACE_NAME" = "" ]; then
echo "n**Error!** You did not enter Username/Password" >>./log/backup.log 1>&1
ps -ef | grep "backup.sh" | grep -v grep | awk '{print $2}' | xargs -i kill -9 {}
fi

#匹配待壓縮資料月份
if [ "$MM" = "01" ]; then
MONTH=$(($YY-1))"10"
elif [ "$MM" = "02" ]; then
MONTH=$(($YY-1))"11"
elif [ "$MM" = "03" ]; then
MONTH=$(($YY-1))"12"
elif [ "$MM" = "04" ]; then
MONTH=$YY"01"
elif [ "$MM" = "05" ]; then
MONTH=$YY"02"
elif [ "$MM" = "06" ]; then
MONTH=$YY"03"
elif [ "$MM" = "07" ]; then
MONTH=$YY"04"
elif [ "$MM" = "08" ]; then
MONTH=$YY"05"
elif [ "$MM" = "09" ]; then
MONTH=$YY"06"
elif [ "$MM" = "10" ]; then
MONTH=$YY"07"
elif [ "$MM" = "11" ]; then
MONTH=$YY"08"
elif [ "$MM" = "12" ]; then
MONTH=$YY"09"
fi

echo "nOK!" >>./log/backup.log 1>&1

#*************************
# 獲取並檢查備份物件
#*************************

echo "nnn" >>./log/backup.log 1>&1
echo "Checking to back up the objectn===================================" >>./log/backup.log 1>&1

#判斷表空間並從資料庫中獲取待備份物件列表
if [ "$SPACE_NAME" = "TBS_DW_01" ]; then
sed "s/Month1/$MONTH/g" ./sql/dw01 > ./sql/dw01.sql
sqlplus $USERPASS @./sql/dw01.sql > /dev/null
DIR="/bkda2/expdump/exp_log/DW/"

elif [ "$SPACE_NAME" = "TBS_DW_02" ]; then
sed "s/Month1/$MONTH/g" ./sql/dw02 > ./sql/dw02.sql
sqlplus $USERPASS @./sql/dw02.sql /dev/null
DIR="/bkda2/expdump/exp_log/DW/"

elif [ "$SPACE_NAME" = "TBS_DW_FT_01" ]; then
sed "s/Month1/$MONTH/g" ./sql/ft01 > ./sql/ft01.sql
sqlplus $USERPASS @./sql/ft01.sql /dev/null
DIR="/bkda2/expdump/exp_log/DW/"

elif [ "$SPACE_NAME" = "TBS_DW_FT_02" ]; then
sed "s/Month1/$MONTH/g" ./sql/ft02 > ./sql/ft02.sql
sqlplus $USERPASS @./sql/ft02.sql > /dev/null
DIR="/bkda2/expdump/exp_log/DW/"

elif [ "$SPACE_NAME" = "TBS_DW_12580" ]; then
sed "s/Month1/$MONTH/g" ./sql/12580 > ./sql/12580.sql
sqlplus $USERPASS @./sql/12580.sql > /dev/null
DIR="/bkda2/expdump/exp_log/DW/"

elif [ "$SPACE_NAME" = "TBS_DW_BASS" ]; then
sed "s/Month1/$MONTH/g" ./sql/bass > ./sql/bass.sql
sqlplus $USERPASS @./sql/bass.sql > /dev/null
DIR="/bkda2/expdump/exp_log/DW/"

elif [ "$SPACE_NAME" = "TBS_DW_NEWS" ]; then
sed "s/Month1/$MONTH/g" ./sql/news > ./sql/news.sql
sqlplus $USERPASS @./sql/news.sql > /dev/null
DIR="/bkda2/expdump/exp_log/DW/"

elif [ "$SPACE_NAME" = "TBS_MK" ]; then
sed "s/Month1/$MONTH/g" ./sql/mk > ./sql/mk.sql
sqlplus $USERPASS @./sql/mk.sql >/dev/null
DIR="/bkda2/expdump/exp_log/MK/"

elif [ "$SPACE_NAME" = "TBS_KR" ]; then
sed "s/Month1/$MONTH/g" ./sql/kr > ./sql/kr.sql
sqlplus $USERPASS @./sql/kr.sql > /dev/null
DIR="/bkda2/expdump/exp_log/KR/"

else
echo "n**Error**! Invalid tablespacename" >>./log/backup.log 1>&1
echo "Available Namelist:" >>./log/backup.log 1>&1
echo "TBS_DW_01 TBS_DW_02 TBS_DW_FT_01 TBS_DW_FT_02 TBS_DW_12580nTBS_DW_BASS TBS_DW_NEWS TBS_MK TBS_KR" >>./log/backup.log 1>&1
ps -ef | grep "backup.sh" | grep -v grep | awk '{print $2}' | xargs -i kill -9 {}
fi

#清除物件列表中的空格,刪除過程檔案
sed 's/ //g' ./object.lst > ./objects
rm ./object.lst

#判斷是否存在需要備份的物件
CATOBJ=`cat ./objects`
if [ "$CATOBJ" = "" ]; then
echo "n**Error!** No object can be backed up" >>./log/backup.log 1>&1
rm ./objects
ps -ef | grep "backup.sh" | grep -v grep | awk '{print $2}' | xargs -i kill -9 {}
fi

echo "nOK!" >>./log/backup.log 1>&1

#*************************
# 開始生成控制檔案
#*************************

echo "nnn" >>./log/backup.log 1>&1
echo "Being create control file....n===================================" >>./log/backup.log 1>&1

#建立控制檔案上部,刪除過程檔案
sed -n '1,1p' objects|sed 's/^V/tables=V/' > ./objects.tmp #修改首行後插入臨時檔案
sed '1d' objects|sed '$d' >> ./objects.tmp #剔除首行和末行並追加到臨時檔案
sed -n '$,$p' objects|sed 's/,$//' >> ./objects.tmp #修改末行後追加到臨時檔案
perl -pe "$/='';s/n//g" objects.tmp > ./$SPACE_NAME"_PART_"$MONTH".ctl" #刪除換行符並插入到控制檔案
sed 's/^tables=//g' objects.tmp | sed 's/^/alter table /g' | sed 's/:/ drop partition /g' | sed 's/,$//g'| sed 's/$/;/g' > $DDIR"/drop_sql/drop_sql.tmp" #生成歷史資料刪除語句
rm ./objects
rm ./objects.tmp

#建立控制檔案下部
echo "ndirectory=exp_dir" >> $SPACE_NAME"_PART_"$MONTH".ctl"
echo "dumpfile="$SPACE_NAME"_PART_"$MONTH".dmp" >> $SPACE_NAME"_PART_"$MONTH".ctl"
echo "job_name="$SPACE_NAME"_PART_"$MONTH >> $SPACE_NAME"_PART_"$MONTH".ctl"
echo "logfile="$SPACE_NAME"_PART_"$MONTH".log" >> $SPACE_NAME"_PART_"$MONTH".ctl"
echo "compression=DATA_ONLY" >> $SPACE_NAME"_PART_"$MONTH".ctl"

echo "nOK!" >>./log/backup.log 1>&1

#***************************
# 開始使用資料泵匯出資料
#***************************

echo "nnn" >>./log/backup.log 1>&1
echo "Being export $SPACE_NAME....n===================================" >> ./log/backup.log 1>&1
expdp $USERPASS parfile=./$SPACE_NAME"_PART_"$MONTH".ctl"

#判斷是否匯出成功
if grep 'successfully completed' /bkda2/expdump/bkdump/$SPACE_NAME"_PART_"$MONTH".log" > /dev/null 2>&1;then
echo "nExpdump_job successfully completed" >>./log/backup.log 1>&1
echo "n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ EXPDP LOG ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~n" >> ./log/backup.log
cat /bkda2/expdump/bkdump/$SPACE_NAME"_PART_"$MONTH".log" >> ./log/backup.log
echo "n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" >> ./log/backup.log
else
echo "n**Error!** Expdump_job failed!" >>./log/backup.log 1>&1
echo "n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ EXPDP LOG ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~n" >> ./log/backup.log
cat /bkda2/expdump/bkdump/$SPACE_NAME"_PART_"$MONTH".log" >> ./log/backup.log
echo "n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" >> ./log/backup.log
ps -ef | grep "backup.sh" | grep -v grep | awk '{print $2}' | xargs -i kill -9 {}
fi

#移動日誌檔案和控制檔案至備份目錄/bkda2/expdump/exp_log/
mv /bkda2/expdump/bkdump/$SPACE_NAME"_PART_"$MONTH".log" $DIR
mv ./$SPACE_NAME"_PART_"$MONTH".ctl" $DIR
echo "nOK!" >>./log/backup.log 1>&1

#************************
# 開始執行歸檔至帶庫
#************************

echo "nnn" >>./log/backup.log 1>&1
echo "NBU Archive Backup beginning....n===================================" >>./log/backup.log 1>&1
echo "nBegin backup "$SPACE_NAME"_PART_"$MONTH".dmp" >>./log/backup.log 1>&1

DUMP_FILE="/bkda2/expdump/bkdump/"$SPACE_NAME"_PART_"$MONTH".dmp"
/usr/openv/netbackup/bin/bparchive $DUMP_FILE

#迴圈判斷是否完成歸檔
until [ ! -f $DUMP_FILE ]
do
sleep 3
done
echo "nOK!" >>./log/backup.log 1>&1
echo "nBackup secsesful" >>./log/backup.log 1>&1
echo "quit" >> $DDIR"/drop_sql/drop_sql.tmp"
mv $DDIR"/drop_sql/drop_sql.tmp" $DDIR"/drop_sql/"$SPACE_NAME"_"$MONTH".sql"
mv "./log/backup.log" "./log/"$SPACE_NAME"_PART_"$MONTH".log"
exit