我在按照DB2的要求,配置完online incremental backup所需参数之后,又按照要求先做了offline的backup, 然后又做了一个online backup和3个online incremental backup, 现在我需要将数据库的状态恢复到第一个online incremental backup的地方。 我restore的时候是用的第一个online incremental backup的时间戳(20080131140321),而且我在rollforward的时候指定了相同的时间点(2008-01-31-14.03.21) 但是我发现恢复之后的数据库到了数据库的最远端,也就是time point3, 而没有停在我要的time point 1. 请问一下,我要怎么能够控制rollforward停在我要的时间点,而不要到了log的最后?万分感谢。
下面是我的详细步骤,请各位高手看一下我错在哪里,或是少了什么步骤?
C:\>db2
db2 => update database configuration for ECD using LOGRETAIN ON
db2 => update database configuration for ECD using TRACKMOD ON
db2 => update database configuration for ECD using NEWLOGPATH "C:\lcbackup\logfiles
db2 => connect to ECD user db2admin using passw0rd
db2 => force application all
db2 => backup db ECD to C:\lcbackup\DB2 (offline gnerate 20080131111310)-----time point 1
db2 => backup db ECD online to C:\lcbackup\DB2 (ist online backup generate 20080131111529) -----time point 1
Did some jobs on server and then
db2 => backup db ECD online incremental to C:\lcbackup\DB2 (1st online incremental backup generate 20080131140321) -----time point 1
Did some jobs again on server and then
db2 => backup db ECD online incremental to C:\lcbackup\DB2 (1st online incremental backup generate 20080131143321) -----time point 2
Did some jobs again on server and then
db2 => backup db ECD online incremental to C:\lcbackup\DB2 (1st online incremental backup generate 20080131144311) -----time point 3
C:\>db2ckrst -d ECD -t 20080131140321 -r database (我打算回复到在time pont2的时候的系统备份)
Suggested restore order of images using timestamp 20080131140321 for database ECD.
====================================================================
restore db ECD incremental taken at 20080131140321
restore db ECD incremental taken at 20080131111529
restore db ECD incremental taken at 20080131140321
====================================================================
恢复步骤
C:\>db2 force applications all
C:\>db2 restore db ECD incremental from C:\lcbackup\DB2 taken at 20080131140321
C:\>db2 restore db ECD incremental from C:\lcbackup\DB2 taken at 20080131111529
C:\>db2 restore db ECD incremental from C:\lcbackup\DB2 taken at 20080131140321
C:\>db2 rollforward db ECD to 2008-01-31-14.03.21 and stop (也试了db2 rollforward db ECD to 2008-01-31-14.03.21 and complete)
这时候我发现rollforwad之后,db2没有停在我指定的时间点,而是到了time ponit 3之后,请问我要怎么才能控制rollforward,不要让它一下子到了end of logs?
---------------------
LZ的脚本没有问题,加上时区就ok!
db2 rollforward db ECD to 2008-01-31-14.03.21 USING LOCAL TIME and stop !
1.在线备份恢复的时候需要增加前滚恢复!
2.在线备份恢复的时候肯定不能增加without rolling forwad!
贴上刚才测试的代码!
alter table ZXT_T activate not logged initially with empty table;
commit;
insert into zxt_t values('1',current_timestamp);
commit;
backup db dwzxt to C:\dblog\backupf
备份成功。此备份映像的时间戳记是:20020114101502
insert into zxt_t values('2',current_timestamp);
commit;
backup db dwzxt online to C:\dblog\backupf
备份成功。此备份映像的时间戳记是:20020114101650
insert into zxt_t values('3',current_timestamp);
commit;
backup db dwzxt online incremental to C:\dblog\backupf
备份成功。此备份映像的时间戳记是:20020114101929
select * from zxt_t;
---
1 '2002-01-14 10:13:46.046001'
2 '2002-01-14 10:16:09.078001'
3 '2002-01-14 10:18:34.500000'
insert into zxt_t values('4',current_timestamp);
commit;
backup db dwzxt online incremental to C:\dblog\backupf
备份成功。此备份映像的时间戳记是:20020114102135
insert into zxt_t values('5',current_timestamp);
commit;
backup db dwzxt online incremental to C:\dblog\backupf
备份成功。此备份映像的时间戳记是:20020114102253
C:\> db2ckrst -d dwzxt -t 20020114102135 -r database
Suggested restore order of images using timestamp 20020114102135 for
database dwzxt.
====================================================================
restore db dwzxt incremental taken at 20020114102135
restore db dwzxt incremental taken at 20020114101650
restore db dwzxt incremental taken at 20020114102135
====================================================================
C:\>db2 restore db dwzxt incremental from C:\dblog\backupf taken at 20020114102
135
SQL2539W 警告!正在复原至与备份映像数据库相同的现有数据库。数据库文件将被删除。
想要继续吗?(y/n) y
DB20000I RESTORE DATABASE 命令成功完成。
C:\>db2
(c) Copyright IBM Corporation 1993,2003
DB2 SDK 8.2.0 的命令行处理器
可从命令提示符处发出数据库管理器命令和 SQL 语句。例如:
db2 => connect to sample
db2 => bind sample.bnd
要获得一般帮助,输入:?。
要获得命令帮助,输入:? command,其中 command 可以是
数据库管理器命令的前几个关键字。例如:
? CATALOG DATABASE 用于关于 CATALOG DATABASE 命令的帮助
? CATALOG 用于关于所有 CATALOG 命令的帮助。
要退出 db2 交互方式,在命令提示符处输入
QUIT。在非交互方式下,所有命令必须以“db2”作前缀。
要列出当前命令选项设置,输入 LIST COMMAND OPTIONS。
要获取更详细的帮助,请参阅 Online Reference Manual。
db2 => restore db dwzxt incremental from C:\dblog\backupf taken at 200201141016
50
DB20000I RESTORE DATABASE 命令成功完成。
db2 => restore db dwzxt incremental from C:\dblog\backupf taken at 200201141021
35
DB20000I RESTORE DATABASE 命令成功完成。
db2 => rollforward db dwzxt to 2002-01-14-10.21.50.00000 and stop
前滚状态
输入数据库别名 = dwzxt
节点数已返回状态 = 1
节点号 = 0
前滚状态 = 未暂挂
下一个要读取的日志文件 =
已处理的日志文件 = S0000016.LOG - S0000017.LOG
上次落实的事务 = 2002-01-14-02.23.02.000000
DB20000I ROLLFORWARD 命令成功完成。
select * from zxt_t;
---
1 '2002-01-14 10:13:46.046001'
2 '2002-01-14 10:16:09.078001'
3 '2002-01-14 10:18:34.500000'
4 '2002-01-14 10:21:09.468001'
5 '2002-01-14 10:22:26.084001'
db2 => restore db dwzxt incremental from C:\dblog\backupf taken at 2002011410213
5
SQL2539W 警告!正在复原至与备份映像数据库相同的现有数据库。数据库文件将被删除。
想要继续吗?(y/n) y
DB20000I RESTORE DATABASE 命令成功完成。
db2 => restore db dwzxt incremental from C:\dblog\backupf taken at 200201141016
50
DB20000I RESTORE DATABASE 命令成功完成。
db2 => restore db dwzxt incremental from C:\dblog\backupf taken at 200201141021
35
DB20000I RESTORE DATABASE 命令成功完成。
db2 => rollforward db dwzxt to 2002-01-14-10.21.50.00000 USING LOCAL TIME and s
top
前滚状态
输入数据库别名 = dwzxt
节点数已返回状态 = 1
节点号 = 0
前滚状态 = 未暂挂
下一个要读取的日志文件 =
已处理的日志文件 = S0000016.LOG - S0000017.LOG
上次落实的事务 = 2002-01-14-10.21.45.000000
select * from zxt_t;
---
1 '2002-01-14 10:13:46.046001'
2 '2002-01-14 10:16:09.078001'
3 '2002-01-14 10:18:34.500000'
4 '2002-01-14 10:21:09.468001'
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/694276/viewspace-167477/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/694276/viewspace-167477/