目的:实现自动针对指定的ORACLE模式用户,导出该模式下的所有表,每个表形成一个导出文件(.dmp)和对应的日志文件(.log)
脚本名:vmdbexportcron.ksh
说明:该脚本直接放入CRONTAB中,我们在工作中是将导出的备份存放在一个NFS目录中,所以在脚本开始部分有相关NFS目录是否正常的判断。
#! /bin/sh
NFS_MOUNTDIR='/home/vmdb'
/bin/logger "+++ VMDB DATABASE export and copy start."
# ++
nfscount=`mount | grep $NFS_MOUNTDIR | wc -l`
if (( nfscount == 0 ))
then
/bin/logger "+++ $NFS_MOUNTDIR directory no mount !"
/bin/logger "+++ remount $NFS_MOUNTDIR directory. "
/etc/init.d/nfs start
fi
#+++++++++++++++++++++++++++++++++
# /home/vmdb is NFS_MOUNTDIR !!
# Be care !!!!
#+++++++++++++++++++++++++++++++++
/bin/logger "+++ delete direcotry $NFS_MOUNTDIR backuped files start."
/bin/rm -rf /home/vmdb/*
/bin/logger "+++ delete direcotry $NFS_MOUNTDIR backuped files end."
# ++
su - oracle -c "/backup/vmdb/db/vmdbexport.ksh <Oracle连接名> <用户名> <用户密码>"
/bin/logger "+++ VMDB DATABASE export and copy finished ."
脚本名:vmdbexport.ksh
说明:该脚本执行真正的导出动作,构造导出脚本,然后执行它。一些 需要过滤的 不必要表,可以添加到脚本的相应行。
#!/bin/ksh
# ++
if [ $# -ne 3 ]
then
echo "+=========================================================================================="
echo "| Usage: $0 ORACLE_NETSERVICENAME ORACLE_USERNAME ORACLE_PASSWORD "
echo "+=========================================================================================="
exit 1
fi
# ++
THISDAY=$(date +%Y%m%d)
ORACLE_HOME='/oracle/product/9ir2'
ORACLE_NETSERVICENAME=$1
ORACLE_USERNAME=$2
ORACLE_PASSWORD=$3
BASEDIR="/backup/vmdb/db"
LOG_FILE=${BASEDIR}/vmdbdbexport.log
TEMPLATE_FILE=${BASEDIR}/template
NFS_MOUNTDIR="/home/vmdb"
THISDIR=${BASEDIR}/${ORACLE_NETSERVICENAME}.${ORACLE_USERNAME}.${THISDAY}
# !! Require !!
THISDIR_RELATE=${ORACLE_NETSERVICENAME}.${ORACLE_USERNAME}.${THISDAY}
THISDAY_EXECUTER=${THISDIR}/export.$ORACLE_NETSERVICENAME.${ORACLE_USERNAME}.sh
THISDAY_TABLIST_FILE=${THISDIR}/tablist.$ORACLE_NETSERVICENAME.${ORACLE_USERNAME}
if [ ! -f "$LOG_FILE" ]
then
touch $LOGFILE
fi
echo "++" >> ${LOG_FILE}
echo "info:" >> ${LOG_FILE}
echo "info:" >> ${LOG_FILE}
echo "info: start time = $(date +'%b %e %T') . " >> ${LOG_FILE}
echo "info: database $ORACLE_NETSERVICENAME ." >> ${LOG_FILE}
echo "info: username $ORACLE_USERNAME ." >> ${LOG_FILE}
echo "info: create directory ${THISDIR} start" >> ${LOG_FILE}
if [ -d "$THISDIR" ]
then
echo "error: $THISDIR aleary exist." >> ${LOG_FILE}
echo "error: $0 abort." >> ${LOG_FILE}
exit 1
else
mkdir $THISDIR
fi
echo "info: create directory ${THISDIR} end." >> ${LOG_FILE}
echo "info: create $THISDAY_TABLIST_FILE start." >> ${LOG_FILE}
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
connect ${ORACLE_USERNAME}/${ORACLE_PASSWORD}@${ORACLE_NETSERVICENAME};
set termout off;
spool $THISDAY_TABLIST_FILE
select '${ORACLE_USERNAME}.'||table_name from user_tables;
spool off;
exit
EOF
if [ $? -eq 0 ]
then
echo "info: create $THISDAY_TABLIST_FILE ok." >> ${LOG_FILE}
else
echo "error: create $THISDAY_TABLIST_FILE wrong, $0 is terminate ." >> ${LOG_FILE}
exit 1
fi
count=0
echo "info: create exp execute shell start." >> ${LOG_FILE}
touch $THISDAY_EXECUTER
echo "#!/bin/ksh" >> $THISDAY_EXECUTER
echo >> $THISDAY_EXECUTER
# Filter: RCNT_ORIGINAL_XXX manage_taxi.GPS_LOG perfstat.STATS
luxsh="sed -e 's/ //g' -e '/RCNT_ORIGINAL/d' -e '/manage_taxi.GPS_LOG$/d' -e '/perfstat.STATS/d' -e '/selected\.$/d' -e '/^---.*--$/d' -e '/TABLE_NAME/d' -e '/^---*--/d' -e '/SQL>/d' -e '/^$/d' ${THISDAY_TABLIST_FILE}"
eval $luxsh | while read table_name
do
sed -e "s/%%1/${table_name}/g" -e "s/%%2/${THISDIR_RELATE}/g" -e "s/%%3/${ORACLE_USERNAME}/g" -e "s/%%4/${ORACLE_PASSWORD}/g" -e "s/%%5/${ORACLE_NETSERVICENAME}/g" $TEMPLATE_FILE > ${THISDIR}/${table_name}.par
echo "$ORACLE_HOME/bin/exp parfile=${THISDIR}/${table_name}.par" >> $THISDAY_EXECUTER
done
echo "sudo /bin/cp -a $THISDIR $NFS_MOUNTDIR" >> $THISDAY_EXECUTER
echo "info: create exp execute shell end." >> ${LOG_FILE}
chmod ug+r+x $THISDAY_EXECUTER
echo "info: execute $THISDAY_EXECUTER start." >> ${LOG_FILE}
$THISDAY_EXECUTER
echo "info: execute $THISDAY_EXECUTER finished." >> ${LOG_FILE}
echo "info: end time = $(date +'%b %e %T') . " >> ${LOG_FILE}
echo "++" >> ${LOG_FILE}
exit 0
文件名:template
说明: vmdbexport.ksh脚本调用
log=/backup/vmdb/db/%%2/%%1.log
file=/backup/vmdb/db/%%2/%%1.dmp
userid=%%3/%%4@%%5
buffer=4096
tables=(%%1)
rows=y
compress=n
脚本名:vmdbexportcron.ksh
说明:该脚本直接放入CRONTAB中,我们在工作中是将导出的备份存放在一个NFS目录中,所以在脚本开始部分有相关NFS目录是否正常的判断。
#! /bin/sh
NFS_MOUNTDIR='/home/vmdb'
/bin/logger "+++ VMDB DATABASE export and copy start."
# ++
nfscount=`mount | grep $NFS_MOUNTDIR | wc -l`
if (( nfscount == 0 ))
then
/bin/logger "+++ $NFS_MOUNTDIR directory no mount !"
/bin/logger "+++ remount $NFS_MOUNTDIR directory. "
/etc/init.d/nfs start
fi
#+++++++++++++++++++++++++++++++++
# /home/vmdb is NFS_MOUNTDIR !!
# Be care !!!!
#+++++++++++++++++++++++++++++++++
/bin/logger "+++ delete direcotry $NFS_MOUNTDIR backuped files start."
/bin/rm -rf /home/vmdb/*
/bin/logger "+++ delete direcotry $NFS_MOUNTDIR backuped files end."
# ++
su - oracle -c "/backup/vmdb/db/vmdbexport.ksh <Oracle连接名> <用户名> <用户密码>"
/bin/logger "+++ VMDB DATABASE export and copy finished ."
脚本名:vmdbexport.ksh
说明:该脚本执行真正的导出动作,构造导出脚本,然后执行它。一些 需要过滤的 不必要表,可以添加到脚本的相应行。
#!/bin/ksh
# ++
if [ $# -ne 3 ]
then
echo "+=========================================================================================="
echo "| Usage: $0 ORACLE_NETSERVICENAME ORACLE_USERNAME ORACLE_PASSWORD "
echo "+=========================================================================================="
exit 1
fi
# ++
THISDAY=$(date +%Y%m%d)
ORACLE_HOME='/oracle/product/9ir2'
ORACLE_NETSERVICENAME=$1
ORACLE_USERNAME=$2
ORACLE_PASSWORD=$3
BASEDIR="/backup/vmdb/db"
LOG_FILE=${BASEDIR}/vmdbdbexport.log
TEMPLATE_FILE=${BASEDIR}/template
NFS_MOUNTDIR="/home/vmdb"
THISDIR=${BASEDIR}/${ORACLE_NETSERVICENAME}.${ORACLE_USERNAME}.${THISDAY}
# !! Require !!
THISDIR_RELATE=${ORACLE_NETSERVICENAME}.${ORACLE_USERNAME}.${THISDAY}
THISDAY_EXECUTER=${THISDIR}/export.$ORACLE_NETSERVICENAME.${ORACLE_USERNAME}.sh
THISDAY_TABLIST_FILE=${THISDIR}/tablist.$ORACLE_NETSERVICENAME.${ORACLE_USERNAME}
if [ ! -f "$LOG_FILE" ]
then
touch $LOGFILE
fi
echo "++" >> ${LOG_FILE}
echo "info:" >> ${LOG_FILE}
echo "info:" >> ${LOG_FILE}
echo "info: start time = $(date +'%b %e %T') . " >> ${LOG_FILE}
echo "info: database $ORACLE_NETSERVICENAME ." >> ${LOG_FILE}
echo "info: username $ORACLE_USERNAME ." >> ${LOG_FILE}
echo "info: create directory ${THISDIR} start" >> ${LOG_FILE}
if [ -d "$THISDIR" ]
then
echo "error: $THISDIR aleary exist." >> ${LOG_FILE}
echo "error: $0 abort." >> ${LOG_FILE}
exit 1
else
mkdir $THISDIR
fi
echo "info: create directory ${THISDIR} end." >> ${LOG_FILE}
echo "info: create $THISDAY_TABLIST_FILE start." >> ${LOG_FILE}
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
connect ${ORACLE_USERNAME}/${ORACLE_PASSWORD}@${ORACLE_NETSERVICENAME};
set termout off;
spool $THISDAY_TABLIST_FILE
select '${ORACLE_USERNAME}.'||table_name from user_tables;
spool off;
exit
EOF
if [ $? -eq 0 ]
then
echo "info: create $THISDAY_TABLIST_FILE ok." >> ${LOG_FILE}
else
echo "error: create $THISDAY_TABLIST_FILE wrong, $0 is terminate ." >> ${LOG_FILE}
exit 1
fi
count=0
echo "info: create exp execute shell start." >> ${LOG_FILE}
touch $THISDAY_EXECUTER
echo "#!/bin/ksh" >> $THISDAY_EXECUTER
echo >> $THISDAY_EXECUTER
# Filter: RCNT_ORIGINAL_XXX manage_taxi.GPS_LOG perfstat.STATS
luxsh="sed -e 's/ //g' -e '/RCNT_ORIGINAL/d' -e '/manage_taxi.GPS_LOG$/d' -e '/perfstat.STATS/d' -e '/selected\.$/d' -e '/^---.*--$/d' -e '/TABLE_NAME/d' -e '/^---*--/d' -e '/SQL>/d' -e '/^$/d' ${THISDAY_TABLIST_FILE}"
eval $luxsh | while read table_name
do
sed -e "s/%%1/${table_name}/g" -e "s/%%2/${THISDIR_RELATE}/g" -e "s/%%3/${ORACLE_USERNAME}/g" -e "s/%%4/${ORACLE_PASSWORD}/g" -e "s/%%5/${ORACLE_NETSERVICENAME}/g" $TEMPLATE_FILE > ${THISDIR}/${table_name}.par
echo "$ORACLE_HOME/bin/exp parfile=${THISDIR}/${table_name}.par" >> $THISDAY_EXECUTER
done
echo "sudo /bin/cp -a $THISDIR $NFS_MOUNTDIR" >> $THISDAY_EXECUTER
echo "info: create exp execute shell end." >> ${LOG_FILE}
chmod ug+r+x $THISDAY_EXECUTER
echo "info: execute $THISDAY_EXECUTER start." >> ${LOG_FILE}
$THISDAY_EXECUTER
echo "info: execute $THISDAY_EXECUTER finished." >> ${LOG_FILE}
echo "info: end time = $(date +'%b %e %T') . " >> ${LOG_FILE}
echo "++" >> ${LOG_FILE}
exit 0
文件名:template
说明: vmdbexport.ksh脚本调用
log=/backup/vmdb/db/%%2/%%1.log
file=/backup/vmdb/db/%%2/%%1.dmp
userid=%%3/%%4@%%5
buffer=4096
tables=(%%1)
rows=y
compress=n