IT分销/经销

db2中恢复表空间中的某个container

如果某个tablespace 的container文件意外丢失了，导致这个表空间offline，那么有这个库的全备，或者这个表空间的备份前提下，我是否可以只对这个container进行完全恢复？谢谢。

关注1

参与8

7同行回答
全部行业
全部行业 互联网服务 IT分销/经销 系统集成
|
按赞同排序
按时间排序

fade1986数据库管理员ss

回复 7# yinjun2008

个人观点：是的。收起

IT分销/经销 · 2012-12-18

yinjun2008技术总监神州数码

这个试验是表空间的恢复啊，不是容器级别的恢复，如果多个容器的表空间有一个容器损坏，好像只能进行表空间级别的恢复，以前还真没见过容器级别的恢复呢收起

系统集成 · 2012-12-16

Meral数据库管理员GUOJI

good~收起

互联网服务 · 2012-12-14

somiss系统工程师上海宝信软件南京分公司

nice !!!收起

互联网服务 · 2012-12-11

fade1986数据库管理员ss

做了一下测试，把测试报告发上来了，谢谢大家。
本实验测试表空间的备份和测试:

1)创建一个测试用的表空间
CREATE LARGE TABLESPACE tbs_test MANAGED BY DATABASE  USING (FILE '/home/db2inst1/db2inst1/NODE0000/TESTDB/tbs/TBS_TEST_01' 20M )

create table tab_test1(id int,name varchar(10)) in tbs_test

db2 => insert into tab_test1 values(1,'wzm')
DB20000I  The SQL command completed successfully.

db2 =>  select TABNAME,owner,TBSPACE from syscat.tables where tabname='TAB_TEST1'

TABNAME                                                                                                                         OWNER                                                                                                                         TBSPACE
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
TAB_TEST1                                                                                                                      DB2INST1                                                                                                                      TBS_TEST

2)再创建一个表空间，其实可以略去这步，我只是想看下在恢复的时候会不会影响其他表空间
CREATE LARGE TABLESPACE tbs_test2 MANAGED BY DATABASE  USING (FILE '/home/db2inst1/db2inst1/NODE0000/TESTDB/tbs/TBS_TEST2_01' 20M )

create table tab_test2(id int,name varchar(10)) in tbs_test2

insert into tab_test2 values(1,'wzm')

3）备份第一个表空间

[db2inst1@dbserver bak_db]$ db2 backup db testdb tablespace TBS_TEST online to '/home/db2inst1/bak_db'

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

[db2inst1@dbserver bak_db]$ db2 list history backup all for testdb

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  B  P  20121211110422001 N D  S0000008.LOG S0000008.LOG
----------------------------------------------------------------------------
  Contains 1 tablespace(s):

  00001 TBS_TEST
----------------------------------------------------------------------------
Comment: DB2 BACKUP TESTDB ONLINE
Start Time: 20121211110422
End Time: 20121211110425
   Status: A
----------------------------------------------------------------------------
  EID: 30 Location: /home/db2inst1/bak_db

此时我看了一下表空间的状态，虽然状态是normal,但是查询了一下建在该表空间上的表:
db2 =>  select * from tab_test1
SQL0293N  Error accessing a table space container.  SQLSTATE=57048

4)我重启了实例:

Tablespace ID                      = 5
Name                               = TBS_TEST
Type                               = Database managed space
Contents                            = All permanent data. Large table space.
State                               = 0x4000
Detailed explanation:
   Offline

5)现在我开始恢复这个表空间
db2 => restore db testdb tablespace (tbs_test) online taken at 20121211110422
DB20000I  The RESTORE DATABASE command completed successfully.

Tablespace ID                      = 5
Name                               = TBS_TEST
Type                               = Database managed space
Contents                            = All permanent data. Large table space.
State                               = 0x0080
Detailed explanation:
   Roll forward pending

6)把这个表空间向前滚
db2 => rollforward db testdb to end of logs tablespace (tbs_test) online

                              Rollforward Status

Input database alias                = testdb
Number of nodes have returned status = 1

Node number                         = 0
Rollforward status                   = not pending
Next log file to be read             =
Log files processed                   =  -
Last committed transaction          = 2012-11-21-13.20.35.000000 Local

DB20000I  The ROLLFORWARD command completed successfully.

Tablespace ID                      = 5
Name                               = TBS_TEST
Type                               = Database managed space
Contents                            = All permanent data. Large table space.
State                               = 0x0000
Detailed explanation:
   Normal

db2 => select * from tab_test1

ID       NAME
----------- ----------
      1 wzm

  1 record(s) selected.

至此,done.收起

IT分销/经销 · 2012-12-11