DB2备份+日志恢复并前滚数据库

任何数据库都是这样的，我们会做数据库的全量备份，增量备份，并记录日志。如果数据库出现意外宕机，则可以用全备+增量备份+日志来进行数据库恢复。
本文主要展示如何使用备份+日志来恢复DB2的数据库，为了更清晰一点儿，使用两台机器151和152；在152机器上利用备份和日志恢复到151的数据库的状态。

151机器上数据操作步骤

1.创建测试库

#1.创建测试数据库
db2 create database yynewlog
#2.启用归档日志
db2 update db cfg for yynewlog using logarchmeth1 DISK:/data/db2data
#3.启用增量备份
db2 update db cfg for yynewlog using TRACKMOD YES

#3.解除backup pending状态
db2 backup database yynewlog

#4.创建测试表
 db2 connect to yynewlog
db2 "create table student (id int not null,
name varchar(20),
sex smallint,
phone varchar(12),
address varchar(30)
)"

#5.填充数据：id=1,id=2
db2 "insert into student values(1,'charles ',0,'19089076756','BEIJING ')"
db2 "insert into student values(2,'zhouzhou ',0,'1076758886','BEIJING ')"

2.全量备份

  使用在线备份数据库，with include logs保留日志，这个选项好像是默认的，但是为了语句的可读理解，最好还是加上。

[db2inst1@db22 ~]$ db2 backup db yynewlog online to /tmp include logs

Backup successful. The timestamp for this backup image is : 20171030175924

  /tmp目录下有了名为YYNEWLOG.0.db2inst1.DBPART000.20171030175924.001的全量备份，全量备份里面是id=1,id=2的记录。

3.增量备份

#新插入id=3,id=4的数据。
db2 "insert into student values(3,'xiaohong',0,'67583886','TAIYUAN')"
db2 "insert into student values(4,'lianliang',0,'13667583886','SHIJIAZHAUNG')"

#增量备份
[db2inst1@db22 ~]$ db2 backup db yynewlog ONLINE INCREMENTAL to /tmp include logs

Backup successful. The timestamp for this backup image is : 20171030180235

/tmp目录下有了名为YYNEWLOG.0.db2inst1.DBPART000.20171030180235.001的备份文件。
全量备份+增量备份里面是id=1，id=2,id=3,id=4的数据。

语法

>--+------------------------+----------------------------------->
   '-INCREMENTAL--+-------+-'   
                  '-DELTA-'     

• INCREMENTAL
  Specifies a cumulative (incremental) backup image. An incremental backup image is a copy of all database data that has changed since the most recent successful, full backup operation.
  指定一个累积的(增量)备份。一个INCREMENTAL备份镜像是一个 从 距离最近的成功备份的全量备份 之后 所有改变的数据的副本。
  
  • DELTA
    Specifies a noncumulative (delta) backup image. A delta backup image is a copy of all database data that has changed since the most recent successful backup operation of any type.
    指定一个非 累积的(delta:差值)。一个delta备份镜像是一个 自动最近的一次成功的任何类型的备份操作之后 所有改变的数据库数据的副本。

4.增量delta备份

db2 "insert into student values(5,'shuang',0,'15067583886','SHENZHEN')"
db2 "insert into student values(6,'licy',0,'15683886675','XIAMEN')"
[db2inst1@db22 ~]$ db2 backup db yynewlog ONLINE INCREMENTAL DELTA to /tmp include logs

Backup successful. The timestamp for this backup image is : 20171030181903

5.活动日志

db2 "insert into student values(7,'shuang',0,'15067583886','SHENZHEN')"
db2 "insert into student values(8,'licy',0,'15683886675','XIAMEN')" 

  此时数据如下：

[db2inst1@db22 ~]$ db2 "select * from student"

ID          NAME                 SEX    PHONE        ADDRESS                       
--------