[from metalink]DBMS_REPAIR example

 

DBMS_REPAIR example [ID 68013.1]
	Modified 19-OCT-2010     Type BULLETIN     Status PUBLISHED

 

Checked for relevance on 12-SEP-2010

 

PURPOSE

 

 This document provides an example of DBMS_REPAIR as introduced in Oracle 8i.

 Oracle provides different methods for detecting and correcting data block

 corruption - DBMS_REPAIR is one option.

 

 WARNING: Any corruption that involves the loss of data requires analysis to

 understand how that data fits into the overall database system. Depending on

 the nature of the repair, you may lose data and logical inconsistencies can

 be introduced; therefore you need to carefully weigh the gains and losses

 associated with using DBMS_REPAIR.

 

 

SCOPE & APPLICATION

 

 This article is intended to assist an experienced DBA working with an Oracle

 Worldwide Support analyst only.  This article does not contain general

 information regarding the DBMS_REPAIR package, rather it is designed to provide

 sample code that can be customized by the user (with the assistance of

 an Oracle support analyst) to address database corruption.  The

 "Detecting and Repairing Data Block Corruption" Chapter of the Oracle8i

 Administrator's  Guide should be read and risk assessment analyzed prior to

 proceeding.

 

 

RELATED DOCUMENTS

 

  Oracle 8i Administrator's Guide,  DBMS_REPAIR Chapter

 

 

Introduction

=============

 

Note: The DBMS_REPAIR package is used to work with corruption in the

transaction layer and the data layer only (software corrupt blocks).

Blocks 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.

 

The only block repair in the initial release of DBMS_REPAIR is to

*** mark the block software corrupt ***.

 

 

A backup of the file(s) with corruption should be made before using package.

 

 

Database Summary

===============

 

A corrupt block exists in table T1. 

 

SQL> desc 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

 

---&gt Note: In the trace file produced from the ANALYZE, it can be determined

---        that the corrupt block contains 3 rows of data (nrows = 3).

---        The leading lines of the trace file follows:

 

Dump file /export/home/oracle/product/8.1.5/admin/V815/udump/v815_ora_2835.trc

Oracle8 Enterprise Edition Release 8.1.5.0.0 - Beta

With the Partitioning option

 

*** 1998.12.16.15.53.02.000

*** SESSION ID:(7.6) 1998.12.16.15.53.02.000

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

 

DBMS_REPAIR.ADMIN_TABLES (repair and orphan key

================================================

 

ADMIN_TABLES provides administrative functions for repair and orphan key tables.

 

SQL> @adminCreate

SQL> connect sys/change_on_install

Connected.

SQL>

SQL> -- Repair Table

SQL>

SQL> declare

  2  begin

  3  -- Create repair table

  4  dbms_repair.admin_tables (

  5  --    table_name => 'REPAIR_TABLE',

  6      table_type => dbms_repair.repair_table,

  7      action => dbms_repair.create_action,

  8      tablespace => 'USERS');          -- default TS of SYS if not specified

  9  end;

 10  /

 

PL/SQL procedure successfully completed.

 

SQL> select owner, object_name, object_type

  2  from dba_objects

  3  where object_name like '%REPAIR_TABLE';

 

OWNER                 OBJECT_NAME                      OBJECT_TYPE

------------------------------------------------------------------

SYS                   DBA_REPAIR_TABLE                 VIEW

SYS                   REPAIR_TABLE                     TABLE

 

 

SQL>

SQL> -- Orphan Key Table

SQL>

SQL> declare

  2  begin

  3  -- Create orphan key table

  4  dbms_repair.admin_tables (

  5      table_type => dbms_repair.orphan_table,

  6      action => dbms_repair.create_action,

  7      tablespace => 'USERS');          -- default TS of SYS if not specified

  8  end;

  9  /

 

PL/SQL procedure successfully completed.

 

SQL> select owner, object_name, object_type

  2  from dba_objects

  3  where object_name like '%ORPHAN_KEY_TABLE';

 

OWNER                 OBJECT_NAME                      OBJECT_TYPE

------------------------------------------------------------------

SYS                   DBA_ORPHAN_KEY_TABLE             VIEW

SYS                   ORPHAN_KEY_TABLE                 TABLE

 

 

DBMS_REPAIR.CHECK_OBJECT

=========================

 

CHECK_OBJECT procedure checks the specified object and populates the repair

table with information about corruption and repair directive(s).  Validation

consists of block checking all blocks in the object.  All blocks previously

marked corrupt will be skipped.

 

Note: In the initial release of DBMS_REPAIR the only repair is to mark the

      block as software corrupt.

 

SQL> @checkObject

SQL> set serveroutput on

SQL>

SQL> declare

  2     rpr_count int;

  3  begin

  4     rpr_count := 0;

  5  dbms_repair.check_object (

  6     schema_name => 'SYSTEM',

  7     object_name => 'T1',

  8     repair_table_name => 'REPAIR_TABLE',

  9     corrupt_count => rpr_count);

 10     dbms_output.put_line('repair count: ' || to_char(rpr_count));

 11  end;

 12  /

repair count: 1

 

PL/SQL procedure successfully completed.

 

SQL> desc repair_table

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 OBJECT_ID                                 NOT NULL NUMBER

 TABLESPACE_ID                             NOT NULL NUMBER

 RELATIVE_FILE_ID                          NOT NULL NUMBER

 BLOCK_ID                                  NOT NULL NUMBER

 CORRUPT_TYPE                              NOT NULL NUMBER

 SCHEMA_NAME                               NOT NULL VARCHAR2(30)

 OBJECT_NAME                               NOT NULL VARCHAR2(30)

 BASEOBJECT_NAME                                    VARCHAR2(30)

 PARTITION_NAME                                     VARCHAR2(30)

 CORRUPT_DESCRIPTION                                VARCHAR2(2000)

 REPAIR_DESCRIPTION                                 VARCHAR2(200)

 MARKED_CORRUPT                            NOT NULL VARCHAR2(10)

 CHECK_TIMESTAMP                           NOT NULL DATE

 FIX_TIMESTAMP                                      DATE

 REFORMAT_TIMESTAMP                                 DATE

 

SQL> select object_name, block_id, corrupt_type, marked_corrupt,

  2  corrupt_description, repair_description

  3  from repair_table;

 

OBJECT_NAME                      BLOCK_ID CORRUPT_TYPE MARKED_COR

------------------------------ ---------- ------------ ----------

CORRUPT_DESCRIPTION

--------------------------------------------------------------------------------

REPAIR_DESCRIPTION

--------------------------------------------------------------------------------

T1                                      3            1 FALSE

kdbchk: row locked by non-existent transaction

        table=0   slot=0

        lockid=32   ktbbhitc=1

mark block software corrupt

 

Data Extraction

===============

 

The repair table indicates that block 3 of file 6 is corrupt - but remember

that this block has not yet been marked as corrupt, therefore now is the

time to extract any meaningful data.  After the block is marked corrupt,

the entire block must be skipped.

 

 

1. Determine the number of rows in the block from ALTER SYSTEM DUMP (nrows = 3).

2. Use the block dump to see if some data can be saved.

 

DBMS_REPAIR.FIX_CORRUPT_BLOCKS  (ORA-1578)

============================================

 

FIX_CORRUPT_BLOCKS procedure fixes the corrupt blocks in the specified objects

based on information in the repair table.  After the block has been marked as

corrupt,  an ORA-1578 results when a full table scan is performed.

 

 

SQL> declare

  2     fix_count int;

  3  begin

  4     fix_count := 0;

  5  dbms_repair.fix_corrupt_blocks (

  6     schema_name => 'SYSTEM',

  7     object_name => 'T1',

  8     object_type => dbms_repair.table_object,

  9     repair_table_name => 'REPAIR_TABLE',

 10     fix_count => fix_count);

 11     dbms_output.put_line('fix count: ' || to_char(fix_count));

 12  end;

 13  /

fix count: 1

 

PL/SQL procedure successfully completed.

 

SQL> select object_name, block_id, marked_corrupt

  2  from repair_table;

 

OBJECT_NAME                      BLOCK_ID MARKED_COR

------------------------------ ---------- ----------

T1                                      3 TRUE

 

SQL> select * from system.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'

 

DBMS_REPAIR.DUMP_ORPHAN_KEYS

==============================

 

DUMP_ORPHAN_KEYS reports on index entries that point to rows in corrupt data

blocks.   

 

SQL> select index_name from dba_indexes

  2  where table_name in (select distinct object_name from repair_table);

 

INDEX_NAME

------------------------------

T1_PK

 

SQL> @dumpOrphanKeys

SQL> set serveroutput on

SQL>

SQL> declare

  2     key_count int;

  3  begin

  4     key_count := 0;

  5  dbms_repair.dump_orphan_keys (

  6     schema_name => 'SYSTEM',

  7     object_name => 'T1_PK',

  8     object_type => dbms_repair.index_object,

  9     repair_table_name => 'REPAIR_TABLE',

 10     orphan_table_name => 'ORPHAN_KEY_TABLE',

 11     key_count => key_count);

 12     dbms_output.put_line('orphan key count: ' || to_char(key_count));

 13  end;

 14  /

orphan key count: 3

PL/SQL procedure successfully completed.

 

SQL> desc orphan_key_table

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 SCHEMA_NAME                               NOT NULL VARCHAR2(30)

 INDEX_NAME                                NOT NULL VARCHAR2(30)

 IPART_NAME                                         VARCHAR2(30)

 INDEX_ID                                  NOT NULL NUMBER

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 PART_NAME                                          VARCHAR2(30)

 TABLE_ID                                  NOT NULL NUMBER

 KEYROWID                                  NOT NULL ROWID

 KEY                                       NOT NULL ROWID

 DUMP_TIMESTAMP                            NOT NULL DATE

 

SQL> select index_name, count(*) from orphan_key_table

  2  group by index_name;

 

INDEX_NAME                       COUNT(*)

------------------------------ ----------

T1_PK                                   3

 

Note: Index entry in the orphan key table implies that the index should be

rebuilt to guarantee the a table probe and an index probe return the same

result set.

 

 

 

 

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS

===============================

 

SKIP_CORRUPT_BLOCKS enables/disables the skipping of corrupt blocks during

index and table scans of a specified object.

 

Note: If an index and table are out of sync, then a SET TRANSACTION READ ONLY

transaction may be inconsistent in situations where one query probes only

the index and then a subsequent query probes both the index and the table. 

If the table block is marked corrupt, then the two queries will return

different results.

 

Suggestion: If SKIP_CORRUPT_BLOCKS is enabled, then rebuild any indexes

identified in the orphan key table (or all index associated with object

if DUMP_ORPHAN_KEYS was omitted).

 

SQL> @skipCorruptBlocks

SQL> declare

  2  begin

  3  dbms_repair.skip_corrupt_blocks (

  4     schema_name => 'SYSTEM',

  5     object_name => 'T1',

  6     object_type => dbms_repair.table_object,

  7     flags => dbms_repair.skip_flag);

  8  end;

  9  /

 

PL/SQL procedure successfully completed.

 

SQL> select table_name, skip_corrupt from dba_tables

  2  where table_name = 'T1';

 

TABLE_NAME                     SKIP_COR

------------------------------ --------

T1                             ENABLED

 

SQL> -- rows in corrupt block skipped, no errors on full table scan

SQL> select * from system.t1;

 

COL1              COL2

--------------------------------------------------------------------------------

4                 dddd

5                 eeee

 

--&gt Notice the pk index has not yet been corrected.

 

SQL> insert into system.t1 values (1,'aaaa');

insert into system.t1 values (1,'aaaa')

                   *

SQL> select * from system.t1 where col1 = 1;

 

no rows selected

 

 

 

 

DBMS_REPAIR.REBUILD_FREELISTS

===============================

 

REBUILD_FREELISTS rebuilds freelists for the specified object.

 

SQL> declare

  2  begin

  3  dbms_repair.rebuild_freelists (

  4     schema_name => 'SYSTEM',

  5     object_name => 'T1',

  6     object_type => dbms_repair.table_object);

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

 

 

 

 

Rebuild Index

=============

 

Note:  Every index identified in the orphan key table should be rebuilt to

ensure consistent results.

 

SQL> alter index system.t1_pk rebuild online;

 

Index altered.

 

SQL> insert into system.t1 values (1, 'aaaa');

 

1 row created.

 

SQL> select * from system.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.

 

Conclusion

==========

At this point the table T1 is available but data loss was incurred.  In general,

data loss must be seriously considered before using the DBMS_REPAIR package for

mining the index segment and/or table block dumps is very complicated and

logical inconsistencies may be introduced.  In the initial release, the only

repair affected by DBMS_REPAIR is to mark the block as software corrupt.

 

References:

============

Note:556733.1 DBMS_REPAIR SCRIPT

窗体底端

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14730395/viewspace-682280/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14730395/viewspace-682280/