oracle磁带库清洁带标签,备份表空间并上传磁带库的脚本

#!/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