Overview

This document provides shell scripts that can be used to perform backups using the Tibero tbexport utility and to set up Online Backup (Hot Backup), along with instructions on how to use them.

Method

1. tbexport (full)

1-1. Creating the Shell Script

$ 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. Script Usage Guide

Description: Performs a tbexport full backup at the configured path, retaining backup copies according to the keepcycle setting

1. Modify the script content
$ vi export.sh
  export_dest=<full path of the directory where export backup will be stored>
  username=sys
  password=<password of sys user>
  sid=<database sid>
  port=<database listener port>
  keepcycle=<number of backup copies to retain>

2. Verify script execution
$ chmod +x export.sh
$ sh export.sh

3. After confirming normal operation, register in crontab
Example) Set to run daily at 22:00 in root crontab
00 22 * * * su - tibero -c /home/tibero/scripts/export.sh

2. Hot Backup (Online Backup)

2-1. Creating the Shell Script

#!/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. Script Usage Guide

When running mkhot.sh, a script (hot.sh) is created in the configured path. Running this script performs a hot backup and retains backup copies according to the keepcycle setting.

1. Modify the script content
$ vi mkhot.sh
  backup_dest=<full path of the directory where hot backup will be stored>
  password=<password of sys user>
  keepcycle=<number of backup copies to retain>

2. Execute the script
$ sh mkhot.sh

3. Verify the created script
$ ls -alrt <backup_dest set in step 1>

4. Verify script execution
$ chmod +x hot.sh
$ sh hot.sh

5. After confirming normal operation, register in crontab
Example) Set to run every Monday at 20:00 in root crontab
00 20 * * 1 su - tibero -c /home/tibero/scripts/hot_backup/hot.sh