有兴趣的同学可以帮我测试一下脚本,有什么问题请回复,以便我继续改进!
脚本用途:在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 fiwhile 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