【Oracle坏块】坏块类型：LOGICAL
一、模拟LOGICAL
模拟环境参考文章【Oracle坏块】Oracle坏块介绍 & 坏块类型：ALL ZERO
模拟LOGICAL
BBED模拟修改kdbhavsp
BBED> set file 22 FILE# 22 BBED> set block 131 BLOCK# 131 BBED> p kdbhavsp sb2 kdbhavsp @134 819 BBED> p /x kdbhavsp sb2 kdbhavsp @134 0x333 BBED> set offset 134 OFFSET 134 BBED> d /v count 128 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 134 to 261 Dba:0x05800083 ------------------------------------------------------- 33033303 00006302 771f6e1f 651f5c1f l 3.3...c.w.n.e.\. 531f4a1f 411f381f 2f1f261f 1d1f141f l S.J.A.8./.&..... 0b1f021f f91ef01e e71ede1e d51ecc1e l .....ܮӮʮ c31eba1e b11ea81e 9f1e961e 8d1e841e l nº.±.¨......... 7b1e721e 691e601e 571e4e1e 451e3c1e l {.r.i.`.W.N.E.<. 331e2a1e 211e181e 0f1e061e fd1df41d l 3.*.!....... eb1de21d d91dd01d c71dbe1d b51dac1d l 鯢.ήŮ¾.µ.¬. a31d9a1d 911d881d 7f1d761d 6d1d641d l £.........v.m.d. <16 bytes per line> BBED> modify /x 3000 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 134 to 261 Dba:0x05800083 ------------------------------------------------------------------------ 30003303 00006302 771f6e1f 651f5c1f 531f4a1f 411f381f 2f1f261f 1d1f141f 0b1f021f f91ef01e e71ede1e d51ecc1e c31eba1e b11ea81e 9f1e961e 8d1e841e 7b1e721e 691e601e 571e4e1e 451e3c1e 331e2a1e 211e181e 0f1e061e fd1df41d eb1de21d d91dd01d c71dbe1d b51dac1d a31d9a1d 911d881d 7f1d761d 6d1d641d <32 bytes per line> BBED> sum Check value for File 22, Block 131: current = 0x39a7, required = 0x3aa4 BBED> sum apply Check value for File 22, Block 131: current = 0x3aa4, required = 0x3aa4
查看是否出现坏块
SQL> select * from v$database_block_corruption; no rows selected SQL> alter system flush buffer_cache; System altered. SQL> select count(*) from test; COUNT(*) ---------- 20000 SQL> select * from v$database_block_corruption; no rows selected
RMAN下使用不加--check logical的validate检查，更新视图
RMAN> validate datafile 22; Starting validate at 02-AUG-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00022 name=/u01/app/oracle/oradata/T1/pdb/test.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 22 OK 0 3929 4096 3235226 File Name: /u01/app/oracle/oradata/T1/pdb/test.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 35 Index 0 0 Other 0 132 Finished validate at 02-AUG-21
数据块正常未发现坏块
------只有check logical才能发现logical corruption
RMAN> validate check logical datafile 22; Starting validate at 02-AUG-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00022 name=/u01/app/oracle/oradata/T1/pdb/test.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 22 FAILED 0 3929 4096 3235226 File Name: /u01/app/oracle/oradata/T1/pdb/test.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 1 35 Index 0 0 Other 0 132 validate found one or more corrupt blocks See trace file /u01/app/oracle/diag/rdbms/t1/T1/trace/T1_ora_26102.trc for details Finished validate at 02-AUG-21
查看视图
SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID ---------- ---------- ---------- ------------------ --------- ---------- 22 131 1 3003657 CORRUPT 3
------v$database_block_corrupt并没有将corruption_type标记为logical，依旧是corrupt
validate运行期间会检测到的corrupt block信息会更新到alter.log和trace file，从alter&tracefile的信息清楚表明是logical corruption
PDB(3):ALTER SYSTEM: Flushing buffer cache inst=0 container=3 global 2021-08-02T10:14:38.810716+08:00 PDB(3):Error backing up file 22, block 131: logical corruption Checker run found 1 new persistent data failures
validate命令是将corruption的具体信息存放在.trace文件，dbverify能直接在结果中输出corrupt的原因
[oracle@T1 trace]$ dbv file=/u01/app/oracle/oradata/T1/pdb/test.dbf DBVERIFY: Release 19.0.0.0.0 - Production on Mon Aug 2 10:24:42 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/T1/pdb/test.dbf Block Checking: DBA = 92274819, Block Type = KTB-managed data block data header at 0x7fc33099f07c kdbchk: the amount of space used is not equal to block size used=7245 fsc=0 avsp=48 dtu=7293 dtl=8064 (s3=0, typ=6) Page 131 failed with check code 6110 DBVERIFY - Verification complete Total Pages Examined : 4096 Total Pages Processed (Data) : 35 Total Pages Failing (Data) : 1 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 132 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 3929 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 3235226 (0.3235226)
再次验证
SQL> alter system flush buffer_cache; System altered. SQL> select count(*) from test; COUNT(*) ---------- 20000 SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID ---------- ---------- ---------- ------------------ --------- ---------- 22 131 1 3003657 CORRUPT 3 Commit complete. SQL> insert into test values(2,'dd'); 1 row created. SQL> commit; Commit complete. SQL> select count(*) from test; COUNT(*) ---------- 20001
可以看到LOGICAL类型的坏块，并不影响事务以及查询操作
二、恢复方法
BBED> set file 22 FILE# 22 BBED> set block 131 BLOCK# 131 BBED> p kdbh struct kdbh, 14 bytes @124 ub1 kdbhflag @124 0x00 (NONE) sb1 kdbhntab @125 1 sb2 kdbhnrow @126 611 sb2 kdbhfrre @128 -1 sb2 kdbhfsbo @130 1240 sb2 kdbhfseo @132 2059 sb2 kdbhavsp @134 48 sb2 kdbhtosp @136 819 ------kdbhavsp和kdbhosp值不同，所以导致LOGICAL坏块类型
------验证坏块
BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/T1/pdb/test.dbf BLOCK = 131 Block Checking: DBA = 92274819, Block Type = KTB-managed data block data header at 0x7fd08a74107c kdbchk: the amount of space used is not equal to block size used=7245 fsc=0 avsp=48 dtu=7293 dtl=8064 (s3=0, typ=6) Block 131 failed with check code 6110 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED
从结果可知，kdbhavsp=819=kdbhosp
查看ITL中fsc是否全是0，若不为0则修改为0，若是则不修改
BBED> p ktbbhitl struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0001 ub2 kxidslt @46 0x0008 ub4 kxidsqn @48 0x00000308 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00000000 ub2 kubaseq @56 0x0000 ub1 kubarec @58 0x00 ub2 ktbitflg @60 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x002dd4fc struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0000 ub2 kxidslt @70 0x0000 ub4 kxidsqn @72 0x00000000 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00000000 ub2 kubaseq @80 0x0000 ub1 kubarec @82 0x00 ub2 ktbitflg @84 0x0000 (NONE) union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00000000 struct ktbbhitl[2], 24 bytes @92 struct ktbitxid, 8 bytes @92 ub2 kxidusn @92 0x0000 ub2 kxidslt @94 0x0000 ub4 kxidsqn @96 0x00000000 struct ktbituba, 8 bytes @100 ub4 kubadba @100 0x00000000 ub2 kubaseq @104 0x0000 ub1 kubarec @106 0x00 ub2 ktbitflg @108 0x0000 (NONE) union _ktbitun, 2 bytes @110
sb2 _ktbitfsc @110 0 ub2 _ktbitwrp @110 0x0000 ub4 ktbitbas @112 0x00000000
从上可以看到fsc值（_ktbitfsc）都等于0，所以不用修改，只需要修改kdbhavsp值就可以了
考虑到oracle数据块存储方式是以16进制存储的，另外使用存储计算机中使用小字节存储，所以需要进制换算以及匹配计算机小字节规则：
kdbhavso=819（十进制）=0333（十六进制）=3303计算机存储（dump命令出来的结果，空位补0保持不变即可）
BBED> set file 22 FILE# 22 BBED> set block 131 BLOCK# 131 BBED> set offset 134 ------kbdhavsp的偏移量 OFFSET 134 BBED> d /v count 20 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 134 to 153 Dba:0x05800083 ------------------------------------------------------- 30003303 00006302 771f6e1f 651f5c1f l 0.3...c.w.n.e.\. 531f4a1f l S.J. <16 bytes per line> BBED> modify /x 3303 File: /u01/app/oracle/oradata/T1/pdb/test.dbf (22) Block: 131 Offsets: 134 to 153 Dba:0x05800083 ------------------------------------------------------------------------ 33033303 00006302 771f6e1f 651f5c1f 531f4a1f <32 bytes per line> BBED> sum apply Check value for File 22, Block 131: current = 0x39a7, required = 0x39a7
检查坏块
BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/T1/pdb/test.dbf BLOCK = 131 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED
由上可知没有坏块产生
[oracle@T1 trace]$ dbv file=/u01/app/oracle/oradata/T1/pdb/test.dbf DBVERIFY: Release 19.0.0.0.0 - Production on Mon Aug 2 10:48:54 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/T1/pdb/test.dbf DBVERIFY - Verification complete Total Pages Examined : 4096 Total Pages Processed (Data) : 43 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 132 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 3921 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 3242039 (0.3242039)
至此数据恢复成功
2、备份恢复
