RE: Repairing corrupt datablocks

From: Jonas A Wetterberg <jonas.a.wetterberg_at_manpower.se>
Date: Fri, 16 Nov 2001 07:13:35 -0800
Message-ID: <F001.003C7553.20011116062522@fatcity.com>

U may use the following command to check an object:

sys.DBMS_REPAIR.CHECK_OBJECT (

       SCHEMA_NAME => schema_name_,
       OBJECT_NAME => object_name_,
       REPAIR_TABLE_NAME => 'REPAIR_TABLE',
       corrupt_count =>  num_corrupt_)

before you run that, make sure the table 'REPAIR_TABLE' exists and that it is owned by user SYS. Oracle supplies a procedure to create that table:

     sys.DBMS_REPAIR.ADMIN_TABLES (
        TABLE_NAME => 'REPAIR_TABLE',
        TABLE_TYPE => sys.dbms_repair.repair_table,
        ACTION     => sys.dbms_repair.create_action,
        TABLESPACE => 'USERS')

/Jonas

-----Ursprungligt meddelande-----

Från: Tatireddy, Shrinivas (MED, Keane)
[mailto:Shrinivas.Tatireddy_at_med.ge.com]
Skickat: den 16 november 2001 11:50
Till: Multiple recipients of list ORACLE-L Ämne: RE: Repairing corrupt datablocks

Hi lists,

I tried execting the dbms_repair with the following steps:

sql> variable x number;

sql> exec dbms_repair.check_object('SCOTT','EMP',:x);

it is giving the error:

PLS-00306: wrong number or types of arguments in call to 'CHECK_OBJECT'

can anybody tell me how to execute this procedure.

Thnx and Regards,

Srinivas

+++++++++++++++++++++++++

Hello

By using DBMS_REPAIR.CHECK_OBJECT I have found 3 corrupt datablocks in our
database, all with error: Block Checking: DBA = 184607007, Block Type = KTB-managed data block
kdbchk: the amount of space used is not equal to block size

used=8169 fsc=0 avsp=112 dtl=8120

My plan now is to fix these data blocks. I have never done this before, so I
consulted my Oracle manual and the manual recomends the following:

First run DBMS_REPAIR.CHECK_OBJECT on the object to find out if the object
contains any corrupt blocks. Then I run DBMS_REPAIR.FIX_CORRUPT_BLOCKS to
mark the block as corrupt. After that run DBMS_REPAIR.DUMP_ORPHAN_KEYS to
dump all the index references to these blocks. Then run DBMS_REPAIR.SKIP_CORRUPT_BLOCKS on the object, and after that recreate the
table.

By doing this the corrupt blocks are removed from the object.

Now I have some questions about this. (My environments is a Oracle 8.1.7 on
a Windows 2000 Advanced Server):

Is this a good way, or is there a better way? Can I access the data in the corrupt blocks in anyway so I can se whats in
the blocks? I'm afraid that i will loose some data by doing it this way.

Regards Jonas
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jonas A Wetterberg
INET: jonas.a.wetterberg_at_manpower.se

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


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

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Tatireddy, Shrinivas (MED, Keane) INET: Shrinivas.Tatireddy_at_med.ge.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


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

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jonas A Wetterberg
INET: jonas.a.wetterberg_at_manpower.se

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


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

To REMOVE yourself from this mailing list, send an E-Mail message