DBMS_REPAIR package is used to work with corruption in the transaction layer and the data layer only (software corrupt blocks).locks with physical corruption (ex. fractured block) are marked as the block is read into the buffer cache and DBMS_REPAIR ignores all blocks marked corrupt.
Step 1: A corrupt block exists in table T1.
SQL> desc scott.t1
Name Null? Type
---- -------- ----------------------------
COL1 NOT NULL NUMBER(38)
COL2 CHAR(512)
SQL> analyze table t1 validate structure;
analyze table t1 validate structure
*
ERROR at line 1:
ORA-01498: block check failure - see trace file
Step 2 : Dumpfile Details
dump file /u01/app/oracle/admin/mddtest/udump/mddtest_ora_2835.trc
kdbchk: row locked by non-existent transaction
table=0 slot=0
lockid=32 ktbbhitc=1
Block header dump: 0x01800003
Object id on Block? Y
seg/obj: 0xb6d csc: 0x00.1cf5f itc: 1 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0002.011.00000121 uba: 0x008018fb.0345.0d --U- 3 fsc
0x0000.0001cf60
data_block_dump
=============
tsiz: 0x7b8
hsiz: 0x18
pbl: 0x28088044
bdba: 0x01800003
flag=-----------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x19d
avsp=0x185
tosp=0x185
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x5ff
0x14:pri[1] offs=0x3a6
0x16:pri[2] offs=0x19d
block_row_dump:
[... remainder of file not included]
end_of_block_dump
Step 3: Create repair table and orphan table
SQL>
Declare
begin
dbms_repair.admin_tables (
table_name => 'REPAIR_TABLE',
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => 'USERS');
end;
/
SQL> select owner, object_name, object_type from dba_objects where object_name like '%REPAIR_TABLE';
OWNER OBJECT_NAME OBJECT_TYPE
-----------------------------
SYS DBA_REPAIR_TABLE VIEW
SYS REPAIR_TABLE TABLE
SQL> declare
begin
dbms_repair.admin_tables (
table_type => dbms_repair.orphan_table,
action => dbms_repair.create_action,
tablespace => 'USERS'); -- default TS of SYS if not specified
end;
/
PL/SQL procedure successfully completed.
SQL> select owner, object_name, object_type from dba_objects where object_name like '%ORPHAN_KEY_TABLE';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------
SYS DBA_ORPHAN_KEY_TABLE VIEW
SYS ORPHAN_KEY_TABLE TABLE
Step 4: Start check object
set serveroutput on
SQL> declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_object (
schema_name => 'SCOTT',
object_name => 'T1',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => rpr_count);
dbms_output.put_line('repair count: 'to_char(rpr_count));
end;
/
repair count: 1
PL/SQL procedure successfully completed.
SQL> select object_name, block_id, corrupt_type, marked_corrupt,corrupt_description, repair_description from repair_table;
OBJECT_NAME : T1
BLOCK_ID : 3
CORRUPT_TYPE : 1
MARKED_COR : 3
CORRUPT_DESCRIPTION : kdbchk: row locked by non-existent transaction
REPAIR_DESCRIPTION : mark block software corrupt
Step 4: Fix corrupted block
SQL> declare
fix_count int;
begin
fix_count := 0;
dbms_repair.fix_corrupt_blocks (
schema_name => 'SYSTEM',
object_name => 'T1',
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: 1
PL/SQL procedure successfully completed.
SQL> select object_name, block_id, marked_corrupt from repair_table;
OBJECT_NAME BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
T1 3 TRUE
SQL> select * from scott.t1;
select * from system.t1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 3)
ORA-01110: data file 6: '/tmp/ts_corrupt.dbf'
Step 5: Fix orphan keys
SQL> select index_name from dba_indexes where table_name in (select distinct object_name from repair_table);
INDEX_NAME
------------------------------
T1_PK
SQL> set serveroutput on
SQL> declare
key_count int;
begin
key_count := 0;
dbms_repair.dump_orphan_keys (
schema_name => 'SYSTEM',
object_name => 'T1_PK',
object_type => dbms_repair.index_object,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => key_count);
dbms_output.put_line('orphan key count: '
to_char(key_count));
end;
/
orphan key count: 3
PL/SQL procedure successfully completed.
SQL> select index_name, count(*) from orphan_key_table group by index_name;
INDEX_NAME COUNT(*)
------------------------------ ----------
T1_PK 3
Step 5: skip corrupt block
SQL> declare
begin
dbms_repair.skip_corrupt_blocks (
schema_name => 'SCOTT',
object_name => 'T1',
object_type => dbms_repair.table_object,
flags => dbms_repair.skip_flag);
end;
/
PL/SQL procedure successfully completed.
SQL> select table_name, skip_corrupt from dba_tables where table_name = 'T1';
TABLE_NAME SKIP_COR
------------------------------ --------
T1 ENABLED
SQL> select * from scott.t1;
COL1 COL2
----------
4 dddd
5 eeee
SQL> insert into scott.t1 values (1,'aaaa');
SQL> select * from system.t1 where col1 = 1;
no rows selected
Step 5: rebuild fresslists
SQL > declare
begin
dbms_repair.rebuild_freelists (
schema_name => 'SYSTEM',
object_name => 'T1',
object_type => dbms_repair.table_object);
end;
/
PL/SQL procedure successfully completed.
Step 6: Rebuild Index
SQL> alter index scott.t1_pk rebuild online;
Index altered.
SQL> insert into system.t1 values (1, 'aaaa');
1 row created.
SQL> select * from scott.t1;
COL1 COL2
-----------
4 dddd
5 eeee
1 aaaa
Note - The above insert statement was used to provide a simple example.This is the perfect world - we know the data that was lost. The temporary table (temp_t1) should also be used to include all rows extracted from the corrupt block.
