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';
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
4
0- 0
已为社区贡献3条内容
所有评论(0)