记一次存储故障导致数据库坏块处理过程
线上架构说明：
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.