Sauvegarde et restauration de base sous DB2
From Tuxunix
Contents |
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
Optimisation Backups/Restore
#> db2 update dbm cfg using BACKBUFSZ 4096 #> db2 update dbm cfg using RESTBUFSZ 4096
Dump de table (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'
