从今天开始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 ```