DB2数据库的备份测试--开始恢复

最新推荐文章于 2021-11-19 15:35:59 发布

congyuji4066

最新推荐文章于 2021-11-19 15:35:59 发布

阅读量172

点赞数

1、察看备份集

[db2inst1@db2 off_back]$ db2 list history backup all for sample

List History File for sample

Number of matching file entries = 6

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

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

B D 20090917043647000 F S0000000.LOG

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

Contains 4 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 IBMDB2SAMPLEREL

00004 IBMDB2SAMPLEXML

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

Comment: DB2 BACKUP SAMPLE OFFLINE

Start Time: 20090917043647

End Time: 20090917043648

Status: A

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

EID: 4 Location:

SQLCA Information

sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2036 sqlerrml: 14

sqlerrmc: /home/off_back

sqlerrp : sqlubMWR

sqlerrd : (1) 0 (2) 0 (3) 0

(4) 0 (5) 0 (6) 0

sqlwarn : (1) (2) (3) (4) (5) (6)

(7) (8) (9) (10) (11)

sqlstate:

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

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

B D 20090917043704001 F D S0000000.LOG S0000000.LOG

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

Contains 4 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 IBMDB2SAMPLEREL

00004 IBMDB2SAMPLEXML

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

Comment: DB2 BACKUP SAMPLE OFFLINE

Start Time: 20090917043704

End Time: 20090917043709

Status: A

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

EID: 5 Location: /home/db2inst1/off_back

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

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

B D 20090917061052001 N D S0000000.LOG S0000000.LOG

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

Contains 4 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 IBMDB2SAMPLEREL

00004 IBMDB2SAMPLEXML

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

Comment: DB2 BACKUP SAMPLE ONLINE

Start Time: 20090917061052

End Time: 20090917061059

Status: A

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

EID: 8 Location: /tmp/db2inst1/sample/on_back

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

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

B D 20090917061511001 O D S0000001.LOG S0000001.LOG

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

Contains 4 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 IBMDB2SAMPLEREL

00004 IBMDB2SAMPLEXML

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

Comment: DB2 BACKUP SAMPLE ONLINE

Start Time: 20090917061511

End Time: 20090917061512

Status: A

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

EID: 11 Location: /tmp/db2inst1/sample/diff_back

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

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

B D 20090917061921001 N D S0000002.LOG S0000002.LOG

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

Contains 4 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 IBMDB2SAMPLEREL

00004 IBMDB2SAMPLEXML

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

Comment: DB2 BACKUP SAMPLE ONLINE

Start Time: 20090917061921

End Time: 20090917061925

Status: A

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

EID: 14 Location: /tmp/db2inst1/sample/off_back

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

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

B D 20090917062532001 O D S0000005.LOG S0000005.LOG

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

Contains 4 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 IBMDB2SAMPLEREL

00004 IBMDB2SAMPLEXML

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

Comment: DB2 BACKUP SAMPLE ONLINE

Start Time: 20090917062532

End Time: 20090917062535

Status: A

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

EID: 21 Location: /tmp/db2inst1/sample/off_back

2、寻求恢复帮助：

[db2inst1@db2 off_back]$ db2ckrst -d sample -t 20090917062532 -r database

Suggested restore order of images using timestamp 20090917062532 for

database sample.

====================================================================

restore db sample incremental taken at 20090917062532

restore db sample incremental taken at 20090917061921

restore db sample incremental taken at 20090917062532

3、开始恢复：

[db2inst1@db2 off_back]$ db2 restore db sample incremental from /tmp/db2inst1/sample/off_back taken at 20090917062532

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.

[db2inst1@db2 off_back]$ db2 restore db sample incremental from /tmp/db2inst1/sample/off_back taken at 20090917061921

DB20000I The RESTORE DATABASE command completed successfully.

[db2inst1@db2 off_back]$ db2 restore db sample incremental from /tmp/db2inst1/sample/off_back taken at 20090917062532

DB20000I The RESTORE DATABASE command completed successfully.

4、恢复日志

1、基于日志恢复：

[db2inst1@db2 NODE0000]$ cd C0000000
[db2inst1@db2 C0000000]$ ls
S0000000.LOG S0000001.LOG S0000002.LOG S0000003.LOG S0000004.LOG S0000005.LOG
[db2inst1@db2 C0000000]$ cd ..
[db2inst1@db2 NODE0000]$ cp -r C0000000 /tmp

[db2inst1@db2 NODE0000]$ db2 restore db sample incremental from /tmp/db2inst1/sample/off_back taken at 20090917062532
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.
[db2inst1@db2 NODE0000]$ db2 restore db sample incremental from /tmp/db2inst1/sample/off_back taken at 20090917061921
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@db2 NODE0000]$ db2 restore db sample incremental from /tmp/db2inst1/sample/off_back taken at 20090917062532
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@db2 NODE0000]$ db2 "rollforward database sample to end of logs and complete overflow log path (/tmp/C0000000/)"

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 1

Node number                            = 0
Rollforward status                     = not pending
Next log file to be read               =
Log files processed                    = S0000005.LOG - S0000026.LOG
Last committed transaction             = 2009-09-17-23.15.10.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.

2、基于时间恢复：

[db2inst1@db2 ~]$ db2 restore db sample incremental from /tmp/db2inst1/sample/off_back taken at 20090917062532
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.
[db2inst1@db2 ~]$ db2 restore db sample incremental from /tmp/db2inst1/sample/off_back taken at 20090917061921
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@db2 ~]$ db2 restore db sample incremental from /tmp/db2inst1/sample/off_back taken at 20090917062532
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@db2 ~]$ db2 "rollforward database sample to 2009-09-17-06.25.32 and complete overflow log path (/tmp/db2inst1/sample/archivelog/)"

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 1

Node number                            = 0
Rollforward status                     = not pending
Next log file to be read               =
Log files processed                    = S0000005.LOG - S0000007.LOG
Last committed transaction             = 2009-09-16-23.15.13.000000 UTC

DB20000I The ROLLFORWARD command completed successfull

备注默认为格林尼治时间，所以需要如下：

1 rollforward 命令 to end of logs 选项是会回滚db cfg中logpath配置路径下的所有已提交事务。而您是在原库上直接restore操作的，所以原配置路径下的日志还存在。则您在前滚操作的时候，就连同这些日志一并前滚了。您可以尝试删除数据，再次指定路径前滚，确认日志结束日志编号。

2 to time_stamp USING LOCAL TIME 中 time_stamp 的时间格式为 yyyy-mm-dd-hh.mm.ss (year, month, day, hour, minutes, seconds)。