2023年7月30日发(作者：)

数据库恢复的几种方式

一、崩溃恢复

1. 工作原理 从数据库日志控制文件中获取不一致起点信息，并根据日志内容对所有在不一致起点后成功提交事务进行再应用，而对其他未完成的事务进行回滚，从而把数据库带回可用、一致状态。

2. 执行方式(假设数据库已处于不可用、不一致状态)

A．自动 - 数据库参数AUTORESTART=ON(缺省)。应用程序连接将导致数据库重新启动而完成崩溃恢复。

B．手动 - 数据库参数AUTORESTART=OFF。这时需要手动执行重起数据库命令 restart database。如果在崩溃前数据库I/O已处于写暂停状态(通常用在镜像分离操作前)时，可以用指定选项 write resume

来移除数据库中表空间的写暂停状态。

二、完全恢复（版本恢复）

1、 工作原理

Tips: 全备份和版本恢复的执行时间可能很长，这需要您在做备份和恢复计划的时候，必须充分考虑这个因素。

2、 执行方式–根据日志设置方式的不同有以下两种:

A．循环日志 without rolling forward选项来清除数据库的ROLLFORWARD PENDING状备份和恢复都必须离线进行。执行DB2命令(backup/database)来进行全备份和版本恢复。

B．归档日志 备份可以在线，而恢复必须离线进行。在执行DB2命令(restore)进行恢复的时候，需指定态。

三、数据库前滚恢复（时间点恢复）

1、 工作原理

2、 执行方式

数据库前滚恢复需要启用归档日志并建立数据库备份。数据库前滚恢复必须离线进行。在执行不带without rolling forward选项的DB2命令restore后，数据库处于前滚暂挂状态(ROLLFORWARD PENDING),这时执行前滚恢复命令rollforward 将使数据库恢复至备份后的某一特定时间点或者日志末尾。

Tips: 1.表空间的前滚恢复将在部分恢复里进行介绍。 2.必须保留备份后到特定时间点之间所有的日志文件。

四、表空间恢复（部分恢复）

1、 工作原理 原理请参考前面的前滚恢复，区别是备份和恢复的对象是表空间。 2、 主要特点

A．相对数据库备份，表空间备份和恢复在时间上占有优势，并且方法灵活。

B．在大型数据库系统中，常规数据、索引、长字符数据、LOB型数据通常放在不用的表空间里，为了确保数据的一致性需要对所有关联的表空间同时进行备份和恢复。

C．系统编目表空间的备份和恢复必须离线进行,并且必须前滚至日志的末尾。而其他的表空间都可在线进行，并且可以前滚至某个特定时间点。

D．通过设定注册表变量DB2_COLLECT_TS_REC_INFO=ON，前滚命令将跳过不需要的日志来缩短恢复时间。

E．即使对象表空间不同，备份和恢复也不能同时进行。

3、 执行方式

A．利用DB2命令backup对所有关联表空间进行同时备份。

B．恢复时,利用DB2命令restore对所有关联表空间进行同时恢复，恢复完成时，表空间处于前滚暂挂状态(ROLLFORWARD PENDING)。

C．利用DB2命令rollforward对表空间进行前滚恢复。

增量备份 – DB2提供了两种以全备份为基础的增量备份模式

1. INCREMENTAL模式–最近一次全备份后所有更新数据的备份

2. DELTA模式–最近一次备份(包括全备份和增量备份)后所有更新数据的备份

一、循环日志下的版本恢复

最简单最基本的一个例子 - 循环日志下的离线备份和恢复。

要点:

做了动作后，别忘了做检查来验证动作的正确性。

认真、细心是DBA最基本的素质。很多时候能救命的并不是多高级的技术。

操作过程：

1． 用户登录：login(user: db2inst1)

2． 创建数据库test1：db2 create db test1

3． 连接：$ db2 connect to test1

4． 备份数据库设定 & 确认日志模式

$ db2 get db cfg > /opt/db2/backup/ ：备份数据库设定

$ db2 terminate

$ db2 get db cfg ：确认日志模式

User exit for logging enabled (USEREXIT) = OFF

Log retain for recovery enabled (LOGRETAIN) = OFF

First log archive method (LOGARCHMETH1) = OFF

Second log archive method (LOGARCHMETH2) = OFF

5． 全备份

$ db2 backup db test1 to /opt/db2/backup/

……

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

6． 检查备份镜像文件

$ db2ckbkp -h /opt/db2/backup/0000.25.001

……

Image Verification Complete - successful.

7． 查看恢复历史记录

$ db2 list history all for test1

8． 进行误删除

备份表数据：$ db2 "export to /home/db2inst1/ of del select * from tab1" ==>18 rows

删除表数据：$ db2 "delete from tab1"

确认：$ db2 "select count(*) from tab1" ==>0 row

9． 恢复和结果确认

恢复数据库：

$ db2 connect reset ：首先断开数据库的连接

$ db2 restore db test1 from /opt/db2/backup/ taken at 25 ：进行恢复

确认

$ db2 "export to /home/db2inst1/ of del select * from tab1"

$ diff ：恢复前后进行数据比较 二、循环日志下的数据库重定向

可以利用备份镜像重定向功能建立新的数据库

1． 重定向恢复

$ db2 backup db test1 to /opt/db2/backup/ ：备份test1数据库

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

$ db2 "restore db test1 from /opt/db2/backup taken at 23 into sales redirect without rolling

forward" ：利用test1备份镜像重定向功能建立sales数据库。

……

DB20000I The RESTORE DATABASE command completed successfully.

因为是数据库是自动存储，不需要指定容器。

确认容器的状态：此时表空间处在恢复暂挂（Restore pending)下，需要进一步进行恢复处理。

$ db2 list tablespaces

Tablespaces for Current Database

Tablespace ID = 0

Name = SYSCATSPACE

Type = Database managed space

Contents = All permanent data. Regular table space.

State = 0x2000100

Detailed explanation:

Restore pending

Storage may be defined ：此时表空间处在恢复暂挂状态

……

2． 继续恢复操作

$ db2 "restore db test1 continue"

DB20000I The RESTORE DATABASE command completed successfully.

$ db2 list tablespaces

……

Name = SYSCATSPACE

State = 0x0000

Detailed explanation:

Normal ：此时表空间为正常状态

……

▲ 表空间显示为正常状态时，数据库可以使用。

▲ 以上两步完成了循环日志下的数据库重定向。 三、数据库的归档日志设置和数据库前滚恢复

1．设置归档日志：可以有多种设置方法，具体参考InfoCenter

$ db2 connect to test1

$ db2 update db cfg using LOGARCHMETH1 logretain

2．设置成归档日志后的备份

设置成归档日志后的数据库处在备份暂挂(BACKUP PENDING)状态,需要进行数据库的全备

$ db2 connect reset

$ db2 backup db test1 to /opt/db2/backup/

备份后数据库恢复正常。

归档数据库的配置

建议大家使用LOGARCHMETH1 ，此参数指定已归档日志的主要目标的介质类型，不要继续使用LOGRETAIN或USEREXIT参数。LOGARCHMETH1参数缺省值为OFF，取值范围可以是 logretain、 userexit、

disk、tsm或vendor，各自的含义如下：

OFF

指定不使用日志归档方法。如果 LOGARCHMETH1 和LOGARCHMETH2都设置为 OFF，那么认为数据库正在使用循环日志记录，且不可前滚恢复。这是缺省值。

LOGRETAIN

此值仅可用于 LOGARCHMETH1，且等价于将 LOGRETAIN 配置参数设置为 RECOVERY。 如果指定此值，将自动更新 LOGRETAIN配置参数。

USEREXIT

此值仅对 LOGARCHMETH1有效，且等价于将 USEREXIT配置参数设置为 ON。如果指定此值，将自动更新 USEREXIT 配置参数。

DISK

此值后必须紧跟冒号（:），然后是现有标准路径名，日志文件将在其中归档。例如，如果将 LOGARCHMETH1设置为 DISK:/u/dbuser/archived_logs，那么将归档日志文件放入名为 /u/dbuser/archived_logs的目录。

注： 如果正在归档至磁带，可以使用db2tapemgr实用程序来存储和检索日志文件。

TSM

如果指定不带任何附加配置参数，此值指示应该使用缺省管理类，将日志文件归档在本地TSM服务器上。如果此值后紧跟冒号（:）和TSM管理类，那么使用指定的管理类来归档日志文件。

VENDOR 指定将使用供应商库来归档日志文件。此值后必须紧跟冒号（:）和库的名称。库中提供的 API 必须使用备份并复原供应商产品的API。

注： 如果将 LOGARCHMETH1 或LOGARCHMETH2设置为 OFF以外的值，那么必须配置数据库以进行前滚恢复。

如果更新USEREXIT或LOGRETAIN配置参数，将自动更新LOGARCHMETH1，反之亦然。然而，如果您要使用USEREXIT或LOGRETAIN, 必须将LOGARCHMETH2设置为OFF。

更改成功后，断开示例数据库DB2TEST1所有应用程序连接：db2 force applications all

断开后，进行数据库的全备。

自动数据库备份(AUTO_DB_BACKUP)参数

四、在线备份和数据库时间点恢复

1.启动了归档日志后进行在线备份（例中同时在备份文件中包含了日志）

$ db2 backup db test1 online to /opt/db2/backup/ include logs

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

2.检查备份日志

显示了在线备份的Timestamp，这个是最小的恢复时间点

$ db2 list history backup all for test1

List History File for test1

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID

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

B D 24001 N D

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

Contains 3 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 SYSTOOLSPACE

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

Comment: DB2 BACKUP TEST1 ONLINE

Start Time: 24

End Time: 22

Status: A

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

EID: 9 Location: /opt/db2/backup

3.连接数据库，进行测试用的数据插入，同时显示本地时间用来跟踪时间点恢复

$ db2 connect to test1

$ db2 "insert into tab1 values(10,'TEN','TEN@')"

$ date

Fri Apr 8 03:42:40 CST 2011

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

$ date +%F' '%T

2011-04-08 03:42:40

----------------------------------可以用date +%F' '%T来替代date，这样时间日期显示更直观。

$ db2 "insert into tab1 values(11,'EN','EN@')"

$ date +%F' '%T

Fri Apr 8 03:43:04 CST 2011

$ db2 "insert into tab1 values(12,'TEST','test@')"

$ date +%F' '%T

Fri Apr 8 03:43:28 CST 2011

$ db2 "insert into tab1 values(13,'M','M@')"

$ date +%F' '%T

Fri Apr 8 03:43:52 CST 2011

4.检查插入结果

$ db2 "select * from tab1"

ID NAME MAIL

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

1 A a@

2 B b@

3 C C@

4 D d@

5 E e@

6 F f@

7 G g@

8 H h@

9 NONE n@

10 TEN TEN@

11 EN EN@

12 TEST test@

13 M M@

5.故障，表空间误删除

$ cd /home/db2inst1/db2inst1/NODE0000/TEST1/

$ mv * /opt/db2/db_name/

$ ls

$ db2 connect reset

DB20000I The SQL command completed successfully. $ db2 connect to test1

SQL0293N Error accessing a table space container. SQLSTATE=57048

6.数据库恢复

$ db2 restore db test1 from /opt/db2/backup/ taken at 24

SQL2539W Warning! Restoring to an existing database that is the same as the

backup image database. The database files will be deleted.

Do you want to continue ? (y/n) y

DB20000I The RESTORE DATABASE command completed successfully.

7.恢复后的数据库处在前滚暂挂状态(ROLL-FORWARD PENDING)

$ db2 connect to test1

SQL1117N A connection to or activation of database "TEST1" cannot be made

because of ROLL-FORWARD PENDING. SQLSTATE=57019

8.时间点前滚恢复，当小于最小恢复时间点时，无法恢复

9.时间点前滚恢复

$ db2 rollforward db test1 to 2011-04-08-03.43.28 using local time and complete

Rollforward Status

Input database alias = test1

Number of nodes have returned status = 1

Node number = 0

Rollforward status = not pending

Next log file to be read =

Log files processed = -

Last committed transaction = 2011-04-08-03.43.26.000000 Local

DB20000I The ROLLFORWARD command completed successfully.

10.结果的确认，选取的时间点刚好在记录ID为12和13之间的时间，所以结果显示ID为12之前的记录。

结果正确！

$ db2 connect to test1

$ db2 "select * from tab1"

ID NAME MAIL

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

1 A a@

2 B b@

3 C C@

4 D d@ 5 E e@

6 F f@

7 G g@

8 H h@

9 NONE n@

10 TEN TEN@

11 EN EN@

12 TEST test@

五、前滚恢复误删除表

1.归档日志的设置

参照实战演练-3

2.对表空间设置删除表恢复

$ db2 alter tablespace userspace1 dropped table recovery on

DB20000I The SQL command completed successfully.

3.备份

$ db2 backup db test1 online to /opt/db2/backup/

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

4.表的创建，数据插入和表删除

$ db2 connect to test1

$ db2 "create table test(id int,name varchar(10)) in userspace1"

$ db2 "insert into test(id,name) values(1,'jack'),(2,'tom'),(3,'blues'),(4,'alies'),(5,'sherk')"

$ db2 "select * from test"

ID NAME

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

1 jack

2 tom

3 blues

4 alies

5 sherk

$ db2 "drop table test"

5.从删除表历史里找到表的DDL

$ db2 list history dropped table all for test1

List History File for test1

Number of matching file entries = 1

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID

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

D T 28 db

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

"DB2INST1"."TEST" resides in 1 tablespace(s):

00001 USERSPACE1

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

Comment: DROP TABLE

Start Time: 28

End Time: 28

Status: A

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

EID: 14

DDL: CREATE TABLE "DB2INST1"."TEST" ( "ID" INTEGER , "NAME" VARCHAR(10) ) IN "USERSPACE1" ;

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

6.表的恢复

$ db2 "restore db test1 tablespace(userspace1) from /opt/db2/backup/ taken at 21"

DB20000I The RESTORE DATABASE command completed successfully.

7.前滚恢复的同时导出被删除的表数据，注意需要提供表ID（可以在删除表历史记录里获取）

$ db2 "rollforward db test1 to end of logs and stop tablespace(userspace1) recover dropped table

db to /opt/db2/testdata/"

Rollforward Status

Input database alias = test1

Number of nodes have returned status = 1

Node number = 0

Rollforward status = not pending

Next log file to be read =

Log files processed = -

Last committed transaction = 2011-04-08-03.43.26.000000 Local

DB20000I The ROLLFORWARD command completed successfully.

8.利用删除表历史中的DDL重建表。

$ db2 connect to test1

$ db2 'CREATE TABLE "DB2INST1"."TEST" ( "ID" INTEGER , "NAME" VARCHAR(10) ) IN "USERSPACE1"'

前滚恢复中导出的数据

$ db2 "import from /opt/db2/testdata/NODE0000/data of del insert into test" SQL3109N The utility is beginning to load data from file

"/opt/db2/testdata/NODE0000/data".

SQL3110N The utility has completed processing. "5" rows were read from the

input file.

SQL3221W ...Begin COMMIT WORK. Input Record Count = "5".

SQL3222W ...COMMIT of any database changes was successful.

SQL3149N "5" rows were processed from the input file. "5" rows were

successfully inserted into the table. "0" rows were rejected.

Number of rows read = 5

Number of rows skipped = 0

Number of rows inserted = 5

Number of rows updated = 0

Number of rows rejected = 0

Number of rows committed = 5

10.结果确认。误删除表的数据找回来了！

$ db2 "select * from test"

ID NAME

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

1 jack

2 tom

3 blues

4 alies

5 sherk

六、INCREMENTAL增量备份和恢复

增量备份只有在中大型，每天有很多更新的数据库中才能体现其价值。

这里只演示其用法。

INCREMENTAL的增量恢复需要

全备份+INCREMENTAL增量备份+增量备份后的LOG。

首先创建数据库：$ db2 create db sales

1.增量备份需要启动归档日志和设置TRACKMOD

为了看清归档日志状况，可以把归档日志放在单独的磁盘里

$ db2 connect to sales

$ db2 update db cfg using logarchmeth1 disk:/opt/db2/archivelog/

$ db2 update db cfg using trackmod on

$ db2 get db cfg

（其余内容忽略）

Database Configuration for Database

First log archive method (LOGARCHMETH1) = DISK:/opt/db2/archivelog/

Track modified pages (TRACKMOD) = ON

2。数据库备份

$ db2 backup db sales to /opt/db2/backup/

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

3。创建测试用表并插入数据

$ db2 connect to sales

$ db2 "create table test(id int not null generated always as identity (start with 1,increment by 1,no cache),msg

char(20))"

$ db2 "insert into test(msg) values(''),(''),(''),

(''),(''),('')"

$ db2 "select * from test"

ID MSG

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

1

2

3

4

5

6

4。在线增量备份。

$ db2 backup database sales online incremental to /opt/db2/backup/

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

5。再插入数据

$ db2 connect to sales

$ db2 "insert into test(msg) values(''),(''),('')"

$ db2 "select * from test"

ID MSG

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

1

2

3

4 5

6

7

8

9

6。故障，误删除表空间

$ db2 list db directory

Database alias = SALES

Database name = SALES

Local database directory = /opt/db2/db_path1

$ rm -rf /home/db2inst1/db2inst1/NODE0000/SALES/*

$ ls /home/db2inst1/db2inst1/NODE0000/SALES

$ db2 connect reset

$ db2 connect to sales

SQL0293N Error accessing a table space container. SQLSTATE=57048

6。数据库自动增量恢复

$ db2 "restore db sales incremental automatic from /opt/db2/backup taken at 20"

SQL2539W Warning! Restoring to an existing database that is the same as the

backup image database. The database files will be deleted.

Do you want to continue ? (y/n) y

DB20000I The RESTORE DATABASE command completed successfully.

7。恢复后数据库处在前滚恢复状态(ROLL-FORWARD PENDING)

$ db2 connect to sales

SQL1117N A connection to or activation of database "SALES" cannot be made

because of ROLL-FORWARD PENDING. SQLSTATE=57019

8。恢复到日志结尾,全部数据恢复成功

$ db2 rollforward db sales to end of logs and stop

Rollforward Status

Input database alias = sales

Number of nodes have returned status = 1

Node number = 0

Rollforward status = not pending

Next log file to be read =

Log files processed = -

Last committed transaction = 2011-04-07-21.17.25.000000 UTC

DB20000I The ROLLFORWARD command completed successfully. $ db2 connect to sales

Database Connection Information

Database server = DB2/LINUX 9.7.0

SQL authorization ID = DB2INST1

Local database alias = SALES

$ db2 "select * from test"

ID MSG

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

1

2

3

4

5

6

7

8

9

9 record(s) selected.

七、DELTA增量备份和恢复

DELTA的增量恢复需要

全备份(或者+INCREMENTAL备份)+全部DELTA增量备份+增量备份后的LOG。

1.增量备份需要启动归档日志和设置TRACKMOD

设置参考实战演练 - 06

2.备份

$ db2 backup db sales to /opt/db2/backup/

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

3.建立测试表

$ db2 connect to sales

$ db2 "create table test_delta(id int not null generated always as identity(start with 1,increment by 1,no

cache),msg char(20))"

4.插入数据并进行DELTA增量备份

$ db2 "insert into test_delta(msg) values(''),(''),('')"

$ db2 "select * from test_delta"

ID MSG

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

1

2

3

3 record(s) selected.

$ db2 backup db sales incremental delta to /opt/db2/backup/

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

$ db2 connect to sales

$ db2 "insert into test_delta(msg) values(''),(''),('')"

$ db2 "select * from test_delta"

ID MSG

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

1

2

3

4

5

6

6 record(s) selected.

$ db2 backup db sales incremental delta to /opt/db2/backup/

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

$ db2 connect to sales

$ db2 "insert into test_delta(msg) values(''),(''),('')"

$ db2 "insert into test_delta(msg) values(''),(''),('')"

$ db2 "select * from test_delta"

ID MSG

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

1

2

3

4

5 6

7

8

9

10

11

12

12 record(s) selected.

$ db2 backup db sales incremental delta to /opt/db2/backup/

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

$ db2 connect to sales

$ db2 "insert into test_delta(msg) values(''),(''),('')"

$ db2 "select * from test_delta"

ID MSG

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

15 record(s) selected.

$ db2 connect reset

DB20000I The SQL command completed successfully.

5.故障，表空间误删除

$ rm -rf /home/db2inst1/db2inst1/NODE0000/SALES/*

$ db2 connect to sales

SQL0293N Error accessing a table space container. SQLSTATE=57048

6.把 0000.24.001 备份镜像移动到另外

的目录，并进行数据库恢复，出现错误提示,恢复无法进行

$ mv /opt/db2/backup/0000.24.001 /opt/db2/

$ db2 "restore db sales incremental automatic from /opt/db2/backup/ taken at 25"

SQL2539W Warning! Restoring to an existing database that is the same as the

backup image database. The database files will be deleted.

Do you want to continue ? (y/n) y

SQL2542N No match for a database image file was found based on the source

database alias "SALES" and timestamp "24" provided.

7.放弃恢复

$ db2 "restore db sales incremental abort from /opt/db2/backup/ taken at 25"

SQL2001N The utility was interrupted. The output data may be incomplete.

8.把 0000.24.001 备份镜像移回

备份后的所在目录，并重新启动恢复

$ mv /opt/db2/0000.24.001 /opt/db2/backup/

$ db2 "restore db sales incremental automatic from /opt/db2/backup/ taken at 25"

SQL2539W Warning! Restoring to an existing database that is the same as the

backup image database. The database files will be deleted.

Do you want to continue ? (y/n) y

DB20000I The RESTORE DATABASE command completed successfully.

9.恢复后数据库处于前滚恢复状态(ROLL-FORWARD PENDING)

$ db2 connect to sales

SQL1117N A connection to or activation of database "SALES" cannot be made

because of ROLL-FORWARD PENDING. SQLSTATE=57019

10.到LOG末尾的前滚恢复

$ db2 rollforward db sales to end of logs and complete

Rollforward Status

Input database alias = sales

Number of nodes have returned status = 1

Node number = 0

Rollforward status = not pending

Next log file to be read =

Log files processed = -

Last committed transaction = 2011-04-07-22.04.55.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.

11.检查数据，结果正常

$ db2 connect to sales Database Connection Information

Database server = DB2/LINUX 9.7.0

SQL authorization ID = DB2INST1

Local database alias = SALES

$ db2 "select * from test_delta"

ID MSG

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

15 record(s) selected.

八、表空间的备份和恢复

在大型数据库里，表空间备份有很多灵活性。

一般来说大表的基础表，索引，LOB字段都需要建立在独立的表空间里。

▲ 表空间级别的备份/恢复操作要求数据库处于归档日志和启用增量备份模式下

1。建立非自动存储数据库

$ db2 "create db backupts automatic storage no on /home/db2inst1/backupts/"

DB20000I The CREATE DATABASE command completed successfully.

2.建立DMS表空间

$ db2 connect to backupts

$ db2 "create tablespace headerts managed by database using (file '/home/db2inst1/ts/HEADERTS' 1000)"

$ db2 "create tablespace detailts managed by database using (file '/home/db2inst1/ts/DETAILTS' 1000)"

$ db2 "create tablespace masterts managed by database using (file '/home/db2inst1/ts/MASTERTS' 1000)"

$ db2 list tablespaces --查看是否创建成功

3。在各自的表空间建立表并插入数据。

$ db2 "create table header(id int not null primary key,from char(2),to char(2),startdate date) in headerts"

$ db2 "create table details(id int not null,seq int not null,item_code char(4),flag char(1),del_flg char(1),primary

key(id,seq),foreign key(id) references header(id)) in detailts"

$ db2 "create table master(id int not null primary key,iten_code char(4),from char(2),to

char(2),effective_startdate date) in masterts"

$ db2 "insert into header(id,from,to,startdate) values(1,'AA','11','2011-07-10'),

(2,'BB','22','2011-07-10'),(3,'CC','33','2011-07-10'),(4,'DD','44','2011-07-10')"

DB20000I The SQL command completed successfully.

$ db2 "insert into details(id,seq,item_code,flag,del_flg) values(1,1,'AAAA','0','0'),

(1,2,'BBBB','0','0'),(1,3,'CCCC','0','0'),(2,1,'AAAA','0','0'),(2,2,'BBBB','0','0')"

DB20000I The SQL command completed successfully.

$ db2 "insert into master values(1,'AAAA','AA','11','2011-07-10')"

$ db2 "insert into master values(2,'BBBB','BB','22','2011-07-10')"

4。数据库参数的设定

$ db2 update db cfg using logarchmeth1 logretain

$ db2 update db cfg using trackmod on

$ db2 connect reset

DB20000I The SQL command completed successfully.

$ db2 backup db backupts to /opt/db2/backup/

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

5.表空间备份，注意有关联的表空间同时备份。

$ db2 "backup db backupts tablespace(masterts) to /opt/db2/backup/"

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

$ db2 "backup db backupts tablespace(headerts,detailts) to /opt/db2/backup/"

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

备份后的文件(注意大小):

$ ls /opt/db2/backup/ -lh

total 1.4G

-rw------- 1 db2inst1 db2iadm1 73M

0000.23.001

-rw------- 1 db2inst1 db2iadm1 18M

0000.27.001

-rw------- 1 db2inst1 db2iadm1 26M

0000.25.001

6。故障！MASTER数据误删除

$ db2 connect to backupts

$ db2 "select * from master"

ID ITEN_CODE FROM TO EFFECTIVE_STARTDATE

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

1 AAAA AA 11 07/10/2011

2 BBBB BB 22 07/10/2011

Apr

Apr

Apr

8

8

8

08:24

08:25

08:26

$ date +%F' '%T ：删除表数据前查看一下系统时间。（在进行表空间恢复时会用到该时间）

2011-04-08 08:27:00

$ db2 "delete from master"

DB20000I The SQL command completed successfully.

$ db2 "select * from master"

ID ITEN_CODE FROM TO EFFECTIVE_STARTDATE

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

0 record(s) selected.

7。表空间恢复

$ db2 "restore db backupts tablespace(masterts) from /opt/db2/backup/ taken at 27"

DB20000I The RESTORE DATABASE command completed successfully.

8。此时表空间处于前滚暂挂状态(Roll forward pending)

$ db2 connect to backupts

$ db2 "select * from master"

ID ITEN_CODE FROM TO EFFECTIVE_STARTDATE

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

SQL0290N Table space access is not allowed. SQLSTATE=55039

$ db2 list tablespaces

Tablespaces for Current Database

Tablespace ID = 6

Name = MASTERTS

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0x0080

Detailed explanation:

Roll forward pending

9.前滚恢复到误删除前的状态

$ db2 "rollforward db backupts to 2011-04-08-08.27.00 using local time and stop tablespace(masterts)"

Rollforward Status

Input database alias = backupts

Number of nodes have returned status = 1

Node number = 0

Rollforward status = not pending

Next log file to be read =

Log files processed = -

Last committed transaction = 1970-01-01-08.00.00.000000 Local

DB20000I The ROLLFORWARD command completed successfully.

10。数据确认恢复成功。

$ db2 connect to backupts

$ db2 "select * from master"

ID ITEN_CODE FROM TO EFFECTIVE_STARTDATE

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

1 AAAA AA 11 07/10/2011

2 BBBB BB 22 07/10/2011

2 record(s) selected.