分类： DB2/Informix
2013-07-27 21:28:59
1、对数据库进行在线全备
Db2 backup database test online
2、查看备份过程中的表空间状态
db2 "select DBPARTITIONNUM, tbsp _id,substr(tbsp _name,1,20) as tbsname,tbsp _state from sysibmadm.tbsp>
DBPARTITIONNUM TBSP_ID TBSNAME TBSP_STATE
-------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 0 SYSCATSPACE BACKUP_IN_PROGRESS
0 1 SYSTOOLSPACE BACKUP_IN_PROGRESS
0 2 SYSTOOLSTMPSPACE NORMAL
0 3 PSAPTEMP NORMAL
0 4 PSAPBTABD BACKUP_IN_PROGRESS
0 5 PSAPBTABI BACKUP_IN_PROGRESS
0 6 PSAPCLUD BACKUP_IN_PROGRESS
0 7 PSAPCLUI BACKUP_IN_PROGRESS
0 8 PSAPDDICD BACKUP_IN_PROGRESS
0 9 PSAPDDICI BACKUP_IN_PROGRESS
0 10 PSAPDOCUD BACKUP_IN_PROGRESS
0 11 PSAPDOCUI BACKUP_IN_PROGRESS
0 12 PSAPEL46CD BACKUP_IN_PROGRESS
0 13 PSAPEL46CI BACKUP_IN_PROGRESS
0 14 PSAPES46CD BACKUP_IN_PROGRESS
0 15 PSAPES46CI BACKUP_IN_PROGRESS
0 16 PSAPLOADD BACKUP_IN_PROGRESS
0 17 PSAPLOADI BACKUP_IN_PROGRESS
0 18 PSAPPOOLD BACKUP_IN_PROGRESS
0 19 PSAPPOOLI BACKUP_IN_PROGRESS
0 20 PSAPPROTD BACKUP_IN_PROGRESS
0 21 PSAPPROTI BACKUP_IN_PROGRESS
0 22 PSAPSOURCED BACKUP_IN_PROGRESS
0 23 PSAPSOURCEI BACKUP_IN_PROGRESS
0 24 PSAPSTABD BACKUP_IN_PROGRESS
0 25 PSAPSTABI BACKUP_IN_PROGRESS
0 26 PSAPUSER1D BACKUP_IN_PROGRESS
0 27 PSAPUSER1I BACKUP_IN_PROGRESS
28 record(s) selected.
可以看见，备份过程中，除了俩个临时表空间（临时表空间不会被备份）的状态是normal，其他的表空间都是在备份一启动就被设置为backup in progress状态。
也可以用list命令查看
db2 list tablespaces show detail
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 436800
Useable pages = 436480
Used pages = 436320
Free pages = 160
High water mark (pages) = 436320
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 3200
Number of containers = 10
Minimum recovery time = 2010-11-28-01.19.43.000000
Tablespace ID = 1
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 25600
Useable pages = 25520
Used pages = 2656
Free pages = 22864
High water mark (pages) = 2656
Page size (bytes) = 4096
Extent size (pages) = 8
Prefetch size (pages) = 800
Number of containers = 10
Minimum recovery time = 2009-03-28-14.40.05.000000
Tablespace ID = 2
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 10
Useable pages = 10
Used pages = 10
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 16384
Extent size (pages) = 8
Prefetch size (pages) = 800
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.28.000000
Tablespace ID = 3
Name = PSAPTEMP
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 16316
Useable pages = 16316
Used pages = 16316
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 1280
Number of containers = 4
Minimum recovery time = 2009-03-28-12.23.19.000000
Tablespace ID = 4
Name = PSAPBTABD
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 13064320
Useable pages = 13064160
Used pages = 13061232
Free pages = 2928
High water mark (pages) = 13061232
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 1600
Number of containers = 10
Minimum recovery time = 2012-03-15-16.42.49.000000
Tablespace ID = 5
Name = PSAPBTABI
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 4385360
Useable pages = 4385280
Used pages = 4381616
Free pages = 3664
High water mark (pages) = 4381616
Page size (bytes) = 4096
Extent size (pages) = 8
Prefetch size (pages) = 800
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.41.000000
Tablespace ID = 6
Name = PSAPCLUD
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 1048000
Useable pages = 1047040
Used pages = 1035712
Free pages = 11328
High water mark (pages) = 1035712
Page size (bytes) = 4096
Extent size (pages) = 64
Prefetch size (pages) = 6400
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.41.000000
Tablespace ID = 7
Name = PSAPCLUI
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 1048000
Useable pages = 1047680
Used pages = 89152
Free pages = 958528
High water mark (pages) = 89152
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 3200
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.41.000000
Tablespace ID = 8
Name = PSAPDDICD
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 260000
Useable pages = 259840
Used pages = 169264
Free pages = 90576
High water mark (pages) = 232752
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 1600
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.42.000000
Tablespace ID = 9
Name = PSAPDDICI
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 260000
Useable pages = 259840
Used pages = 70496
Free pages = 189344
High water mark (pages) = 96144
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 1600
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.42.000000
Tablespace ID = 10
Name = PSAPDOCUD
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 65000
Useable pages = 64640
Used pages = 13728
Free pages = 50912
High water mark (pages) = 13728
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 3200
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.42.000000
Tablespace ID = 11
Name = PSAPDOCUI
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 65000
Useable pages = 64640
Used pages = 10400
Free pages = 54240
High water mark (pages) = 17184
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 3200
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.42.000000
Tablespace ID = 12
Name = PSAPEL46CD
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 1048000
Useable pages = 1047840
Used pages = 207376
Free pages = 840464
High water mark (pages) = 278976
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 1600
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.43.000000
Tablespace ID = 13
Name = PSAPEL46CI
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 1300000
Useable pages = 1299840
Used pages = 6160
Free pages = 1293680
High water mark (pages) = 12064
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 1600
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.43.000000
Tablespace ID = 14
Name = PSAPES46CD
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 1301440
Useable pages = 1301280
Used pages = 1155440
Free pages = 145840
High water mark (pages) = 1299984
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 1600
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.43.000000
Tablespace ID = 15
Name = PSAPES46CI
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 1048000
Useable pages = 1047840
Used pages = 420128
Free pages = 627712
High water mark (pages) = 455056
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 1600
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.43.000000
Tablespace ID = 16
Name = PSAPLOADD
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 65000
Useable pages = 64000
Used pages = 1344
Free pages = 62656
High water mark (pages) = 1920
Page size (bytes) = 4096
Extent size (pages) = 64
Prefetch size (pages) = 6400
Number of containers = 10
Minimum recovery time = 2011-12-18-00.41.02.000000
Tablespace ID = 17
Name = PSAPLOADI
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 65000
Useable pages = 64640
Used pages = 544
Free pages = 64096
High water mark (pages) = 608
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 3200
Number of containers = 10
Minimum recovery time = 2009-03-28-12.21.43.000000
Tablespace ID = 18
Name = PSAPPOOLD
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 780000
Useable pages = 779920
Used pages = 470608
Free pages = 309312
High water mark (pages) = 512000
Page size (bytes) = 4096
Extent size (pages) = 8
Prefetch size (pages) = 800
Number of containers = 10
Minimum recovery time = 2012-03-15-16.42.50.000000
Tablespace ID = 19
Name = PSAPPOOLI
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 780000
Useable pages = 779920
Used pages = 333424
Free pages = 446496
High water mark (pages) = 342664
Page size (bytes) = 4096
Extent size (pages) = 8
Prefetch size (pages) = 800
Number of containers = 10
Minimum recovery time = 2009-03-28-12.22.00.000000
Tablespace ID = 20
Name = PSAPPROTD
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 130000
Useable pages = 129600
Used pages = 35904
Free pages = 93696
High water mark (pages) = 35904
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 3200
Number of containers = 10
Minimum recovery time = 2009-03-28-12.22.00.000000
Tablespace ID = 21
Name = PSAPPROTI
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 130000
Useable pages = 129760
Used pages = 16272
Free pages = 113488
High water mark (pages) = 19776
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 1600
Number of containers = 10
Minimum recovery time = 2009-03-28-12.22.00.000000
Tablespace ID = 22
Name = PSAPSOURCED
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 130000
Useable pages = 129280
Used pages = 34496
Free pages = 94784
High water mark (pages) = 48320
Page size (bytes) = 4096
Extent size (pages) = 64
Prefetch size (pages) = 6400
Number of containers = 10
Minimum recovery time = 2009-03-28-12.22.00.000000
Tablespace ID = 23
Name = PSAPSOURCEI
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 130000
Useable pages = 129600
Used pages = 33952
Free pages = 95648
High water mark (pages) = 51776
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 3200
Number of containers = 10
Minimum recovery time = 2009-03-28-12.22.01.000000
Tablespace ID = 24
Name = PSAPSTABD
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 1280000
Useable pages = 1279920
Used pages = 980648
Free pages = 299272
High water mark (pages) = 980648
Page size (bytes) = 4096
Extent size (pages) = 8
Prefetch size (pages) = 800
Number of containers = 10
Minimum recovery time = 2012-03-15-16.42.44.000000
Tablespace ID = 25
Name = PSAPSTABI
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 780000
Useable pages = 779920
Used pages = 573736
Free pages = 206184
High water mark (pages) = 573736
Page size (bytes) = 4096
Extent size (pages) = 8
Prefetch size (pages) = 800
Number of containers = 10
Minimum recovery time = 2009-03-28-12.22.07.000000
Tablespace ID = 26
Name = PSAPUSER1D
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 212990
Useable pages = 212800
Used pages = 118448
Free pages = 94352
High water mark (pages) = 118448
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 1600
Number of containers = 10
Minimum recovery time = 2009-09-28-04.01.35.000000
Tablespace ID = 27
Name = PSAPUSER1I
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 1409020
Useable pages = 1408880
Used pages = 94624
Free pages = 1314256
High water mark (pages) = 94624
Page size (bytes) = 4096
Extent size (pages) = 8
Prefetch size (pages) = 800
Number of containers = 10
Minimum recovery time = 2009-03-28-12.22.07.000000
同样，处于备份中的表空间，其状态是0x0800。
2、进行表空间备份，查看被备份的表空间状态和其他没有被备份的表空间状态
db2 backup database tablespace NIT#ES701D online to /db2/NIT/db2backup
查看表空间状态
Tablespace ID = 17
Name = NIT#DOCUI
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 6400
Useable pages = 6392
Used pages = 1226
Free pages = 5166
High water mark (pages) = 1226
Page size (bytes) = 16384
Extent size (pages) = 2
Prefetch size (pages) = 8
Number of containers = 4
Tablespace ID = 18
Name = NIT#ES701D
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0800
Detailed explanation:
Backup in progress
Total pages = 330752
Useable pages = 330744
Used pages = 322154
Free pages = 8590
High water mark (pages) = 323410
Page size (bytes) = 16384
Extent size (pages) = 2
Prefetch size (pages) = 8
Number of containers = 4
被备份的表空间的状态是0x0800，其他没有被备份的表空间的状态是0x0000.
通过list history命令，也只有被备份的表空间的一段信息输出
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B P 20130727045205001 N D S0000151.LOG S0000151.LOG
----------------------------------------------------------------------------
Contains 1 tablespace(s):
00001 NIT#ES701D
----------------------------------------------------------------------------
Comment: DB2 BACKUP NIT ONLINE
Start Time: 20130727045205
End Time: 20130727045312
Status: A
----------------------------------------------------------------------------
EID: 852 Location: /db2/NIT/db2backup
结论：
1、数据库在线全备的时候，会把所有的表空间设置为0x0800状态，bakuping up in progress
2、在这种状态下，设置数据库只读会失败。
3、只备份一个表空间的时候，只有被备份的表空间的状态是0x0800
