Sauvegarde et restauration de base sous DB2

From Tuxunix
Jump to: navigation, search

Vérifier que les Backups se sont bien déroulé

#> db2 list history backup all for nomBase

Ou

#> db2 "select start_time,end_time,comment,sqlcode from sysibmadm.db_history"

Script auto backup/restore online sur la même machine

Pré-requis online

  • Mettre en logretain votre base
#> db2 update db cfg for nombase using logarchmeth1 LOGRETAIN
  • Effectuer un backup offline
#> db2 backup db nombase

Script

Exemple


  • No comment
[root BACKUP]# ./backupLocalDb2.sh
No arguments !
Please, Usage: ./backupLocalDb2.sh --help
  • help
[root BACKUP]# ./backupLocalDb2.sh --help

-------------------------------------------
mar d�c  1 16:42:09 CET 2009

Usage: backupLocalDb2.sh [-bvrh] [date_time file restore]
-b --backup : Backup start for nomBDD
-r --restore [Date_Time_File] : Datetime of Backup File for
 database nomBDD (ex: 20091027133022)
--purge : delete all old backup (higher 1 day)
-v --version : Version of backupLocalDb2.sh
-h --help : Usage run
  • Lancer un backup
[rootBACKUP]# ./backupLocalDb2.sh -b

-------------------------------------------
mar d�c  1 19:28:32 CET 2009
 
Espace disponible 255G [done].
Start backup local...
Backup successful. The timestamp for this backup 
image is : 20091201192838
Backup [done].
  • purger les anciens bakcups
[root BACKUP]# ./backupLocalDb2.sh --purge

-------------------------------------------
mar d�c  1 19:29:24 CET 2009

Suppression old backup [/db2data/backup/]
/db2data/backup/BDD.0.db2inst1.NODE0000.CATN0000.20091110133720.001
/db2data/backup/test
/db2data/backup/BDD.0.db2inst1.NODE0000.CATN0000.20091110133917.001
/db2data/backup/BDD.0.db2inst1.NODE0000.CATN0000.20091110151555.001
/db2data/backup/BDD.0.db2inst1.NODE0000.CATN0000.20091110171510.001
Suppression old backup Ok

  • Faire une restoration
[root BACKUP]# ./backupLocalDb2.sh -r 20091201192838

-------------------------------------------
mar d�c  1 19:30:01 CET 2009

Start restore backup local with file...
SQL2540W  Restore is successful, however a warning "2539" was encountered
during Database Restore while processing in No Interrupt mode.
Restore [done].

Rollforward Execute...
Rollforward Status
Input database alias                   = nomBdd
Number of nodes have returned status   = 1
Node number                            = 0
Rollforward status                     = not pending
Next log file to be read               =
Log files processed                    = S0000004.LOG - S0000004.LOG
Last committed transaction             = 2009-12-01-18.28.41.000000 UTC
DB20000I  The ROLLFORWARD command completed successfully.
Rollforward Execute [done]


Code


  1.
     #!/bin/sh
  2.
      
  3.
     #
  4.
     #@Name nothing
  5.
     #@Fonction Backup local db2
  6.
     #@Version 0.1
  7.
     #@Authors pierre@tuxedo.fr
  8.
     #
  9.
      
 10.
     logFileBackup="/var/log/db2BackupLocal.log"
 11.
     pathBackup="/db2data/backup/"
 12.
      
 13.
     if [ ! -d ${pathBackup} ]; then
 14.
     echo "Error path backup !" |tee -a ${logFileBackup}
 15.
     exit 1;
 16.
     fi
 17.
      
 18.
     function initVars(){
 19.
     echo "-------------------------------------------"
 20.
     | tee -a ${logFileBackup}
 21.
     /bin/date | tee -a ${logFileBackup}
 22.
     echo "" | tee -a ${logFileBackup}
 23.
     spaceMini="60"
 24.
     mdpDb2inst="db2inst1"
 25.
     } # init
 26.
      
 27.
      
 28.
      
 29.
     function removeOldBck(){
 30.
     echo "Suppression old backup [${pathBackup}]"
 31.
     | tee -a ${logFileBackup}
 32.
     /usr/bin/find ${pathBackup} -type f -mtime
 33.
     +1 -exec rm -f {} \; -print | tee -a ${logFileBackup}
 34.
     [ "$?" != "0" ] && {
 35.
     echo "Suppression failed "
 36.
     | tee -a ${logFileBackup}
 37.
     echo "Exit. " | tee -a ${logFileBackup}
 38.
     exit 1;
 39.
     } || {
 40.
     echo "Suppression old backup Ok "
 41.
     | tee -a ${logFileBackup}
 42.
     }
 43.
     } # removeOldBck
 44.
      
 45.
     function bckpStart(){
 46.
     echo "Start backup local..." | tee -a ${logFileBackup}
 47.
     su - db2inst1 -c 'db2 "backup database nomBdd
 48.
     online to '${pathBackup}' compress"' | tee -a ${logFileBackup}
 49.
     [ "$?" == "0" ] && {
 50.
     echo "Backup [done]." | tee -a ${logFileBackup}
 51.
     } || {
 52.
     echo "Backup failed. " | tee -a ${logFileBackup}
 53.
     }
 54.
     } # bckpStart
 55.
      
 56.
     function restorStart(){
 57.
     echo "Start restore backup local with file..."
 58.
     | tee -a ${logFileBackup}
 59.
     su - db2inst1 -c 'db2 restore database nomBdd user db2inst1 using
 60.
     '${mdpDb2inst}' from '${pathBackup}' taken at '${dateTimeFileBackup}'
 61.
     WITHOUT PROMPTING' | tee -a ${logFileBackup}
 62.
     [ "$?" == "0" ] && {
 63.
     echo "Restore [done]." | tee -a ${logFileBackup}
 64.
     echo "Rollforward Execute..." |tee -a ${logFileBackup}
 65.
     su - db2inst1 -c 'db2 rollforward database nomBdd to end of logs and complete;' | tee -a ${logFileBackup}
 66.
     [ "$?" == "0" ] && {
 67.
     echo "Rollforward Execute [done]" |tee -a ${logFileBackup}
 68.
     } || {
 69.
     echo "Rollforward Execute [Ko]" |tee -a ${logFileBackup}
 70.
     } || {
 71.
     echo "Restore failed. " | tee -a ${logFileBackup}
 72.
     }
 73.
     } # bckpStart
 74.
      
 75.
     function help(){
 76.
      
 77.
     echo -e ""
 78.
     echo -e "Usage: $(basename $0) [-bvrh] [date_time file restore]"
 79.
     echo -e "-b --backup : Backup start for nomBdd"
 80.
     echo -e "-r --restore [Date_Time_File] : Datetime of Backup File for database nomBdd(ex: 20091027133022)"
 81.
     echo -e "--purge : delete all old backup (higher 1 day)"
 82.
     echo -e "-v --version : Version of $(basename $0)"
 83.
     echo -e "-h --help : Usage run"
 84.
     echo -e ""
 85.
      
 86.
     }
 87.
      
 88.
     function version(){
 89.
      
 90.
     echo -e "$(basename $0), version 0.1"
 91.
      
 92.
     } # version
 93.
      
 94.
     function checkSpaceFree(){
 95.
      
 96.
     freeSpace=$(/bin/df -hT ${pathBackup} | tail -1 | awk '{ print $5 }')
 97.
     Suffix_1=${freeSpace##+([a-zA-Z])}
 98.
      
 99.
     if [[ $Suffix_1 > $spaceMini ]] || ( $Suffix_1 == $spaceMini ) ; then
100.
     echo "Pas assez d' espace disponible pour le backup..." | tee -a ${logFileBackup}
101.
     echo "Exit." | tee -a ${logFileBackup}
102.
     exit 1;
103.
     else
104.
     echo "Espace disponible ${freeSpace} [done]." | tee -a ${logFileBackup}
105.
     fi
106.
      
107.
     } # checkSpaceFree
108.
      
109.
     #Main
110.
     #
111.
      
112.
     function main(){
113.
      
114.
     [ ${#} == 0 ] && {
115.
     echo "No arguments !"
116.
     echo "Please, Usage: ${0} --help"
117.
     exit 1;
118.
     }
119.
      
120.
     initVars
121.
      
122.
     while getopts ":bvrh-:" opt; do
123.
     case ${opt} in
124.
     b)
125.
     checkSpaceFree
126.
     bckpStart
127.
     ;;
128.
     r)
129.
     [ ! ${2} ] && {
130.
     echo "No date_time found !" | tee -a ${logFileBackup}
131.
     echo "Please, Usage: $0 --help" | tee -a ${logFileBackup}
132.
     echo "Exit." | tee -a ${logFileBackup}
133.
     exit 1
134.
     } && {
135.
     checkDateTime=$(echo "${2}" | grep "^[0-9]*$")
136.
     [ ! -z ${checkDateTime} ] && {
137.
     echo "Check date_time..." | tee -a ${logFileBackup}
138.
     } || {
139.
     echo "Erreur valeur date_Time" | tee -a ${logFileBackup}
140.
     exit 1;
141.
     }
142.
     } || {
143.
     dateTimeFileBackup="${2}"
144.
     restorStart
145.
     }
146.
     ;;
147.
     v)
148.
     version
149.
     ;;
150.
     h)
151.
     help
152.
     ;;
153.
     -) case $OPTARG in
154.
     backup)
155.
     checkSpaceFree
156.
     bckpStart
157.
     ;;
158.
     restore)
159.
     [ ! ${2} ] && {
160.
     echo "No date_time found !" | tee -a ${logFileBackup}
161.
     echo "Please, Usage: $0 --help" | tee -a ${logFileBackup}
162.
     echo "Exit." | tee -a ${logFileBackup}
163.
     exit 1
164.
     } && {
165.
     checkDateTime=$(echo "${2}" | grep "^[0-9]*$")
166.
     [ ! -z ${checkDateTime} ] && {
167.
     echo "Check date_time..." | tee -a ${logFileBackup}
168.
     } || {
169.
     echo "Erreur valeur date_Time" | tee -a ${logFileBackup}
170.
     exit 1;
171.
     }
172.
     } || {
173.
     dateTimeFileBackup="${2}"
174.
     restorStart
175.
     }
176.
     ;;
177.
     purge)
178.
     removeOldBck
179.
     ;;
180.
     help)
181.
     help
182.
     ;;
183.
     version)
184.
     version
185.
     ;;
186.
     *)
187.
     echo "Option illegale -$OPTARG" | tee -a ${logFileBackup}
188.
     return 1
189.
     ;;
190.
     esac ;;
191.
     *)
192.
     echo "Option illegale ${opt}" | tee -a ${logFileBackup}
193.
     return 1
194.
     ;;
195.
     esac
196.
     done
197.
      
198.
     } # main
199.
      
200.
     main $@

By TuXedo

Supervision du process de backup

Process en cours

#> db2 list utilities show detail

Backup terminé

Lorsque vous passer par un outil externe pour backuper une base DB2, il est parfois nécessaire de vérifier la validité de celui-ci :

#!/bin/sh

#
#Check last backup DB2
#

if [ -f /home/db2inst1/sqllib/db2profile ]; then
   . /home/db2inst1/sqllib/db2profile
fi

fileLog=/home/db2inst1/checkBckpDb2.log

db2 "connect to nomBdd user xxxx using xxxx"
[ "$?" == "0" ] && {
               tpsBckp=$(db2 -x "select time(char(timestamp(start_time))) - time(char(timestamp(end_time))) from sysibmadm.db_history where start_time like '$(date +%Y%m%d)%' and operation = 'B' and sqlcode is null")
               if [ ! -z "${tpsBckp}" ] && [ "${tpsBckp:2:`expr length $tpsBckp` - 2}" > "1000" ]; then
                       echo "Backup DB2 of date : $(date +%Y%m%d) Ok [done]." > $fileLog
               else
                       echo "Backup DB2 of date : $(date +%Y%m%d) Error..." > $fileLog
               fi
               } || {
                       echo "Connexion DB2 Error..." $fileLog
               }

db2 terminate
  • Plugins Nagios qui interpréte le log du script précédent est lance un backup local en cas d'erreur :
#!/bin/sh

SERVICE_NAME='SERVICE_DB2_BACKUP'
CHECK_NAME=`cat /home/db2inst1/checkBckpDb2.log`
manualBckp=""
 
if /bin/echo -e "$CHECK_NAME\n" | grep -q Ok
then
      STATUS=0
fi

if /bin/echo -e "$CHECK_NAME\n" | grep -q WARNING
then
      STATUS=1
fi

if /bin/echo -e "$CHECK_NAME\n" | grep -q Error
then
      CHECK_NAME="WARNING DB2 Auto manual backup launch... (please verify)"
      echo "$CHECK_NAME" > /home/db2inst1/checkBckpDb2.log
      manualBckp="true"
      STATUS=1
fi

if /bin/echo -e "$CHECK_NAME\n" | grep -q UNKNOWN
then
      STATUS=-1
fi

#/bin/echo -e "$1\t$SERVICE_NAME\t$STATUS\t$CHECK_NAME\n"
/bin/echo -e "$1\t$SERVICE_NAME\t$STATUS\t$CHECK_NAME\n" | /usr/local/nagios/nsca/send_nsca 172.16.1.4 -c /usr/local/nagios/nsca/send_nsca.cfg

[ ! -z "${manualBckp}" ] && {
               ./backupLocalDb2.sh -b
               } || {
               echo "No manual backup necessary..."
               }
  • backupLocalDb2.sh : Fait référence a la section "Script auto backup/restore"

backup/restore online sur une autre machine

Pré-requis online

  • Mettre en logretain votre base
#> db2 update db cfg for nombase using logarchmeth1 LOGRETAIN
  • Effectuer un backup offline
#> db2 backup db nombase


  • Sur machine1 :
#> db2 "backup database '${nomBdd}' online to '${pathBackup}' INCLUDE LOGS without prompting"
  • Sur machine2 :
#> db2 restore database '${nomBdd}' user db2inst1 using '${mdpDb2inst}' from '${pathBackup}' taken at '${dateTimeFileBackup}' logtarget '${pathBackup}/LOGPATHRECOVERY' WITHOUT PROMPTING
#> db2 "rollforward db '${nomBdd}' to end of logs and stop overflow log path ('${pathBackup}/LOGPATHRECOVERY')"

Script auto backup/restore online sur une autre machine

File:BackupLocalDb2.sh

Optimisation Backups/Restore

#> db2 update dbm cfg using BACKBUFSZ 4096
#> db2 update dbm cfg using RESTBUFSZ 4096


Dump de table lobs (export)

Format IXF

#!/bin/ksh
exec 2>&1
LOG=/home/db2inst1/dumpDB2.log

[ ! -e "$LOG" ] && touch $LOG

if [ -f /home/db2inst1/sqllib/db2profile ]; then
    . /home/db2inst1/sqllib/db2profile
fi

test_message_warning() {
  if [ "$?" != "0" ]
  then
     echo "!!! Message de Warning : voir LOG DB2 !!! " | tee -a $LOG
  fi
}
echo "----------------------------------------------------" | tee -a $LOG

echo 'Date de debut dump DB2 : '`date` | tee -a $LOG

echo "Connexion a DB2" | tee -a $LOG
db2 "connect to XXXX user xxx using xXxXxX" >> $LOG

echo "Export TABLEUSR" >> $LOG
db2 export to '/db2data/backup/XXXX.ixf' of ixf lobs to '/db2data/backup/' lobfile 'XXXX_LOB' 'select * from xxx.XXXXX'
test_message_warning

echo "Export TABLECAN" >> $LOG
db2 export to '/db2data/backup/XXXX.ixf' of ixf lobs to '/db2data/backup/' lobfile 'XXXX_LOB' 'select * from xxx.XXXX'
test_message_warning

echo "Fin dump TABLEUSR TABLECAN" >> $LOG

echo "Deconnexion" >> $LOG
db2 terminate >> $LOG

echo 'Date de Fin dump : '`date` >> $LOG

Options Export

  • coldel: définie le caractère utilisé pour le delimiteur de la colonne.
  • lobs to: Définie le répertoire ou sera stocké le ou les fichiers lob.
  • lobfile: Définie le nom du fichier lob.

Erreur rencontré avec export IXF

  • SQL3132W The character data in column "DATA" will be truncated to size "32700"

Il faut alors ajouter la gestion des lobs en export : lobs to '/db2data/backup/' lobfile 'PIWCAN_LOB'

Dump de schema par table (export del)

Script automatique qui dump l'ensemble des tables sur un schéma précis

#!/bin/sh

#
#@Name          backupSchemaByTables.sh
#@Fonction      Backup local db2 schema RCL
#@Version       0.2
#@Authors       pterrier@ecritel.net
#

nomSchema="NOMSCHEMA"

echo "--- $(date) ---"
db2 "connect to nomBdd user xxxxx using xxxxxx"

listTable=$(db2 -x "select TABNAME from syscat.tables where TABSCHEMA = '$nomSchema' ORDER BY TABNAME ASC")

for i in ${listTable}; do

echo "Table name backup $i in progress..."
db2 "export to ${nomSchema}.${i}.del of del select * from ${nomSchema}.${i}"
[ "$?" != "0" ] && echo Dump error on $i

done

db2 terminate


Import de schema par table (import del)

Script d'import automatique via des sources export del :

#!/bin/sh

#
#@Name          importReclamationsAllTables.sh
#@Fonction      import local db2 schema RCL
#@Version       0.1
#@Authors       pterrier@ecritel.net
#

nomSchema="SCHEMA"

echo "--- $(date) ---"
db2 "connect to nomBDD user xxxx using xxxxxx"

listTable=$(db2 -x "select TABNAME from syscat.tables where TABSCHEMA = '$nomSchema' ORDER BY TABNAME ASC")

for i in ${listTable}; do

echo "Table name backup $i in progress..."
db2 "import from ${nomSchema}.${i}.del of del replace into ${nomSchema}.${i}"
[ "$?" != "0" ] && echo Dump error on $i

done

db2 terminate


Dump de schema par table (export ixf)

#!/bin/sh

#
#@Name          backupAllTables.sh
#@Fonction      Backup local db2 by schema
#@Version       0.3
#@Authors       pierre@tuxedo.fr
#

function initVars(){

	#check user db2inst1 launch
	[ "$(id -u)" != "513" ] && { 
		echo "User not authorized!"
		exit 1
		}
	logFile="backupXXXXAllTables.log"
	date_jour=$(date +%Y%m%d_%H%M%S)
	mkdir -p ${date_jour}
	echo "--- $(date) ---" >> ${logFile}
	echo "--- BACKUP RCL ---" >> ${logFile}
	userDb2=""
	pwdDb2=""
	nomSchema=""

} #initVars

function exportSCH(){
	db2 "connect to eb02d user ${userDb2} using ${pwdDb2}" | tee -a ${logFile}

	listTable=$(db2 -x "select TABNAME from syscat.tables where TABSCHEMA = '$nomSchema' ORDER BY TABNAME ASC")

	for i in ${listTable}; do
		#Expect table if necessary
		[ "$i" == "XXXX" ] || [ "$i" == "XXXX" ] && continue
		echo "Table name backup $i in progress..." | tee -a ${logFile}
		db2 "export to ${date_jour}/${nomSchema}.${i}.ixf of ixf select * from ${nomSchema}.${i}" >> ${logFile} 2>&1
		[ "$?" != "0" ] && echo Dump error on $i | tee -a ${logFile}
	done

	db2 terminate | tee -a ${logFile}
} #exportSCH

function help(){

echo -e ""
echo -e "Usage: ${0##*/} [-uph] "
echo -e "-u [NOM_USER]: connexion user"
echo -e "-p ['PASSWORD']: password"
echo -e "-s [NOM_SCHEMA]: schema"
echo -e "-h | --help : Usage run"
echo -e ""

exit 0

} #help

#
#MAIN
#
[ ${#} == 0 ] && {
      echo "No arguments !"
      echo "Please, Usage: ${0##*/} -h"
      exit 1;
      }


initVars

while getopts ":p:u:s:h-:" opt; do
      case ${opt} in
              h|help)
              help
              ;;
              u)
              [ ! ${OPTARG} ] && {
                      echo "User not found !" | tee -a ${logFile}
                      echo "Please, Usage: ${0##*/} --help" | tee -a ${logFile}
                      exit 1
                      } || {
                      userDb2=${OPTARG}
                      }
              ;;
		p)
		[ ! ${OPTARG} ] && {
                     echo "Password not found !" | tee -a ${logFile}
                      echo "Please, Usage: ${0##*/} --help" | tee -a ${logFile}
                      exit 
                      } || {
                      pwdDb2=${OPTARG}
                      }
		;;
		s)
		[ ! ${OPTARG} ] && {
                     echo "Schema not found !" | tee -a ${logFile}
                      echo "Please, Usage: ${0##*/} --help" | tee -a ${logFile}
                      exit 
                      } || {
                      nomSchema=${OPTARG}
                      }
		;;
		-) case ${OPTARG} in
			help)
			help
			;;
			*)
                       echo "Option illegale -${OPTARG}"
                       exit 1
                       ;;
		esac ;;
		*)
		echo "Option illegale ${opt}"
		exit 1
		;;
      esac
done

#Call function if parameters
[ ! -z ${userDb2} ] && [ ! -z ${pwdDb2} ] && [ ! -z ${nomSchema} ] && {
	exportSCH
	} || {
	echo "Argument empty"
	exit 1
	}