CNDBA社区
corruption_type: 坏块类型
■ ALL ZERO - 磁盘上的块头只包含0字节，在ORACLE 7，若它没有被修复，该块可能是有效的，在Oracle 8将会被格式话成标准空块.
■ FRACTURED - 数据块头看起来合理，但块头跟块后tailchk属于不同版本(值不同）.
■ CHECKSUM - 数据块的检查值不一致，不确定为什么检查值失败，但可能是数据块中间的扇区版本值不同
■ CORRUPT - 数据块被错误标识或者不是一个数据块(比如数据块地址丢失)
■ LOGICAL - 逻辑坏块
■ NOLOGGING - 数据块中没有重做日志存在(比如，主库上NOLOGGING操作可能在物理备库上产生这种类型的坏块)
前期重新创建测试环境步骤省略，详情请看文章 >>> 坏块类型-ALL ZERO模拟与修复
前期检查
13:50:41 JWT@pdb>select count(*) from jwt.marvin;
COUNT(*)
----------
10000
Elapsed: 00:00:00.06
13:51:50 JWT@pdb>13:51:50 JWT@pdb>select * from v$database_block_corruption;
no rows selected
Elapsed: 00:00:00.15
--当前不存在坏块
13:52:04 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.42
13:53:42 JWT@pdb>select name from v$datafile where file#=29;
NAME
--------------------------------------------------------------------------------
/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
Elapsed: 00:00:00.03
模拟 CHECKSUM
dd模拟 修改checkval_kcbh
SYS@orcl>alter tablespace jwt read only;
Tablespace altered.
Elapsed: 00:00:00.89
BBED> set filename '/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf'
FILENAME /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
BBED> set block 233 -- 任选表数据所在块
BLOCK# 233
BBED> show
FILE# 0
BLOCK# 233
OFFSET 0
DBA 0x00000000 (0 0,233)
FILENAME /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
BIFILE bifile.bbd
LISTFILE
BLOCKSIZE 8192
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
查看checkval值
BBED> p chkval_kcbh
ub2 chkval_kcbh @16 0xf9e7
BBED> map /v
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 233 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> dump /v offset 16 count 20
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 233 Offsets: 16 to 35 Dba:0x00000000
-------------------------------------------------------
e7f90000 01000000 68710100 7fe27a20 l ........hq....z
00800000 l ....
<16 bytes per line>
BBED> set offset 16
OFFSET 16
BBED> modify /x e7f0
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
MODE Edit
BBED> modify /x e7f0
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 233 Offsets: 16 to 35 Dba:0x00000000
------------------------------------------------------------------------
e7f00000 01000000 68710100 7fe27a20 00800000
<32 bytes per line>
....不退出BBED....
查看是否出现坏块..
22:13:47 SYS@orcl>alter session set container =pdb001;
Session altered.
Elapsed: 00:00:00.20
22:13:58 SYS@orcl>conn jwt/123456@pdb;
22:05:05 JWT@pdb>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.76
22:15:09 JWT@pdb>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.03
22:16:47 JWT@pdb>select count(*) from marvin;
select count(*) from marvin
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 29, block # 233)
ORA-01110: data file 29:
'/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf'
Elapsed: 00:00:04.61
22:18:04 JWT@pdb>select * from v$database_block_corruption;
no rows selected
Elapsed: 00:00:00.02
视图中没出现...实际查询报错有坏块...
更新视图
rman target /
RMAN> validate datafile 29;
Starting validate at 2018-03-16 22:18:56
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
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 544924312
File Name: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 1 95
Index 0 0
Other 0 1179
validate found one or more corrupt blocks
See trace file /u01/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_13652.trc for details
Finished validate at 2018-03-16 22:18:59
再次查询，发现坏块
22:18:11 JWT@pdb>select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
29 233 1 0 CHECKSUM 6
Elapsed: 00:00:00.01
恢复方法
1、BBED
1、使用原未退出的BBED环境，使用revert命令恢复,当然这实际环境并不可取
2、退出当前环境重新进入BBED，恢复
--获取dump中的checksum值 即当前坏块值e7f0
oracle@12cDG:/home/oracle>dd if=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf bs=8192 skip=233 count=1 conv=swab|od -x
1+0 records in
1+0 records out
0000000 06a2 0000 e900 4007 98e2 7a20 0000 0106
0000020 e7f0 0000 0100 0000 6871 0100 7fe2 7a20
0000040 0080 0000 0200 3201 e000 4007 0600 0a00
0000060 a224 0000 740a 4002 5903 3d00 5d22 0000
0000100 98e2 7a20 0000 0000 0000 0000 0000 0000
0000120 0000 0000 0000 0000 0000 0000 0000 0000
0000140 0000 0000 0001 5d02 ffff cc04 fc07 3003
0000160 3003 0000 5d02 8e1f 841f 7a1f 701f 661f
0000200 5c1f 521f 481f 3e1f 341f 2a1f 201f 161f
0000220 0c1f 021f f81e ee1e e41e da1e d01e c61e
**
中间省略输出
*
0017620 0203 c23d 4702 7373 2c01 0203 c23d 4602
0017640 7373 2c01 0203 c23d 4502 7373 2c01 0203
0017660 c23d 4402 7373 2c01 0203 c23d 4302 7373
0017700 2c01 0203 c23d 4202 7373 2c01 0203 c23d
0017720 4102 7373 2c01 0203 c23d 4002 7373 2c01
0017740 0203 c23d 3f02 7373 2c01 0203 c23d 3e02
0017760 7373 2c01 0203 c23d 3d02 7373 0106 98e2
0020000
--获取当前数据块坏块的CHECKSUM值
oracle@12cDG:/home/oracle>dd if=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf bs=8192 skip=233 count=1 |od -x
0000000 a206 0000 00e9 0740 e298 207a 0000 0601
0000020 f0e7 0000 0001 0000 7168 0001 e27f 207a
0000040 8000 0000 0002 0132 00e0 0740 0006 000a
0000060 24a2 0000 0a74 0240 0359 003d 225d 0000
0000100 e298 207a 0000 0000 0000 0000 0000 0000
0000120 0000 0000 0000 0000 0000 0000 0000 0000
0000140 0000 0000 0100 025d ffff 04cc 07fc 0330
0000160 0330 0000 025d 1f8e 1f84 1f7a 1f70 1f66
0000200 1f5c 1f52 1f48 1f3e 1f34 1f2a 1f20 1f16
0000220 1f0c 1f02 1ef8 1eee 1ee4 1eda 1ed0 1ec6
0000240 1ebc 1eb2 1ea8 1e9e 1e94 1e8a 1e80 1e76
**
中间省略输出
0017560 012c 0302 3dc2 024a 7373 012c 0302 3dc2
0017600 0249 7373 012c 0302 3dc2 0248 7373 012c
0017620 0302 3dc2 0247 7373 012c 0302 3dc2 0246
0017640 7373 012c 0302 3dc2 0245 7373 012c 0302
0017660 3dc2 0244 7373 012c 0302 3dc2 0243 7373
0017700 012c 0302 3dc2 0242 7373 012c 0302 3dc2
0017720 0241 7373 012c 0302 3dc2 0240 7373 012c
0017740 0302 3dc2 023f 7373 012c 0302 3dc2 023e
0017760 7373 012c 0302 3dc2 023d 7373 0601 e298
0020000
退出之前BBED环境重新进入BBED
oracle@12cDG:/home/oracle>bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sat Mar 17 16:27:55 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf'
FILENAME /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
BBED> set block 233
BLOCK# 233
--直接sum apply即可，sum 要求值即为当前数据块检查值要求值
BBED> sum
Check value for File 0, Block 233:
current = 0xf0e7, required = 0xf9e7
BBED> sum apply
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
MODE Edit
BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
Check value for File 0, Block 233:
current = 0xf9e7, required = 0xf9e7
BBED> sum
Check value for File 0, Block 233:
current = 0xf9e7, required = 0xf9e7
刷新数据库缓存
16:22:41 JWT@pdb>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.10
16:30:42 JWT@pdb>select count(*) from jwt.marvin;
COUNT(*)
----------
10000
Elapsed: 00:00:00.05
16:30:47 JWT@pdb>select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
29 233 1 0 CHECKSUM 6
Elapsed: 00:00:00.07
更新视图
RMAN> validate datafile 29;
Starting validate at 2018-03-17 16:31:33
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
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 545080043
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-17 16:31:35
再次验证
16:30:59 JWT@pdb>select * from v$database_block_corruption;
no rows selected
Elapsed: 00:00:00.00
数据块恢复成功
至于数据块恢复成功
注意: 该坏块类型要么使用备份恢复（即当前备份）,要么使用BBED在开启参数DB_BLOCK_CHECKSUM=TRUE(typical）默认开启，直接使用sum命令检查，在sum apply应用即可，其他丢数据的方式就不考虑了.
最好直接使用BBED是最快的…
还有种方法可以算出当前块检查值，即DBV命令
EXAMPLE 1:
oracle@12cDG:/home/oracle>dbv file=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
DBVERIFY: Release 12.2.0.1.0 - Production on Sat Mar 17 16:22:46 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
Page 233 is marked corrupt
Corrupt block relative dba: 0x074000e9 (file 29, block 233)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x074000e9
last change scn: 0x0000.0000.207ae298 seq: 0x1 flg: 0x06
spare3: 0x0
consistency value in tail: 0xe2980601
check value in block header: 0xf0e7
computed block checksum: 0x900
DBVERIFY - Verification complete
Total Pages Examined : 1280
Total Pages Processed (Data) : 182
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 : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 545080043 (0.545080043)
发现坏块
块头当前检查值 ： f0e7 --BBED 块检查current值
计算块检查值: 900 --这里可以理解current 到 required的差值（即 current + 差值 = required 需进行异或运算）
两者异或值为f9e7 等同required值
BBED> sum
Check value for File 0, Block 233:
current = 0xf0e7, required = 0xf9e7
EXAMPLE 2：
oracle@12cDG:/home/oracle>dbv file=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
DBVERIFY: Release 12.2.0.1.0 - Production on Sat Mar 17 16:38:42 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
Page 233 is marked corrupt
Corrupt block relative dba: 0x074000e9 (file 29, block 233)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x074000e9
last change scn: 0x0000.0000.207ae298 seq: 0x1 flg: 0x06
spare3: 0x0
consistency value in tail: 0xe2980601
check value in block header: 0xf06e
computed block checksum: 0x989
DBVERIFY - Verification complete
Total Pages Examined : 1280
Total Pages Processed (Data) : 182
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 : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 545080043 (0.545080043)
发现坏块
块头当前检查值 ： f06e --BBED 块检查current值
计算块检查值: 989 --这里可以理解current 到 required的差值（即 current + 差值 = required 需进行异或运算）
两者异或值为f9e7 等同required值
BBED> sum
Check value for File 0, Block 233:
current = 0xf06e, required = 0xf9e7
所以可以直接可以根绝两者计算块检查要求值来修改，不过没有直接sum再 sum apply来的直接
总结：该坏块类型出现的几率应该是非常少的…
版权声明：本文为博主原创文章，未经博主允许不得转载。
- 上一篇：坏块类型-FRACTURED模拟与修复
- 下一篇：坏块类型-CORRUPT模拟及恢复
