DBMS_REPAIR的使用

select file_id, block_id from dba_extents where segment_name = 'TEST' AND OWNER='APPS';
FILE_ID BLOCK_ID
4 33609
4 33617
4 33625
4 33633
4 33641
4 33649
4 33657
4 33665
4 33673
4 33681
4 33689
4 33697
4 33705
4 33713
4 33721
4 33729
4 33801
4 33929
4 34057
4 34185
4 34313

bbed parfile=par.bbd blocksize=8192

copy file 2 block 17 to file 4 block 33617
copy file 2 block 17 to file 4 block 33804

[oracle@server ~]$ dbv file=/u01/app/oracle/oradata/db10g/users01.dbf

DBVERIFY: Release 10.2.0.1.0 - Production on Sat Apr 4 09:00:24 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/db10g/users01.dbf
Page 33617 is marked corrupt
Corrupt block relative dba: 0x01008351 (file 4, block 33617)
Bad header found during dbv:
Data in bad block:
type: 255 format: 7 rdba: 0x00800011
last change scn: 0x0000.004a496b seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x496b2002
check value in block header: 0x66bc
computed block checksum: 0x0

DBVERIFY - Verification complete

Total Pages Examined : 34560
Total Pages Processed (Data) : 30087
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2198
Total Pages Failing (Index): 0
Total Pages Processed (Other): 580
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1693
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 5297581 (0.5297581)

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from apps.test;
select count(*) from apps.test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 33617)
ORA-01110: data file 4: '/u01/app/oracle/oradata/db10g/users01.dbf'

SQL> -- Repair Table

declare
begin
-- Create repair table
dbms_repair.admin_tables (
-- table_name => 'REPAIR_TABLE',
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => 'USERS'); -- default TS of SYS if not specified
end;
/

SQL> set serveroutput on
SQL>
declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_object (
schema_name => 'APPS',
object_name => 'TEST',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => rpr_count);
dbms_output.put_line('repair count: ' || to_char(rpr_count));
end;
/
repair count: 2

declare
fix_count int;
begin
fix_count := 0;
dbms_repair.fix_corrupt_blocks (
schema_name => 'APPS',
object_name => 'TEST',
object_type => dbms_repair.table_object,
repair_table_name => 'REPAIR_TABLE',
fix_count => fix_count);
dbms_output.put_line('fix count: ' || to_char(fix_count));
end;
/
fix count: 2

declare
begin
dbms_repair.skip_corrupt_blocks (
schema_name => 'APPS',
object_name => 'TEST',
object_type => dbms_repair.table_object,
flags => dbms_repair.skip_flag);
end;
/

SQL> conn apps/apps
Connected.
SQL> SELECT /*+FULL(A)*/ COUNT(*) FROM APPS.TEST A;

COUNT(*)
----------
47039

[@more@]dbms_repair