一次db2 crash的调查

昨晚收到来自production DB2 server的两封邮件，一个是dprop停了，另外一个是放db2diag.log的文件系统满了，于是开始调查原因。
在db2diag.log里面发现了一些error信息，db2 instance 发生了crash，并且自动重启了，这造成了dprop的停止，crash的时候在文件db2diag.log的目录下生成了大量的db2 FODC文件，造成文件系统满。
那db2 instance crash根本的原因是什么那？
从下面的error log里面，不难猜测是来自操作系统文件访问造成的。
从DMS这个关键字眼可以看出，是向一个DMS的表空间写数据
从LOB这个关键字眼可以看出，是向放LOB数据的表空间写数据
这些信息还不足以找到这个文件，那么看下面的这个关于文件大小的信息
Target File Information :
  Size               = 1073741824
对数据库的存储目录进行查找
find "db path for DMS tablespace" -type f -print | xargs ls -l | sort -k5,5rn | head -30
终于找到了这个文件
接着进行分析，根据这个文件所在的文件系统，找pv的相关信息，并且结合操作系统级别的errpt输出，也发现了同样的问题。
现在很明显了，问题的根本原因是和存储有关系，接着把支持SAN服务的人拉到chat里面，确认他们那边确实发现了问题，他们已经修复了这个问题。

一些想法： DB2要是能在db2diag.log里面直接显示发生错误的文件就好了，就不用多方面查找，猜测了，这个猜测还是需要DBA来做的的，AIX的人无法根据db2diag.log里面的错误信息判断哪个文件出现了问题。
另外一点就是，一般发生错误的时候，在errpt的输出里面都会有相关的信息的，所以多个方面一起确认是稳妥的方法。

MESSAGE : ZRC=0x840F0001=-2079391743=SQLO_ACCD "Access Denied"
          DIA8701C Access denied for resource "", operating system return code
          was "".
CALLED  : OS, -, pwrite
OSERR   : EROFS (30) "Read-only file system"
DATA #1 : File handle, PD_TYPE_SQO_FILE_HDL, 8 bytes
  File Handle              = 96
  File System Block Size   = 4096 bytes
  File System Type         = jfs2
  File Handle Flags :
    Require Sector Align   = No
    DIO/CIO Mode           = Yes
    Raw Block Device       = No
    Reserved Handle        = No
    Flush On Close         = No
    Thread-Level Lock      = No
    Write-through Mode     = Yes
    File Not Tracked       = Yes
DATA #2 : unsigned integer, 8 bytes
2048
DATA #3 : signed integer, 8 bytes
392339456
DATA #4 : signed integer, 8 bytes
-1
DATA #5 : String, 105 bytes
Search for ossError*Analysis probe point after this log entry for further
self-diagnosis of this problem.


FUNCTION: DB2 Common, OSSe, ossErrorIOAnalysis, probe:100
CALLED  : OS, -, pwrite
OSERR   : EROFS (30) "Read-only file system"
DATA #1 : String, 116 bytes
A total of 3 analysis will be performed :
 - User info
 - Target file info
 - I/O attempt
                                                       
 Target file handle = 96
DATA #2 : String, 184 bytes
  Real user ID of current process       = 213
  Effective user ID of current process  = 213
  Real group ID of current process      = 214
  Effective group ID of current process = 214
DATA #3 : String, 41 bytes
current sbrk(0) value: 0x0000000128d55b40
DATA #4 : String, 263 bytes
Target File Information :
  Size               = 1073741824
  Link               = No
  Reference path     = N/A
  Type               = 0x8000
  Permissions        = rw-------
  UID                = 213
  GID                = 214
  Last modified time = 1506505822               
DATA #5 : String, 33 bytes                             
I/O attempt not implemented yet.
CALLSTCK: (Static functions may not be resolved correctly, as they are resolved to the nearest symbol)
  [0] 0x0900000014669E4C pdOSSeLoggingCallback + 0x59C
  [1] 0x090000000D82337C oss_log__FP9OSSLogFacUiN32UlN26iPPc + 0x1BC
  [2] 0x090000000D8237F0 ossLogSysRC + 0x70
  [3] 0x090000000D84BCBC ossErrorIOAnalysis__FCPC21OSSErrorAnalysisParam + 0xD7C
  [4] 0x09000000164C3A84 sqloSystemErrorHandler + 0x518
  [5] 0x09000000144CDCE4 sqloseekwrite64 + 0x68C
  [6] 0x09000000144E8128 sqloWriteBlocks + 0x364
  [7] 0x09000000144E7D34 sqlbWriteBlocks__FP16SqlbOpenFileInfoPvlUlUiPUlP12SQLB_GLOBALS + 0x58
  [8] 0x0900000016B9759C sqlbDMSMapAndWrite__FP16SQLB_OBJECT_DESC14SQLB_AS_SCHEMEUiUsP12SQLB_POOL_CBPllPUiT8T7PcT8P12SQLB_GLOBALS + 0x254
  [9] 0x0900000016B97B6C sqlbDMSMapAndWrite__FP16SQLB_OBJECT_DESC14SQLB_AS_SCHEMEUiUsP12SQLB_POOL_CBPllPUiT8T7PcT8P12SQLB_GLOBALS + 0x824
  [10] 0x0900000015F1646C sqlbDMSDirectWrite__FP20SQLB_DIRECT_WRITE_CB + 0x4574
  [11] 0x0900000017A9D684 sqlbDirectWrite__FP20SQLB_DIRECT_WRITE_CB + 0x2DC
  [12] 0x0900000016C1A0B8 sqldx_diskwrite__FP9SQLDX_LWAUiiPcT2 + 0x564
  [13] 0x0900000016C1B0C8 sqldxWriteLobData__FP9SQLDX_LWAUcUsPC12SQLDX_IOPARMPc + 0xA74
  [14] 0x0900000012F98C5C sqldxReplaceLob__FP9SQLDX_LWAUcUsP8SQLDX_LDUiT5PcT5 + 0x234
  [15] 0x0900000018ACA938 sqldxCreateLob__FP9SQLDX_LWAUcUsiT4PcT4P8SQLDX_LD + 0x388
  [16] 0x0900000017C36858 sqldxLobCreate__FP8sqeAgentP8SQLD_TCBUcUsiT5PcT5P8SQLDX_LD + 0x6DC
  [17] 0x0900000012E8C25C sqldmadx__FP8sqeAgentP8SQLD_TCBUcUsP9sqllf_csoiT6P8SQLD_LFDPUl + 0x858
  [18] 0x090000001398BE78 @114@sqldFormatLobLongCol__FP8sqeAgentP10sqlz_valueT2P8SQLD_TCBiPiT2 + 0x900
  [19] 0x0900000014E98E9C @114@sqldFormatNonDMSCols__FP8sqeAgentP13SQLD_TDATARECT2PP10SQLD_VALUEP8SQLD_TCBPsPiT7P8SQLD_CCBP17SQLU_FORMATTER_CBP8SQLD_VAL + 0xA54
  [20] 0x0900000014E997A4 sqldFullFormatFixedVar__FP8sqeAgentP13SQLD_TDATARECP15SQLD_TDATAREC32T2PP15SQLD_TDATAREC32PP10SQLD_VALUEiP8SQLD_TCBPiPUcP15SQLD + 0x52C
  [21] 0x0900000014579830 sqldRowInsert__FP8sqeAgentUsT2UcUliPP10SQLD_VALUEP8SQLZ_RIDPPv + 0x3B00
  [22] 0x09000000145915C0 sqldRowInsert__FP8sqeAgentUsT2UcUliPP10SQLD_VALUEP8SQLZ_RIDPPv + 0x1A0
  [23] 0x0900000014590F60 sqlrinsr__FP8sqlrr_cbUsT2iT2PP10SQLD_VALUEQ3_10sqlri_iudo11t_iudoFlags17t_iudoFlagsKernelP8SQLZ_RIDPPv + 0xD0
  [24] 0x09000000145911B8 sqlriisr__FP8sqlrr_cb + 0x17C
  [25] 0x09000000147C089C sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm + 0x74
  [26] 0x0900000014A7E5CC sqlrr_execute__FP14db2UCinterfaceP9UCstpInfo + 0xF9C
  [27] 0x0900000014A7DA24 sqlrr_execute__FP14db2UCinterfaceP9UCstpInfo + 0x3F4
  [28] 0x0900000014B97730 sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UCinterface + 0x1834
  [29] 0x0900000014B92D94 @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0x11B4
  [30] 0x0900000014B92D94 @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0x11B4
  [31] 0x0900000014B93270 @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0x1690
  [32] 0x09000000130A2608 @72@sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xA8
  [33] 0x09000000130A3118 @72@sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x5F8
  [34] 0x09000000152CFB30 RunEDU__8sqeAgentFv + 0x4C784
  [35] 0x09000000152400FC RunEDU__8sqeAgentFv + 0x124
  [36] 0x0900000014153EA8 EDUDriver__9sqzEDUObjFv + 0x130
  [37] 0x0900000015260034 sqloEDUEntry + 0x390
  [38] 0x090000000051AE10 _pthread_body + 0xF0
  [39] 0xFFFFFFFFFFFFFFFC ?unknown + 0xFFFFFFFF

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/725820/viewspace-2145563/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/725820/viewspace-2145563/