DB2归档及恢复试验

[试验环境]
1.单节点:Windows XP, DB2 9.5
2.8+1节点:AIX 5.2, DB2 9.5 + DPF

[试验步骤]
1. 修改日志文件大小参数

修改日志文件大小参数,改到最小,否则很难搞到日志爆满。
这是日志的原始大小:

$ls -l
total 24048
-rw——- 1 db2inst1 db2iadm1 4104192 Apr 09 10:46 S0000000.LOG
-rw——- 1 db2inst1 db2iadm1 4104192 Apr 09 10:46 S0000001.LOG
-rw——- 1 db2inst1 db2iadm1 4104192 Apr 09 10:46 S0000002.LOG

执行以下命令:

db2 “force applications all”
db2 terminate
db2 connect to sample
db2 “update db cfg for sample using logfilsiz 4″

这里执行完即可发现C:\DB2\NODE0000\SQL00002\SQLOGDIR目录下的日志都变成很小的了:

$ls -l
total 144
-rw——- 1 db2inst1 db2iadm1 24576 Apr 09 10:47 S0000000.LOG
-rw——- 1 db2inst1 db2iadm1 24576 Apr 09 10:47 S0000001.LOG
-rw——- 1 db2inst1 db2iadm1 24576 Apr 09 10:47 S0000002.LOG

注意改成4个4kpagesize后,实际上日志大小有6个4kpagesize。
说明日志的实际大小为:logfilsiz*4k+2k。

另:日志最小为4个4k pagesize,否则会报错:

$db2 “update db cfg for sample using logfilsiz 1″
SQL5130N The value specified for the configuration parameter “logfilsiz” is
not in the valid range of “4″ to “1048572″.

2. 修改LOGARCHMETH1参数以指定备份目录

修改LOGARCHMETH1 参数,LOGARCHMETH2这里就不改了,这样只备份至一个地方

db2 “update db cfg for sample using LOGARCHMETH1 disk:/db2home2/db2inst2/dbbackup”

注意,修改该参数之前,必须保证该备份目录存在,否则会报错:

$db2 “update db cfg for sample using LOGARCHMETH1 disk:c:/db2/backup”
SQL5099N 由数据库配置参数 “LOGARCHMETH1″ 指示的值 “c:/db2/backup”
无效,原因码为 “2″。 SQLSTATE=08004

完了之后,尝试连接,可以发现归档目录自动创建了一些文件夹:
F:\db2backup\DB2\SAMPLE\NODE0000\C0000000
这时,如果连接数据库会报错:
中文环境:

$db2 connect to sample
SQL1116N 因为 BACKUP PENDING,所以不能连接或激活数据库 “SAMPLE”。
SQLSTATE=57019

英文环境:

$db2 connect to sample
SQL1116N A connection to or activation of database “SAMPLE” cannot be made
because of BACKUP PENDING. SQLSTATE=57019

3. 发出备份命令备份数据库

如果是单节点数据库,则执行如下命令:

db2 backup db sample TO /db2home2/db2inst2/dbbackup

如果是DPF多节点数据库,必须在所有节点上执行备份,否则查询跨分区表空间仍会报错:

$db2 “select count(*) from act”
1
———–
SQL1116N A connection to or activation of database “SAMPLE” cannot be made
because of BACKUP PENDING. SQLSTATE=57019

$db2 “select * from act”
ACTNO ACTKWD ACTDESC
—— —— ——————–
10 MANAGE MANAGE/ADVISE
20 ECOST ESTIMATE COST
70 CODE CODE PROGRAMS
80 TEST TEST PROGRAMS
170 ADMDC ADM DATA COMM
SQL1116N A connection to or activation of database “SAMPLE” cannot be made
because of BACKUP PENDING. SQLSTATE=57019

因此,在DPF多节点数据库环境,则执行如下命令:

$db2 BACKUP db sample ON ALL DBPARTITIONNUMS TO /db2home2/db2inst2/dbbackup
Part Result
—- ————————————————————————
0000 DB20000I The BACKUP DATABASE command completed successfully.
0001 DB20000I The BACKUP DATABASE command completed successfully.
0002 DB20000I The BACKUP DATABASE command completed successfully.
0003 DB20000I The BACKUP DATABASE command completed successfully.
0004 DB20000I The BACKUP DATABASE command completed successfully.
0005 DB20000I The BACKUP DATABASE command completed successfully.
0006 DB20000I The BACKUP DATABASE command completed successfully.
0007 DB20000I The BACKUP DATABASE command completed successfully.
0008 DB20000I The BACKUP DATABASE command completed successfully.
Backup successful. The timestamp for this backup image is : 20100409114802

可以使用list backup命令进行查询:

$db2 list backup all for db sample

List History File for sample
Number of matching file entries = 2
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
– — —————— —- — ———— ———— ————–
B D 20100409112609001 F D S0000000.LOG S0000000.LOG
—————————————————————————-
Contains 3 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 IBMDB2SAMPLEREL
—————————————————————————-
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20100409112609
End Time: 20100409112611
Status: A
—————————————————————————-
EID: 3 Location: /db2home2/db2inst2/dbbackup
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
– — —————— —- — ———— ———— ————–
B D 20100409114802001 F D S0000030.LOG S0000030.LOG
—————————————————————————-
Contains 3 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 IBMDB2SAMPLEREL
—————————————————————————-
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20100409114802
End Time: 20100409114812
Status: A
—————————————————————————-
EID: 34 Location: /db2home2/db2inst2/dbbackup

这时,查看备份目录的文件:

$ls -l
total 958568
-rw——- 1 db2inst2 db2iadm1 102588416 Apr 09 11:26 SAMPLE.0.db2inst2.NODE0000.CATN0000.20100409112609.001
-rw——- 1 db2inst2 db2iadm1 102588416 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0000.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0001.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0002.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0003.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0004.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0005.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0006.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0007.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0008.CATN0000.20100409114802.001
drwxr-x— 3 db2inst2 db2iadm1 256 Apr 09 11:20 db2inst2

4. 做一些insert、update、delete等DML操作

5. 发出恢复命令恢复数据库

恢复数据库操作,数据库进入ROLL-FORWARD PENDING状态

$db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing without prompting”
SQL2540W Restore is successful, however a warning “2539″ was encountered
during Database Restore while processing in No Interrupt mode.

这时如果连接数据库,会报如下错误:

$db2 connect to sample
SQL1117N A connection to or activation of database “SAMPLE” cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019

如果是多分区数据库,则使用如下语句:

db2_all ‘<<+0< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+1< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+2< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+3< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+4< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+5< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+6< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+7< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+8< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘

查询状态,注意默认时间是UTC时间:

$db2 “rollforward db sample query status”

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 DB pending S0000030.LOG – 2010-04-09-03.48.06.000000 UTC
1 not pending – 1970-01-01-00.00.00.000000 UTC
2 not pending – 1970-01-01-00.00.00.000000 UTC
3 not pending – 1970-01-01-00.00.00.000000 UTC
4 not pending – 1970-01-01-00.00.00.000000 UTC
5 not pending – 1970-01-01-00.00.00.000000 UTC
6 not pending – 1970-01-01-00.00.00.000000 UTC
7 not pending – 1970-01-01-00.00.00.000000 UTC
8 not pending – 1970-01-01-00.00.00.000000 UTC

$db2 “rollforward db sample query status”

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 DB pending S0000030.LOG – 2010-04-09-03.48.06.000000 UTC
1 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC
2 DB pending S0000000.LOG – 2010-04-09-03.48.10.000000 UTC
3 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC
4 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC
5 DB pending S0000000.LOG – 2010-04-09-03.48.10.000000 UTC
6 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC
7 DB pending S0000000.LOG – 2010-04-09-03.48.10.000000 UTC
8 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC

$db2 “rollforward db sample query status using local time”

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 DB pending S0000030.LOG – 2010-04-09-11.48.06.000000 Local
1 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local
2 DB pending S0000000.LOG – 2010-04-09-11.48.10.000000 Local
3 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local
4 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local
5 DB pending S0000000.LOG – 2010-04-09-11.48.10.000000 Local
6 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local
7 DB pending S0000000.LOG – 2010-04-09-11.48.10.000000 Local

8 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local

6. 执行前滚操作

执行以下命令:

db2 rollforward db sample to end of logs
db2 rollforward db sample complete

以下是操作日志:

$db2 rollforward db sample to end of logs

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 DB working S0000383.LOG S0000030.LOG-S0000382.LOG 2010-04-09-14.15.03.000000 Local
1 DB working S0000004.LOG S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
2 DB working S0000004.LOG S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
3 DB working S0000004.LOG S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
4 DB working S0000001.LOG S0000000.LOG-S0000001.LOG 2010-04-09-13.51.53.000000 Local
5 DB working S0000005.LOG S0000000.LOG-S0000005.LOG 2010-04-09-13.51.53.000000 Local
6 DB working S0000002.LOG S0000000.LOG-S0000002.LOG 2010-04-09-13.51.53.000000 Local
7 DB working S0000002.LOG S0000000.LOG-S0000002.LOG 2010-04-09-13.51.53.000000 Local
8 DB working S0000004.LOG S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local

DB20000I The ROLLFORWARD command completed successfully.

这个时候还没完,还必须发出一个complete命令,否则连接数据库还是报错。

$db2 rollforward db sample complete

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 not pending S0000030.LOG-S0000383.LOG 2010-04-09-14.15.03.000000 Local
1 not pending S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
2 not pending S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
3 not pending S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
4 not pending S0000000.LOG-S0000001.LOG 2010-04-09-13.51.53.000000 Local
5 not pending S0000000.LOG-S0000005.LOG 2010-04-09-13.51.53.000000 Local
6 not pending S0000000.LOG-S0000002.LOG 2010-04-09-13.51.53.000000 Local
7 not pending S0000000.LOG-S0000002.LOG 2010-04-09-13.51.53.000000 Local
8 not pending S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local

DB20000I The ROLLFORWARD command completed successfully.

7. 完成。数据库恢复正常。


Data Warehouse For Ever原创文章,转载请注明出处

  1. it was very interesting to read.
    I want to quote your post in my blog. It can?
    And you et an account on Twitter?

  2. Yes, you can quote my post. My site url will be: http://www.dw4e.com in 2 weeks later.
    My twitter account is luojp. But twitter was blocked in China. :(

  3. I would like to exchange links with your site dw4ecom.xmp04.host.35.com
    Is this possible?

  4. enable it to settle towards the ground when feasible. It is actually really considerably

  5. It is not better to select high-heeled shoe, if you are an obese.

    All footwear at Bourne is hand-crafted especially by Opanka construction and the use
    of adhesives and machinery is minimal while creating the shoes, thus making the designs eco-friendly.
    1990′s – Bra became a highlight on women’s wardrobe.

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>