-----------------------------------------------------------------------------------------------------------------------------
db2 "backup db zzdb1 online to /home/db2inst1/backup compress include logs"


db2 "drop table t1"
db2 "create table t1 (id int , c2 varchar(200)) "


db2 "
begin
declare i int default 0;
while (i<105000) do
insert into t1 values(i, 'aaaaaaaddddddddddddaaaaaaaaaaaaaabbbbbbffffffffffffffffffbbbbbbbbbbbbbbbccfffffffffffffffffffccccccccccccccc'||char(i) );
commit;
set i=i+1;
end while;
end
"


--提取 恢复历史文件
db2 "restore db zzdb1 from /home/db2inst1/backup  taken at 20140829184600
on /home/db2inst2 
replace history file
"
db2 list backup all for zzdb1


db2 "restore db zzdb1 from /home/db2inst1/backup taken at 20140829184229
on /home/db2inst2  logtarget /home/db2inst2/log
redirect generate script /home/db2inst2/redirect.ddl
"


--前滚 redirect.ddl 这个文件需要修改
db2 -tvf  /home/db2inst2/redirect.ddl   184517 184619


--恢复日志
db2 "restore db testdb logs from /home/db2inst1/backup taken at 20140829184229
logtarget /home/db2inst2/log
"

--前滚到日志末尾
db2 "rollforward db zzdb1 to end of logs and stop overflow log path ('/home/db2inst2/log')"


或


--前滚到某一时间点
db2 "rollforward db zzdb1 to 2014-08-29.18.45.17 using local time and stop overflow log path ('/home/db2inst2/log')"
db2 "rollforward db zzdb1 stop overflow log path ('/home/db2inst2/log')" 


----------------------------------------------------------------------------------------------------------------------------

--优化备份、复原和恢复性能

增大 UTIL_HEAP_SZ 配置参数来分配更多内存，在最大程度上减少完成备份、恢复和复原操作所需的时间。


在备份恢复期间应监控 UTIL_HEAP_SZ内存的使用情况，
db2 get snapshot for database on mydb


除非显示的输入以下命令参数，否则DB2将自动设置：
1）WITH num_buffers BUFFERS
2）PARALLELISM n
3）BUFFER buffer-size
db2 get dbm cfg | find /i "BUFSZ"
备份缓冲区默认大小(4KB)   (BACKBUFSZ) = 1024
复原缓冲区默认大小(4KB)   (RESTBUFSZ) = 1024


对于多个表空间，备份和恢复缓冲区应设置成表空间扩展数据块的公倍数+1。
缓冲区数量应设置成备份目标的2倍。


--BLK_LOG_DSK_FUL
将 blk_log_dsk_ful 设置为 yes 会导致 DB2 数据库系统遇到“日志磁盘已满”错误时应用程序挂起，从而允许您解决该错误并允许事务完成。可通过增加文件系统容量或除去同一文件系统上大量文件来解决日志磁盘已满情况。


db2 "create table t1 (c1 varchar(20), c2 varchar(20))"
db2 "
begin
atomic declare i int default 0;
while (i<100000) do insert into t1 values('aaaaaaaaa' || char(i),'bbbbbb'||char(i) );
set i=i+1;
end while;
end
"

db2 get snapshot for database on testdb


db2 force applications all


db2 "force application (agent_id)"


db2 update db cfg for testdb using logarchmeth1 disk:/data/archivelog


db2 get db cfg for testdb | grep -i log


--不进行日志记录    +c 关闭自动提交
db2 +c "alter table t1 activate not logged initially"
db2 +c "delete from t1"
--提交后，表自动转回日志记录状态
db2 +c "commit"


--查看日志备份
db2 list history archive log all for testdb


--恢复日志
db2 "restore db testdb logs from /data/backup/testdb taken at 20131220191008 
logtarget /data/backup/log 
"


--从第三方备份软件库恢复日志 
db2 "restore db sample  logs  load /opt/simpana/Base64/libDb2Sbt.so taken at  20131220191008 
LOGTARGET /db2log  without prompting 
"


--查看备份
db2 list history backup all for testdb
db2 list history backup  since 20131213010101 for testdb
db2 "select timestamp(start_time) as starttime, end_time,char(firstlog,15) as firstlog ,char(lastlog,15) as lastlog ,seqnum, char(location,30) as location
from sysibmadm.db_history
where operation='B'  and sqlcode is null
"


db2 "select OPERATION,
OBJECTTYPE,
START_TIME,
SEQNUM,
char(FIRSTLOG,20) firstlog,
char(LASTLOG,20) lastlog,
char(LOCATION,50),
DEVICETYPE
from sysibmadm.db_history
"


--查看开始备份前的日志号
db2pd -logs -db testdb


--在线压缩全备份
db2 backup db testdb online to  /db2data2/backup1 compress  include logs 


--离线压缩全备份  默认offline 
db2 backup db testdb to /db2data2/backup1  compress


--强制重置BACKUP PENDING状态
db2dart testdb /CHST /WHAT DBBP OFF


--查看前滚状态
db2 rollforward db testdb query status


db2bm 表空间与缓冲池之间的传输进程
db2med 缓冲池与存储介质之间的传输进程


传输速度控制参数
UTIL_IMPACT_LIM    
UTIL_IMPACT_PRIORITY


--In order to use the incremental backup functionality, database has to be enabled for it.
This is done by turning the TRACKMOD database configuration parameter on.
When TRACKMOD is turned on, database keeps track of table spaces that have been modified.
When an incremental backup command is issued, it will skip the table spaces that have not been modified since last backup.


connect to ds2;
update db cfg using trackmod on deferred ;
connect reset;


增量备份 必须开启参数 TRACKMOD
1）incremental delta 差异增量，备份从上一次差异备份后的变化，备份量小。恢复时需要各个差异增量备份和全备份才可恢复。
2）incremental 累积增量，从上一次全备份后的变化，备份量大。只取一个增量备份和全备份即可恢复。


如果大部分表空间被频繁修改，则增量备份耗时与全备份几乎相同。


--表空间差异增量备份
db2 "backup database ds2 tablespace ( ordertbs, tbs_data2 ) online incremental delta
to  /home/db2inst1/backups exclude logs without prompting "


--表空间恢复
db2 "restore db testdb tablespace ( ordertbs ) online incremental automatic 
from /home/db2inst1/backups taken at    without prompting
"
db2 "rollforward db testdb  to end of logs and complete tablespace ( ordertbs ) online "


--检查恢复需要的备份文件，增量备份方式
"db2ckrst" utility is available to see what backup  images will be applied, prior to actually running the automatic restore. 
db2ckrst -d DS2 -t -r tablespace ordertbs


--备份文件的检查
--The check backup command (db2ckbkp) can be used to test the integrity of a backup image and to determine whether or not the image can be restored.
     db2ckbkp DS2.3.db2inst1.DBPART000..001
--It can also be used to display the metadata stored in the backup header.
     db2ckbkp  -h  DS2.3.db2inst1.DBPART000..001


--检查在TSM设备上的备份是否完整
db2adutl verify <选项> db <数据库名>


-----------------------------------------------------------------------------------------------------------------------------


--恢复到时间点注意事项
db2 "rollforward db testdb to 2013-12-21.20.05.00 using local time overflow log path ('/data/backup/testdb/bak')"
db2 "rollforward db testdb  stop overflow log path ('/data/backup/testdb/bak') "
SQL1275N： The stoptime passed to roll-forward must be greater than or equal to "备份结束的时间点"
SQL4970N：Roll-forward recovery on database cannot reach the specified stop point. 前滚只能到最后一个日记记录的时间。
SQL1266N：前滚只能往后不能往前，假设 t1 < t2 ,rollforward 到 t2 后，不能再 rollforward 到 t1 
出现SQL1266N后，只能 db2 rollforward db testdb stop


--在线备份下恢复到其他实例（表空间都是自动存储管理）
数据库目录和自动存储路径都存储在 on 指定的目录
在线备份，恢复时指定 logtarget 将介质中的文件抽取，前滚时指定 overflow log path 选项进行日志的前滚：
db2 restore db sample from /data taken at 20101215010203 on /home/db2recover logtarget /data2/log
db2 "rollforward db sample to end of logs overflow log path ('/data/log') "
db2 "rollforward db sample stop overflow log path ('/data2/log') "


--离线备份下恢复到其他实例（表空间不全是自动存储管理）  
db2 "restore db testdb taken at 20131020000447  on /data/i2_testdb
  redirect generate script /data/redirect.ddl
"
db2 -tvf  /data/redirect.ddl  需要修改脚本
db2 "rollforward db testdb to end of logs and stop "
db2 update db cfg for testdb using MIRRORLOGPATH default


--在线备份日志的情况下恢复（表空间不全是自动存储管理）
db2 "restore db testdb taken at 20131020000447  on /data/i2_testdb logtarget /data/i2_testdb/log
   redirect generate script /data/i2_testdb/redirect.ddl
"
db2 -tvf  /data/i2_testdb/redirect.ddl  需要修改脚本
db2 "rollforward db testdb to end of logs and stop overflow log path ('/data/i2_testdb/log') "
db2 "rollforward db testdb stop overflow log path ('/data/i2_testdb/log') "




--------------------------------------------------------------------------------------
--软检查点前回收的日志文件的百分比，发生灾难时所需要读取的日志数(softmax/100)
也决定了数据缓冲区向磁盘写入数据的频率
db2 get db cfg | grep -i softmax


--更改的页阈值，数据缓冲区中被改变数据页的百分比，当达到这个值时，会进行一次磁盘写入
db2 get db cfg | grep -i chngpgs_thresh


---------------------------------------------------------------------------------------------
--恢复删除的表
db2 "alter tablespace tbs_data2 dropped table recovery on "
db2 "create table t2 (id int , name char(20))"
db2 "insert into t2 values(10,'zhang')"
db2 backup db testdb online to /data/backup/testdb include logs
db2 drop table t2
db2 list history dropped table all for testdb
db2 "restore db testdb from /data/backup/testdb  tablespace(tbs_data2) taken at 20131023105651"
db2 "rollforward database testdb to end of logs tablespace(tbs_data2) recover dropped table
000000000500e1ec00070004 to /data/backup/test_d.txt
"


--表空间是否 "已drop表的恢复功能"
db2 "select char(tbspace,20) "Tablespace" , DROP_RECOVERY from syscat.tablespaces order by 1"




----------------------------------------------------
-- 使用全备份和增量备份恢复数据库
----------------------------------------------------
1）使用全备份介质恢复
db2 restore db testdb from /data/backup/testdb taken at 20131201202020
2）使用增量备份或Delta备份介质恢复
db2 restore db testdb incremental automatic from /data/backup/testdb taken at 20131203202020
automatic 无论多少个累积增量或delta增量，DB2会自动检索恢复顺序并自动恢复数据库
3）使用日志前滚
db2 rollforward db testdb to end of logs and complete


--增量恢复检查
db2ckrst 检测要恢复的增量备份镜像的时间戳
db2ckrst -d testdb -t 20140901135602


--删除20130101之前的所有备份和恢复历史记录
db2 prune history 20130101
    
db2 "prune logfile prior to S0000600.LOG"

db2 list utilities  show detail


--实例参数导出导入
db2cfexp  inst.out  backup
db2cfimp 导入


----------------------------------------------------------------------------------------------------
--如何确定前滚期间需要什么日志文件？
$ db2 rollforward db test to 2008-03-01 and stop
SQL1275N  The stoptime passed to roll-forward must be greater than or equal to
"2008-03-04-12.45.54.000000 UTC", because  database "TEST" on node(s) "0,1"
contains information later than the specified time.


$ db2 rollforward db testdb to 2008-03-04-12.45.54.000000 and stop
DB20000I  The ROLLFORWARD command completed successfully.


以上例子表明，在前滚期间，如果命令中指定的时间点（PIT）过早，那么会收到错误消息（SQL1275N）。该错误消息 告诉您正确的 PIT。
可以考虑使用 BACKUP 和 INCLUDE 日志。但是，在 DPF 数据库中，BACKUP 和 INCLUDE 日志会生成错误消息（SQL2032N）。
因此，不能使用该方法。


在 DB2 9.5 及以后版本，可以使用 “TO END OF BACKUP” 子句和 ROLLFORWARD 命令
将一个分区数据库中的所有分区前滚到最小恢复时间。
这个最小恢复时间是前滚期间数据库处于一致状态（数据库编目中列出的对象与磁盘上物理存在的对象相 匹配）
的最早时间点。


db2 rollforward db testdb to end of backup and stop


在恢复历史文件的输出中，最早和最晚日志非常重要。
最早日志：对于在线备份，该日志是在线备份期间开始写入的第一个日志。
最晚日志：对于在线备份，该日志是在线备份完成后写入的最后一个日志。


对于离线备份，最早和最晚日志是同一个。


如果最早和最晚日志之间的日志文件丢失，则这个在线备份是无效的。
因为在恢复时，必须至少前滚到备份结束的时间点，这个点就是前滚操作结束的最小恢复时间。

--截断（切换）日志，并将所有未归档的日志全部归档 
db2 archive log for db testdb