oracle修复坏块(BBED/RMAN)

本文适用范围：
一、通过BBED方式进行坏块修复
二、通过备份表空间或者全库备份进行坏块修复
三、没有备份、使用跳过坏块方式处理，此方法会丢数

首先我们创建数据
1、创建测试数据

SQL> select * from test;

A1		     A2
-------------------- --------------------
lifang		     qwert
xiaoqing	     poiuy

2、查看对应file#,block#

select rowid,
       dbms_rowid.rowid_relative_fno(rowid) rel_fno,
       dbms_rowid.rowid_block_number(rowid) blockno,
       dbms_rowid.rowid_row_number(rowid) rowno
  from test;

ROWID		      REL_FNO	 BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAR8OAAPAAAACFAAA	   15	     133	  0
AAAR8OAAPAAAACFAAB	   15	     133	  1

3、人为构造坏块

BBED> set dba 15,133 
	DBA            	0x03c00085 (62914693 15,133)

BBED> map
 File: /u01/data/test.dbf (15)
 Block: 133                                   Dba:0x03c00085
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[1], 4 bytes                    @114     

 sb2 kdbr[2]                                @118     

 ub1 freespace[8032]                        @122     

 ub1 rowdata[34]                            @8154    

 ub4 tailchk                                @8188    

BBED> set offset 8188
	OFFSET         	8188

BBED> m /x ff offset 8188            --破坏尾部8188偏移量
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
 File: /u01/data/test.dbf (15)
 Block: 133              Offsets: 8188 to 8191           Dba:0x03c00085
------------------------------------------------------------------------
 ff060962 

 <32 bytes per line>

BBED> sum apply
Check value for File 15, Block 133:
current = 0x9a49, required = 0x9a49

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/data/test.dbf
BLOCK = 133

Block 133 is corrupt
Corrupt block relative dba: 0x03c00085 (file 0, block 133)
Fractured block found during verification
Data in bad block:
 type: 6 format: 2 rdba: 0x03c00085
 last change scn: 0x0000.0000.00336209 seq: 0x1 flg: 0x06
 spare3: 0x0
 consistency value in tail: 0x620906ff
 check value in block header: 0x9a49
 computed block checksum: 0x0


DBVERIFY - Verification complete

Total Blocks Examined         : 1--( 检查总页数)
Total Blocks Processed (Data) : 0--(处理的总页数（数据）) 
Total Blocks Failing   (Data) : 0 --(总页数失败（数据）)    
Total Blocks Processed (Index): 0--(处理的总页数（索引）)
Total Blocks Failing   (Index): 0--(总页面失败（索引）) 
Total Blocks Empty            : 0 --(总页数空) 
Total Blocks Marked Corrupt   : 1 --(总页数标记为损坏) 
Total Blocks Influx           : 2 --(总页面数量) 
Message 531 not found;  product=RDBMS; facility=BBED

第二种查看坏块方式

[oracle@oracle19c ~]$ dbv file=/u01/data/test.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on Tue Aug 24 14:56:56 2021

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/data/test.dbf
Page 133 is influx - most likely media corrupt
Corrupt block relative dba: 0x03c00085 (file 15, block 133)
Fractured block found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x03c00085
 last change scn: 0x0000.0000.00336209 seq: 0x1 flg: 0x06
 spare3: 0x0
 consistency value in tail: 0x620906ff
 check value in block header: 0x9a49
 computed block checksum: 0x0



DBVERIFY - Verification complete

Total Pages Examined         : 131072--( 检查总页数)
Total Pages Processed (Data) : 4 --(处理的总页数（数据）) 
Total Pages Failing   (Data) : 0--(总页数失败（数据）)    
Total Pages Processed (Index): 0--(处理的总页数（索引）) 
Total Pages Failing   (Index): 0--(总页面失败（索引）) 
Total Pages Processed (Other): 130--(处理的总页数（其他）) 
Total Pages Processed (Seg)  : 0--(处理的总页数（Seg）) 
Total Pages Failing   (Seg)  : 0--(总页数失败（Seg) 
Total Pages Empty            : 130937 --(总页数空) 
Total Pages Marked Corrupt   : 1--(总页数标记为损坏) 
Total Pages Influx           : 1--(总页面数量) 
Total Pages Encrypted        : 0 --(加密总页数) 
Highest block SCN            : 3367426 (0.3367426)--(最高块SCN) 

第三种查看坏块方式

RMAN>  backup check logical validate datafile 15;

Starting backup at 24-AUG-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=276 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00015 name=/u01/data/test.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
15   FAILED 0              130937       131072          3367426   
  File Name: /u01/data/test.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       1              5               
  Index      0              0               
  Other      0              130             

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25434.trc for details
Finished backup at 24-AUG-21

根据文件号和块号查出损坏的是对象,表还是LOB segment
#如果被损坏的块是索引，通常可以通过索引重建来解决

查看损坏的数据块【此视图中的数据在RMAN的validate操作之后，会被写入】：

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
     FILE#     BLOCK#	  BLOCKS CORRUPTION_CHANGE# CORRUPTIO	  CON_ID
---------- ---------- ---------- ------------------ --------- ----------
	15	  133	       1		  0 FRACTURED	       1

查看具体是存储的什么内容

SQL> SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &fileid
and &blockid between block_id AND block_id + blocks - 1;		
Enter value for fileid: 15
old   3: WHERE file_id = &fileid
new   3: WHERE file_id = 15
Enter value for blockid: 133
old   4: and &blockid between block_id AND block_id + blocks - 1
new   4: and 133 between block_id AND block_id + blocks - 1

TABLESPACE_NAME 	       SEGMENT_TYPE    OWNER     SEGMENT_NAME
------------------------------ --------------------------------------------------------------------------------
TEST			                TABLE                              SYS            TEST

查询发现是SYS用户下的test表出现了坏块

---

BBED方式修复坏块

修复坏块

BBED> set dba 15,133
	DBA            	0x03c00085 (62914693 15,133)

BBED> map
 File: /u01/data/test.dbf (15)
 Block: 133                                   Dba:0x03c00085
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes                      @0       
区块头结构
struct ktbbh, 72 bytes                     @20      
交易固定标题结构
struct kdbh, 14 bytes                      @100     
数据头结构
struct kdbt[1], 4 bytes                    @114     
表目录项结构
sb2 kdbr[2]                                @118     
行目录
ub1 freespace[8032]                        @122     
可用空间
ub1 rowdata[34]                            @8154    
行数据
ub4 tailchk                                @8188    
尾检

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        0x03c00085
   ub4 bas_kcbh                             @8        0x00336209
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x9a49
   ub2 spare3_kcbh                          @18       0x0000

根据bas_kcbh+type_kcbh+seq_kcbh修复tailchk值，由于是litter_endian倒序修改
0x00336209 0x06 0x01
6209 06 01 变为 01060962

BBED> m /x 01060962 offset 8188
 File: /u01/data/test.dbf (15)
 Block: 133              Offsets: 8188 to 8191           Dba:0x03c00085
------------------------------------------------------------------------
 01060962 

 <32 bytes per line>

BBED> sum apply
Check value for File 15, Block 133:
current = 0x9ab7, required = 0x9ab7

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/data/test.dbf
BLOCK = 133


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

---

RMAN备份表空间或者全库备份的方式修复坏块

1、备份表空间

RMAN> run
{allocate channel t1 device type disk;
backup tablespace 'TEST' format '/u01/data/test_%u_%s_%p.bak';
release channel t1;
}2> 3> 4> 5> 

using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=276 device type=DISK

Starting backup at 24-AUG-21
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00015 name=/u01/data/test.dbf
channel t1: starting piece 1 at 24-AUG-21
channel t1: finished piece 1 at 24-AUG-21
piece handle=/u01/data/test_0q07at2u_26_1.bak tag=TAG20210824T154846 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-AUG-21

released channel: t1

2、创建坏块后，检查下对应数据文件

RMAN>  backup check logical validate datafile 15;

Starting backup at 24-AUG-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=276 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00015 name=/u01/data/test.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
15   FAILED 0              130937       131072          3367426   
  File Name: /u01/data/test.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       1              5               
  Index      0              0               
  Other      0              130             

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28496.trc for details
Finished backup at 24-AUG-21

3、通过备份修复坏块

RMAN> blockrecover datafile 15 block 133;

Starting recover at 24-AUG-21
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00015
channel ORA_DISK_1: reading from backup piece /u01/data/test_0q07at2u_26_1.bak
channel ORA_DISK_1: piece handle=/u01/data/test_0q07at2u_26_1.bak tag=TAG20210824T154846
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 24-AUG-21

4、再次检查，看是否还有坏块

RMAN>  backup check logical validate datafile 15;

Starting backup at 24-AUG-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00015 name=/u01/data/test.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
15   OK     0              130937       131072          3367433   
  File Name: /u01/data/test.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              5               
  Index      0              0               
  Other      0              130             

Finished backup at 24-AUG-21

11.2之后的版本，可以通过备库进行坏块自动修复，对应用无感，以下内容为主库alert日志中的部分内容

2021-08-26T15:28:14.579274+08:00
Automatic block media recovery requested for (file# 15, block# 153)
2021-08-26T15:28:14.767553+08:00
Automatic block media recovery requested for (file# 15, block# 153)
Automatic block media recovery successful for (file# 15, block# 153)
2021-08-26T15:28:14.799116+08:00
Automatic block media recovery successful for (file# 15, block# 153)

---

标记坏块，重建表，有数据丢失

可以使用10231事件忽略坏块，然后使用CTAS方式重建表或者数据泵导出（跳过损坏的块），别忘记rebuild index
会有数据丢失

alter session SET EVENTS '10231 trace name context forever,level 10';
create table tab_new as select * from test;
rename tab to tab_bak;
rename tab_new to new;
alter index indexname rebuild;
alter session SET EVENTS '10231 trace name context off';