2009-11-02 DB2备份恢复测试

某业务系统的IBM TotalStorage DS4300盘阵要进行升级微码，更换一块坏盘和更换两个RAID控制器上的电池等操作。操作期间盘阵上的DB2数据库会受到影响，为减少业务中断时间，打算在操作前用备份&恢复的方法将数据库迁移到盘阵备机上，操作完成后再迁移回原盘阵。
由于方案中涉及相关技术，本实验对DB2的备份和恢复功能进行了测试，验证了DB2也可以像Oracle一样在用联机全库备份文件还原数据库后，多次应用归档日志文件进行前滚（因为可能有数据不断写入数据库，这样做可以减少数据损失）。DB2和Oracle在很多地方真的很像，本次实验中不少地方笔者也是按照Oracle的思路去理解和操作的。但应该指出这样并不严谨，DB2和Oracle肯定还是有区别的，而备份恢复又是数据库的大事，真正在生产环境中进行操作，应该先彻底弄清楚DB2的备份恢复机制。

[@more@]

一、对GGYY库进行一次联机全库备份

相关命令：db2 backup db ggyy online to /data/backup

操作记录：

db2inst1@suse-db:/data/backup> db2 backup db ggyy online to /data/backup

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

db2inst1@suse-db:/data/backup> ls -lht
total 98M
-rw------- 1 db2inst1 db2iadm1 98M 2009-11-02 01:45 GGYY.0.db2inst1.NODE0000.CATN0000.20091102014458.001
db2inst1@suse-db:/data/backup> db2 list history backup all for ggyy

List History File for ggyy

Number of matching file entries = 1

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20091102014458001 N D S0000010.LOG S0000010.LOG
----------------------------------------------------------------------------
Contains 3 tablespace(s):

00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
----------------------------------------------------------------------------
Comment: DB2 BACKUP GGYY ONLINE
Start Time: 20091102014458
End Time: 20091102014504
Status: A
----------------------------------------------------------------------------
EID: 24 Location: /data/backup

二、手动归档日志

相关命令：db2 archive log for db ggyy（执行三次）

操作记录：

db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> db2 archive log for db ggyy
DB20000I The ARCHIVE LOG command completed successfully.
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> db2 archive log for db ggyy
DB20000I The ARCHIVE LOG command completed successfully.
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> db2 archive log for db ggyy
DB20000I The ARCHIVE LOG command completed successfully.
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> ls -lht
total 216K
-rw-r----- 1 db2inst1 db2iadm1 12K 2009-11-02 01:55 S0000013.LOG
-rw-r----- 1 db2inst1 db2iadm1 12K 2009-11-02 01:55 S0000012.LOG
-rw-r----- 1 db2inst1 db2iadm1 192K 2009-11-02 01:55 S0000011.LOG

三、删除数据库

相关命令：db2 drop db ggyy

操作记录：

db2inst1@suse-db:~/db2inst1/NODE0000> ls
GGYY SAMPLE SQL00001 SQL00002 SQL00003 sqldbdir TOOLSDB
db2inst1@suse-db:~/db2inst1/NODE0000> db2 drop db ggyy
DB20000I The DROP DATABASE command completed successfully.
db2inst1@suse-db:~/db2inst1/NODE0000> ls
SAMPLE SQL00001 SQL00002 sqldbdir TOOLSDB

四、RESTORE恢复

相关命令：db2 restore db ggyy from /data/backup

操作记录：

db2inst1@suse-db:~/db2inst1/NODE0000> db2 restore db ggyy from /data/backup
DB20000I The RESTORE DATABASE command completed successfully.
db2inst1@suse-db:~/db2inst1/NODE0000> ls
GGYY SAMPLE SQL00001 SQL00002 SQL00003 sqldbdir TOOLSDB
db2inst1@suse-db:~/db2inst1/NODE0000> db2 connect to ggyy
SQL1117N A connection to or activation of database "GGYY" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019

五、第一次ROLLFORWARD恢复

相关命令：db2 rollforward db ggyy to end of logs

操作记录：

db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> ls -lht
total 192K
-rw-r----- 1 db2inst1 db2iadm1 192K 2009-11-02 01:55 S0000011.LOG
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> db2 rollforward db ggyy to end of logs
SQL4970N Roll-forward recovery on database "GGYY" cannot reach the specified
stop point (end-of-log or point-in-time) on database partition(s) "0".
Roll-forward recovery processing has halted on log file "S0000010.LOG".
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> cd ..
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000> ls
C0000000 S0000008.LOG S0000009.LOG S0000010.LOG S0000012.LOG S0000013.LOG
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000> cd C0000000/
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> mv ../S0000010.LOG ./
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> ls -lht
total 204K
-rw-r----- 1 db2inst1 db2iadm1 192K 2009-11-02 01:55 S0000011.LOG
-rw-r----- 1 db2inst1 db2iadm1 12K 2009-11-02 01:45 S0000010.LOG
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> db2 rollforward db ggyy to end of logs

Rollforward Status

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

Node number = 0
Rollforward status = DB working
Next log file to be read = S0000011.LOG
Log files processed = S0000010.LOG - S0000011.LOG
Last committed transaction = 2009-11-01-17.54.25.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> db2 connect to ggyy
SQL1117N A connection to or activation of database "GGYY" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019

六、第二次ROLLFORWARD恢复

相关命令：db2 rollforward db ggyy to end of logs

操作记录：

db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> mv ../S0000012.LOG ./
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> db2 rollforward db ggyy to end of logs

Rollforward Status

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

Node number = 0
Rollforward status = DB working
Next log file to be read = S0000012.LOG
Log files processed = S0000010.LOG - S0000012.LOG
Last committed transaction = 2009-11-01-17.54.25.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> db2 rollforward db ggyy to end of logs

Rollforward Status

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

Node number = 0
Rollforward status = DB working
Next log file to be read = S0000013.LOG
Log files processed = S0000010.LOG - S0000012.LOG
Last committed transaction = 2009-11-01-17.54.25.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.

七、第三次ROLLFORWARD恢复

相关命令：db2 rollforward db ggyy to end of logs
db2 rollforward db ggyy to end of logs and stop

操作记录：

db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> mv ../S0000013.LOG ./
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> db2 rollforward db ggyy to end of logs

Rollforward Status

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

Node number = 0
Rollforward status = DB working
Next log file to be read = S0000013.LOG
Log files processed = S0000010.LOG - S0000013.LOG
Last committed transaction = 2009-11-01-17.54.25.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> db2 rollforward db ggyy to end of logs

Rollforward Status

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

Node number = 0
Rollforward status = DB working
Next log file to be read = S0000014.LOG
Log files processed = S0000010.LOG - S0000013.LOG
Last committed transaction = 2009-11-01-17.54.25.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> db2 connect to ggyy
SQL1117N A connection to or activation of database "GGYY" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000> db2 rollforward db ggyy to end of logs and stop

Rollforward Status

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

Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000010.LOG - S0000013.LOG
Last committed transaction = 2009-11-01-17.54.25.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.
db2inst1@suse-db:/data/palog/db2inst1/GGYY/NODE0000/C0000000

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11662464/viewspace-1028360/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11662464/viewspace-1028360/