6.2 DB2备份还原相关操作

从今天开始DB2相关的内容

系统为 Redhat 7.4

数据库为 v10.5fp10

上节我们说了如何建立数据库，这节内容为设置常见的参数

DB2中一个实例下可以有多个数据库，一个数据库只能属于一个实例

所有命令在db2inst1用户下运行

1. 备份还原相关参数查看

在线日志位置

db2 get db cfg  |grep -i newlogpat
db2 update db cfg for testdb using NEWLOGPATH /newpath

日志镜像参数

db2 get db cfg  |grep -i mirror
db2 update db cfg for testdb using MIRRORLOGPATH /newpath

归档日志参数

OFF代表循环日志模式

db2 get db cfg for testdb |grep -i logarchmeth1

查看归档历史记录

db2 list history archive log all for testdb

查看当前第一个活动日志

db2 get db cfg for testdb |grep -i first

修改在线日志的大小和数量

db2 update db cfg for testdb using logsecond 20
db2 update db cfg for testdb using logprimary 20
db2 update db cfg for testdb using logfilsiz 20

2. 备份数据库

2.1 离线备份

db2 backup db testdb to '/db2data/backup'

2.2 在线备份

db2 backup db testdb online to '/db2data/backup' include logs

2.3 在线表空间备份

backup db testdb tablespace (TB1,TB2) online to  '/db2data/backup'
backup db testdb tablespace (TB1,TB2) online to  '/db2data/backup' include logs

2.4 增量备份

db2 get db cfg for testdb |grep -i trackmod

db2 update db cfg for testdb using trackmod ON

db2 backup db testdb online incremental  累积备份

db2 backup db testdb online incremental delta 迭代备份

2.5 检查备份是否正常

db2ckbkp -h xxx

2.6 监控备份状态

db2 list utilities show detail

3. 还原

3.1 还原前检查

查看bufferpool

查看源库和目标库bufferpool情况，若目标库内存不满足源库，需要设为初始

db2set DB2_OVERRIDE_BPF=1000

还原完毕后重新设置

 db2 alter bufferpool bp4k size 200000

db2set DB2_OVERRIDE_BPF=

db2stop force

db2start

关闭目标库连接

db2 list applications
db2 connect to ebank
db2 unquiesce db
db2 connect reset
db2 force application all
db2 terminate

如下一步中删除库失败则考虑重启实例

db2stop
db2start

3.2 删除库

drop database testdb

3.3 还原库

全备还原

db2 restore db sample from  /data1  taken at 2020023051525 logtarget /data1/logs

增量还原

db2 restore db sample incremental automatic taken at 2020023051525 logtarget /data1/logs 最后一次备份时间

还原表空间

db2 "restore db sample tablespace (TBS1) online  from   /data1  taken at 2020023051525

只还原日志

db2 restore db sample logs from  /data1 logtarget /data1/logs

重定向还原

db2 restore db sample from /backup taken at <timestamp> redirect generate script redirect.ddl


db2 -tvf redirect.ddl

停止恢复

db2 restore database testdb abort

4.前滚恢复

db2 "rollforward db testdb  to end of logs and stop overflow log path (/data1/logs)"

db2 rollforward db testdb  to 2011-01-20-0.34.18.890729 using local time  overflow log path (/data1/logs)"

db2 "rollforward db testdb to end of backup and stop"

5. 查看还原进度

db2 list utilities show detail

db2pd -utilities

6.2 DB2备份还原相关操作 backup-restore