DB2关于指定时间点前滚恢复方法
1)备份数据库
[db2inst1@bytest ~ 22]$db2 backup db sample online include logs
Backup successful. The timestamp for this backup image is : 20051223120837
[db2inst1@bytest ~ 23]$
（2）删除数据库
[db2inst1@bytest ~ 5]$db2 drop db sample
SQL1035N The database is currently in use. SQLSTATE=57019
[db2inst1@bytest ~ 6]$db2 force application all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst1@bytest ~ 5]$db2 drop db sample
SQL1035N The database is currently in use. SQLSTATE=57019
[db2inst1@bytest ~ 6]$db2 force application all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst1@bytest ~ 7]$db2 drop db sample
DB20000I The DROP DATABASE command completed successfully.
[db2inst1@bytest ~ 8]$
DB20000I The DROP DATABASE command completed successfully.
[db2inst1@bytest ~ 8]$
(3)恢复数据库
[db2inst1@bytest ~ 27]$db2 restore db sample taken at 20051223120837 logtarget /home/db2inst1/db2log/
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@bytest ~ 28]$
[db2inst1@bytest ~ 27]$db2 restore db sample taken at 20051223120837 logtarget /home/db2inst1/db2log/
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@bytest ~ 28]$
（4）查看时间标记
[db2inst1@bytest ~ 28]$db2 list history backup all for db sample
[db2inst1@bytest ~ 28]$db2 list history backup all for db sample
List History File for sample
Number of matching file entries = 3
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20051223120655001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20051223120655001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20051223120655
End Time: 20051223120731
Status: A
----------------------------------------------------------------------------
EID: 1 Location: /home/db2inst1
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20051223120655
End Time: 20051223120731
Status: A
----------------------------------------------------------------------------
EID: 1 Location: /home/db2inst1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20051223120837001 N D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20051223120837001 N D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE ONLINE
Start Time: 20051223120837
End Time: 20051223120902
Status: A
----------------------------------------------------------------------------
EID: 2 Location: /home/db2inst1
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE ONLINE
Start Time: 20051223120837
End Time: 20051223120902
Status: A
----------------------------------------------------------------------------
EID: 2 Location: /home/db2inst1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
R D 20051223121039001 F 20051223120837
----------------------------------------------------------------------------
Contains 2 tablespace(s):
-- --- ------------------ ---- --- ------------ ------------ --------------
R D 20051223121039001 F 20051223120837
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: RESTORE SAMPLE WITH RF
Start Time: 20051223121039
End Time: 20051223121113
Status: A
----------------------------------------------------------------------------
EID: 3 Location:
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: RESTORE SAMPLE WITH RF
Start Time: 20051223121039
End Time: 20051223121113
Status: A
----------------------------------------------------------------------------
EID: 3 Location:
[db2inst1@bytest ~ 29]$
根据时间标记可以在上面列出的信息中找出如下信息：
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20051223120837001 N D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20051223120837001 N D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE ONLINE
Start Time: 20051223120837
End Time: 20051223120902
Status: A
----------------------------------------------------------------------------
EID: 2 Location: /home/db2inst1
从这段信息中可以得知备份的开始时间（Start Time: 20051223120837）和结束时间（End Time: 20051223120902）
以及进行前滚所需要的日志（S0000000.LOG），该日志文件已经在/home/db2inst1/db2log/中。
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE ONLINE
Start Time: 20051223120837
End Time: 20051223120902
Status: A
----------------------------------------------------------------------------
EID: 2 Location: /home/db2inst1
从这段信息中可以得知备份的开始时间（Start Time: 20051223120837）和结束时间（End Time: 20051223120902）
以及进行前滚所需要的日志（S0000000.LOG），该日志文件已经在/home/db2inst1/db2log/中。
其中，End Time: 20051223120902为备份的结束时间，同时它也是前滚时的最小恢复时间点（PIT），也就是说，
如果按照指定时间点恢复，最小要恢复到这个时间点，如果用提前于这个时间的时间来前滚，将会返回信息提示：
[db2inst1@bytest ~ 31]$db2 ”rollforward db sample to 2005-12-23-12.09.00.000000 using local time and stop overflow log path(/home/db2inst1/db2log/)“
SQL1275N The stoptime passed to roll-forward must be greater than or equal to
"2005-12-23-12.09.02.000000", because database "SAMPLE" on node(s) "0"
contains information later than the specified time.
[db2inst1@bytest ~ 32]$
所以至少要恢复到这个时间点。命令如下：
[db2inst1@bytest ~ 33]$db2 "rollforward db sample to 2005-12-23-12.09.02.000000 using local time and stop overflow log path(/home/db2inst1/db2log/)"
如果按照指定时间点恢复，最小要恢复到这个时间点，如果用提前于这个时间的时间来前滚，将会返回信息提示：
[db2inst1@bytest ~ 31]$db2 ”rollforward db sample to 2005-12-23-12.09.00.000000 using local time and stop overflow log path(/home/db2inst1/db2log/)“
SQL1275N The stoptime passed to roll-forward must be greater than or equal to
"2005-12-23-12.09.02.000000", because database "SAMPLE" on node(s) "0"
contains information later than the specified time.
[db2inst1@bytest ~ 32]$
所以至少要恢复到这个时间点。命令如下：
[db2inst1@bytest ~ 33]$db2 "rollforward db sample to 2005-12-23-12.09.02.000000 using local time and stop overflow log path(/home/db2inst1/db2log/)"
Rollforward Status
Input database alias = sample
Number of nodes have returned status = 1
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000000.LOG
Last committed transaction = 2005-12-23-12.09.02.000000
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000000.LOG
Last committed transaction = 2005-12-23-12.09.02.000000
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@bytest ~ 34]$db2 connect to sample
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
[db2inst1@bytest ~ 35]$
此时，数据库是可用状态。