mysql 前滚指定时间_DB2关于指定时间点前滚恢复方法 | 学步园

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 ~ 7]$db2 drop db sample

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]$

(4)查看时间标记

[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):

00001 SYSCATSPACE

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):

00001 SYSCATSPACE

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):

00001 SYSCATSPACE

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):

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/中。

其中，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/)"

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 = 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

[db2inst1@bytest ~ 35]$

此时，数据库是可用状态。