当前位置:操作系统 > Unix/Linux >>

使用DBMS_REPAIR修复坏块

使用DBMS_REPAIR修复坏块
 
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、创建演示环境
[sql] 
--当前环境  
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 <<EOF  
> 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 <<EOF   
> 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 <<EOF   
> 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'  
4、使用DBMS_REPAIR修复坏块
[sql] 
Step a 创建相应的表对象  
--使用DBMS_REPAIR.ADMIN_TABLES过程创建一个表对象,用于记录需要被修复的表  
sys@USBO> BEGIN  
  2    DBMS_REPAIR.ADMIN_TABLES (  
  3       TABLE_NAME => 'REPAIR_TABLE',  
  4       TABLE_TYPE => dbms_repair.repair_table,  
  5       ACTION     => dbms_repair.create_action,  
  6       TABLESPACE => 'USERS');  
  7  END;  
  8  /  
  
PL/SQL procedure successfully completed.  
  
--使用DBMS_REPAIR.ADMIN_TABLES过程创建一个表对象,用于记录在表块损坏后那些孤立索引,也就是指向坏块的那些索引                       
sys@USBO> BEGIN  
  2    DBMS_REPAIR.ADMIN_TABLES   
  3    (  
  4       TABLE_NAME => 'ORPHAN_KEY_TABLE',  
  5       TABLE_TYPE => DBMS_REPAIR.ORPHAN_TABLE,  
  6       ACTION     => DBMS_REPAIR.CREATE_ACTION,  
  7       TABLESPACE => 'USERS'  
  8    );  
  9  END;  
 10  /  
  
PL/SQL procedure successfully completed.  
  
  
Step b 校验受损的对象  
--使用DBMS_REPAIR.CHECK_OBJECT来检测对象上受损的情形,并返回受损块数  
sys@USBO> SET SERVEROUTPUT ON  
sys@USBO> DECLARE num_corrupt INT;  
  2  BEGIN  
  3   num_corrupt := 0;  
  4   DBMS_REPAIR.CHECK_OBJECT (  
  5       SCHEMA_NAME => 'SYS',  
  6       OBJECT_NAME => 'TB_OBJ',  
  7       REPAIR_TABLE_NAME => 'REPAIR_TABLE',  
  8       CORRUPT_COUNT =>  num_corrupt);  
  9   DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));  
 10  END;
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,