Oracle表或分区导出脚本
有兴趣的同学可以帮我测试一下脚本，有什么问题请回复，以便我继续改进！
脚本用途：在Oracle10g或以上版本中导出指定表或指定分区，并压缩导出文件。
脚本使用前提：
1. 数据库必须支持expdp，因为脚本调用的是expdp
2. 需要先在数据库里建立一个名为EXPDPDIR的目录，并把读写权限授予public或system
脚本使用说明：
1. 脚本可以直接执行，也适合放在cronjob中
2. expdp使用system用户导出，如果需要使用其它用户导出，需要修改脚本中的ORA_USER设置。system用户密码需要根据实际环境修改PASSWD。
3. Oracle的环境变量ORACLE_HOME,PATH和ORACLE_SID需要根据实际环境修改
4. DUMP_DIR参数需要与数据库目录EXPDPDIR的设置保持一致
#!/usr/bin/bash
######################################################################
## Environment. change as needed
######################################################################
export ORACLE_HOME="/app/oracle/product/10.2"
export PATH=${ORACLE_HOME}/bin:$PATH
export ORACLE_SID="ora10g"
######################################################################
## Parameters, change as needed
######################################################################
LOGDIR="/app/oracle/log"
## Expdp directory,the same directory as created in database
DUMP_DIR="/app/oracle/datapump"
ORA_DUMPDIR="EXPDPDIR"
## Expdp user
ORA_USER="SYSTEM"
## Password for expdp user
PASSWD="SYSTEM"
CURRDATE=$(/usr/bin/date '+%Y%m%d')
######################################################################
## Get commands path
######################################################################
EXPDP=${ORACLE_HOME}/bin/expdp
GZIP=/usr/bin/gzip
GREP=/usr/bin/grep
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
TOUCH=/usr/bin/touch
RM=/usr/bin/rm
######################################################################
## Funcations
######################################################################
######################################################################
## display Copyright information
######################################################################
printInfo() {
echo "Script to export table or partition."
echo "Copyright (C) 2011-2012 Yu jun / ERG-Beijing Ltd."
}
######################################################################
## display help
######################################################################
printHelp()
{
echo
echo "The purpose of the script is to export table or partion from a"
echo "give schema using datapump and compress the export file. The "
echo "script use \"system\" to export table, you need to change the"
echo "user's password as needed(The password is in the script)."
echo "Prerequisite: "
echo " 1. The version of the Oracle must be 10g or above."
echo " 2. You need create a directory named \"EXPDPDIR\" in database "
echo " and give read and write permission to system or public."
echo
echo "Usage: $(basename $0) -u <Owner> -t <Table> [-p <Partition>]"
echo
echo " -u Table owner."
echo " -t Table name."
echo " -p Patition name. If do not use -p options,the whole table will"
echo " be exported."
echo
echo "Example : $(basename $0) -u scott -t emp -p p1"
echo "This will export partion p1 from table scott.emp then"
echo "compress the export file."
echo
echo "Exit Values: "
echo " 96 Can not create lock file"
echo " 97 Another instance is running"
echo " 98 Bad user to run the script"
echo " 99 Bad options"
echo " 100 Export file exist"
echo " 101 Expdp fail"
echo " 102 Export file compress fail"
echo " 103 Exit with trap signal"
echo
}
######################################################################
## Checks command line options (pass $@ as parameter)
######################################################################
# Checks command line options (pass $@ as parameter).
checkOptions() {
if [ $# -eq 0 ]; then
printInfo
printHelp
exit 99
fi
while getopts u:t:p: OPT $@; do
case $OPT in
u) # table owner
OWNER=$OPTARG
;;
t) # table name
TAB_NAME=$OPTARG
;;
p) # partition name
PART_NAME=$OPTARG
;;
?) printInfo
printHelp
exit 99
;;
esac
done
if [ -z $OWNER ] || [ -z ${TAB_NAME} ]; then
printInfo
printHelp
exit 99
fi
if [ -z $PART_NAME ]; then
LOGFILE=$LOGDIR"/expdp_"$OWNER"_"${TAB_NAME}".log"
else
LOGFILE=$LOGDIR"/expdp_"$OWNER"_"${TAB_NAME}"_"${PART_NAME}".log"
fi
}
######################################################################
## lock the script against parallel run
######################################################################
lock() {
## lockfile name
if [ -z ${PART_NAME} ] ; then
LOCKF="/tmp/expdp_"${OWNER}"_"${TAB_NAME}".lck"
else
LOCKF="/tmp/expdp_"${OWNER}"_"${TAB_NAME}"_"${PART_NAME}".lck"
fi
## create lock file
if [ -f $LOCKF ]; then
echo "$(date) [Error] : Another instance of the script is running."
exit 97
fi
$TOUCH $LOCKF
if [ $? -ne 0 ]; then
echo "$(date) [Error] : Can not create lock file."
exit 96
fi
}
######################################################################
## Export data from database and compresss the dumpfile
######################################################################
expdp_comp_tab()
{
####################################################################
## Function:
## Export data from oracle and compress the dump file
##
## Prerequisite:
## Create a directory on database for datapump and grant read
## and write permission to public.
##
## Input Values:
## NULL
## Output: (two files)
## 1. Export file, located on DataPummp Directory with name
## owner_table_partition.dmp
## 2. Log file, located on DataPummp Directory with name
## owner_table_partition.log
####################################################################
## for expdp parameter "tables"
if [ -z ${PART_NAME} ] ; then
EXP_TAB_PART=${OWNER}"."${TAB_NAME}
else
EXP_TAB_PART=${OWNER}"."${TAB_NAME}":"${PART_NAME}
fi
## for expdp parameter "dumpfile"
if [ -z ${PART_NAME} ] ; then
DUMP_FILE_NAME=${OWNER}"_"${TAB_NAME}"_"$CURRDATE".dmp"
else
DUMP_FILE_NAME=${OWNER}"_"${TAB_NAME}"_"${PART_NAME}"_"$CURRDATE".dmp"
fi
## for expdp parameter "logfile"
if [ -z ${PART_NAME} ] ; then
DUMP_LOG_NAME=${OWNER}"_"${TAB_NAME}"_"$CURRDATE".log"
else
DUMP_LOG_NAME=${OWNER}"_"${TAB_NAME}"_"${PART_NAME}"_"$CURRDATE".log"
fi
## the compressed dumpfile name
COMP_DUMP_FILE=${DUMP_FILE_NAME}".gz"
## the full dumpfile name
FULL_DUMP_FILE=${DUMP_DIR}"/"${DUMP_FILE_NAME}
## the full logfile name
FULL_DUMP_LOG=${DUMP_DIR}"/"${DUMP_LOG_NAME}
## the full compressed dumpfile name
FULL_COMP_DUMP=${DUMP_DIR}"/"${COMP_DUMP_FILE}
if [ -f ${FULL_DUMP_FILE} ] ; then ## Dumpfile exists
echo "$(date) [Error] : Dumpfile ${FULL_DUMP_FILE} exist!" ## Give a warning
return 100
elif [ -f ${FULL_COMP_DUMP} ] ; then
echo "$(date) [Error] : Dumpfile ${FULL_COMP_DUMP} exist!" ## Give a warning
return 100
fi
## Export a given table or partition
$EXPDP ${ORA_USER}/${PASSWD} directory=${ORA_DUMPDIR} dumpfile=${DUMP_FILE_NAME} logfile=${DUMP_LOG_NAME} tables=${EXP_TAB_PART} > /dev/null 2>&1
## If expdp succeed or not, using datapump logfile for judgement
## not using exit code because of oracle bug
MESG=$($GREP "successfully completed" ${FULL_DUMP_LOG})
if [ $? -eq 0 ] ; then
echo "$(date) [Info] : Expdp successfully completed for table ${EXP_TAB_PART}!"
else
echo "$(date) [Error] : Expdp failed for table ${EXP_TAB_PART}!"
echo " Please check logfile ${FULL_DUMP_LOG}!"
return 101
fi
## Compress the dump file
ERROR=$($GZIP ${FULL_DUMP_FILE} 2>&1)
if [ $? -eq 0 ] ; then
echo "$(date) [Info] : Compress successfully completed for file ${FULL_DUMP_FILE}!"
else
echo "$(date) [Error] : Compress failed for file ${FULL_DUMP_FILE}!"
echo "Error Messages:"
echo "*** : "$ERROR
return 102
fi
}
######################################################################
## Main Program
######################################################################
## Check command line options
checkOptions $@
## Output to logfile, if want to send output to screen, comment out the following line
#exec 1>>$LOGFILE 2>&1
## Script start
echo ==== Script $0 started on $(date) ====
## The script need to be run as user "oracle"
CUSER=$(id |cut -d "(" -f2 | cut -d ")" -f1)
if [ "$CUSER" != "oracle" ] ; then
echo "$(date) [Error] : The script need to be run as user \"oracle\""
exit 98
fi
## lock the script against parallel run
lock
## trap signal
trap '$RM $LOCKF' 0
trap 'exit 103' 1 2 3 15
## Export table partition and compress the dump file
expdp_comp_tab
## Check the result
REXP=$?
if [ "$REXP" = "0" ]; then
LOGMSG="Script $0 ended successfully"
else
LOGMSG="Script $0 ended in error"
fi
echo ==== $LOGMSG on $(date) ====
echo
## exit
exit $REXP
