oracle dbms_repair,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