DB2 只进行单个表空间的恢复

问题：我有一个DB2的备份要拿到 新环境来恢复，但由于某些原因，比如磁盘空间不足，只想恢复其中的一个表空间，如何做？


方法：可以使用表空间级别的恢复，测试过程如下

1.) 查看当前数据库的状态，备份、并删除数据库

$ db2pd -db test -tab

Database Member 0 -- Database TEST -- Active -- Up 0 days 00:00:06 -- Date 2016-05-04-13.35.10.021000

Tablespace Configuration:
Address            Id    Type Content ...  Name
0x0000000016E2FF40 0     DMS  Regular ...  SYSCATSPACE
0x000000001CA60080 1     SMS  SysTmp  ...  TEMPSPACE1
0x000000001CA6D220 2     DMS  Large   ...  TBS1
0x000000001CA7A3C0 3     DMS  Large   ...  SYSTOOLSPACE
0x000000001CA87560 4     DMS  Large   ...  TBS2
0x000000001CA94700 5     DMS  Large   ...  TBS3

Tablespace Statistics:
Address            Id    ....   State      
0x0000000016E2FF40 0     ....   0x00000000 
0x000000001CA60080 1     ....   0x00000000 
0x000000001CA6D220 2     ....   0x00000000 
0x000000001CA7A3C0 3     ....   0x00000000 
0x000000001CA87560 4     ....   0x00000000 
0x000000001CA94700 5     ....   0x00000000 

Tablespace Autoresize Statistics:
Address            Id    AS  AR  InitSize   
0x0000000016E2FF40 0     Yes Yes 33554432   
0x000000001CA60080 1     Yes No  0          
0x000000001CA6D220 2     No  No  -4096      
0x000000001CA7A3C0 3     Yes Yes 33554432   
0x000000001CA87560 4     No  No  -4096      
0x000000001CA94700 5     No  No  -4096      

..

Containers:
Address            TspId ....  Container
0x0000000016E2BA20 0     ....  C:\DB2INST1\NODE0000\TEST\T0000000\C0000000.CAT
0x000000001CAA2000 1     ....  C:\DB2INST1\NODE0000\TEST\T0000001\C0000000.TMP
0x0000000016E3F5C0 2     ....  c:\conpath\con1
0x0000000016E3FDA0 3     ....  C:\DB2INST1\NODE0000\TEST\T0000003\C0000000.LRG
0x0000000016E20520 4     ....  c:\conpath\con2
0x0000000016E20B60 5     ....  c:\conpath\con3

$ db2 "backup db test to c:\"
Backup successful. The timestamp for this backup image is : 20160504133600

$ db2 "drop db test"
DB20000I  The DROP DATABASE command completed successfully.

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

由于数据库已经被彻底删掉，因此，这相当于模拟出了一个新环境：

2.) 进行表空间级别的恢复，以表空间TBS1为例（要求数据库在备份之前使用了归档日志）

$ db2 "restore db test rebuild with tablespace(syscatspace, tbs1) from c: redirect"
SQL1277W  A redirected restore operation is being performed. During a table
space restore, only table spaces being restored can have their paths
reconfigured. During a database restore, storage group storage paths and DMS
table space containers can be reconfigured.
DB20000I  The RESTORE DATABASE command completed successfully.

//注意，set tablespace container仅仅是针对非automatic storage类型的容器做，如果表空间是automatic storage类型的，可以跳过此步
$ db2 "set tablespace containers for 2 using(file C:\CONPATH\newpath\con001 80000)"
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

$ db2 "restore db test continue"
DB20000I  The RESTORE DATABASE command completed successfully.

//  rollforward的时候，可以加上OVERFLOW LOG PATH，来指定日志的位置，并且不一定要rollfoward到‘end of log'，也可以指定一个时间戳
$ db2 "rollforward db test to end of logs and stop"
SQL1271W  Database "TEST" is recovered but one or more table spaces are
offline on members or nodes "0".

$ db2 "activate db test"
DB20000I  The ACTIVATE DATABASE command completed successfully.

$ db2pd -db test -tablespaces

Database Member 0 -- Database TEST -- Active -- Up 0 days 00:00:02 -- Date 2016-05-04-14.12.06.773000

Tablespace Configuration:
Address            Id    Type Content PageSz ..  Name
0x00000000159DFF40 0     DMS  Regular 4096   ..  SYSCATSPACE
0x000000001A3A0080 1     SMS  SysTmp  4096   ..  TEMPSPACE1
0x000000001A3AD220 2     DMS  Large   4096   ..  TBS1
0x000000001A3BA3C0 3     DMS  Large   4096   ..  SYSTOOLSPACE
0x000000001A3C7560 4     DMS  Large   4096   ..  TBS2
0x000000001A3D4700 5     DMS  Large   4096   ..  TBS3

Tablespace Statistics:
Address            Id    TotalPgs   UsablePgs  UsedPgs  ..   State      
0x00000000159DFF40 0     32768      32764      27492    ..   0x00000000 
0x000000001A3A0080 1     1          1          1        ..   0x00000000 
0x000000001A3AD220 2     80000      79968      23648    ..   0x00000000 
0x000000001A3BA3C0 3     8192       8188       0        ..   0x00000100 
0x000000001A3C7560 4     100000     99968      0        ..   0x00000100 
0x000000001A3D4700 5     100000     99968      0        ..   0x00000100 

Tablespace Autoresize Statistics:
Address            Id    AS  AR  InitSize ..
0x00000000159DFF40 0     Yes Yes 33554432 ..
0x000000001A3A0080 1     Yes No  0        ..
0x000000001A3AD220 2     No  No  -4096    ..
0x000000001A3BA3C0 3     Yes Yes 33554432 ..
0x000000001A3C7560 4     No  No  -4096    ..
0x000000001A3D4700 5     No  No  -4096    ..

Containers:
Address            TspId ContainNum Type   ..  Container
0x00000000159DBA20 0     0          File   ..  C:\DB2INST1\NODE0000\TEST\T0000000\C0000000.CAT
0x000000001A3E2000 1     0          Path   ..  C:\DB2INST1\NODE0000\TEST\T0000001\C0000000.TMP
0x00000000159EF5C0 2     0          File   ..  C:\CONPATH\newpath\con001
0x00000000159CCBE0 4     0          File   ..  c:\conpath\con2
0x00000000159D0920 5     0          File   ..  c:\conpath\con3

从上面的输出中可以看到表空间TBS1的状态是正常的，完成恢复，可以正常访问里面的数据。表空间TBS2、TBS3都是restore pending.

查看容器目录，发现c:\conpath\newpath\con1存在了， 但c:\conpath\con2, c:\conpath\con3 不存在，达到目地。

http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001978.html?cp=SSEPGG_9.7.0%2F3-6-2-4-106&lang=en