CNDBA社区
坏块类型-CORRUPT模拟及恢复
corruption_type: 坏块类型
■ ALL ZERO - 磁盘上的块头只包含0字节，在ORACLE 7，若它没有被修复，该块可能是有效的，在Oracle 8将会被格式话成标准空块.
■ FRACTURED - 数据块头看起来合理，但块头跟块后tailchk属于不同版本(值不同）.
■ CHECKSUM - 数据块的检查值不一致，不确定为什么检查值失败，但可能是数据块中间的扇区版本值不同
■ CORRUPT - 数据块被错误标识或者不是一个数据块(比如数据块地址丢失)
■ LOGICAL - 逻辑坏块
■ NOLOGGING - 数据块中没有重做日志存在(比如，主库上NOLOGGING操作可能在物理备库上产生这种类型的坏块)
前期重新创建测试环境步骤省略，详情请看文章 >>> 坏块类型-ALL ZERO模拟与修复
前期检查
19:17:58 JWT@pdb>select count(*) from jwt.marvin;
COUNT(*)
----------
10000
Elapsed: 00:00:00.01
19:18:18 JWT@pdb>select * from v$database_block_corruption;
no rows selected
Elapsed: 00:00:00.01
19:18:43 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 227
29 232
29 233
29 236
29 231
29 235
29 239
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
29 243
29 234
29 229
29 237
29 245
29 242
17 rows selected.
Elapsed: 00:00:00.24
19:19:25 JWT@pdb>select name from v$datafile where file#=29;
NAME
--------------------------------------------------------------------------------
/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
Elapsed: 00:00:00.00
模拟 CORRUPT
dd 模拟修改type_kcbh
设置只读表空间
19:19:33 JWT@pdb>alter tablespace jwt read only;
Tablespace altered.
Elapsed: 00:00:00.59
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> 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 type_kcbh
ub1 type_kcbh @0 0x06
BBED> dump /v offset 0 count 30
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 235 Offsets: 0 to 29 Dba:0x00000000
-------------------------------------------------------
06a20000 eb004007 30337e20 00000106 l ......@.03~ ....
e4f10000 01001200 28720100 1733 l ........(r...3
<16 bytes per line>
BBED> set mode edit
MODE Edit
BBED> modify /x ff offset 0
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 235 Offsets: 0 to 29 Dba:0x00000000
------------------------------------------------------------------------
ffa20000 eb004007 30337e20 00000106 e4f10000 01001200 28720100 1733
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 235:
current = 0xf11d, required = 0xf11d
查看是否出现坏块..
19:23:11 JWT@pdb>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:01.13
19:51:07 JWT@pdb>select count(*) from marvin;
select count(*) from marvin
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 29, block # 235)
ORA-01110: data file 29:
'/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf'
Elapsed: 00:00:01.15
19:51:15 JWT@pdb>select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
29 235 1 0 CORRUPT 6
Elapsed: 00:00:00.14
恢复方法
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> set offset 0
OFFSET 0
BBED> map /v
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 235 Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (255)
BBED> p kcbh
BBED-00400: invalid blocktype (255)
BBED> p type_kcbh
BBED-00400: invalid blocktype (255)
可以看到使用BBED命令会报无效的块类型，因为该坏块是由于块类行错误造成的
利用dump命令查找出type_kcbh值 type_kcbh位于块头 offset 0，第一行前两位字节
BBED> dump /v offset 0 count 32
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 235 Offsets: 0 to 31 Dba:0x00000000
-------------------------------------------------------
ffa20000 eb004007 30337e20 00000106 l ......@.03~ ....
1df10000 01001200 28720100 17337e20 l ........(r...3~
<16 bytes per line>
当前为ff，至于要修改成什么值，需要查看到底损坏的是什么类型的数据
19:51:36 JWT@pdb>col owner for a30;
19:57:01 JWT@pdb>col segment_name for a60;
19:57:01 JWT@pdb>set linesize 999
SELECT tablespace_name, segment_type, owner, segment_name
19:57:01 JWT@pdb>19:57:01 2 FROM dba_extents
19:57:01 3 WHERE file_id = &fileid
19:57:01 4 and &blockid between block_id AND block_id + blocks - 1;
Enter value for fileid: 29
old 3: WHERE file_id = &fileid
new 3: WHERE file_id = 29
Enter value for blockid: 235
old 4: and &blockid between block_id AND block_id + blocks - 1
new 4: and 235 between block_id AND block_id + blocks - 1
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
------------------------------ ------------------ ------------------------------ ------------------------------------------------------------
JWT TABLE JWT MARVIN
损坏的为表数据而非索引（索引的话可直接删除重建索引)，根据下列表格，可判定为type为06
type_kcbh: 0x06=trans data --块类型 trans=data 说明该块存储的是数据而非索引等其他数据对象
参考表格：
Header Block Types
ID Type
01 Undo segment header
02 Undo data block
03 Save undo header
04 Save undo data block
05 Data segment header (temp, index, data and so on)
06 KTB managed data block (with ITL) --正常表数据
07 Temp table data block (no ITL) --临时表数据
08 Sort Key
09 Sort Run
10 Segment free list block
11 Data file heade
修改type为06
BBED> modify /x 06 offset 0
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
MODE Edit
BBED> modify /x 06 offset 0
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf (0)
Block: 235 Offsets: 0 to 31 Dba:0x00000000
------------------------------------------------------------------------
06a20000 eb004007 30337e20 00000106 1df10000 01001200 28720100 17337e20
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 235:
current = 0xf1e4, required = 0xf1e4
设置之后，可以看到BBED命令原本报错的命令又可以使用
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 0x074000eb
ub4 bas_kcbh @8 0x207e3330
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV)
ub2 chkval_kcbh @16 0xf1e4
ub2 spare3_kcbh @18 0x0000
BBED> p type_kcbh
ub1 type_kcbh @0 0x06
验证
19:57:47 JWT@pdb>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.33
20:25:07 JWT@pdb>select count(*) from marvin;
COUNT(*)
----------
10000
Elapsed: 00:00:00.04
20:25:14 JWT@pdb>select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
29 235 1 0 CORRUPT 6
Elapsed: 00:00:00.05
更新视图
RMAN> validate datafile 29;
Starting validate at 2018-03-17 20:25:54
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 OK 0 6 1281 545141552
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 20:25:56
再次验证
20:25:38 JWT@pdb>select * from v$database_block_corruption;
no rows selected
Elapsed: 00:00:00.01
至此.....数据块修复成功
2、备份恢复
RMAN>blockrecover datafile 29 block 235;
注意RMAN的BLOCKRECOVER在恢复的时候是不需要将数据文件置于脱机状态的。
或者
库为mount状态
RMAN>restore pluggable database pdb001 datafile 29;
RMAN>recover pluggable database pdb001;
3、DBMS_REPAIR包(丢数据)
此方法步骤省略，详情请见坏块类型-ALL ZERO模拟与修复
4、CTAS复制方式(丢数据)
此方法步骤省略，详情请见坏块类型-ALL ZERO模拟与修复
版权声明：本文为博主原创文章，未经博主允许不得转载。
- 上一篇：坏块类型-CHECKSUM模拟及恢复
- 下一篇：坏块类型-LOGICAL模拟及恢复
