Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: troubleshooting an error with dbms_repair

Re: troubleshooting an error with dbms_repair

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 31 Mar 2007 09:20:58 -0700
Message-ID: <1175358055.307986@bubbleator.drizzle.com>


fraund_at_uiuc.edu wrote:
> Continuing this post, I've discovered the error that was hanging me
> up. I was using the tablespace name instead of the schema name, but
> now i'm running into another problem. The block error that I am
> encountering is preventing the dbms_repair utility from working. There
> are at least 5 tables affected by this block error and for most
> operations (for instance drop table) on them I get the ORA-00604,
> 01578, and 01111 sequence of errors. Here is an example of dbms_repair
> not working:
>
> SQL> SET SERVEROUTPUT ON
> SQL> DECLARE num_corrupt INT;
> 2 BEGIN
> 3 num_corrupt := 0;
> 4 DBMS_REPAIR.CHECK_OBJECT (
> 5 SCHEMA_NAME => 'OFOR98',
> 6 OBJECT_NAME => 'UIUC_SLO_PARAMETERS',
> 7 REPAIR_TABLE_NAME => 'REPAIR_TEST',
> 8 CORRUPT_COUNT => num_corrupt);
> 9 DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR
> (num_corrupt));
> 10 END;
> 11 /
> DECLARE num_corrupt INT;
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01578: ORACLE data block corrupted (file # 1, block # 50619)
> ORA-01110: data file 1: '/home/oracle/databases/WG73/system.dbf'
> ORA-06512: at "SYS.DBMS_REPAIR", line 293
> ORA-06512: at line 4
>
> According to the example, I should be receiving a 'number corrupt: 1'
> message.
>
> Is there any way to find out if any tables other than the 5 I've
> discovered are corrupted by this block? Has anyone seen dbms_repair
> not work in this fashion and is there a workaround?
>
> Philipp

The corruption is in your SYSTEM tablespace and I would hope that the only schema owning objects within it are SYS, SYSTEM, and OUTLN.

So why: SCHEMA_NAME => 'OFOR98'

And ... if you only have one corrupt block then you can only have one corrupt table unless that block is part of a cluster so what you are saying, and what you are posting, don't add up. Which indicates to me that you are likely making things worse rather than better.

Open an SR immediately and send your RDA. You do have an RDA don't you. <g>

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sat Mar 31 2007 - 11:20:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US