[Shell] Oracle dmp 백업 및 데이터 삭제 후 임포트 스크립트

---

ORACLE 테이블 데이터 삭제 시 기록이 너무 많은 경우(delete문 대신 truncate를 써야 하는 경우)

기존 database의 테이블 별로 dmp로 보관 기간만큼의 데이터를 백업 후 테이블 데이터 모두 삭제 하여 임포트 하는 스크립트

---

#!/bin/bash

# SCRIPT CONFIGURATION

#############################################################################################

FULLBACKUP=Y # Y or N

CLEAR=Y # Y or N

BACKUP_MONTHS=12

#############################################################################################

FILE_DATE=`date '+%Y_%m_%d'`

TIME=`date +%H:%M:%S`

DBUSERNAME=dbusername

DBPASSWD=dbpassword

SCRIPTPATH=$( cd "$(dirname "$0")" ; pwd -P ) # script file absolute real path

# LOG FILE WRITE

#############################################################################################

FileName=${0##*/} # with file extension. ex) filename.sh

FileNameWithoutExtension=${FileName%.*} # without file extension. ex) filename

LOG_FILE_PATH=$SCRIPTPATH/log

LOG_FILE=${LOG_FILE_PATH}/${FileNameWithoutExtension}_${FILE_DATE}

DMP_FILE_PATH=$SCRIPTPATH/dmp

# MAKE LOG FOLDER

if [ ! -d $LOG_FILE_PATH ]

then

mkdir $LOG_FILE_PATH

fi

# MAKE DMP FOLDER 

if [ ! -d $DMP_FILE_PATH ]

then

mkdir $DMP_FILE_PATH

fi

#SET NLS_LANG : THIS IS SKIP.

#############################################################################################

#DB_LANG=`echo -e "set heading off; \n set feedback off; \

#\n select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';" | sqlplus -s appm/${DBPASSWD} | xargs`

#export NLS_LANG=American_America.$DB_LANG

##############################################################################################

function table_data_clear(){

local FILE=$1

local TABLE=$2

local QUERY=$3

echo "[DEBUG][${TIME}][${TABLE}] -----------------------------------------------------------" >> ${LOG_FILE}.log

# 1.export table data

exp ${DBUSERNAME}/${DBPASSWD} file=${FILE} tables=${TABLE} query=\"${QUERY}\" log=${LOG_FILE}.tmp

# 1-1. log file rewrite

cat ${LOG_FILE}.tmp >> ${LOG_FILE}.log

# 2.truncate table

echo -e "TRUNCATE TABLE ${TABLE};" | sqlplus -s ${DBUSERNAME}/${DBPASSWD} >> ${LOG_FILE}.log

# 3.import table data

imp ${DBUSERNAME}/${DBPASSWD} file=${FILE} ignore=y full=y log=${LOG_FILE}.tmp

cat ${LOG_FILE}.tmp >> ${LOG_FILE}.log

echo "[DEBUG][${TIME}][${TABLE}] -----------------------------------------------------------" >> ${LOG_FILE}.log

}

function table_data_fullbackup(){

local FILE=$1

        local TABLE=$2

echo "[DEBUG][${TIME}][${TABLE}] -----------------------------------------------------------" >> ${LOG_FILE}.log

exp ${DBUSERNAME}/${DBPASSWD} file=${FILE} tables=${TABLE} log=${LOG_FILE}.tmp

cat ${LOG_FILE}.tmp >> ${LOG_FILE}.log

echo "[DEBUG][${TIME}][${TABLE}] -----------------------------------------------------------" >> ${LOG_FILE}.log

}

###################################################################################################################

# TABLE FULL BACKUP 

if [ "${FULLBACKUP}" = "Y" ] 

then

echo "[DEBUG][${TIME}][FULL BACKUP START] -----------------------------------------------------------" >> ${LOG_FILE}.log

TABLES=('테이블1' '테이블2')

for i in "${TABLES[@]}"

do

table_data_fullbackup ${DMP_FILE_PATH}/${i}_${FILE_DATE}_full.dmp ${i}

done

echo "[DEBUG][${TIME}][FULL BACKUP END] -----------------------------------------------------------" >> ${LOG_FILE}.log

fi

###################################################################################################################

# TABLE CLEAR

if [ "${CLEAR}" = "Y" ]

then

echo "[DEBUG][${TIME}][CLEAR START] -----------------------------------------------------------" >> ${LOG_FILE}.log

TABLE=테이블명1

table_data_clear ${DMP_FILE_PATH}/${TABLE}_${FILE_DATE}.dmp ${TABLE} "WHERE 날짜컬럼 > ADD_MONTHS(SYSDATE, -${BACKUP_MONTHS})"

echo "[DEBUG][${TIME}][CLEAR END] -----------------------------------------------------------" >> ${LOG_FILE}.log

fi

###################################################################################################################

# delete tmp log file.

if [ -f "${LOG_FILE}.tmp" ]

then

        rm ${LOG_FILE}.tmp

fi

---