DB2增差量备份恢复详尽演练
DB2分增量备份(Incremental Backup)和Delta备份(Delta Backup)两种增量备份方式. 增量备份相当于Oracle中的差量备份,即做一次全备份后,其它每次增量,都备份自全量以来所有的变化. Delta备份相当于Oracle中的增量备份,每次增量只备份前一次备份以来的增量部份. 我下面附了一个详细的增量备份例子在下面. Delta备份与之类似, 如要测试,只需将备份命令中的"incremental"变为"incremental delta "即可. 至于Delta备份的手工单步恢复要参考db2ckrst命令的顺序. -- $ db2level DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09010" with level identifier "02010107". Informational tokens are "DB2 v9.1.0.0", "s060629", "AIX64", and Fix Pack "0". Product is installed at "/opt/db2_09_01". su - db2inst1 cd /home/db2inst1/ mkdir db2bk1 db2bk2 db2logs -- --启用用户出口 db2 update db cfg for xcldb using userexit on --启用归档日志 db2 update db cfg for xcldb using logretain on -- 启用增量备份功能 db2 update db cfg for xcldb using trackmod on --做一次脱机备份 db2 backup db xcldb to /home/db2inst1/db2bk1
如何创建测试库可以看: DB2手工创建库
增量备份测试计划 第一次备份 db2 connect to xcldb -- db2 "drop table t_x01" -- db2 "drop table t_x02" db2 "create table t_x01 like syscat.tables" db2 connect reset -- 全备份一次，以后的增量都基于此全备 20131216230354 db2 backup db xcldb online to /home/db2inst1/db2bk2 include logs 第二次备份 db2 connect to xcldb db2 "insert into t_x01 select * from syscat.tables" db2 "select count(*) from t_x01" db2 connect reset -- 备份包含日志，库备完后，记得手工把日志备份一下 20131216230432 db2 backup db xcldb online incremental to /home/db2inst1/db2bk2 include logs 第三次备份 --再次修改数据 db2 connect to xcldb db2 "create table t_x02 like syscat.tables" db2 "insert into t_x02 select * from syscat.tables" db2 "select count(*) from t_x02" db2 connect reset -- 备份包含日志 20131216230506 db2 backup db xcldb online incremental to /home/db2inst1/db2bk2 include logs 第四次备份 --把t_x01表删除 db2 connect to xcldb db2 "drop table t_x01" db2 connect reset -- 20131216230624 db2 backup db xcldb online incremental to /home/db2inst1/db2bk2 include logs -- --
查看备份文件 [db2inst1@O11g64 ~]$ ls -l /home/db2inst1/db2bk2/ total 134648 -rw-r-----. 1 db2inst1 db2iadm1 55795712 Dec 16 23:03 XCLDB.0.db2inst1.NODE0000.CATN0000.20131216230354.001 -rw-r-----. 1 db2inst1 db2iadm1 9863168 Dec 16 23:04 XCLDB.0.db2inst1.NODE0000.CATN0000.20131216230432.001 -rw-r-----. 1 db2inst1 db2iadm1 36110336 Dec 16 23:05 XCLDB.0.db2inst1.NODE0000.CATN0000.20131216230506.001 -rw-r-----. 1 db2inst1 db2iadm1 36110336 Dec 16 23:06 XCLDB.0.db2inst1.NODE0000.CATN0000.20131216230624.001 --查看备份历史 db2 list history backup all for xcldb --得到恢复顺序 db2ckrst -d xcldb -t 20131216230506 -r database -- -- --
删除库 db2 drop db xcldb -- --
恢复库: 恢复第三次备份时的数据库 恢复库方法一: db2 restore database xcldb incremental automatic from /home/db2inst1/db2bk2 taken at 20131216230506 logtarget /home/db2inst1/db2logs 恢复库方法二: ls /home/db2inst1/db2bk2 db2 "restore db xcldb incremental from /home/db2inst1/db2bk2 taken at 20131216230506 " db2 "restore db xcldb incremental from /home/db2inst1/db2bk2 taken at 20131216230354 " db2 "restore db xcldb incremental from /home/db2inst1/db2bk2 taken at 20131216230506 logtarget /home/db2inst1/db2logs " 检查恢复情况: --查看日志日否已恢复 ls /home/db2inst1/db2logs --查看数据库状态及所需日志 db2 rollforward db xcldb query status 前滚方法一: --得到日志目录 db2 get db cfg for xcldb -- Path to log files /home/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/ --得到指定目录 cp /home/db2inst1/db2logs/* /home/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/ ls /home/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/ --前滚 */ db2 "rollforward db xcldb to end of logs and complete" 前滚方法二: 不复制日志,直接指定目录后完成前滚 db2 "rollforward db xcldb to end of logs and complete overflow log path ('/home/db2inst1/db2logs')" -- 检查成果: 因为是恢复的第三次备份,所以两个表应当都有记录 db2 connect to xcldb db2 "select count(*) from t_x01" db2 "select count(*) from t_x02" db2 connect rest -- 至此备份恢复完成.