Oracle Datapump export scripts Example

데이타베이스 작업을 하면서 제일 많이 사용하는 스크립트 중 하나이다.
Perl 버젼과 Bash 버젼이 둘다 있지만, Bash 버젼이 좀 더 편한다.
환경변수와 스키마명만 변경하면 언제든지 사용할 수 있다.

아래는 expdp.sh 내용이다.

#!/bin/sh
#
# starlits@adminschool.net
# Sun Jun 22 13:59:04 KST 2008
 
 
 
# env
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10g
export ORACLE_BIN=${ORACLE_HOME}/bin
export ORACLE_SID=SUVDB
export PATH=$PATH:$ORACLE_HOME/bin
# export NLS_LANG=KOREAN_KOREA.KO16MSWIN949
export NLS_LANG=KOREAN_KOREA.KO16KSC5601
export SHLIB_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/jlib:/usr/lib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/jdbc/lib
export CLASSPATH=$ORACLE_HOME/lib/classgen.jar:$ORACLE_HOME/jdbc/lib:
export EDITOR=vi
export TNSADMIN=$ORACLE_HOME/network/admin
 
# directory
DUMP_DIR=/home/oracle/backup/dpbackup
 
EXPDP_BIN=${ORACLE_BIN}/expdp
 
SYS_USER=system
SYS_PASS=oracle
 
DATELOG=`date '+%Y%m%d_%H%M%S'`
 
UPDIR="${DUMP_DIR}/${DATELOG}"
 
LIST="
sit
scott
"
 
 
if [ ! -x $EXPDP_BIN ] ; then
    echo "Don't Exist '$EXPDP_BIN'"
    echo "Please Check Directory or filename"
    exit -1
fi
 
HISTLOG=${DUMP_DIR}/history_${DATELOG}.log
 
for user in $LIST ; do
 
    echo "####################################################" | tee -a $HISTLOG
    echo "Oracle 10g Data Pump Schema = [$user]" | tee -a $HISTLOG
    date | tee -a $HISTLOG
    echo "####################################################" | tee -a $HISTLOG
 
    FILENAME="${user}_expdp_${DATELOG}.dmp"
    LOGFILE="${user}_expdp_${DATELOG}.log"
    JOB_NAME=exp_${user}_full
 
    CMD="$EXPDP_BIN ${SYS_USER}/${SYS_PASS} \
         directory=dp_test_dir dumpfile=${FILENAME} \
         schemas=${user} \
         logfile=${LOGFILE} \
         job_name=${JOB_NAME}
    "
 
    echo $CMD | tee -a $HISTLOG
    $CMD | tee -a $HISTLOG
done
 
 
if [ ! -d $UPDIR ] ; then
    echo "create directory '${UPDIR}'" | tee -a $HISTLOG
    mkdir $UPDIR
fi
 
echo "mv ${DUMP_DIR}/*${DATELOG}.* ${UPDIR}" | tee -a $HISTLOG
mv ${DUMP_DIR}/*${DATELOG}.* ${UPDIR}