对于oracle数据块物理损坏的情形，在我们有备份的情况下可以直接使用备份来恢复。对于通过备份恢复，oracel为我们提供了很多种方式，冷备，，基于用户管理方式，rman方式等等。对于这几种方式我们需要实现基于数据库以及文件级别的恢复。rman同时也提供了基于块介质方式的恢复。也就是说我们根本不需要还原数据文件，而是直接从备份文件基于块来提取以实现联机恢复。可参考基于rman实现坏块介质恢复(blockrecover) 。这是比较理想的情形。如果没有任何备份怎么办？我们可以使用oracle自带的dbms_repair包来实现修复。注意，不要被文章题目有所误导。这里的修复是有损修复也就是说将受损的数据块标记为坏块，不对其进行访问而已。就好比我们磁盘有坏道，找个磁盘修复工具将坏道标出来不使用，同理。那受损的数据岂不是无力回天啦，呜呜......要记得随时备份阿。。
相关阅读：
使用rman的duplicate功能创建物理dataguard
oracle基础教程之通过rman复制数据库
rman备份策略制定参考内容
rman备份学习笔记
oracle数据库备份加密 rman加密
1、dbms_repair包所含的过程
procedure_name description
----------------- ------------------------------------
admin_tables provides administrative functions (create, drop, purge) for repair or orphan key tables.
note: these tables are always created in the sys schema.
check_object detects and reports corruptions in a table or index
dump_orphan_keys reports on index entries that point to rows in corrupt data blocks
fix_corrupt_blocks marks blocks as software corrupt that have been previously identified as corrupt by the check_object procedure
rebuild_freelists rebuilds the free lists of the object
segment_fix_status provides the capability to fix the corrupted state of a bitmap entry when segment space management is auto
skip_corrupt_blocks when used, ignores blocks marked corrupt during table and index scans.
if not used, you get error ora-01578 when encountering blocks marked corrupt.
2、dbms_repair的一些局限性
tables with lob data types, nested tables, and varrays are supported, but the out-of-line columns are ignored.
clusters are supported in the skip_corrupt_blocks and rebuild_freelists procedures, but not in the check_object procedure.
index-organized tables and lob indexes are not supported.
the dump_orphan_keys procedure does not operate on bitmap indexes or function-based indexes.
the dump_orphan_keys procedure processes keys that are no more than 3,950 bytes long.
3、创建演示环境
--当前环境
sys@usbo> select * from v$version where rownum
banner
--------------------------------------------------------------------------------
oracle database 11g enterprise edition release 11.2.0.1.0 - production
--创建表空间
sys@usbo> create tablespace tbs_tmp datafile '/u02/database/usbo/oradata/tbs_tmp.dbf' size 10m autoextend on;
--创建表对象tb_obj及其索引
sys@usbo> create table tb_obj tablespace tbs_tmp as select * from dba_objects;
sys@usbo> create index i_tb_obj on tb_obj(object_id);
--表段上的相关信息
sys@usbo> select segment_name , header_file , header_block,blocks
2 from dba_segments where segment_name ='tb_obj';
segment_name header_file header_block blocks
------------------------------ ----------- ------------ ----------
tb_obj 6 130 1152
--使用linux自带的dd命令来损坏数据块
[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=131 > corrupt me!
> eof
0+1 records in
0+1 records out
12 bytes (12 b) copied, 0.000209854 seconds, 57.2 kb/s
[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=141 > corrupt me!
> eof
0+1 records in
0+1 records out
12 bytes (12 b) copied, 0.00019939 seconds, 60.2 kb/s
[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=151 > corrupt me!
> eof
0+1 records in
0+1 records out
12 bytes (12 b) copied, 2.1672e-05 seconds, 554 kb/s
sys@usbo> alter system flush buffer_cache;
--下面的查询收到了错误提示
sys@usbo> select count(*) from tb_obj;
select count(*) from tb_obj
*
error at line 1:
ora-01578: oracle data block corrupted (file # 6, block # 131)
ora-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'