CNDBA社区
corruption_type: 坏块类型
■ ALL ZERO - 磁盘上的块头只包含0字节，在ORACLE 7，若它没有被修复，该块可能是有效的，在Oracle 8将会被格式话成标准空块.
■ FRACTURED - 数据块头看起来合理，但块头跟块后tailchk属于不同版本(值不同）.
■ CHECKSUM - 数据块的检查值不一致，不确定为什么检查值失败，但可能是数据块中间的扇区版本值不同
■ CORRUPT - 数据块被错误标识或者不是一个数据块(比如数据块地址丢失)
■ LOGICAL - 逻辑坏块
■ NOLOGGING - 数据块中没有重做日志存在(比如，主库上NOLOGGING操作可能在物理备库上产生这种类型的坏块)
前期创建测试环境步骤省略，详情请看文章 >>> 坏块类型-ALL ZERO模拟与修复
前期环境检查
13:48:57 SYS@orcl>alter database open;
Database altered.
Elapsed: 00:00:03.99
13:49:07 SYS@orcl>conn jwt/123456@pdb;
Connected.
13:49:15 > select * from v$database_block_corruption;
13:49:15 > select * from v$database_block_corruption;
no rows selected
Elapsed: 00:00:00.02
13:55:17 SYS@orcl>select count(*) from jwt.marvin;
COUNT(*)
----------
20000
Elapsed: 00:00:00.03
--说明当前环境并不存在坏块现象
13:55:40 SYS@orcl>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 211
29 220
29 230
29 213
29 218
29 215
29 216
29 217
29 221
29 214
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
29 223
29 231
29 222
29 212
29 219
29 229
SYS@orcl>select name from v$datafile where file#=29;
NAME
--------------------------------------------------------------------------------
/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
Elapsed: 00:00:00.03
模拟 FRACTURED
BBED模拟 前后版本不一致(tailchk)
设置只读表空间
14:00:27 SYS@orcl>14:00:27 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 228 -- 任选表数据所在块
BLOCK# 228
BBED> show
FILE# 0
BLOCK# 228
OFFSET 0
DBA 0x00000000 (0 0,228)
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
查看tailchk值
BBED> p tailchk
ub4 tailchk @8188 0x6e1d0601
tailchk = SCN BASE低两位 + type +seq (bas_kcbh低两位字节 + type_kcbh + seq_kcbh)
BBED> p bas_kcbh
ub4 bas_kcbh @8 0x20786e1d
BBED> p type_kcbh
ub1 type_kcbh @0 0x06
BBED> p seq_kcbh
ub1 seq_kcbh @14 0x01
BBED> set offset 8188
OFFSET 8188
BBED> dump /v count 10
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 228 Offsets: 8188 to 8191 Dba:0x00000000
-------------------------------------------------------
01061d6e l ...n
<16 bytes per line>
BBED> sum
Check value for File 0, Block 228:
current = 0xe9fb, required = 0xe9fb
BBED> set mode edit
MODE Edit
BBED> modify /x 01061de6 offset 8188
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 228 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
01061de6
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 228:
current = 0x61fb, required = 0x61fb
BBED> print tailchk
ub4 tailchk @8188 0xe61d0601
--刷新数据库缓存
15:55:03 JWT@pdb>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.19
16:02:07 JWT@pdb>select count(*) from jwt.marvin;
select count(*) from jwt.marvin
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 29, block # 228)
ORA-01110: data file 29:
'/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf'
Elapsed: 00:00:09.20
--发现坏块
16:02:52 JWT@pdb>select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
29 228 1 0 FRACTURED 6
Elapsed: 00:00:00.09
恢复方法:
1、BBED
由于该类型的坏块，是使用BBED工具模拟破坏tailchk的..所以可以直接在模拟页面直接使用revert命令恢复所有操作，在刷新数据库缓存即可(这种实际环境是不可能用到的)
恢复手段：该类型的坏块是由于tail与块头值不一致，导致数据块版本不一致
oracle@12cDG:/u01/oracle/12.2.0/dbs>bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Mar 15 16:32:13 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 228
BLOCK# 228
BBED> p tailchk
ub4 tailchk @8188 0xe61d0601
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub2 wrp2_kcbh @2 0x0000
ub4 rdba_kcbh @4 0x074000e4
ub4 bas_kcbh @8 0x20786e1d
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV)
ub2 chkval_kcbh @16 0x61fb
ub2 spare3_kcbh @18 0x0000
BBED> dump /v offset 8188 count 10
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 228 Offsets: 8188 to 8191 Dba:0x00000000
-------------------------------------------------------
01061de6 l ....
<16 bytes per line>
从上可知，数据块tailchk值为6e1d0601
由于机器低字节序的原因，所有修复使用值为01061d6e
···
也可以从DBV命令中last change scn 参数里的最后四位获取到tailchk值中的前四个字节 即SCN BASE
（last change scn 参数里的最后四位 = SCN BASE值 --需要修改的数据库版本值)
oracle@12cDG:/home/oracle>dbv file= /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
DBVERIFY: Release 12.2.0.1.0 - Production on Thu Mar 15 16:32:54 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 228 is influx - most likely media corrupt
Corrupt block relative dba: 0x074000e4 (file 29, block 228)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x074000e4
last change scn: 0x0000.0000.20786e1d seq: 0x1 flg: 0x06
spare3: 0x0
consistency value in tail: 0xe61d0601
check value in block header: 0x61fb
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Pages Examined : 1280
Total Pages Processed (Data) : 79
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1194
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6
Total Pages Marked Corrupt : 1
Total Pages Influx : 1
Total Pages Encrypted : 0
Highest block SCN : 544763421 (0.544763421)
···
BBED> modify /x 01061d6e offset 8188
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
MODE Edit
BBED> modify /x 01061d6e offset 8188
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 228 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
01061d6e
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 228:
current = 0xe9fb, required = 0xe9fb
BBED>
验证....
16:14:59 JWT@pdb>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.20
16:43:56 JWT@pdb>select count(*) from jwt.marvin;
COUNT(*)
----------
10000
Elapsed: 00:00:00.01
数据块恢复成功....
2、DBMS_REPAIR包恢复 (丢失数据)
此方法步骤省略，详情见坏块类型-ALL ZERO模拟与修复
3、CTAS方式复制（丢失数据）
此方法步骤省略，详情请见坏块类型-ALL ZERO模拟与修复
4、备份恢复（不丢）
版权声明：本文为博主原创文章，未经博主允许不得转载。
- 上一篇：坏块类型-ALL ZERO模拟与修复
- 下一篇：坏块类型-CHECKSUM模拟及恢复
