Jephe Wu - http://linuxtechres.blogspot.com
Environment: Linux server and IBM db2 database, backup database on one server, then restore it to another server
Objective: backup and restore db2 database in both online and offline mode
Part I - online backup and restore
1. online backup script by cronjob
[db2inst1@db1 ~]$ more /db2log/db2inst1/scripts/monthly_backup.sh
#!/bin/sh
export PATH=/sbin:/usr/sbin:/bin:/usr/bin
. /db2/db2inst1/sqllib/db2profile
db2 "BACKUP DATABASE DB1 ONLINE TO \"/db2log/db2inst1/DB1/backups\" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 INCLUDE LOGS WITHOUT PROMPTING" > /tmp/db2monthlybackup.log
sync;sync;sync;sleep 10
echo "running ls -lart /db2log/db2inst1/DB1/backups/*.001" >> /tmp/db2monthlybackup.log
ls -lart /db2log/db2inst1/DB1/backups/*.001 >> /tmp/db2monthlybackup.log
mutt -s "Monthly db2 online backup with logs for DB1 database" jephe1@domain.com jephe2@domain.com < /tmp/db2monthlybackup.log
2. restore online full database backup file with logs from another server
assuming we are going to restore from db1 to db1dr
a. restore to /db2/db2inst1 directory
db2 "restore database db1 from /data to /db2/db2inst1 into db1dr with 2 buffers buffer 1024 parallelism 1 without prompting"
b. restore log files to specified logtarget folder from backup image itself
nice -n 19 "restore db DB1 logs from /db2/db2inst1/backup into DB1DR logtarget /db2/db2inst1/db/DB1/logs"
c. restore to the end of the logs under /db2/db2inst1/db/DB1/logs and complete
db2 "rollforward database db1dr to end of logs and complete overflow log path (\"/data/db2log/DB1/logs\")"
d. restore to the end of the logs under /db2/db2inst1/db/DB1/logs but don't complete
db2 "rollforward database db1dr to end of logs overflow log path (\"/data/db2log/DB1/logs\")"
note: if the the number of files is big, you can use the following command to check how many files left waiting for process:
watch "ls -lut | grep -v 'May 29'| wc -l"
if the today' date is May 29.
e. if you need to restore to the specified date
you need to copy all the necessary log files to the /data/db2log/DB1/logs, then run the following command:
db2 "rollforward database db1dr to 2008-09-25-11.00.00.000000 using local time and complete overflow log path (\"/data/db2log/DB1/logs\")"
f. query database last transaction log date and pending status
db2 "rollforward database db1dr query status"
g. other commands such as
db2 rollforward database dbname complete
h. backup db2 configuration information
db2 get dbm cfg
db2 get db cfg for db1
Part II - offline backup and restore
1. backup (stop db2 server first)
db2 backup database db1 to .
note: offline backup database db1 to the current folder
If it's online backup, use 'db2 backup database db1 online to .'
2. restore
db2 "drop database db1"
db2 "restore database db1 from /home/db2inst1/db1_offline_backup/"
note: you need to put the backup file to /home/db2inst1/db1_offline_backup first
Part III - configuration after restoration
1. login as root, to create schema user, e.g. user1useradd user1;passwd user1
2. assign permission to user1
db2 connect to db1dr
db2 "grant createtab,connect,implicit_schema on database to user user1"
3. test connection for user1
db2 connect to db1dr user user1
Posts
