数据库内存故障恢复一例

前些天，一个系统的管理员说他们的系统变得非常慢，每天都要跑的一个批处理程序，原来差不多30分钟内完成，现在要2个多小时。让其把日志文件发来看看，结果发现其中有如下问题：
    2011-02-10-17.51.48.643883+480 I286427750G930     LEVEL: Warning

PID     : 25788                TID  : 3073371024  PROC : db2sysc

INSTANCE: db2inst1             NODE : 000         DB   : TCCCS2

APPHDL  : 0-35687              APPID: C0A8C0F4.P111.113980093219

AUTHID  : TCTCS 

EDUID   : 60081                EDUNAME: db2agent (TCCCS)

FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FirstConnect, probe:1516

MESSAGE : ZRC=0x850F0005=-2062614523=SQLO_NOSEG

          "No Storage Available for allocation"

          DIA8305C Memory allocation failure occurred.

DATA #1 : String, 286 bytes

Failed to allocate the desired database shared memory set.

Check to make sure the configured DATABASE_MEMORY + overflow

does not exceed the maximum shared memory on the system.

Attempting to start up with only the system buffer pools.

Desired database shared memory set size is (bytes):

DATA #2 : unsigned integer, 4 bytes

1570439168



2011-02-10-17.51.49.585291+480 E286434135G777     LEVEL: Warning

PID     : 25788                TID  : 3073371024  PROC : db2sysc

INSTANCE: db2inst1             NODE : 000         DB   : TCCCS2

APPHDL  : 0-35687              APPID: C0A8C0F4.P111.113980093219

AUTHID  : TCTCS 

EDUID   : 60081                EDUNAME: db2agent (TCCCS)

FUNCTION: DB2 UDB, buffer pool services, sqlbStartPools, probe:2

MESSAGE : ADM6073W  The table space "KSCCS_CSS" (ID "7") is configured to use

          buffer pool ID "1", but this buffer pool is not active at this time.

          In the interim the table space will use buffer pool ID "4096".  The

          inactive buffer pool should become available at next database startup

          provided that the required memory is available.


2011-02-10-17.51.49.954075+480 I286436476G530     LEVEL: Severe

PID     : 25788                TID  : 3073371024  PROC : db2sysc

INSTANCE: db2inst1             NODE : 000         DB   : TCCCS2

APPHDL  : 0-35687              APPID: C0A8C0F4.P111.113980093219

AUTHID  : TCTCS 

EDUID   : 60081                EDUNAME: db2agent (TCCCS)

FUNCTION: DB2 UDB, buffer pool services, sqlbinit, probe:620

DATA #1 : String, 70 bytes

Database will come up with hidden buffer pools.

totalBufferPoolPages:

DATA #2 : signed integer, 8 bytes

64


2011-02-10-17.51.50.334564+480 I286437007G645     LEVEL: Warning

PID     : 25788                TID  : 3073371024  PROC : db2sysc

INSTANCE: db2inst1             NODE : 000         DB   : TCCCS2

APPHDL  : 0-35687              APPID: C0A8C0F4.P111.113980093219

AUTHID  : TCTCS 

EDUID   : 60081                EDUNAME: db2agent (TCCCS)

FUNCTION: DB2 UDB, Self tuning memory manager, stmmStartSTMMIfNecessary, probe:490

MESSAGE : ZRC=0x87AE015F=-2018639521=STMM_DAEMON_COULD_NOT_START

          "STMM Daemon could not be started"

DATA #1 : String, 89 bytes

The self tuning memory manager was not started because buffer pool initialization failed.


2011-02-10-17.51.51.259420+480 E286438444G768     LEVEL: Warning

PID     : 25788                TID  : 3073371024  PROC : db2sysc

INSTANCE: db2inst1             NODE : 000         DB   : TCCCS2

APPHDL  : 0-35687              APPID: C0A8C0F4.P111.113980093219

AUTHID  : TCTCS 

EDUID   : 60081                EDUNAME: db2agent (TCCCS)

FUNCTION: DB2 UDB, sort/list services, sqlsOptimizeNumMergeRuns, probe:10

MESSAGE : ADM9000W  Prefetching was disabled during sort merge; performance may

          be suboptimal.  If this message persists, consider increasing the

          buffer pool size for temporary table space "TEMPSPACE1" (ID "1") or

          increase the value of the SORTHEAP DB configuration parameter to

          reduce the extent of sort spilling.
  从日志可以看到，在数据库启动时，无法成功申请到共享内存，从而导致后续的STMM和prefectch等功能无法启用，这必然会严重影响到系统的性能。  
  共享内存对DB2数据库是绝对必要的，数据库系统为了防止没有共享内存导致系统无法启动，从而也无法修改配置的情形，在无法申请到共享内存的情况下，启用隐藏共享内存，隐藏共享内存都比较小，所以数据库虽然启动了，但性能严重降低。幸亏平时业务系统的交易量相当的小，否则，就不会是仅仅从批处理时才能发现问题了。
    查看了数据库的配置参数，发现有一个参数比较可疑：Utilies heap size（UTIL_HEAP_SZ）的大小为11万多（4K页），合计要440M多的内存被其占用。在其他数据库中，看到这个参数的默认值之尤5000，因此想到可能是此参数被修改过的原因。
   将此参数更改为5000，然后重启数据库，数据库可以正常启动，并且成功申请到了共享内存。STMM等功能也能成功启用。