oracle db2备份数据库,DB2备份恢复(增量备份与恢复) – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 134296...

全备数据库

[db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp include logs

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

[db2inst1@xifenfei ~]$ db2 list history backup all for xff

List History File for xff

Number of matching file entries = 1

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

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

B D 20120411181918001 N D S0000015.LOG S0000015.LOG

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

Contains 3 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 SYSTOOLSPACE

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

Comment: DB2 BACKUP XFF ONLINE

Start Time: 20120411181918

End Time: 20120411181925

Status: A

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

EID: 27 Location: /tmp

修改数据

[db2inst1@xifenfei ~]$ db2 list tables

Table/View Schema Type Creation time

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

T_XFF DB2INST1 T 2012-04-05-09.45.29.148434

T_XIFENFEI DB2INST1 T 2012-04-06-05.50.11.111469

T_XIFENFEI01 DB2INST1 T 2012-04-11-16.55.51.853649

3 record(s) selected.

[db2inst1@xifenfei ~]$ db2 "drop table t_xff"

DB20000I The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2 "drop table t_xifenfei"

DB20000I The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2 "drop table t_xifenfei01"

DB20000I The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2 "create table t_01xff like syscat.tables"

DB20000I The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2 "insert into t_01xff select * from syscat.tables"

DB20000I The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2 "select count(*) from t_01xff"

1

-----------

370

1 record(s) selected.

[db2inst1@xifenfei ~]$ db2 list tables

Table/View Schema Type Creation time

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

T_01XFF DB2INST1 T 2012-04-11-18.23.05.723478

1 record(s) selected.

增量备份SQL2426N解决

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental to /tmp

SQL2426N The database has not been configured to allow the incremental backup

operation. Reason code = "1".

[db2inst1@xifenfei ~]$ db2 ? SQL2426N

SQL2426N The database has not been configured to allow the incremental

backup operation. Reason code = "".

Explanation:

Incremental backups are not enabled for a table space until after

modification tracking has been activated for the database and a

non-incremental backup has been performed on the table space.

Possible reason codes:

1. The configuration parameter TRACKMOD has not been set for the

database.

2. The TRACKMOD configuration parameter has been set but at least one

table space has not had a non-incremental backup taken since the

TRACKMOD parameter was set.

User response:

The action is based on the reason code as follows:

1. Activate modification tracking for the database by setting the

TRACKMOD database configuration parameter to on, then perform a full

database backup.

2. Consult the db2diag.log file to determine the name of the table

space, then perform a full backup of that table space.

[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep TRACKMOD

Track modified pages (TRACKMOD) = NO

[db2inst1@xifenfei ~]$ db2 update db cfg for xff using TRACKMOD ON

DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.

SQL1363W One or more of the parameters submitted for immediate modification

were not changed dynamically. For these configuration parameters, the database

must be shutdown and reactivated before the configuration parameter changes

become effective.

[db2inst1@xifenfei ~]$ db2stop force

04/11/2012 17:49:59 0 0 SQL1064N DB2STOP processing was successful.

SQL1064N DB2STOP processing was successful.

[db2inst1@xifenfei ~]$ db2start

04/11/2012 17:50:09 0 0 SQL1063N DB2START processing was successful.

SQL1063N DB2START processing was successful.

累积增量备份

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental to /tmp

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

[db2inst1@xifenfei ~]$ db2 list history backup all for xff

List History File for xff

Number of matching file entries = 2

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

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

B D 20120411181918001 N D S0000015.LOG S0000015.LOG

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

Contains 3 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 SYSTOOLSPACE

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

Comment: DB2 BACKUP XFF ONLINE

Start Time: 20120411181918

End Time: 20120411181925

Status: A

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

EID: 27 Location: /tmp

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

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

B D 20120411182708001 O D S0000017.LOG S0000017.LOG

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

Contains 3 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 SYSTOOLSPACE

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

Comment: DB2 BACKUP XFF ONLINE

Start Time: 20120411182708

End Time: 20120411182712

Status: A

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

EID: 33 Location: /tmp

再次修改数据

[db2inst1@xifenfei ~]$ db2 "create table t_02xff like syscat.tables"

DB20000I The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2 "insert into t_02xff select * from syscat.tables"

DB20000I The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2 list tables

Table/View Schema Type Creation time

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

T_01XFF DB2INST1 T 2012-04-11-18.23.05.723478

T_02XFF DB2INST1 T 2012-04-11-18.30.26.639326

2 record(s) selected.

[db2inst1@xifenfei ~]$ db2 "select count(*) from t_02xff"

1

-----------

371

1 record(s) selected.

迭代备份

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental delta to /tmp

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

[db2inst1@xifenfei ~]$ ll /tmp/XFF*

-rw------- 1 db2inst1 db2iadm1 122044416 Apr 11 18:19 /tmp/XFF.0.db2inst1.NODE0000.CATN0000.20120411181918.001

-rw------- 1 db2inst1 db2iadm1 55128064 Apr 11 18:27 /tmp/XFF.0.db2inst1.NODE0000.CATN0000.20120411182708.001

-rw------- 1 db2inst1 db2iadm1 55128064 Apr 11 18:31 /tmp/XFF.0.db2inst1.NODE0000.CATN0000.20120411183129.001

--这里可以看出最近一次的增量备份和迭代备份备份文件大小相同,说明迭代备份是在最近一次增量备份基础之上进行

[db2inst1@xifenfei ~]$ db2 list history backup all for xff

List History File for xff

Number of matching file entries = 3

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

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

B D 20120411181918001 N D S0000015.LOG S0000015.LOG

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

Contains 3 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 SYSTOOLSPACE

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

Comment: DB2 BACKUP XFF ONLINE

Start Time: 20120411181918

End Time: 20120411181925

Status: A

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

EID: 27 Location: /tmp

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

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

B D 20120411182708001 O D S0000017.LOG S0000017.LOG

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

Contains 3 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 SYSTOOLSPACE

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

Comment: DB2 BACKUP XFF ONLINE

Start Time: 20120411182708

End Time: 20120411182712

Status: A

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

EID: 33 Location: /tmp

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

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

B D 20120411183129001 E D S0000019.LOG S0000019.LOG

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

Contains 3 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 SYSTOOLSPACE

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

Comment: DB2 BACKUP XFF ONLINE

Start Time: 20120411183129

End Time: 20120411183133

Status: A

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

EID: 36 Location: /tmp

还原数据库

[db2inst1@xifenfei ~]$ db2 restore db xff incremental automatic from /tmp taken at 20120411183129

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@xifenfei ~]$ db2 connect to xff

SQL1117N A connection to or activation of database "XIFENFEI" cannot be made

because of ROLL-FORWARD PENDING. SQLSTATE=57019

--数据库需要前滚,因为是在线备份

恢复数据库

[db2inst1@xifenfei ~]$ db2 "rollforward db xff to end of logs and stop"

Rollforward Status

Input database alias = xff

Number of nodes have returned status = 1

Node number = 0

Rollforward status = not pending

Next log file to be read =

Log files processed = S0000019.LOG - S0000019.LOG

Last committed transaction = 2012-04-11-10.31.30.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.

验证恢复过程

[db2inst1@xifenfei ~]$ db2 connect to xff

Database Connection Information

Database server = DB2/LINUX 9.5.9

SQL authorization ID = DB2INST1

Local database alias = XFF

[db2inst1@xifenfei ~]$ db2 list tables

Table/View Schema Type Creation time

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

T_01XFF DB2INST1 T 2012-04-11-18.23.05.723478

T_02XFF DB2INST1 T 2012-04-11-18.30.26.639326

2 record(s) selected.

[db2inst1@xifenfei ~]$ db2 "select count(*) from t_01xff"

1

-----------

370

1 record(s) selected.

[db2inst1@xifenfei ~]$ db2 "select count(*) from t_02xff"

1

-----------

371

1 record(s) selected.

本篇主要测试了增量备份和数据库恢复,在下篇中将对不完全恢复进行测试,坚持逐步学习db2数据库相关知识