## 归档模式
db2 get db cfg fortest|grep -i logarchmeth1
First log archive method (LOGARCHMETH1)= OFF # 设置归档的方式,可以指定DISK、TSM等参数。
-- 启用归档模式
db2 update db cfg fortest using logarchmeth1 disk:/db2backup/archlog
-- 启用跟踪
db2 update db cfg fortest using trackmod on
-- 重启数据库
db2stop force
db2start
## 离线备份命令
db2 backup db test to /dev/null
-- 将备份模式从“循环日志”修改为“归档日志”时,需要做离线完全备份,否则连接时会报 backup-pending错误。
db2 backup db test to /db2backup/backup
## 在线备份命令
db2 backup db test online [to <path>]
-- 在线全量备份
db2 backup db test online to /db2backup/backup compress
-- 迭代增量备份
db2 backup db test online incremental delta to /db2backup/backup compress
-- 累积增量备份
db2 backup db test online incremental to /db2backup/backup compress
-- 表空间备份
db2 backup db test tablespace(tbs_name) online to /data1/dbbak
-- 备份介质检查
db2ckbkp -h backup_file_name
-- 查看备份进度
db2 list utilities show detail
## 一周备份计划
-- 周日 离线完全备份
db2 backup db test online to /db2backup/backup compress
-- 周一 迭代增量
db2 backup db test online incremental delta to /db2backup/backup compress
-- 周二 迭代增量
db2 backup db test online incremental delta to /db2backup/backup compress
-- 周三 增量
db2 backup db test online incremental to /db2backup/backup compress
-- 周四 迭代增量
db2 backup db test online incremental delta to /db2backup/backup compress
-- 周五 迭代增量
db2 backup db test online incremental delta to /db2backup/backup compress
-- 周六 增量
db2 backup db test online incremental to /db2backup/backup compress
## 版本恢复
-- 1.数据库恢复
db2 restore db test from /data1 taken at 20110118032318
-- 2.表空间恢复
db2 "restore db test tablespace(tbs_name) online from /data1 taken at 20110118032318"
-- 3.增量备份恢复
automatic 选项自动按顺序进行恢复
db2 restore db test incremental automatic taken at 20161204223439
db2ckrst 根据恢复历史文件产生恢复顺序
db2ckrst -d test -t 20110118032318 -r database
-- 4.恢复日志
-- 备份带日志
db2 backup db test online include logs
-- 恢复
db2 restore db sample from /data1 taken at 20161204223439 logtarget /data1/logs
-- 前滚(如果使用include logs 进行备份,前滚时,需要指定日志)
db2 "rollforward db test to end of logs and stop overflow log path (/data1/logs)"
-- 如果不想恢复数据库,仅恢复备份介质中的日志,需要先指定Logs选项,然后指定logtarget
db2 restore db test logs from /data1 logtarget /data1/logs
db2 restore db test logs from . logtarget /db2backup/backup/test
-- 5.恢复到另外一个数据库目录或存储路径
TO target-directory / dbpath on target-direcotyr / on path-list
db2 restore db test dbpath on /data1/dbdir
db2 resotre db test on /data1
-- 6.重定向恢复
db2 restore db test1 into test2 redirect
db2 list tablespace show detail
db2 list tablespace containers for 2 show detail
db2 "set tablespace containers for 2 using (
file '/db2fs3/db2inst1/NODE0000/TEST/T0000002/C0000000.LRG' 1000,
file '/db2fs1/db2inst1/NODE0000/TEST/T0000002/C0000001.LRG' 1000,
file '/db2fs2/db2inst1/NODE0000/TEST/T0000002/C0000002.LRG' 1000)"
-- 生成自动脚本
db2 restore db test taken at 20161214203816 redirect generate script redirect.ddl
db2 restore db test taken at 20161214203816 into test2 redirect generate script redirect.ddl
-- 版本恢复后,需要进行前滚恢复,否则会出现如下错误:
db2 connect to test
SQL1117N 因为处于 ROLL-FORWARD PENDING 状态,所以不能连接或激活数据库
"TEST"。 SQLSTATE=57019
-- 前滚恢复 到备份时间点
db2 rollforward db test to end of backup and stop
-- 前滚恢复 到日志截止时间点
db2 rollforward db test to end of logs and stop
-- 前滚到某个时间点
db2 rollforward db test to 2016-12-07-00.00.00.000000
db2 rollforward db test to 2016-12-07-00.00.00.000000 using local time## 示例:在同一服务器上,将数据库 test 恢复为数据库 test2
-- 备份数据库 test
db2 backup db test online compress
ls
TEST.0.db2inst1.DBPART000.20161230094642.001
-- 恢复备份介质中的日志
db2 restore db test logs from . logtarget /db2backup/backup/test
du -h
16K ./test/NODE0000/LOGSTREAM0000
20K ./test/NODE0000
24K ./test
123M .ls -l ./test/NODE0000/LOGSTREAM0000
total 12
-rw-------. 1 db2inst1 db2grp 12288 Dec 30 14:08 S0000032.LOG
-- 如果使用 restore ... into ... redirect 恢复数据库,必须使用 restore ... continue 正式恢复
-- 恢复数据库 test2
db2 restore db test taken at 20161230094642 into test2 redirect
--通过 restore ... continue 开始正式恢复
db2 restore db testcontinue
-- or 如果不使用 redirect ,则不需要使用 continue
db2 restore db test taken at 20161230094642 into test2
-- 前滚数据库 test2
db2 "rollforward db test2 to end of logs and stop overflow log path (/db2backup/backup/test)"
连接验证
db2 connect to test2
db2 list tablespaces
## 删除表恢复
0、模拟数据
-- 创建测试表
create table t(id int
, name varchar(50)
, last_modify timestamp
);
-- 批量写入数据
begin atomic
declareid int default 100;while(id>0)do
insert into t(id,name,last_modify) values (id, '测试数据'|| cast(id as varchar(10)), current timestamp);set id=id-1;
end while;
end@
drop table t;
--1、查看删除表历史
$ db2 list history dropped table all fortest
List History File fortest
Number of matching file entries = 1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
D T 20161215130819 000000000000cf0a00020004
----------------------------------------------------------------------------
"DB2INST1"."T" resides in 1 tablespace(s):
00001 USERSPACE1
----------------------------------------------------------------------------
Comment: DROP TABLE
Start Time: 20161215130819
End Time: 20161215130819
Status: A
----------------------------------------------------------------------------
EID: 64
DDL: CREATE TABLE "DB2INST1"."T"("ID" INTEGER , "NAME" VARCHAR(50) , "LAST_MODIFY" TIMESTAMP ) IN "USERSPACE1" ORGANIZE BY ROW;
--2、恢复表空间
db2 "restore db test tablespace(userspace1) online taken at 20161215134534"
--3、前滚恢复时指定要恢复的表的backupid(对应list dropped table 中的backup id,每个backup id 对应一张删除表)
db2 force applications all
db2 "rollforward db test to end of logs tablespace(userspace1) recover dropped table 000000000000320d00020004 to /db2backup/backup/test"
--4、重建表结构
通过步骤1,获取表结构
--5、通过表空间前滚,可以获取删除表的数据,通过load/import 加载即可:
db2 "load from data of del insert into t copy yes to /db2backup/backup"
--小结
经测试,此删除表恢复方法,对于该表删除后发生的其它表操作,如新建表,增删改数据等不影响。
## 查看备份记录
db2 list backup all fortest
-- 查看归档日志历史
db2 list history archive log all fortest
-- 查看活动日志
db2 get db cfg fortest|grep -i "First active log file"
First active log file= S0000014.LOG
--------------------------------------------------------------------------------
## 删除历史归档日志
启用归档模式后,需要监控归档日志的每日增量情况,防止磁盘空间不足等问题。
理论上,全备后,归档日志可以删除,但是如果全备为自动模式执行,需要确定备份文件是否备份成功,然后在删除归档日志。
-- 归档日志删除方式1:
1、制定清除脚本删除归档日志;
2、可以通过数据库配置参数: rec_his_retentn 和 auto_del_rec_obj来制定保留周期。
3、使用 prune history *** and delete 删除归档日志与备份
-- 操作步骤
1、查看系统默认配置如下:
db2 get db cfg |grep -i REC_HIS_RETENTN
Recovery history retention (days)(REC_HIS_RETENTN)= 366
db2 get db cfg |grep -i AUTO_DEL_REC_OBJ
Auto deletion of recovery objects (AUTO_DEL_REC_OBJ)= OFF
2、修订系统配置参数(需要重启数据库)
db2 update db cfg using REC_HIS_RETENTN 7
db2 update db cfg using AUTO_DEL_REC_OBJ on
db2stop
db2start
3、删除日志
db2 prune history 20161212152034 and delete
--归档日志删除方式2:
find$path -mtime +7 -name "S*.LOG"|xargsrm -f
使用此方法可以用来删除备份等文件
find$path -mtime +7 -name "*.tgz"|xargsrm -f
--db2 日志监控
$ db2 get snapshot for db on test|grep -i log |more
Catalog database partition number = 0
Catalog network node name = inspurk1a
--------------------------------------------------------------------------------
-- 数据写入测试
drop table t
create table t(id int, name varchar(500), last_modify timestamp)
-- 批量写入数据
begin atomic \
declareid int default 400000; \
while(id>0)do \
insert into t(id,name,last_modify) values (id, '测试数据123456789测试数据123456789测试数据123456789'|| cast(id as varchar(10)), current timestamp); \
set id=id-1; \
end while; \
end
select count(1) from t