记一次存储故障导致数据库坏块处理过程
发表于：2025-01-21 作者：千家信息网编辑
千家信息网最后更新 2025年01月21日，记一次存储故障导致数据库坏块处理过程线上架构说明： IBM DS4800存储一套 P560小机HA架构一套 两个数据库资源组平时run在HA架构中的任意一台中，资源组全部使用共享存储
千家信息网最后更新 2025年01月21日记一次存储故障导致数据库坏块处理过程
记一次存储故障导致数据库坏块处理过程
线上架构说明：
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.
数据
数据库
过程
存储
空间
信息
处理
两个
架构
程序
资源
故障
不同
使用率
备份
应用程序
情况
状态
用户
程度
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
csgo玩什么服务器
ps5星际战甲连不上服务器
ios 用什么软件开发
数据库管理系统的工作
福建工业软件开发收费
方舟怎么建设专用服务器
网络安全班会教育主题ppt
波阳软件开发技术
正在等待服务器管理器
大数据时代高校网络安全教育探究
网络安全信息意识培养ppt
吕梁网络安全教育平台
大波浪服务器
苹果手机下载软件服务器异常
财经数据库app
三级网络技术教学视频百度云
数据库安全性实验 报告
app服务器状态异常
岳阳口碑好的软件开发哪家便宜
网络安全基础课程
outlook数据库
物联网数据库ppt
宜兴埠附近有做软件开发的公司吗
100tb存储服务器
长春短视频软件开发服务商
普通高中网络技术应用选修3
瑞丽市软件开发有限公司
企业 文件服务器
软件开发行业薪酬管理制度
阿里云服务器怎么登陆
