问题:我有一个DB2的备份要拿到
新环境来恢复,但由于某些原因,比如磁盘空间不足,只想恢复其中的一个表空间,如何做?
方法:可以使用表空间级别的恢复,测试过程如下
-------------
2.) 进行表空间级别的恢复,以表空间TBS1为例(要求数据库在备份之前使用了归档日志)
从上面的输出中可以看到表空间TBS1的状态是正常的,完成恢复,可以正常访问里面的数据。表空间TBS2、TBS3都是restore pending.
方法:可以使用表空间级别的恢复,测试过程如下
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