<p>
原创转载起注明出处<br />
我们可能有时候会遇到如下错误<br />
一、错误展示<br />
SQL> select * from test123;<br />
<br />
select * from test123<br />
<br />
ORA-01578: ORACLE data block corrupted (file # 1, block # 61658)<br />
ORA-01110: data file 1: '/oracle_ocfs/oradata/pprac/system01.dbf'<br />
二、原因<br />
这样的情况下可能是因为底层的硬件问题引起的，在进行读物理磁盘数据块的时候会进行物理一致性校验，也就是会计算上次写入磁盘的<br />
时候的校验和和这次读取时候的校验和是否相同，不同则出现错误1578，参数db_block_checksum来控制这个功能10G默认是TRUE也就是对<br />
所有文件进行校验如果设置为FALSE就是只对SYSTEM文件进行校验。当然还涉及到逻辑检验，但是这个错误是物理检验出错引起的。<br />
三、校验工具<br />
而我们有那些方法进行数据文件进行坏块校验哪？<br />
1、DBV工具<br />
命令和结果如下<br />
[oracle@b000-vmracdb1 lib]$ dbv file=/oracle_ocfs/oradata/pprac/system01.dbf
</p>
<p>
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Aug 9 16:43:53 2010
</p>
<p>
Copyright (c) 1982, 2005, Oracle. All rights reserved.
</p>
<p>
DBVERIFY - Verification starting : FILE = /oracle_ocfs/oradata/pprac/system01.dbf<br />
Page 61658 is marked corrupt<br />
Corrupt block relative dba: 0x0040f0da (file 1, block 61658)<br />
Bad check value found during dbv: <br />
Data in bad block:<br />
type: 6 format: 2 rdba: 0x0040f0da<br />
last change scn: 0x0000.00d056c1 seq: 0x1 flg: 0x06<br />
spare1: 0x0 spare2: 0x0 spare3: 0x0<br />
consistency value in tail: 0x56c10601<br />
check value in block header: 0x6988<br />
computed block checksum: 0xde02
</p>
<p>
<br />
</p>
<p>
DBVERIFY - Verification complete
</p>
<p>
Total Pages Examined : 64000<br />
Total Pages Processed (Data) : 38268<br />
Total Pages Failing (Data) : 0<br />
Total Pages Processed (Index): 7142<br />
Total Pages Failing (Index): 0<br />
Total Pages Processed (Other): 1901<br />
Total Pages Processed (Seg) : 0<br />
Total Pages Failing (Seg) : 0<br />
Total Pages Empty : 16688<br />
Total Pages Marked Corrupt : 1<br />
Total Pages Influx : 0<br />
Highest block SCN : 13691243 (0.13691243)
</p>
<p>
这里Total Pages Marked Corrupt : 1说明有了坏块 <br />
2、RMAN工具<br />
使用命令<br />
backup check logical validate database;<br />
同时对物理和逻辑进行校验<br />
然后我们可以查看视图v$database_block_corruption，结果如下<br />
SQL> select * from v$database_block_corruption;<br />
<br />
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE<br />
---------- ---------- ---------- ------------------ ---------------<br />
1 61658 1 0 CHECKSUM<br />
3、ANALYZE命令<br />
命令是：ANALYZE TABLE TEST123 VALIDATE STRUCTURE CASCADE ONLINE;<br />
四、模拟错误 <br />
为了试验我们使用BBED工具进行模拟，千万不要用这个工具在生产上玩哦。同时要建立一个表如下：<br />
create table test123<br />
(id number,name char(2000));<br />
然后插入数据我的数据如下： <br />
ID NAME<br />
---------- --------------------------------------------------------------------------------<br />
1 test<br />
2 test2<br />
3 test3<br />
4 test4<br />
5 test5<br />
6 test6<br />
7 test7<br />
8 test8<br />
9 test9<br />
10 test10<br />
然后确定文件号和数据块好如下：<br />
SQL> select dbms_rowid.rowid_relative_fno(rowid) file_num,<br />
2 dbms_rowid.rowid_block_number(rowid) block_num,id,name from test123;<br />
<br />
FILE_NUM BLOCK_NUM ID NAME<br />
---------- ---------- ---------- --------------------------------------------------------------------------------<br />
1 61658 1 test<br />
1 61658 2 test2<br />
1 61658 3 test3<br />
1 61659 4 test4<br />
1 61659 5 test5<br />
1 61659 6 test6<br />
1 61660 7 test7<br />
1 61660 8 test8<br />
1 61660 9 test9<br />
1 61661 10 test10<br />
这里我准备用1,61658来试验<br />
下面使用BBED来进行模拟错误：<br />
（1）安装BBED目录在$ORACLE_HOME/rdbms/lib下面使用make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed来进行编译<br />
（2）编写参数文件bbed.par如下：<br />
[oracle@b000-vmracdb1 lib]$ more bbed.par <br />
blocksize=8192<br />
listfile=list<br />
mode=edit<br />
（3）编写list文件，里面放数据文件列表如下：<br />
[oracle@b000-vmracdb1 lib]$ more list<br />
1 /oracle_ocfs/oradata/pprac/system01.dbf<br />
2 /oracle_ocfs/oradata/pprac/undotbs01.dbf<br />
3 /oracle_ocfs/oradata/pprac/sysaux01.dbf<br />
4 /oracle_ocfs/oradata/pprac/users01.dbf<br />
5 /oracle_ocfs/oradata/pprac/example01.dbf<br />
6 /oracle_ocfs/oradata/pprac/undotbs02.dbf<br />
（4）进入BBED使用命令<br />
bbed parfile=bbed.par 密码就是blockedit<br />
[oracle@b000-vmracdb1 lib]$ /home/oracle/product/database/rdbms/lib/bbed parfile=bbed.par<br />
Password:
</p>
<p>
BBED: Release 2.0.0.0.0 - Limited Production on Mon Aug 9 16:52:31 2010
</p>
<p>
Copyright (c) 1982, 2005, Oracle. All rights reserved.
</p>
<p>
************* !!! For Oracle Internal Use only !!! ***************
</p>
<p>
BBED> <br />
（5）找到块命令SET<br />
BBED> set dba 1,61658 <br />
DBA 0x0040f0da (4255962 1,61658)<br />
（6）我们来找到以test为开头的记录<br />
BBED> find /c test<br />
File: /oracle_ocfs/oradata/pprac/system01.dbf (1)<br />
Block: 61658 Offsets: 2170 to 2681 Dba:0x0040f0da<br />
------------------------------------------------------------------------<br />
74657374 33202020 20202020 20202020 20202020 20202020 20202020 20202020 <br />
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 <br />
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020<br />
。。。。。。<br />
这里看到了第一个test偏移量2170，这个也就是说相对整个块的一个位置，我也没有深究。<br />
然后找到他<br />
BBED> dump /v dba 1,61658 offset 2170 count 64<br />
File: /oracle_ocfs/oradata/pprac/system01.dbf (1)<br />
Block: 61658 Offsets: 2170 to 2233 Dba:0x0040f0da<br />
-------------------------------------------------------<br />
74657374 33202020 20202020 20202020 l test3 <br />
20202020 20202020 20202020 20202020 l <br />
20202020 20202020 20202020 20202020 l <br />
20202020 20202020 20202020 20202020 l
</p>
<p>
<16 bytes="" per="" line=""><br />
看到了是test123<br />
然后找下一个<br />
BBED> f<br />
File: /oracle_ocfs/oradata/pprac/system01.dbf (1)<br />
Block: 61658 Offsets: 4179 to 4242 Dba:0x0040f0da<br />
------------------------------------------------------------------------<br />
74657374 32202020 20202020 20202020 20202020 20202020 20202020 20202020 <br />
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
</p>
<p>
<32 bytes="" per="" line=""><br />
偏移量4179<br />
BBED> dump /v dba 1,61658 offset 4179 count 64<br />
File: /oracle_ocfs/oradata/pprac/system01.dbf (1)<br />
Block: 61658 Offsets: 4179 to 4242 Dba:0x0040f0da<br />
-------------------------------------------------------<br />
74657374 32202020 20202020 20202020 l test2 <br />
20202020 20202020 20202020 20202020 l <br />
20202020 20202020 20202020 20202020 l <br />
20202020 20202020 20202020 20202020 l <br />
同样也找到了<br />
在往下还有test能找到，但是我这里已经被我修改了，找不到了。<br />
然后我们修改，弄坏数据块<br />
modify 1000 dba 1,61658 这里是修改的偏移量就是最后找到的TEST*的偏移量我改是test<br />
修改完成后这个块就坏了。 <br />
但是这里注意由于DB_CACHE_BUFFER的原因会缓存数据块，即使修改了读取的时候从SGA读出来的话也不报错<br />
需要清空BUFFER或者重启下，不要让数据库高速缓存缓存数据块。<br />
然后我们<br />
SQL> select * from test123 where id=1;<br />
<br />
ID NAME<br />
---------- --------------------------------------------------------------------------------<br />
1 test<br />
<br />
SQL> select * from test123;<br />
<br />
select * from test123<br />
<br />
ORA-01578: ORACLE data block corrupted (file # 1, block # 61658)<br />
ORA-01110: data file 1: '/oracle_ocfs/oradata/pprac/system01.dbf'<br />
错误就出来了。<br />
五、如何解决<br />
然后我们就是来解决这个问题，说实话如果是表的数据块坏了真的没有太多的办法，只能先解救我们没有<br />
损坏块的数据方法如下：<br />
---有RMAN备份和归档的情况下：<br />
这个比较简单，使用命令<br />
blockrecover datafile 1 block 61658;<br />
就可以了，无需进行不完全恢复。
</p>
<p>
----没RMAN备份和归档的情况下：<br />
1、设置SKIP<br />
SQL> begin<br />
2 dbms_repair.skip_corrupt_blocks(schema_name => 'SYS',object_name => 'TEST123',object_type => DBMS_REPAIR.TABLE_OBJECT,flags => DBMS_REPAIR.SKIP_FLAG);<br />
3 END;<br />
4 /<br />
也就是跳过损坏的块。<br />
2、查看下是否是SKIP<br />
SQL> select table_name,skip_corrupt from dba_tables where table_name='TEST123';<br />
<br />
TABLE_NAME SKIP_CORRUPT<br />
------------------------------ ------------<br />
TEST123 ENABLED<br />
3、再次查看<br />
SQL> select * from test123;<br />
<br />
ID NAME<br />
---------- --------------------------------------------------------------------------------<br />
4 test4<br />
5 test5<br />
6 test6<br />
7 test7<br />
8 test8<br />
9 test9<br />
10 test10<br />
木有了坏块的数据已经没有了。如果想恢复失去的数据，找找ORACLE SUPPORT，或者数据挖掘，反正都是很麻烦的事情，而且不一定行。<br />
当然如果是索引的坏就重建吧，这个没事。<br />
<br />
<br />
如下仅仅记录一下处理坏块需要的技术<br />
未标记损坏块时，未损坏的记录还能通过RIWID读出<br />
<br />
<br />
backup validate database;<br />
select * from v$database_block_corruption;<br />
<br />
<br />
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#<br />
, greatest(e.block_id, c.block#) corr_start_block#<br />
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#<br />
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)<br />
- greatest(e.block_id, c.block#) + 1 blocks_corrupted<br />
, corruption_type description<br />
FROM dba_extents e, v$database_block_corruption c<br />
WHERE e.file_id = c.file#<br />
AND e.block_id <= c.block# + c.blocks - 1<br />
AND e.block_id + e.blocks - 1 >= c.block#<br />
UNION<br />
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#<br />
, header_block corr_start_block#<br />
, header_block corr_end_block#<br />
, 1 blocks_corrupted<br />
, corruption_type||' Segment Header' description<br />
FROM dba_segments s, v$database_block_corruption c<br />
WHERE s.header_file = c.file#<br />
AND s.header_block between c.block# and c.block# + c.blocks - 1<br />
UNION<br />
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#<br />
, greatest(f.block_id, c.block#) corr_start_block#<br />
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#<br />
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)<br />
- greatest(f.block_id, c.block#) + 1 blocks_corrupted<br />
, 'Free Block' description<br />
FROM dba_free_space f, v$database_block_corruption c<br />
WHERE f.file_id = c.file#<br />
AND f.block_id <= c.block# + c.blocks - 1<br />
AND f.block_id + f.blocks - 1 >= c.block#<br />
order by file#, corr_start_block#;<br />
<br />
<br />
<br />
<br />
execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('SYS','TESTER',flags=>dbms_repair.skip_flag);<br />
execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('SYS','TESTER',flags=>dbms_repair.noskip_flag);<br />
<br />
<br />
11GR2<br />
backup validate database;<br />
RECOVER CORRUPTION LIST;<br />
<br />
<br />
<br />
<br />
DRA 11G<br />
backup validate database;<br />
list failure;<br />
advise failure;<br />
repair failure preview;<br />
repair failure;<br />
<br />
<br />
可以使用下面语法代替RECOVER CORRUPTION LIST;11R2以前<br />
run {blockrecover corruption list;} <br />
<br />
<br />
修复单块<br />
<br />
<br />
blockrecover datafile 1 block 61658;<br />
<br />
<br />
<br />
<br />
ROWID_CREATE函数<br />
function ROWID_CREATE(rowid_type IN number,<br />
object_number IN number,<br />
relative_fno IN number,<br />
block_number IN number,<br />
row_number IN number)<br />
return ROWID;<br />
<br />
如果是LOB块的错误需要另外的处理的方式<br />
http://blog.itpub.net/7728585/viewspace-1580285/
</p>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31377785/viewspace-2122730/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
下一篇：
没有了~
请登录后发表评论
登录
全部评论
