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
----------- -------------------- ------ ------------ ------------------------------
          1 charles                   0 19089076756  BEIJING
          2 zhouzhou                  0 1076758886   BEIJING
          3 xiaohong                  0 67583886     TAIYUAN
          4 lianliang                 0 13667583886  SHIJIAZHAUNG
          5 shuang                    0 15067583886  SHENZHEN
          6 licy                      0 15683886675  XIAMEN
          7 shuang                    0 15067583886  SHENZHEN
          8 licy                      0 15683886675  XIAMEN                        

  8 record(s) selected.

152机器上的恢复步骤

1.拷贝文件到目标机器

scp /tmp/YYNEWLOG.* db2inst1@192.168.225.152:/tmp

将拷贝YYNEWLOG.0.db2inst1.DBPART000.20171030175924.001 ,
YYNEWLOG.0.db2inst1.DBPART000.20171030180235.001 ,
YYNEWLOG.0.db2inst1.DBPART000.20171030181903.001 这三个文件。

2.备份恢复步骤:

三个时间戳
20171030175924:全备
20171030180235:增量1
20171030181903:差异增量2

#1.指定通过增量备份恢复的形式最后要恢复到 20171030181903这个是时间戳。
[db2inst1@db22 ~]$ db2 restore db YYNEWLOG  INCREMENTAL from /tmp  taken at 20171030181903
DB20000I  The RESTORE DATABASE command completed successfully.
#2.第一步恢复全备
[db2inst1@db22 ~]$ db2 restore db YYNEWLOG  INCREMENTAL from /tmp  taken at 20171030175924 logtarget /data/db2data/logs
DB20000I  The RESTORE DATABASE command completed successfully.
#3.第二步恢复备份1:
[db2inst1@db22 ~]$ db2 restore db YYNEWLOG  INCREMENTAL from /tmp  taken at 20171030180235 logtarget /data/db2data/logs
SQL2580W  Warning! Restoring logs to a path which contains existing log files.
Attempting to overwrite an existing log file during restore will cause the
restore operation to fail.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.
#4.第三步恢复差异增量备份2:
[db2inst1@db22 ~]$ db2 restore db YYNEWLOG  INCREMENTAL from /tmp  taken at 20171030181903 logtarget /data/db2data/logs
SQL2580W  Warning! Restoring logs to a path which contains existing log files.
Attempting to overwrite an existing log file during restore will cause the
restore operation to fail.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

此时,logtarget下有前滚所需的日志:

[root@db22 logs]# ls NODE0000/LOGSTREAM0000/
S0000007.LOG  S0000010.LOG  S0000013.LOG

3.确定所需的活动日志:

3.1 151机器上的活动日志:

[db2inst1@db22 ~]$ db2 get db cfg for yynewlog | grep -i 'First active log'
 First active log file                                   = S0000015.LOG

3.1 152机器上的活动日志:

[db2inst1@db22 ~]$ db2 get db cfg for yynewlog | grep -i 'First active log'
 First active log file                                   = S0000013.LOG

3.3 确定所需的日志

所以恢复的话是需要S0000014.LOG和S0000015.LOG的,151上的第一个活动日志是S0000015.LOG,故S0000014.LOG已经归档了,要把该日志拷贝到归档日志的目录中(恢复时的overflow log path
中,在此我们指定overflow log path为/data/db2data/logs下,刚刚restore时的logtarget):

3.3.1 拷贝归档日志

#先找一下归档日志路径
[root@db22 tmp]# find / -name S0000014.LOG
/db/log/db2inst1/TESTMOVE/NODE0000/LOGSTREAM0000/C0000000/S0000014.LOG
/data/bak/db2inst1/SAMPLE/NODE0000/LOGSTREAM0000/C0000001/S0000014.LOG
/data/db2data/db2inst1/SCHOOL/NODE0000/LOGSTREAM0000/C0000000/S0000014.LOG
/data/db2data/db2inst1/YYNEWLOG/NODE0000/LOGSTREAM0000/C0000000/S0000014.LOG
/home/db2inst1/db2inst1/NODE0000/SQL00003/LOGSTREAM0000/S0000014.LOG
/home/db2inst1/db2inst1/NODE0000/SQL00002/LOGSTREAM0000/S0000014.LOG

#拷贝归档日志:
scp /data/db2data/db2inst1/YYNEWLOG/NODE0000/LOGSTREAM0000/C0000000/S0000014.LOG  db2inst1@192.168.225.152:/data/db2data/logs/NODE0000/LOGSTREAM0000
#overflow log path 下有该日志了
[root@db22 logs]# ls NODE0000/LOGSTREAM0000/
S0000007.LOG  S0000010.LOG  S0000013.LOG  S0000014.LOG

3.3.2 拷贝归档日志

#152机器上的活动日志目录
[db2inst1@db22 ~]$ db2 get db cfg for yynewlog | grep -i 'log'

 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00003/LOGSTREAM0000/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) = 

所以152机器上的活动日志目录是:/home/db2inst1/db2inst1/NODE0000/SQL00003/LOGSTREAM0000/

拷贝活动日志

[root@db22 tmp]# scp -r /home/db2inst1/db2inst1/NODE0000/SQL00005/LOGSTREAM0000 db2inst1@192.168.225.152:/home/db2inst1/db2inst1/NODE0000/SQL00003db2inst1@192.168.225.152's password:
S0000024.LOG            100% 4104KB   4.0MB/s   00:00
S0000020.LOG            100% 4104KB   4.0MB/s   00:00
S0000022.LOG            100% 4104KB   4.0MB/s   00:01
S0000015.LOG            100% 4104KB   4.0MB/s   00:00
S0000017.LOG            100% 4104KB   4.0MB/s   00:00
S0000026.LOG            100% 4104KB   4.0MB/s   00:01
S0000023.LOG            100% 4104KB   4.0MB/s   00:00
S0000027.LOG            100% 4104KB   4.0MB/s   00:01
S0000021.LOG            100% 4104KB   4.0MB/s   00:00
S0000019.LOG            100% 4104KB   4.0MB/s   00:00
S0000016.LOG            100% 4104KB   4.0MB/s   00:01
S0000025.LOG            100% 4104KB   4.0MB/s   00:01
S0000018.LOG            100% 4104KB   4.0MB/s   00:01    

4.前滚恢复

[db2inst1@db22 ~]$ db2 "rollforward db yynewlog to end of logs overflow log path (/data/db2data/logs)"

                                 Rollforward Status

 Input database alias                   = yynewlog
 Number of members have returned status = 1

 Member ID                              = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0000016.LOG
 Log files processed                    = S0000013.LOG - S0000014.LOG
 Last committed transaction             = 2017-10-31-08.43.15.000000 Local

DB20000I  The ROLLFORWARD command completed successfully.
[db2inst1@db22 ~]$ db2 "rollforward db yynewlog complete"

                                 Rollforward Status

 Input database alias                   = yynewlog
 Number of members have returned status = 1

 Member ID                              = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000013.LOG - S0000014.LOG
 Last committed transaction             = 2017-10-31-08.43.15.000000 Local

DB20000I  The ROLLFORWARD command completed successfully.

5.校验

[db2inst1@db22 ~]$ db2 "connect to yynewlog"

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.1.1.1
 SQL authorization ID   = DB2INST1
 Local database alias   = YYNEWLOG

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

ID          NAME                 SEX    PHONE        ADDRESS
----------- -------------------- ------ ------------ ------------------------------
          1 charles                   0 19089076756  BEIJING
          2 zhouzhou                  0 1076758886   BEIJING
          3 xiaohong                  0 67583886     TAIYUAN
          4 lianliang                 0 13667583886  SHIJIAZHAUNG
          5 shuang                    0 15067583886  SHENZHEN
          6 licy                      0 15683886675  XIAMEN
          7 shuang                    0 15067583886  SHENZHEN
          8 licy                      0 15683886675  XIAMEN                        

6.成功

7.疑问解析

7.1 Log files processed = S0000013.LOG - S0000014.LOG,没有15.LOG;是否并不需要活动日志?

前滚恢复返回:

 Next log file to be read               = S0000016.LOG
 Log files processed                    = S0000013.LOG - S0000014.LOG

不拷贝活动日志的情况下来试试恢复会如何

db2 restore db YYNEWLOG  INCREMENTAL from /tmp  taken at 20171030181903
db2 restore db YYNEWLOG  INCREMENTAL from /tmp  taken at 20171030175924
db2 restore db YYNEWLOG  INCREMENTAL from /tmp  taken at 20171030180235
db2 restore db YYNEWLOG  INCREMENTAL from /tmp  taken at 20171030181903 logtarget /data/db2data/logs
#拷贝14.LOG日
[root@db22 logs]# ls NODE0000/LOGSTREAM0000/
S0000013.LOG  S0000014.LOG
[root@db22 logs]# ls /home/db2inst1/db2inst1/NODE0000/SQL00003/LOGSTREAM0000/
[root@db22 logs]# 

#前滚

[db2inst1@db22 ~]$ db2 "rollforward db yynewlog to end of logs overflow log path (/data/db2data/logs)"

                                 Rollforward Status

 Input database alias                   = yynewlog
 Number of members have returned status = 1

 Member ID                              = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0000015.LOG
 Log files processed                    = S0000013.LOG - S0000013.LOG
 Last committed transaction             = 2017-10-30-10.19.11.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.
[db2inst1@db22 ~]$ db2 "rollforward db yynewlog complete"
SQL1273N  An operation reading the logs on database "YYNEWLOG" cannot continue
because of a missing log file "S0000014.LOG" on database partition "0" and log
stream "0".

由上面的实验可以看到我们溢出目录中是有14.LOG文件的,但是却报找不到日志文件的错误

将活动日志拷贝过来之后:

[db2inst1@db22 ~]$ db2 "rollforward db yynewlog complete"
SQL1273N  An operation reading the logs on database "YYNEWLOG" cannot continue
because of a missing log file "S0000014.LOG" on database partition "0" and log
stream "0".
[db2inst1@db22 ~]$ db2 "rollforward db yynewlog to end of logs overflow log path (/data/db2data/logs)"

                                 Rollforward Status

 Input database alias                   = yynewlog
 Number of members have returned status = 1

 Member ID                              = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0000016.LOG
 Log files processed                    = S0000013.LOG - S0000014.LOG
 Last committed transaction             = 2017-10-31-02.03.51.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.
[db2inst1@db22 ~]$ db2 "rollforward db yynewlog complete"

                                 Rollforward Status

 Input database alias                   = yynewlog
 Number of members have returned status = 1

 Member ID                              = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000013.LOG - S0000014.LOG
 Last committed transaction             = 2017-10-31-02.03.51.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.

  将活动日志拷贝过来之后再rollforward就可以成功了。

7.2 我只想恢复id =7 的数据,不想要id=8的数据怎么办?重点怎么确定时间戳?

参考:

db2 rollforward 命令时读取日志的顺序

数据库备份恢复的一点总结

时间: 2024-11-03 21:55:05

DB2备份+日志恢复并前滚数据库的相关文章

【RAC】将单实例备份集恢复为rac数据库

[RAC]将单实例备份集恢复为rac数据库 一.1  BLOG文档结构图     一.2  前言部分   一.2.1  导读 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① 单实例环境的备份集如何恢复到rac环境(重点) ② rman恢复数据库的一般步骤 ③ rac环境的简单操作   注意:本篇BLOG中代码部分需要特别关注的地方我都用黄色背景和红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33,thread

用VB备份和恢复SQL Server数据库的方法

server|备份|恢复|数据|数据库 摘要 备份与恢复是数据库管理员维护数据库安全性和完整性的重要操作.本文分析并介绍用Access备份SQL数据库的方法,很好地达到了数据备份的目的.同时,就Access 数据库的安全接口作了简略讨论,使备份后不致丧失数据安全性.并提出了改进备份与恢复策略的一些建议. 引言 对于关键应用的数据库系统应根据具体环境和条件制订一个完善可行确保系统安全的备份计划,没有备份的系统是不可靠.危险的,后果将是严重的,破坏性病毒.误操作.自然灾害等等都可能会对数据库系统造成

在C#中运用SQLDMO备份和恢复SQL Server数据库

server|备份|恢复|数据|数据库     SQLDMO(SQL Distributed Management Objects,SQL分布式管理对象)封装了Microsoft SQL Server数据库中的对象.SQLDMO是Microsoft SQL Server中企业管理器所使用的应用程序接口,所以它可以执行很多功能,其中当然也包括对数据库的备份和恢复. SQLDMO由Microsoft SQL Server自带的SQLDMO.dll提供,由于SQLDMO.dll是一个COM对象(有关C

用SQL备份和恢复SQL SERVER数据库

server|备份|恢复|数据|数据库 <HTML><HEAD><TITLE>SQL Server 数据库的备份与恢复</TITLE><meta http-equiv="Content-Type" content="text/html; charset=gb2312"></HEAD><BODY><form method="post" name=myform&

【RAC】将RAC备份集恢复为单实例数据库

[RAC]将RAC备份集恢复为单实例数据库 1.1  BLOG文档结构图   1.2  前言部分   1.2.1  导读 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① rac数据库的备份集是如何恢复到单实例的数据库 ② ASM文件系统到OS文件系统的转换 ③ 一般的备份恢复过程       本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力. 1.2.2  实验环境介绍   源库:1

asp.net 备份和恢复数据库的方法

 这篇文章主要介绍了asp.net 备份和恢复数据库的方法示例,需要的朋友可以参考下    代码如下: /**********************************************************************************    *  * 功能说明:备份和恢复SQL Server数据库  * 作者: 刘功勋;  * 版本:V0.1(C#2.0);时间:2007-1-1  * 当使用SQL Server时,请引用 COM组件中的,SQLDMO.dll

从炉石传说数据库故障谈谈MongoDB的数据库备份和恢复手段

看到这个消息,我的第一反应是重新翻出尘封已久的ipad,装上炉石准备上线领补偿.等等,作为一个数据库行业从业人员,是不是还应该干点什么?恩,很有必要再重新审视一下我们的数据库有没有做好容灾,否则,今天你看别人热闹,明天可能就别人看你热闹了.借此机会我想给大家普及一下MongoDB数据库的备份和恢复手段(当然炉石传说应该不一定是使用MongoDB作为数据库),以帮助大家做好容灾,过个好年.同时,我也为我们阿里云MongoDB服务做下广告,我们的MongoDB服务拥有完善的自动备份/恢复功能,灵活的

MongoDB的数据库如何备份和恢复?

MongoDB数据库如何备份?恢复MongoDB数据库应如何操作?最近数据库多灾多难,这些问题也成为开发者关注的重点.2016年12月爆出MongoDB数据库安全问题(见MongoDB黑客赎金事件解读及防范).2017年1月又被炉石传说数据库故障给刷屏了.作为一个数据库行业从业人员,看到这个新闻是不是还应该干点什么?恩,很有必要再重新审视一下我们的数据库有没有做好容灾,借此机会给大家普及一下MongoDB数据库的备份和恢复手段.   MongoDB数据库备份手段 全量逻辑备份/恢复 Mongod

asp.net 备份和恢复数据库的方法示例_实用技巧

复制代码 代码如下: /**********************************************************************************  * * 功能说明:备份和恢复SQL Server数据库 * 作者: 刘功勋; * 版本:V0.1(C#2.0);时间:2007-1-1 * 当使用SQL Server时,请引用 COM组件中的,SQLDMO.dll组件 * 当使用Access中,请浏览添加引用以下两个dll *          引用C:\