利用exp自动逻辑导出脚本

目的：实现自动针对指定的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