개요

본 문서는 Tibero tbexport 유틸리티를 사용한 백업과, Online Backup(Hot Backup)을 설정하기 위해 사용할 수 있는 Shell 스크립트를 제공하며, 사용하는 방법에 대한 기술입니다.

방법

1. tbexport(full)

1-1. Shell 스크립트 생성

$ vi export.sh

#!/bin/bash

#####################################################
EXPORT_DEST=/home/tibero/scripts/export
USERNAME=sys
PASSWORD=tibero
SID=tibero
PORT=8629
KEEPCYCLE=7
#####################################################

BDIR=$EXPORT_DEST
KCC=$KEEPCYCLE

if [ ! -d ${BDIR} ];
then
  mkdir -p ${BDIR}
fi

let "CNT=1"
let "TMPI=0"
while [ ${CNT} -le ${KCC} ]
do
  if [ ! -d ${BDIR}/${CNT} ];
  then
    mkdir ${BDIR}/${CNT}
  fi
  let "CNT=${CNT}+1"
done

#export
EXP_DATE=`date +%Y%m%d`
DMP_FILE=$BDIR/1/full_$EXP_DATE.dmp
LOG_FILE=$BDIR/1/full_$EXP_DATE.log

let "CNT=${KCC}"
rm $BDIR/$KCC/*.*
while  [ ! ${CNT} -eq 1 ]
do
  let "TMPI=${CNT}-1"
  mv ${BDIR}/${TMPI}/* ${BDIR}/${CNT}/
  let "CNT=${CNT}-1"
done

cd $BDIR/1
tbexport username=$USERNAME password=$PASSWORD sid=$SID file=$DMP_FILE log=$LOG_FILE port=$PORT full=y script=y

1-2. 스크립트 사용 가이드

설명 : 설정 경로에 tbexport full backup 수행, keepcycle 설정 값 만큼의 백업본 유지

1. 스크립트 내용 수정
$ vi export.sh
  export_dest=<export backup 받은 디렉토리 풀 경로>
  username=sys
  password=<sys 유저의 password>
  sid=<db의 sid>
  port=<db의 listner port>
  keepcycle=<백업본 유지할 개수>

2. 스크립트 동작 여부 확인
$ chmod +x export.sh
$ sh export.sh

3. 정상 동작 확인 후 crontab에 등록
ex) root crontab에 매일 22시에 수행하도록 설정
00 22 * * * su - tibero -c /home/tibero/scripts/export.sh

2. Hot Backup(Online Backup)

2-1. Shell 스크립트 생성

#!/usr/bin/sh
#####################################################

BACKUP_DEST=/home/tibero/scripts/hot_backup
PASSWORD=tibero
KEEPCYCLE=3

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

BDIR=$BACKUP_DEST
PASS=$PASSWORD
KCC=$KEEPCYCLE

if [ ! -d ${BDIR} ];
then
  mkdir -p ${BDIR}
fi

if [ ! -d ${BDIR}/SQL ];
then
  mkdir ${BDIR}/SQL
fi

if [ ! -d ${BDIR}/LOG ];
then
  mkdir ${BDIR}/LOG
fi

let "KCC=${KEEPCYCLE}"
let "TMPI=0"

case ${KCC} in
  0) let "KCC=1"
	;;
esac

let "CNT=1"

while [ ${CNT} -le ${KCC} ]
do
  if [ ! -d ${BDIR}/${CNT} ];
  then
    mkdir ${BDIR}/${CNT}
  fi
  let "CNT=${CNT}+1"
done

if [ -d ${BDIR}/SQL ];
then
  echo "SET HEADING OFF" >> ${BDIR}/SQL/start.sql
  echo "SET FEEDBACK OFF" >> ${BDIR}/SQL/start.sql
  echo "SET PAGES 50000" >> ${BDIR}/SQL/start.sql
  echo "SET LINE 120" >> ${BDIR}/SQL/start.sql
  echo "SET SERVEROUTPUT ON" >> ${BDIR}/SQL/start.sql
  echo "SPOOL ${BDIR}/SQL/run.sql" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "DECLARE" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "  V_TNM	SYS.V\$TABLESPACE.NAME%TYPE;" >> ${BDIR}/SQL/start.sql
  echo "  V_TID	SYS.V\$TABLESPACE.TS#%TYPE;" >> ${BDIR}/SQL/start.sql
  echo "  V_FNM	SYS.V\$DATAFILE.NAME%TYPE;" >> ${BDIR}/SQL/start.sql
  echo "  V_SEQ	SYS.V\$LOG.SEQUENCE#%TYPE;" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "  CURSOR LS_TBS IS" >> ${BDIR}/SQL/start.sql
  echo "    SELECT TS#, NAME FROM SYS.V\$TABLESPACE WHERE TS# NOT IN (SELECT TS# FROM SYS.V\$TEMPFILE);" >> ${BDIR}/SQL/start.sql
  echo "  " >> ${BDIR}/SQL/start.sql
  echo "  CURSOR LS_FNM IS" >> ${BDIR}/SQL/start.sql
  echo "    SELECT NAME FROM SYS.V\$DATAFILE WHERE TS# = V_TID;" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "BEGIN" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "  SELECT SEQUENCE# INTO V_SEQ FROM SYS.V\$LOG WHERE STATUS = 'CURRENT';" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.ENABLE(50000);" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS'';');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM SWITCH LOGFILE;');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM CHECKPOINT;');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('SET HEADING OFF');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('SET FEEDBACK OFF');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('SPOOL ${BDIR}/LOG/'||TO_CHAR(SYSDATE,'YYYYMMDD')||'.log');" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "  FOR I IN LS_TBS" >> ${BDIR}/SQL/start.sql
  echo "  LOOP" >> ${BDIR}/SQL/start.sql
  echo "  " >> ${BDIR}/SQL/start.sql
  echo "    V_TNM := I.NAME;" >> ${BDIR}/SQL/start.sql
  echo "    V_TID := I.TS#;" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "    DBMS_OUTPUT.PUT_LINE('SELECT '''||V_TNM||' TABLESPACE BEGIN BACKUP: ''||SYSDATE FROM DUAL;');" >> ${BDIR}/SQL/start.sql
  echo "    DBMS_OUTPUT.PUT_LINE('ALTER TABLESPACE '||V_TNM||' BEGIN BACKUP;');" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "    FOR J IN LS_FNM" >> ${BDIR}/SQL/start.sql
  echo "    LOOP" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "      V_FNM := J.NAME;" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "      IF 'Y' = '${COMYN}' THEN" >> ${BDIR}/SQL/start.sql
  echo "        DBMS_OUTPUT.PUT_LINE('!cp '||V_FNM||' ${BDIR}/TMP/');" >> ${BDIR}/SQL/start.sql
  echo "	DBMS_OUTPUT.PUT_LINE('!compress ${BDIR}/TMP/*');" >> ${BDIR}/SQL/start.sql
  echo "	DBMS_OUTPUT.PUT_LINE('!mv ${BDIR}/TMP/*.Z ${BDIR}/1/');" >> ${BDIR}/SQL/start.sql
  echo "      ELSE" >> ${BDIR}/SQL/start.sql
  echo "        DBMS_OUTPUT.PUT_LINE('!cp '||V_FNM||' ${BDIR}/1/');" >> ${BDIR}/SQL/start.sql
  echo "      END IF;" >> ${BDIR}/SQL/start.sql
  echo "    " >> ${BDIR}/SQL/start.sql
  echo "    END LOOP;" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "    DBMS_OUTPUT.PUT_LINE('ALTER TABLESPACE '||V_TNM||' END BACKUP;');" >> ${BDIR}/SQL/start.sql
  echo "    DBMS_OUTPUT.PUT_LINE('SELECT '''||V_TNM||' TABLESPACE END BACKUP: ''||SYSDATE FROM DUAL;');" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "  END LOOP;" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('ALTER DATABASE BACKUP CONTROLFILE TO ''${BDIR}/1/con_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'.bak'';');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ''${BDIR}/1/cre_con_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'.sql'';');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM SWITCH LOGFILE;');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM CHECKPOINT;');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('SPOOL OFF');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('SPOOL ${BDIR}/SQL/cp_arch.sh');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('SET SERVEROUTPUT ON');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('DECLARE');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('  V_ARC SYS.V\$ARCHIVED_LOG.NAME%TYPE;');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('  CURSOR LS_ARC IS SELECT NAME FROM SYS.V\$ARCHIVED_LOG WHERE SEQUENCE# > '||V_SEQ||';');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('BEGIN');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('  DBMS_OUTPUT.ENABLE(50000);');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('  FOR I IN LS_ARC');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('  LOOP');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('    V_ARC := I.NAME;');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('    DBMS_OUTPUT.PUT_LINE(''cp ''||V_ARC||'' ${BDIR}/1/'');');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('  END LOOP;');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('END;');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('/');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('SPOOL OFF');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('!chmod +x ${BDIR}/SQL/cp_arch.sh');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('!${BDIR}/SQL/cp_arch.sh');" >> ${BDIR}/SQL/start.sql
  echo "  DBMS_OUTPUT.PUT_LINE('QUIT');" >> ${BDIR}/SQL/start.sql
  echo "" >> ${BDIR}/SQL/start.sql
  echo "END;" >> ${BDIR}/SQL/start.sql
  echo "/" >> ${BDIR}/SQL/start.sql
  echo "SPOOL OFF" >> ${BDIR}/SQL/start.sql
  echo "QUIT" >> ${BDIR}/SQL/start.sql
fi

echo "#!/usr/bin/sh" > ${BDIR}/hot.sh
echo "" >> ${BDIR}/hot.sh
echo "rm -f ${BDIR}/${KCC}/*" >> ${BDIR}/hot.sh

let "CNT=${KCC}"
while  [ ! ${CNT} -eq 1 ]
do
  let "TMPI=${CNT}-1"
  echo "mv ${BDIR}/${TMPI}/* ${BDIR}/${CNT}/" >> ${BDIR}/hot.sh
  let "CNT=${CNT}-1"
done

echo "tbsql sys/${PASS} @${BDIR}/SQL/start.sql" >> ${BDIR}/hot.sh
echo "tbsql sys/${PASS} @${BDIR}/SQL/run.sql" >> ${BDIR}/hot.sh
echo "" >> ${BDIR}/hot.sh
echo "rm -f ${BDIR}/SQL/run.sql" >> ${BDIR}/hot.sh
chmod +x ${BDIR}/hot.sh

2-2. 스크립트 사용 가이드

mkhot.sh 수행 시 설정 경로에 스크립트(hot.sh) 생성, 해당 스크립트 수행시 hot backup 수행, keepcycle 설정 값만큼 백업본 유지

1. 스크립트 내용 수정
$ vi mkhot.sh
  backup_dest=<hot backup 받은 디렉토리 풀 경로>
  password=<sys 유저의 password>
  keepcycle=<백업본 유지할 개수>

2. 스크립트 수행
$ sh mkhot.sh

3. 생성된 스크립트 확인
$ ls -alrt <1번에서 설정한 backup_dest>

4. 스크립트 동작 여부 확인
$ chmod +x hot.sh
$ sh hot.sh

5. 정상 동작 확인 후 crontab에 등록
ex) root crontab에 매주 월요일 20시에 수행하도록 설정
00 20 * * 1 su - tibero -c /home/tibero/scripts/hot_backup/hot.sh