#!/bin/sh
#应用Oracle用户的环境变量
. /oracle/.profile
#指定程序安装路径
PDIR="/bkda2/expdump/bin/backup/month"
#删除历史数据程序路径
DDIR="/bkda2/expdump/bin/drop_his"
#********************
# 程序初始化
#********************
#当前年
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7490392/viewspace-1039632/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7490392/viewspace-1039632/