CNDBA社区
坏块类型-LOGICAL模拟及恢复
corruption_type: 坏块类型
■ ALL ZERO - 磁盘上的块头只包含0字节，在ORACLE 7，若它没有被修复，该块可能是有效的，在Oracle 8将会被格式话成标准空块.
■ FRACTURED - 数据块头看起来合理，但块头跟块后tailchk属于不同版本(值不同）.
■ CHECKSUM - 数据块的检查值不一致，不确定为什么检查值失败，但可能是数据块中间的扇区版本值不同
■ CORRUPT - 数据块被错误标识或者不是一个数据块(比如数据块地址丢失)
■ LOGICAL - 逻辑坏块
■ NOLOGGING - 数据块中没有重做日志存在(比如，主库上NOLOGGING操作可能在物理备库上产生这种类型的坏块)
前期重新创建测试环境步骤省略，详情请看文章 >>> 坏块类型-ALL ZERO模拟与修复
前期检查
17:42:03 JWT@pdb>select count(*) from jwt.marvin;
COUNT(*)
----------
10000
Elapsed: 00:00:00.13
18:05:44 JWT@pdb>select * from v$database_block_corruption;
no rows selected
Elapsed: 00:00:00.07
18:05:53 JWT@pdb>select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from jwt.marvin;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
29 228
29 238
29 230
29 244
29 246
29 227
29 232
29 233
29 236
29 247
29 231
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
29 235
29 239
29 234
29 229
29 237
29 245
17 rows selected.
Elapsed: 00:00:00.18
18:06:05 JWT@pdb>18:06:05 JWT@pdb>select name from v$datafile where file#=29;
NAME
--------------------------------------------------------------------------------
/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
Elapsed: 00:00:00.02
模拟 LOGICAL
dd 模拟修改kdbhavsp
设置只读表空间
18:06:29 JWT@pdb>alter tablespace jwt read only;
Tablespace altered.
Elapsed: 00:00:00.86
BBED> set filename '/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf'
FILENAME /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
BBED> set block 235 -- 任选表数据所在块
BLOCK# 235
BBED> map /v
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 235 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub2 wrp2_kcbh @2
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[2], 48 bytes @44
struct kdbh, 14 bytes @100
ub1 kdbhflag @100
sb1 kdbhntab @101
sb2 kdbhnrow @102
sb2 kdbhfrre @104
sb2 kdbhfsbo @106
sb2 kdbhfseo @108
sb2 kdbhavsp @110
sb2 kdbhtosp @112
struct kdbt[1], 4 bytes @114
sb2 kdbtoffs @114
sb2 kdbtnrow @116
sb2 kdbr[605] @118
ub1 freespace[816] @1328
ub1 rowdata[6044] @2144
ub4 tailchk @8188
BBED> p kdbhavsp
sb2 kdbhavsp @110 816
BBED> p /x kdbhavsp
sb2 kdbhavsp @110 0x330
BBED> set offset 110
OFFSET 110
BBED> dump /v count 20
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 235 Offsets: 110 to 129 Dba:0x00000000
-------------------------------------------------------
30033003 00005d02 8e1f841f 7a1f701f l 0.0...].....z.p.
661f5c1f l f./.
<16 bytes per line>
BBED> dump /v count 128
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 235 Offsets: 110 to 237 Dba:0x00000000
-------------------------------------------------------
30033003 00005d02 8e1f841f 7a1f701f l 0.0...].....z.p.
661f5c1f 521f481f 3e1f341f 2a1f201f l f./.R.H.>.4.*. .
161f0c1f 021ff81e ee1ee41e da1ed01e l ................
c61ebc1e b21ea81e 9e1e941e 8a1e801e l ................
761e6c1e 621e581e 4e1e441e 3a1e301e l v.l.b.X.N.D.:.0.
261e1c1e 121e081e fe1df41d ea1de01d l &...............
d61dcc1d c21db81d ae1da41d 9a1d901d l ................
861d7c1d 721d681d 5e1d541d 4a1d401d l ..|.r.h.^.T.J.@.
<16 bytes per line>
BBED> modify /x 3000
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
MODE Edit
BBED> modify /x 3000
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 235 Offsets: 110 to 237 Dba:0x00000000
------------------------------------------------------------------------
30003003 00005d02 8e1f841f 7a1f701f 661f5c1f 521f481f 3e1f341f 2a1f201f
161f0c1f 021ff81e ee1ee41e da1ed01e c61ebc1e b21ea81e 9e1e941e 8a1e801e
761e6c1e 621e581e 4e1e441e 3a1e301e 261e1c1e 121e081e fe1df41d ea1de01d
d61dcc1d c21db81d ae1da41d 9a1d901d 861d7c1d 721d681d 5e1d541d 4a1d401d
<32 bytes per line>
BBED> sum
Check value for File 0, Block 235:
current = 0x9642, required = 0x9542
BBED> sum apply
Check value for File 0, Block 235:
current = 0x9542, required = 0x9542
查看是否出现坏块..
18:08:33 JWT@pdb>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.53
18:29:08 JWT@pdb>select count(*) from marvin;
COUNT(*)
----------
10000
Elapsed: 00:00:00.04
18:29:15 JWT@pdb>select * from v$database_block_corruption;
no rows selected
Elapsed: 00:00:00.05
未发现坏块...
--不加check logical的validate检查,更新视图
RMAN> validate datafile 29;
Starting validate at 2018-03-18 18:32:28
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=00029 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.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
---- ------ -------------- ------------ --------------- ----------
29 OK 0 6 1281 545450262
File Name: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 183
Index 0 26
Other 0 1065
Finished validate at 2018-03-18 18:32:30
18:31:34 JWT@pdb>select * from v$database_block_corruption;
no rows selected
Elapsed: 00:00:00.06
数据块正常未发现坏块....
--只有check logical才能发现logical corruption
RMAN> validate check logical datafile 29;
Starting validate at 2018-03-18 18:33:45
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=00029 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.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
---- ------ -------------- ------------ --------------- ----------
29 FAILED 0 6 1281 545450262
File Name: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 1 183
Index 0 26
Other 0 1065
validate found one or more corrupt blocks
See trace file /u01/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7298.trc for details
Finished validate at 2018-03-18 18:33:46
RMAN>
---v$database_block_corrupt并没有将CORRUPTION_TYPE标记为logical，依旧是corrupt
18:33:20 JWT@pdb>select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
29 235 1 545450262 CORRUPT 6
Elapsed: 00:00:00.00
---validate运行期间会将检测到的corrupt block信息更新到alert.log和trace file，从alert & tracefile的信息清楚的表明是logical corruption
2018-03-18T18:29:08.501385+08:00
PDB001(6):ALTER SYSTEM: Flushing buffer cache inst=0 container=6 global
2018-03-18T18:33:45.524686+08:00
PDB001(6):Error backing up file 29, block 235: logical corruption
Checker run found 1 new persistent data failures
--validate命令是将corruption的具体信息存放在.trc文件，dbverify能直接在结果中输出corrupt的原因
oracle@12cDG:/u01/oracle/diag/rdbms/orcl/orcl/trace>dbv file=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
DBVERIFY: Release 12.2.0.1.0 - Production on Sun Mar 18 18:37:44 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
Block Checking: DBA = 121635051, Block Type = KTB-managed data block
data header at 0x7fd89de19064
kdbchk: the amount of space used is not equal to block size
used=7272 fsc=0 avsp=48 dtu=7320 dtl=8088 (s3=0, typ=6)
Page 235 failed with check code 6110
DBVERIFY - Verification complete
Total Pages Examined : 1280
Total Pages Processed (Data) : 183
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 26
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1065
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 545450262 (0.545450262)
再次验证
18:50:58 JWT@pdb>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.24
18:51:06 JWT@pdb>select count(*) from marvin;
COUNT(*)
----------
10000
Elapsed: 00:00:00.00
18:51:11 JWT@pdb>select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
29 235 1 545450262 CORRUPT 6
Elapsed: 00:00:00.02
18:52:02 JWT@pdb>insert into marvin values(2,'dd');
insert into marvin values(2,'dd')
*
ERROR at line 1:
ORA-00372: file 29 cannot be modified at this time
ORA-01110: data file 29:
'/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf'
Elapsed: 00:00:00.13
18:53:01 JWT@pdb>alter tablespace jwt read write;
Tablespace altered.
Elapsed: 00:00:00.11
18:53:16 JWT@pdb>insert into marvin values(2,'dd');
1 row created.
Elapsed: 00:00:00.00
18:53:18 JWT@pdb>commit
18:53:20 2 ;
Commit complete.
Elapsed: 00:00:00.01
18:53:22 JWT@pdb>select count(*) from marvin;
COUNT(*)
----------
10001
Elapsed: 00:00:00.00
可以看到该类型坏块，并不影响正常事务以及查询操作....
恢复方法
1、BBED
1、使用原未退出的BBED环境，使用revert命令恢复,当然这实际环境并不可取
2、退出当前环境重新进入BBED，恢复
BBED> set filename '/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf'
FILENAME /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
BBED> set block 235
BLOCK# 235
--查看数据区域头结构
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
sb1 kdbhntab @101 1
sb2 kdbhnrow @102 605
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 1228
sb2 kdbhfseo @108 2044
sb2 kdbhavsp @110 48
sb2 kdbhtosp @112 816
--从上可以看到kdbhavsp 与 kdbhtosp值不同，所以导致报LOGICAL坏块类型
--验证坏块
BBED> verify
DBVERIFY - Verification starting
FILE = /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
BLOCK = 235
Block Checking: DBA = 121635051, Block Type = KTB-managed data block
data header at 0x7fa3a2410064
kdbchk: the amount of space used is not equal to block size
used=7272 fsc=0 avsp=48 dtu=7320 dtl=8088 (s3=0, typ=6)
Block 235 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 = 816 = kdbhtosp
查看itl中fsc是否全是0，若不为0则修改为0，若是则不修改
BBED> p ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0003
ub2 kxidslt @46 0x0018
ub4 kxidsqn @48 0x00002547
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x024002c2
ub2 kubaseq @56 0x0455
ub1 kubarec @58 0x3e
ub2 ktbitflg @60 0x225d (KTBFUPB)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x2082e916
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
从上可以看到fsc值(_ktbitfsc)都等于0，所以不用修改，只需要修改kdbhavsp值就可以了
考虑到oracle数据块存储方式是以16进制存储的另外使用存储计算机中使用小字节存储，所以需要进制进行换算以及匹配计算机小字节规则：
kdbhavsp=816(十进制)=330(十六进制0330)=3003计算机存储(dump命令出的结果四位字节，空位补0保持值不变即可)
BBED> set offset 110
OFFSET 110
BBED> dump /v count 20
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 235 Offsets: 110 to 129 Dba:0x00000000
-------------------------------------------------------
30003003 00005d02 8e1f841f 7a1f701f l 0.0...].....z.p.
661f5c1f l f./.
<16 bytes per line>
BBED> modify /x 3003
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
MODE Edit
BBED> modify /x 3003
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 235 Offsets: 110 to 129 Dba:0x00000000
------------------------------------------------------------------------
30033003 00005d02 8e1f841f 7a1f701f 661f5c1f
<32 bytes per line>
BBED> sum
Check value for File 0, Block 235:
current = 0x9542, required = 0x9642
BBED> sum apply
Check value for File 0, Block 235:
current = 0x9642, required = 0x9642
再次检查测试是否还存在坏块
BBED> verify
DBVERIFY - Verification starting
FILE = /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
BLOCK = 235
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@12cDG:/home/oracle>dbv file=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
DBVERIFY: Release 12.2.0.1.0 - Production on Sun Mar 18 20:53:00 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
DBVERIFY - Verification complete
Total Pages Examined : 1280
Total Pages Processed (Data) : 183
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 26
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1065
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 545471551 (0.545471551)
--z至此数据块恢复成功....
2、备份恢复
RMAN>blockrecover datafile 29 block 235;
注意RMAN的BLOCKRECOVER在恢复的时候是不需要将数据文件置于脱机状态的。
或者
库为mount状态
RMAN>restore pluggable database pdb001 datafile 29;
RMAN>recover pluggable database pdb001;
版权声明：本文为博主原创文章，未经博主允许不得转载。
- 上一篇：坏块类型-CORRUPT模拟及恢复
- 下一篇：坏块类型-NOLOGGING模拟及恢复
