Chinaunix

标题: DB2 offline备份后，DB2会宕掉，有人知道原因吗？ [打印本页]

作者: penny61 时间: 2007-01-29 16:21
标题: DB2 offline备份后，DB2会宕掉，有人知道原因吗？
我的ＳＡＰ系统使用的是ＤＢ２数据库，我在做online备份后，一切正常，但每当我做offline备份后，数据库就会宕掉，日志里记录的信息如下：
MESSAGE : ADM6017E  The table space "KTX#BTABD" (ID "9") is full. Detected on
      container "/db2/KTX/sapdata2/NODE0000/KTX#BTABD.container001" (ID
      "1").  The underlying file system is full or the maximum allowed
      space usage for the file system has been reached. It is also possible
      that there are user limits in place with respect to maximum file size
      and these limits have been reached.

MESSAGE : ADM6017E  The table space "KTX#STABD" (ID "14") is full. Detected on
      container "/db2/KTX/sapdata4/NODE0000/KTX#STABD.container001" (ID
      "1").  The underlying file system is full or the maximum allowed
      space usage for the file system has been reached. It is also possible
      that there are user limits in place with respect to maximum file size
      and these limits have been reached.

MESSAGE : ADM6017E  The table space "KTX#STABD" (ID "14") is full. Detected on
      container "/db2/KTX/sapdata3/NODE0000/KTX#STABD.container000" (ID
      "0").  The underlying file system is full or the maximum allowed
      space usage for the file system has been reached. It is also possible
      that there are user limits in place with respect to maximum file size
      and these limits have been reached.

MESSAGE : ADM6017E  The table space "KTX#BTABD" (ID "9") is full. Detected on
      container "/db2/KTX/sapdata1/NODE0000/KTX#BTABD.container000" (ID
      "0").  The underlying file system is full or the maximum allowed
      space usage for the file system has been reached. It is also possible
      that there are user limits in place with respect to maximum file size
      and these limits have been reached.

MESSAGE : ADM6017E  The table space "KTX#BTABI" (ID "10") is full. Detected on
      container "/db2/KTX/sapdata2/NODE0000/KTX#BTABI.container000" (ID
      "0").  The underlying file system is full or the maximum allowed
      space usage for the file system has been reached. It is also possible
      that there are user limits in place with respect to maximum file size
      and these limits have been reached.

MESSAGE : ADM6017E  The table space "KTX#STABI" (ID "15") is full. Detected on
      container "/db2/KTX/sapdata3/NODE0000/KTX#STABI.container001" (ID
      "1").  The underlying file system is full or the maximum allowed
      space usage for the file system has been reached. It is also possible
      that there are user limits in place with respect to maximum file size
      and these limits have been reached.

MESSAGE : ADM6017E  The table space "KTX#BTABI" (ID "10") is full. Detected on
      container "/db2/KTX/sapdata1/NODE0000/KTX#BTABI.container001" (ID
      "1").  The underlying file system is full or the maximum allowed
      space usage for the file system has been reached. It is also possible
      that there are user limits in place with respect to maximum file size
      and these limits have been reached.

我在重新启数据库后，一切正常，我再查看table spaces空间时，记录如下
KTX#BTABD　　大小：142,626,816　　使用百分比：80.87％　空闲空间：27,288,064
KTX#BTABI　　大小：52,146,176　　　使用百分比：60.66　　空闲空间：20,511,744
KTX#STABD　　大小：44,169,216　　　使用百分比：55.18　　空闲空间：19,795,712
KTX#STABI　　大小：16,144,384　　　使用百分比：73.84　　空闲空间：4,222,976
所有的page size都是16K

AIX里的文件系统也有大量的空间，命令 df -m
/dev/datalv4 51200.00  18669.83 64%       8    1% /db2/KTX/sapdata4
/dev/datalv3 51200.00  19693.83 62%       8    1% /db2/KTX/sapdata3
/dev/datalv2  102400.00 7280.01 93%       7    1% /db2/KTX/sapdata2
/dev/datalv1  102400.00 6380.01 94%       8    1% /db2/KTX/sapdata1

请问哪位大侠知道原因吗？

作者: iamtubie 时间: 2007-01-29 18:47
lv1,and lv2 is near full
so container is on which lv ？
offline backup cmd is what , i must know detailed
and what is your offline backup steps?

作者: lizhuo 时间: 2007-01-30 11:11
某个表空间容器已满

作者: 大梦 时间: 2007-01-30 19:41
有点奇怪！
看来真正原因不是因为表空间满，重启就一切正常！

db2level看看你的版本信息！

作者: psc2001 时间: 2007-01-31 10:41
都是file or path类型的TBS么
备份的FS是哪个
df出来的好像都是DBfs

作者: tony_hjy211 时间: 2007-01-31 13:47
标题: 并不是空间已经满了
出现这个问题,并不一定说空间已经满了,可能是表空间的High water mark (pages) 已经接近表空间
的大小.也会出现上述的情况.建议减低High water mark (pages) .

作者: tony_hjy211 时间: 2007-01-31 13:48
标题: 并不是空间已经满了
出现这个问题,并不一定说空间已经满了,可能是表空间的High water mark (pages) 已经接近表空间
的大小.也会出现上述的情况.建议减低High water mark (pages) .

作者: psc2001 时间: 2007-01-31 15:38

原帖由 tony_hjy211 于 2007-1-31 13:48 发表
出现这个问题,并不一定说空间已经满了,可能是表空间的High water mark (pages) 已经接近表空间
的大小.也会出现上述的情况.建议减低High water mark (pages) .

有这个说法么
上次TBS　HWM到95％也没有这个问题么

作者: tony_hjy211 时间: 2007-02-01 12:12
标题: 我也许对high water 概念有些误解
我也许对high water 概念有些误解,但是有种情况不是很理解
我在生产环境中,导入一些数据到数据库,日志提示空间已经满了(可是我的表空间是足够大的)
我查看表空间状态,没有可用page,high water也接近space size 的大小,
我增加表空间的大小,大概10G,在查看表空间状态,free page is 0 ,high water 接近space size
数据也不能导入(导入数据5000条).我觉得high water对表空间使用大小有影响

作者: psc2001 时间: 2007-02-01 13:34

原帖由 tony_hjy211 于 2007-2-1 12:12 发表
我也许对high water 概念有些误解,但是有种情况不是很理解
我在生产环境中,导入一些数据到数据库,日志提示空间已经满了(可是我的表空间是足够大的)
我查看表空间状态,没有可用page,high water也接近space size ...

增加TBS容量要过一点时间才会加上去
DB2会自动平衡各个表空间的数据量

你增加了空间后还是0 free好像不太可能吧

作者: huyuhui001 时间: 2007-02-06 00:24

原帖由 psc2001 于 2007-2-1 13:34 发表

增加TBS容量要过一点时间才会加上去
DB2会自动平衡各个表空间的数据量

你增加了空间后还是0 free好像不太可能吧

如果确实是增加成功了，是需要时间做balance的，整个tablespace的变化不是马上反映出来的，需要等待balance。

作者: psc2001 时间: 2007-02-06 09:15

原帖由 huyuhui001 于 2007-2-6 00:24 发表

如果确实是增加成功了，是需要时间做balance的，整个tablespace的变化不是马上反映出来的，需要等待balance。

这个我知道
但是平衡的时间应该不是太长吧

作者: beginner-bj 时间: 2007-02-06 09:19

原帖由 psc2001 于 2007-2-6 09:15 发表

这个我知道
但是平衡的时间应该不是太长吧

时间长短跟表空间大小有关。

db2 list utilities可以看到

作者: penny61 时间: 2007-02-07 08:54
以下是tablespaces的详细信息

Tablespace ID                      = 9
Name                               = KTX#BTABD
Type                               = Database managed space
Contents                            = Any data
State                               = 0x0000
Detailed explanation:
   Normal
Total pages                         = 8914176
Useable pages                      = 8914144
Used pages                         = 7271040
Free pages                         = 1643104
High water mark (pages)             = 7271040
Page size (bytes)                   = 16384
Extent size (pages)                = 16
Prefetch size (pages)             = 16
Number of containers                = 2
Minimum recovery time             = 2007-01-15-10.40.29.000000

Tablespace ID                      = 10
Name                               = KTX#BTABI
Type                               = Database managed space
Contents                            = Any data
State                               = 0x0000
Detailed explanation:
   Normal
Total pages                         = 3259136
Useable pages                      = 3259104
Used pages                         = 2004064
Free pages                         = 1255040
High water mark (pages)             = 2004064
Page size (bytes)                   = 16384
Extent size (pages)                = 16
Prefetch size (pages)             = 16
Number of containers                = 2

Tablespace ID                      = 14
Name                               = KTX#STABD
Type                               = Database managed space
Contents                            = Any data
State                               = 0x0000
Detailed explanation:
   Normal
Total pages                         = 2760576
Useable pages                      = 2760544
Used pages                         = 1529248
Free pages                         = 1231296
High water mark (pages)             = 1529248
Page size (bytes)                   = 16384
Extent size (pages)                = 16
Prefetch size (pages)             = 16
Number of containers                = 2
Minimum recovery time             = 2007-01-15-10.40.01.000000

Tablespace ID                      = 15
Name                               = KTX#STABI
Type                               = Database managed space
Contents                            = Any data
State                               = 0x0000
Detailed explanation:
   Normal
Total pages                         = 1009024
Useable pages                      = 1008992
Used pages                         = 748736
Free pages                         = 260256
High water mark (pages)             = 748736
Page size (bytes)                   = 16384
Extent size (pages)                = 16
Prefetch size (pages)             = 16
Number of containers                = 2

作者: penny61 时间: 2007-02-07 08:56

原帖由大梦于 2007-1-30 19:41 发表
有点奇怪！
看来真正原因不是因为表空间满，重启就一切正常！

db2level看看你的版本信息！

s80:db2ktx 5> db2level
DB21085I Instance "db2ktx" uses "64" bits and DB2 code release "SQL08022" with
level identifier "03030106".
Informational tokens are "DB2 v8.1.1.88", "s050422", "U800789", and FixPak "9".
Product is installed at "/usr/opt/db2_08_01".

作者: penny61 时间: 2007-02-07 08:57

原帖由 psc2001 于 2007-1-31 10:41 发表
都是file or path类型的TBS么
备份的FS是哪个
df出来的好像都是DBfs

ＤＢ２使用的是文件系统
备份到磁带机上：　/dev/rmt2

作者: huyuhui001 时间: 2007-02-09 09:24
我觉得你还是需要监控一下哪个file system会满。

作者: sam_8286 时间: 2007-02-12 09:41
备份出来的数据文件会不会超过2G限制？

作者: mymm 时间: 2007-02-12 11:24

原帖由 huyuhui001 于 2007-2-9 09:24 发表
我觉得你还是需要监控一下哪个file system会满。

同意!

DOWN时DIAG里报什么错没？表空间什么状态?

作者: penny61 时间: 2007-02-15 09:05
问题已经解决了，把root、ktxadm、db2ktx用户的关于文件大小的设置全改成不受限制，就没有出现前面的错误了，以下是改了之后的状态：

S80//ulimit -a
time(seconds)       unlimited
file(blocks)       unlimited
data(kbytes)       unlimited
stack(kbytes)       unlimited
memory(kbytes)    unlimited
coredump(blocks)    2097151
nofiles(descriptors) 2000

S80//su - ktxadm
s80:ktxadm 1> ulimit -a
time(seconds)       unlimited
file(blocks)       unlimited
data(kbytes)       unlimited
stack(kbytes)       unlimited
memory(kbytes)    unlimited
coredump(blocks)    2097151
nofiles(descriptors) 2000

S80//su - db2ktx
s80:db2ktx 1> ulimit -a
time(seconds)       unlimited
file(blocks)       unlimited
data(kbytes)       unlimited
stack(kbytes)       unlimited
memory(kbytes)    unlimited
coredump(blocks)    unlimited
nofiles(descriptors) 2000

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)