记一次存储故障导致数据库坏块处理过程

记一次存储故障导致数据库坏块处理过程

线上架构说明：

    IBM DS4800存储一套    P560小机HA架构一套    两个数据库资源组平时run在HA架构中的任意一台中，资源组全部使用共享存储

问题描述：

由于存储在数据库运行过程中发生了异常宕机，导致两个库存在不同程度的坏块

错误信息及解决过程

数据库A：

A:root:/db2dumph/istclhis >2016-04-09-04.26.10.787138   Instance:istclhis   Node:000PID:1405020(db2agnsc (WCELHISP) 0)   TID:1   Appid:nonebase sys utilities  sqleMarkDBad Probe:210Database logging stopped due to mark db bad.PID:1405020 TID:1 Node:000 Title: SQLE_AGENTCBDump File:/db2dumph/istclhis/14050201.000PID:1405020 TID:1 Node:000 Title: SQLE_AGENT_PRIVATECBDump File:/db2dumph/istclhis/14050201.000PID:1405020 TID:1 Node:000 Title: SQLE_DBCBDump File:/db2dumph/istclhis/14050201.000PID:1405020 TID:1 Node:000 Title: SQLE_TRAN_CBDump File:/db2dumph/istclhis/14050201.000PID:1405020 TID:1 Node:000 Title: SQLE_MASTER_APP_CBDump File:/db2dumph/istclhis/14050201.000PID:1405020 TID:1 Node:000 Title: SQLE_APP_CBDump File:/db2dumph/istclhis/14050201.000PID:1405020 TID:1 Node:000 Title: SQLE_COORDINATOR_CBDump File:/db2dumph/istclhis/14050201.0002016-04-09-04.26.10.798863   Instance:istclhis   Node:000PID:1405020(db2agnsc (WCELHISP) 0)   TID:1   Appid:nonedata management  sqldRedo Probe:5124DIA8500C A data file error has occurred, record id is "".ZRC=0x87040001M-^?2016-04-09-04.26.10.799431   Instance:istclhis   Node:000PID:1405020(db2agnsc (WCELHISP) 0)   TID:1   Appid:nonedata management  sqldmrdo Probe:770DIA8500C A data file error has occurred, record id is "".ZRC=0x87040001M-^?016-04-09-04.26.10.799998   Instance:istclhis   Node:000PID:1405020(db2agnsc (WCELHISP) 0)   TID:1   Appid:nonedata management  sqldmrdo Probe:770Error during REDO of LSN:0x487FCB0A : A782 8672 A223                             M-'..rM-"#2016-04-09-04.26.10.802006   Instance:istclhis   Node:000PID:1405020(db2agnsc (WCELHISP) 0)   TID:1   Appid:nonedata management  sqldmrdo Probe:770Error during REDO of log record:0x40119237 : 016A 0005 0007 1EE6 4164 0F00 00D6 04A8    .j.....M-fAd...M-V.M-(0x40119247 : 0000 1000 00D6 0100 00CE 5353 3031 3031    .....M-V...M-NSS01010x40119257 : 4E52 4437 3942 3030 3130 2020 2020 3130    NRD79B0010    100x40119267 : 2020 2020 2020 2020 2020 2020 2020 2020                   0x40119277 : 2020 2020 2020 2020 2020 2020 2020 2020                   0x40119287 : 2020 5050 4C41 4330 3730 4E4A 3031 3030      PPLAC070NJ01000x40119297 : 4346 5753 5730 3730 3730 4E4A 3031 2020    CFWSW07070NJ01 0x401192A7 : 4C32 4620 3643 5554 2030 2E35 5420 2020    L2F 6CUT 0.5T   0x401192B7 : 2020 2020 2020 2020 2020 2020 2020 2020                   0x401192C7 : 2020 2020 2020 2020 2020 2020 2020 2020                   0x401192D7 : 2020 2020 2020 2020 2020 2020 2020 2020                   0x401192E7 : 2020 2020 2020 2020 2020 2020 2020 2020                   0x401192F7 : 2020 2020 2020 2020 2020 2020 2020 2020                   0x40119307 : 2020 2020 2020 2020 2020 2020 2020 2016                   .0x40119317 : 0408 2130 0100 0088                        ..!0....2016-04-09-04.26.10.804185   Instance:istclhis   Node:000PID:1405020(db2agnsc (WCELHISP) 0)   TID:1   Appid:nonerecovery manager  sqlpRecDbRedo Probe:155REDO failed on LSN0x487FCB08 : 0x0000A7828672A223                         ..M-'..rM-"#PID:1405020 TID:1 Node:000 Title: SQLP_DBCBDump File:/db2dumph/istclhis/14050201.0002016-04-09-04.26.10.807143   Instance:istclhis   Node:000PID:1405020(db2agnsc (WCELHISP) 0)   TID:1   Appid:nonerecovery manager  sqlpPRecProcLog Probe:250DIA8500C A data file error has occurred, record id is "".ZRC=0x87040001M-^?2016-04-09-04.26.10.807784   Instance:istclhis   Node:000PID:1405020(db2agnsc (WCELHISP) 0)   TID:1   Appid:nonerecovery manager  sqlpPRecProcLog Probe:250qEntry for 0000A7828672A223entryFlags 1 queueId 10 waitOthers 0 numBlocked 0 lrHeader:查看备份信息WSRHISDB:istclhis:/istclhis >db2 list history backup all for wcelhispOp Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID -- --- ------------------ ---- --- ------------ ------------ --------------  B  D  20160408043017001   N    A  S5618312.LOG S5618358.LOG  ----------------------------------------------------------------------------  Contains 9 tablespace(s):  00001 SYSCATSPACE                                                             00002 USERSPACE1                                                             00003 TBS32K01                                                               00004 INX32K01                                                               00005 TBS8K01                                                                 00006 INX8K01                                                                 00007 TBS8K02                                                                 00008 INX8K02                                                                 00009 TBS8K03                                                                ----------------------------------------------------------------------------    Comment: DB2 BACKUP WCELHISP ONLINE                                        Start Time: 20160408043017   End Time: 20160408045340 ----------------------------------------------------------------------------  00008 Location: adsm/libtsm.aWSRHISDB:istclhis:/istclhis >db2adutl queryQuery for database WCELHISPRetrieving FULL DATABASE BACKUP information.    1 Time: 20160414043017  Oldest log: S5632477.LOG  DB Partition Number: 0    Sessions: 1     2 Time: 20160413043017  Oldest log: S5629252.LOG  DB Partition Number: 0    Sessions: 1     3 Time: 20160412043017  Oldest log: S5625975.LOG  DB Partition Number: 0    Sessions: 1     4 Time: 20160411043016  Oldest log: S5622825.LOG  DB Partition Number: 0    Sessions: 1     5 Time: 20160410043016  Oldest log: S5619565.LOG  DB Partition Number: 0    Sessions: 1     6 Time: 20160408043017  Oldest log: S5618312.LOG  DB Partition Number: 0    Sessions: 1     7 Time: 20160407043016  Oldest log: S5615044.LOG  DB Partition Number: 0    Sessions: 1     8 Time: 20160406043016  Oldest log: S5611782.LOG  DB Partition Number: 0    Sessions: 1     9 Time: 20160405043017  Oldest log: S5608522.LOG  DB Partition Number: 0    Sessions: 1    10 Time: 20160404043017  Oldest log: S5605279.LOG  DB Partition Number: 0    Sessions: 1    11 Time: 20160403043017  Oldest log: S5602016.LOG  DB Partition Number: 0    Sessions: 1    12 Time: 20160402043017  Oldest log: S5598746.LOG  DB Partition Number: 0    Sessions: 1    13 Time: 20160401043017  Oldest log: S5595490.LOG  DB Partition Number: 0    Sessions: 1    14 Time: 20160331043016  Oldest log: S5592249.LOG  DB Partition Number: 0    Sessions: 1    15 Time: 20160330043016  Oldest log: S5589006.LOG  DB Partition Number: 0    Sessions: 1    16 Time: 20160329043017  Oldest log: S5585759.LOG  DB Partition Number: 0    Sessions: 1    17 Time: 20160328043016  Oldest log: S5582508.LOG  DB Partition Number: 0    Sessions: 1    18 Time: 20160327043017  Oldest log: S5579256.LOG  DB Partition Number: 0    Sessions: 1    19 Time: 20160320043016  Oldest log: S5556434.LOG  DB Partition Number: 0    Sessions: 1    20 Time: 20160319043017  Oldest log: S5553162.LOG  DB Partition Number: 0    Sessions: 1    21 Time: 20160318043017  Oldest log: S5549896.LOG  DB Partition Number: 0    Sessions: 1    22 Time: 20160317043017  Oldest log: S5546624.LOG  DB Partition Number: 0    Sessions: 1    23 Time: 20160310043017  Oldest log: S5523773.LOG  DB Partition Number: 0    Sessions: 1    24 Time: 20160309043016  Oldest log: S5520518.LOG  DB Partition Number: 0    Sessions: 1    25 Time: 20160308043016  Oldest log: S5517262.LOG  DB Partition Number: 0    Sessions: 1    26 Time: 20160307043016  Oldest log: S5514015.LOG  DB Partition Number: 0    Sessions: 1    27 Time: 20160306043017  Oldest log: S5510775.LOG  DB Partition Number: 0    Sessions: 1    28 Time: 20160305043016  Oldest log: S5507523.LOG  DB Partition Number: 0    Sessions: 1    29 Time: 20160304043017  Oldest log: S5504270.LOG  DB Partition Number: 0    Sessions: 1    30 Time: 20160303043017  Oldest log: S5501010.LOG  DB Partition Number: 0    Sessions: 1    31 Time: 20160302043016  Oldest log: S5497760.LOG  DB Partition Number: 0    Sessions: 1    32 Time: 20160301043017  Oldest log: S5494510.LOG  DB Partition Number: 0    Sessions: 1    33 Time: 20160229043017  Oldest log: S5491258.LOG  DB Partition Number: 0    Sessions: 1 Retrieving INCREMENTAL DATABASE BACKUP information.  No INCREMENTAL DATABASE BACKUP p_w_picpaths found for WCELHISPRetrieving DELTA DATABASE BACKUP information.  No DELTA DATABASE BACKUP p_w_picpaths found for WCELHISPRetrieving TABLESPACE BACKUP information.  No TABLESPACE BACKUP p_w_picpaths found for WCELHISPRetrieving INCREMENTAL TABLESPACE BACKUP information.  No INCREMENTAL TABLESPACE BACKUP p_w_picpaths found for WCELHISPRetrieving DELTA TABLESPACE BACKUP information.  No DELTA TABLESPACE BACKUP p_w_picpaths found for WCELHISPRetrieving LOAD COPY information.  No LOAD COPY p_w_picpaths found for WCELHISPRetrieving LOG ARCHIVE information.  No LOG ARCHIVE p_w_picpaths found for WCELHISP确保没有用户使用Db2:#db2 list applications for wcelhisp#db2stop force#db2start数据库恢复db2 restore db wcelhisp use tsm taken at 20160408043017这时数据库处于rollforward-pending state的状态，需要做roll forward 操作：WSRHISDB:istclhis:/istclhis/cfg >db2 "rollforward db wcelhisp to end of logs and stop overflow log path(/istclhis/arclog/WCELHISP/NODE0000)"SQL4970N  Roll-forward recovery on database "WCELHISP" cannot reach thespecified stop point (end-of-log or point-in-time) because of missing logfile(s) on node(s) "0".arclog还原dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618318.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618319.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618320.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618321.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618322.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618323.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618324.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618325.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618326.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618327.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618328.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618329.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618330.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618331.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618332.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618333.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618334.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618335.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618336.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618337.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618338.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618339.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618340.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618341.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618342.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618343.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618344.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618345.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618346.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618347.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618348.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618349.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618350.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618351.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618352.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618353.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618354.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618355.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618356.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618357.LOGdsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618358.LOGWSRHISDB:istclhis:/istclhis/cfg >db2 rollforward db wcelhisp query status using local time                                                                                  Rollforward Status Input database alias                   = wcelhisp Number of nodes have returned status   = 1 Node number                            = 0 Rollforward status                     = DB  working Next log file to be read               = S5618324.LOG Log files processed                    = S5618312.LOG - S5618322.LOG Last committed transaction             = 2016-04-08-04.34.46.000000WSRHISDB:istclhis:/db2dumph/istclhis >dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618324.LOG                                             IBM Tivoli Storage ManagerCommand Line Backup/Archive Client Interface  Client Version 5, Release 4, Level 0.0  Client date/time: 04/09/16   18:27:09(c) Copyright by IBM Corporation and other(s) 1990, 2007. All Rights Reserved.Retrieve function invoked.Node Name: WSRHISDBSession established with server WSRTSM01_SERVER1: Windows  Server Version 5, Release 4, Level 0.0  Server date/time: 04/09/16   18:27:20  Last access: 04/09/16   18:27:19--- User Action is Required ---File '/istclhis/arclog/WCELHISP/NODE0000/S5618324.LOG' existsSelect an appropriate action  1. Replace this object  2. Replace all objects that already exist  3. Skip this object  4. Skip all objects that already exist  A. Abort this operationAction [1,2,3,4,A] : 2 ** Interrupted **ANS1114I Waiting for mount of offline media.Retrieving      32,776,192 /istclhis/arclog/WCELHISP/NODE0000/S5618324.LOG [Done]     Retrieve processing finished.Total number of objects retrieved:        1Total number of objects failed:           0Total number of bytes transferred:    31.25 MBData transfer time:                    7.53 secNetwork data transfer rate:        4,246.39 KB/secAggregate data transfer rate:      2,116.78 KB/secElapsed processing time:           00:00:38WSRHISDB:istclhis:/db2dumph/istclhis >db2 "rollforward db wcelhisp to end of logs and stop overflow log path(/istclhis/arclog/WCELHISP/NODE0000)"                                 Rollforward Status Input database alias                   = wcelhisp Number of nodes have returned status   = 1 Node number                            = 0 Rollforward status                     = not pending Next log file to be read               = Log files processed                    = S5618312.LOG - S5618359.LOG Last committed transaction             = 2016-04-07-20.54.22.000000DB20000I  The ROLLFORWARD command completed successfully.WSRHISDB:istclhis:/db2dumph/istclhis >db2 connect to wcelhisp   Database Connection Information Database server        = DB2/6000 8.1.6 SQL authorization ID   = ISTCLHIS Local database alias   = WCELHISPWSRHISDB:istclhis:/db2dumph/istclhis >db2 list tablespace show detail |grep  -i 0xWSRHISDB:istclhis:/db2dumph/istclhis >db2 list tablespaces show detail |grep  -i 0x State                                = 0x0000 State                                = 0x0000 State                                = 0x0000 State                                = 0x0000 State                                = 0x0000 State                                = 0x0000 State                                = 0x0000 State                                = 0x0000 State                                = 0x0000 State                                = 0x0000 State                                = 0x0000 State                                = 0x0000

数据库B

一、数据库报错信息及定位：10.54.200.8 WCELPPTP数据库报错db2diag.log报错016-04-18-04.29.29.516197   Instance:istclppt   Node:000PID:1400852(db2agent (WCELPPTP) 0)   TID:1   Appid:GA36C803.D1C9.1801F7202929index manager  sqlischd Probe:99   Database:WCELPPTPDIA8541C The index key could not be found, the value was "".ZRC=0x8709002C2016-04-18-04.29.29.516829   Instance:istclppt   Node:000PID:1400852(db2agent (WCELPPTP) 0)   TID:1   Appid:GA36C803.D1C9.1801F7202929index manager  sqlischd Probe:99   Database:WCELPPTPObj={10;6;1} Par={9;6}10是tablespaceID 6是objectID 1是类型列index（0是table）查询select * from SYSCAT.INDEXES where TBSPACEID =10 and INDEX_OBJECTID= 6二、处理过程：1、停止所有应用程序db2 force applications all2、停止10.54.200.3上删除ppaneldb的程序3、数据库全备db2adutl delete keep 32 db WCELPPTP without prompting  sleep 10  db2 " backup db WCELPPTP online use tsm "WSRCELDB:istclppt:/istclppt/arclog/WCELPPTP/NODE0000 >~/cfg/db_backup.kshQuery for database WCELPPTPRetrieving FULL DATABASE BACKUP information.  Taken at: 20160305013017  DB Partition Number: 0    Sessions: 1 Retrieving INCREMENTAL DATABASE BACKUP information.  No INCREMENTAL DATABASE BACKUP p_w_picpaths found for WCELPPTPRetrieving DELTA DATABASE BACKUP information.  No DELTA DATABASE BACKUP p_w_picpaths found for WCELPPTPRetrieving TABLESPACE BACKUP information.  No TABLESPACE BACKUP p_w_picpaths found for WCELPPTPRetrieving INCREMENTAL TABLESPACE BACKUP information.  No INCREMENTAL TABLESPACE BACKUP p_w_picpaths found for WCELPPTPRetrieving DELTA TABLESPACE BACKUP information.  No DELTA TABLESPACE BACKUP p_w_picpaths found for WCELPPTPRetrieving LOAD COPY information.  No LOAD COPY p_w_picpaths found for WCELPPTPBackup successful. The timestamp for this backup p_w_picpath is : 20160501080011ISTCLPPT                       db2bp                363        *LOCAL.istclppt.100BF1000011   0001 1          0                1986798         Performing a Backup            Notdb2 list history backup all for wcelpptpOp Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID -- --- ------------------ ---- --- ------------ ------------ --------------  B  D  20160501080011001   N    A  S0409006.LOG S0409007.LOG  ----------------------------------------------------------------------------  Contains 11 tablespace(s):  00001 SYSCATSPACE                                                             00002 USERSPACE1                                                             00003 TBS8K01                                                                 00004 INX8K01                                                                 00005 TBS8K02                                                                 00006 INX8K02                                                                 00007 TBS8K03                                                                 00008 INX8K03                                                                 00009 TBS8K04                                                                 00010 INX8K04                                                                 00011 TBS8K05                                                                ----------------------------------------------------------------------------    Comment: DB2 BACKUP WCELPPTP ONLINE                                        Start Time: 20160501080011   End Time: 20160501085502 ----------------------------------------------------------------------------  00013 Location: adsm/libtsm.aWSRCELDB:istclppt:/test/20160415 >db2adutl queryQuery for database WCELBRMPRetrieving FULL DATABASE BACKUP information.  No FULL DATABASE BACKUP p_w_picpaths found for WCELBRMPRetrieving INCREMENTAL DATABASE BACKUP information.  No INCREMENTAL DATABASE BACKUP p_w_picpaths found for WCELBRMPRetrieving DELTA DATABASE BACKUP information.  No DELTA DATABASE BACKUP p_w_picpaths found for WCELBRMPRetrieving TABLESPACE BACKUP information.  No TABLESPACE BACKUP p_w_picpaths found for WCELBRMPRetrieving INCREMENTAL TABLESPACE BACKUP information.  No INCREMENTAL TABLESPACE BACKUP p_w_picpaths found for WCELBRMPRetrieving DELTA TABLESPACE BACKUP information.  No DELTA TABLESPACE BACKUP p_w_picpaths found for WCELBRMPRetrieving LOAD COPY information.  No LOAD COPY p_w_picpaths found for WCELBRMPRetrieving LOG ARCHIVE information.  No LOG ARCHIVE p_w_picpaths found for WCELBRMPQuery for database WCELPPTPRetrieving FULL DATABASE BACKUP information.    1 Time: 20160501080011  Oldest log: S0409006.LOG  DB Partition Number: 0    Sessions: 1     2 Time: 20160501013016  Oldest log: S0409003.LOG  DB Partition Number: 0    Sessions: 1     3 Time: 20160430013017  Oldest log: S0409001.LOG  DB Partition Number: 0    Sessions: 1     4 Time: 20160429013016  Oldest log: S0408999.LOG  DB Partition Number: 0    Sessions: 1     5 Time: 20160428013016  Oldest log: S0408998.LOG  DB Partition Number: 0    Sessions: 1     6 Time: 20160427013016  Oldest log: S0408992.LOG  DB Partition Number: 0    Sessions: 1     7 Time: 20160426013017  Oldest log: S0408936.LOG  DB Partition Number: 0    Sessions: 1     8 Time: 20160425013017  Oldest log: S0408930.LOG  DB Partition Number: 0    Sessions: 1     9 Time: 20160424013016  Oldest log: S0408924.LOG  DB Partition Number: 0    Sessions: 1    10 Time: 20160423013016  Oldest log: S0408902.LOG  DB Partition Number: 0    Sessions: 1    11 Time: 20160422013016  Oldest log: S0408878.LOG  DB Partition Number: 0    Sessions: 1    12 Time: 20160421013016  Oldest log: S0408849.LOG  DB Partition Number: 0    Sessions: 1    13 Time: 20160420013016  Oldest log: S0408812.LOG  DB Partition Number: 0    Sessions: 1    14 Time: 20160419013016  Oldest log: S0408773.LOG  DB Partition Number: 0    Sessions: 1    15 Time: 20160418013017  Oldest log: S0408735.LOG  DB Partition Number: 0    Sessions: 1    16 Time: 20160417013017  Oldest log: S0408706.LOG  DB Partition Number: 0    Sessions: 1    17 Time: 20160416090013  Oldest log: S0408682.LOG  DB Partition Number: 0    Sessions: 1    18 Time: 20160415013016  Oldest log: S0408625.LOG  DB Partition Number: 0    Sessions: 1    19 Time: 20160414013017  Oldest log: S0408586.LOG  DB Partition Number: 0    Sessions: 1    20 Time: 20160413013017  Oldest log: S0408547.LOG  DB Partition Number: 0    Sessions: 1    21 Time: 20160412013017  Oldest log: S0408492.LOG  DB Partition Number: 0    Sessions: 1    22 Time: 20160411083232  Oldest log: S0408428.LOG  DB Partition Number: 0    Sessions: 1    23 Time: 20160408013017  Oldest log: S0408300.LOG  DB Partition Number: 0    Sessions: 1    24 Time: 20160407013017  Oldest log: S0408253.LOG  DB Partition Number: 0    Sessions: 1    25 Time: 20160406013016  Oldest log: S0408216.LOG  DB Partition Number: 0    Sessions: 1    26 Time: 20160405013017  Oldest log: S0408186.LOG  DB Partition Number: 0    Sessions: 1    27 Time: 20160404013016  Oldest log: S0408174.LOG  DB Partition Number: 0    Sessions: 1    28 Time: 20160403013016  Oldest log: S0408146.LOG  DB Partition Number: 0    Sessions: 1    29 Time: 20160310013016  Oldest log: S0407338.LOG  DB Partition Number: 0    Sessions: 1    30 Time: 20160309013017  Oldest log: S0407290.LOG  DB Partition Number: 0    Sessions: 1    31 Time: 20160308013017  Oldest log: S0407253.LOG  DB Partition Number: 0    Sessions: 1    32 Time: 20160307013016  Oldest log: S0407219.LOG  DB Partition Number: 0    Sessions: 1    33 Time: 20160306013017  Oldest log: S0407161.LOG  DB Partition Number: 0    Sessions: 1 Retrieving INCREMENTAL DATABASE BACKUP information.  No INCREMENTAL DATABASE BACKUP p_w_picpaths found for WCELPPTPRetrieving DELTA DATABASE BACKUP information.  No DELTA DATABASE BACKUP p_w_picpaths found for WCELPPTPRetrieving TABLESPACE BACKUP information.  No TABLESPACE BACKUP p_w_picpaths found for WCELPPTPRetrieving INCREMENTAL TABLESPACE BACKUP information.  No INCREMENTAL TABLESPACE BACKUP p_w_picpaths found for WCELPPTPRetrieving DELTA TABLESPACE BACKUP information.  No DELTA TABLESPACE BACKUP p_w_picpaths found for WCELPPTPRetrieving LOAD COPY information.  No LOAD COPY p_w_picpaths found for WCELPPTPRetrieving LOG ARCHIVE information.  No LOG ARCHIVE p_w_picpaths found for WCELPPTPQuery for database WCELUACPRetrieving FULL DATABASE BACKUP information.  No FULL DATABASE BACKUP p_w_picpaths found for WCELUACPRetrieving INCREMENTAL DATABASE BACKUP information.  No INCREMENTAL DATABASE BACKUP p_w_picpaths found for WCELUACPRetrieving DELTA DATABASE BACKUP information.  No DELTA DATABASE BACKUP p_w_picpaths found for WCELUACPRetrieving TABLESPACE BACKUP information.  No TABLESPACE BACKUP p_w_picpaths found for WCELUACPRetrieving INCREMENTAL TABLESPACE BACKUP information.  No INCREMENTAL TABLESPACE BACKUP p_w_picpaths found for WCELUACPRetrieving DELTA TABLESPACE BACKUP information.  No DELTA TABLESPACE BACKUP p_w_picpaths found for WCELUACPRetrieving LOAD COPY information.  No LOAD COPY p_w_picpaths found for WCELUACPRetrieving LOG ARCHIVE information.  No LOG ARCHIVE p_w_picpaths found for WCELUACP4、ppanledb表数据导出18912.61        expppanedb.ixfWSRCELDB:root:/test/20160415 >more exp.sh#/bin/shdb2 connect to wcelpptpdb2 "export to /test/20160415/expppanedb.ixf of ixf messages /test/20160415/expppaneldb.log select * from w2.ppaneldb"WSRCELDB:istclppt:/test/20160415 >tail -f expppaneldb.logSQL3104N  The Export utility is beginning to export data to file"/test/20160415/expppanedb.ixf".SQL3105N  The Export utility has finished exporting "5381266" rows.5、数据验证select count (* ) from W2.PPANELDB6、空间确认此次将ppaneldb从tbs8k04迁移至tbs8k05查看tbs8k05表空间使用情况空间足够迁移7、建立新表SET SCHEMA = 'ISTCLPPT' ;CREATE TABLE "W2"."PPANELDB160501" (  "CHIPID"  CHARACTER(14)      NOT NULL ,  ......)  IN "TBS8K05"  INDEX IN "INX8K04" ;ALTER TABLE "W2"."PPANELDB160501 "  DATA CAPTURE NONE  LOCKSIZE ROW  APPEND OFF  NOT VOLATILE ;ALTER TABLE "W2"."PPANELDB160501 "  ADD PRIMARY KEY    ("CHIPID") ;GRANT SELECT , INSERT , UPDATE , DELETE ON TABLE "W2"."PPANELDB160501 " TO USER "WC1USR";GRANT SELECT , INSERT , UPDATE , DELETE ON TABLE "W2"."PPANELDB160501 " TO USER "WC2USR";8、import数据至新表 WSRCELDB:istclppt:/test/20160415 >more imp.sh#/bin/shdb2 connect to wcelpptpdb2 "import from /test/20160415/expppanedb.ixf of ixf commitcount 10000 messages /test/20160415/imp.log replace into w2.PPANELDB160501"nohup ./imp.sh&导入过程中实时查看archivelog空间使用率，如满了则进行tsm归档WSRCELDB:root:/test/20160415 >df -g |grep istclppt/dev/istclppt      0.75      0.04   95%      679     7% /istclppt/dev/cldbarclog      5.00      4.22   16%       50     1% /istclppt/arclog/dev/cldb          0.25      0.25    1%        8     1% /istclppt/cldb/dev/cldbactlog      3.12      2.46   22%       43     1% /istclppt/cldb/actlog/dev/cptbsl        1.50      1.50    1%        4     1% /istclppt/cldb/cptbsl/dev/cldbmactlog      3.12      2.46   22%       41     1% /istclppt/cldb/mactlog/dev/cldbtmp      30.00     29.60    2%       13     1% /istclppt/temptbsWSRCELDB:istclppt:/istclppt >~/cfg/arc_log.kshIBM Tivoli Storage ManagerCommand Line Backup/Archive Client Interface  Client Version 5, Release 4, Level 0.0  Client date/time: 05/01/16   09:49:38(c) Copyright by IBM Corporation and other(s) 1990, 2007. All Rights Reserved.Archive function invoked.Node Name: WSRCELDBSession established with server WSRTSM01_SERVER1: Windows  Server Version 5, Release 4, Level 0.0  Server date/time: 05/01/16   09:49:45  Last access: 05/01/16   09:45:51Normal File-->        20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409339.LOG [Sent]     Successful deletion of archived file /istclppt/arclog/WCELPPTP/NODE0000/S0409339.LOGNormal File-->        20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409340.LOG [Sent]     Successful deletion of archived file /istclppt/arclog/WCELPPTP/NODE0000/S0409340.LOGNormal File-->        20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409341.LOG [Sent]     Successful deletion of archived file /istclppt/arclog/WCELPPTP/NODE0000/S0409341.LOGNormal File-->        20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409342.LOG [Sent]     Successful deletion of archived file /istclppt/arclog/WCELPPTP/NODE0000/S0409342.LOGNormal File-->        20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409343.LOG [Sent]     Successful deletion of archived file /istclppt/arclog/WCELPPTP/NODE0000/S0409343.LOGNormal File-->        20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409344.LOG [Sent]     Successful deletion of archived file /istclppt/arclog/WCELPPTP/NODE0000/S0409344.LOGNormal File-->        20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409345.LOG [Sent]  q v导入完成SQL3110N  The utility has completed processing.  "5381266" rows were read fromthe input file.SQL3221W  ...Begin COMMIT WORK. Input Record Count = "5381266".SQL3222W  ...COMMIT of any database changes was successful.SQL3149N  "5381266" rows were processed from the input file.  "5381266" rowswere successfully inserted into the table.  "0" rows were rejected.9、新表创建indexSET SCHEMA = 'ISTCLPPT' ;CREATE INDEX "W2"."160501_INDEX1"  ON "W2"."PPANELDB160501"    ( "CASETID" ASC )  DISALLOW REVERSE SCANS ;......10、删除view，rename原表，rename indexDROP VIEW W2.VPPANELDB;DROP VIEW W2.VPPANELDB2;RENAME TABLE W2.PPANELDB TO PPANELDBOLD;rename indexesRENAME INDEX W2.PPANELDB_INDEX1 TO OLD_INDEX1;......11、新表rename，index renameRENAME TABLE W2.PPANELDB160501 TO PPANELDB;rename indexesRENAME INDEX "W2"."160501_INDEX1" TO PPANELDB_INDEX1 ;   ......12、重建view的DDLSET SCHEMA = W2;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","WC1USR" ;CREATE VIEW W2.VPPANELDB AS     SELECT ...) ;SET SCHEMA = 'ISTCLPPT' ;GRANT CONTROL ON TABLE "W2"."VPPANELDB" TO USER "ISTCLPPT" ;GRANT SELECT , INSERT , UPDATE , DELETE ON TABLE "W2"."VPPANELDB" TO USER "ISTCLPPT" WITH GRANT OPTION ;SET SCHEMA = ISTCLPPT;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","ISTCLPPT" ;create view w2.vppaneldb2 as     select a.lt_outboxid, b.DPNGRP_ID, b.dpartno, dec(a.pnl_chpbod) pnl_chpbod     from w2.ppaneldb a, w2.ppngrp_dpn_ary b, w2.ppngrp c     where c.MASK_FLG = 'Y' and b.DPNGRP_ID = c.DPNGRP_ID and b.dpartno = a.stb_partno;SET SCHEMA = 'ISTCLPPT' ;13、runstat新表db2 runstats on table w2.PPANELDB and indexes allWSRCELDB:istclppt:/istclppt >db2 "reorgchk update statistics on table w2.PPANELDB"Doing RUNSTATS ....Table statistics:F1: 100 * OVERFLOW / CARD < 5F2: 100 * (Effective Space Utilization of Data Pages) > 70F3: 100 * (Required Pages / Total Pages) > 80SCHEMA    NAME                  CARD    OV    NP    FP ACTBLK    TSIZE  F1  F2  F3 REORG----------------------------------------------------------------------------------------W2        PPANELDB           5381266     0 5e+06 5e+06      - 2.27e+10   0  51 100 -*-----------------------------------------------------------------------------------------Index statistics:F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS) * INDEXPAGESIZE) > 50F6: (100 - PCTFREE) * ((INDEXPAGESIZE - 96) / (ISIZE + 12)) ** (NLEVELS - 2) * (INDEXPAGESIZE - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20SCHEMA   NAME                 CARD  LEAF ELEAF  LVLS ISIZE  NDEL   KEYS  F4  F5  F6  F7  F8 REORG -------------------------------------------------------------------------------------------------Table: W2.PPANELDBSYSIBM   SQL160501093122750  5e+06 24215     0     3    14     0  5e+06 100  62   1   0   0 -----W2       PPANELDB_INDEX1     5e+06  4228     0     3     7     0     13 100  77  11   0   0 -----W2       PPANELDB_INDEX10    5e+06  4119     0     3    15     0  30870 100  81   7   0   0 -----W2       PPANELDB_INDEX11    5e+06  5853     0     3    15     0 711172 100  84   5   0   0 -----W2       PPANELDB_INDEX12    5e+06  4119     0     3    11     0   3750 100  79   9   0   0 -----W2       PPANELDB_INDEX13    5e+06  3971     0     3    21     0    625 100  82   6   0   0 -----W2       PPANELDB_INDEX14    5e+06  4111     0     3    11     0      2 100  79   9   0   0 -----W2       PPANELDB_INDEX15    5e+06  4111     0     3    11     0      2 100  79   9   0   0 -----W2       PPANELDB_INDEX16    5e+06  4102     0     3    13     0  12438 100  80   8   0   0 -----W2       PPANELDB_INDEX17    5e+06  4101     0     3    13     0  11809 100  80   8   0   0 -----W2       PPANELDB_INDEX18    5e+06  4082     0     3    13     0   3604 100  80   8   0   0 -----W2       PPANELDB_INDEX19    5e+06  4112     0     3    11     0    270 100  79   9   0   0 -----W2       PPANELDB_INDEX2     5e+06  4316     0     3     5     0      8 100  76  12   0   0 -----W2       PPANELDB_INDEX20    5e+06  4228     0     3     7     0     16 100  77  11   0   0 -----W2       PPANELDB_INDEX21    5e+06  4079     0     3    13     0   1974 100  80   8   0   0 -----W2       PPANELDB_INDEX22    5e+06  4164     0     3     9     0    741 100  78  10   0   0 -----W2       PPANELDB_INDEX23    5e+06 48857     0     4   106     0  3e+06 100  88   9   0   0 -----W2       PPANELDB_INDEX24    5e+06  3920     0     3    31     0   2998 100  84   5   0   0 -----W2       PPANELDB_INDEX25    5e+06  9984     0     3    11     0  3e+06 100  87   3   0   0 -----W2       PPANELDB_INDEX26    5e+06  4210     0     3    13     0  59807 100  80   8   0   0 -----W2       PPANELDB_INDEX3     5e+06  4519     0     3     2     0      1 100  72  15   0   0 -----W2       PPANELDB_INDEX4     5e+06  4228     0     3     7     0     65 100  77  11   0   0 -----W2       PPANELDB_INDEX5     5e+06  4090     0     3    12     0      2 100  80   9   0   0 -----W2       PPANELDB_INDEX6     5e+06  4316     0     3     5     0      2 100  76  12   0   0 -----W2       PPANELDB_INDEX7     5e+06  4228     0     3     7     0     69 100  77  11   0   0 -----W2       PPANELDB_INDEX8     5e+06  4115     0     3    11     0   1771 100  79   9   0   0 -----W2       PPANELDB_INDEX9     5e+06  4228     0     3     7     0      2 100  77  11   0   0 ------------------------------------------------------------------------------------------------------CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessaryfor indexes that are not in the same sequence as the base table. When multipleindexes are defined on a table, one or more indexes may be flagged as needingREORG.  Specify the most important index for REORG sequencing.Tables defined using the ORGANIZE BY clause and the corresponding dimensionindexes have a '*' suffix to their names. The cardinality of a dimension indexis equal to the Active blocks statistic of the table.