mysql修复坏块_使用DBMS_REPAIR修复坏块

对于Oracle数据块物理损坏的情形，在我们有备份的情况下可以直接使用备份来恢复。对于通过备份恢复，Oracel为我们提供了很多种方

对于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<2;

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'

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网