• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1434
  • Last Modified:

Data block corrupted but dbms_repair.check_object shows no corrupt blocks as 0

Hi Anyone,

I have received errors and been unable to export two tables in my 8i database.

The oracle errors shows the file# and block#s in the dump/trace file.

If try to exp either of these tables I get block errors consistent with what is being reported.

When I look up the objects associated with the bad blocks I also see the same two tables which cannot be exported due to bad data.

When I ran the dbms_repair utility (after setting up properly etc.) it returns a count of 0 corrupt blocks.

If I run dbv utilities on my database when offline I get a list of half dozen or so bad blocks all consistent with errors and objects which are inaccessible due to bad blocks.

When running ANALYZE TABLE I see the bad blocks but it won't populate the INVALID_ROWS table created from @F:\ORANT817\RDBMS\ADMIN\UTLVALID.SQL script.  So it sees bad block but no data.

Output from dbv is interesting too...
DBVERIFY - Verification complete

Total Pages Examined         : 102400
Total Pages Processed (Data) : 99438
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 482
Total Pages Empty            : 2468
Total Pages Marked Corrupt   : 12
Total Pages Influx           : 4

Any ideas to recover destructive or non-destructive?  I am out of options.  (ps don't want to use tape)

3 Solutions
If the blocks had already been marked corrupt, I don't know that DBMS_REPAIR would have reported them again.  Run DBMS_REPAIR with the FIX_CORRUPT_BLOCKS option and see how many blocks it repairs.
ismellmyhandAuthor Commented:
Ill give a try.  How did they get marked to begin with I wonder?  I am only person associated with it and db_block_checking is generally off.
Identify the corrupted objects

set linesize 1000

SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents
WHERE file_id = 25
and 62761 between block_id AND block_id + blocks - 1;

There are different scenarios according what is corrupted.

When a data block is corrupted in a table, it should be understood that the data in the corrupted
block is lost. The only way to not lose any data from the table is to restore from a valid backup
and recover until a point in time before the corruption occurred.
If the data in the corrupted block can be recreated, then the following methods might be useful.

Event method:

 Event 10231 can be set to skip corrupted blocks on full table scans in the
INIT.ORA file. The object can be exported after setting this event. This is not guaranteed to
work for every kind of corruption. This works only when the block is soft corrupted, sequence
is set to 0. The event can be set as follows:
event = “10231 trace name context forever, level 10”

Before this try to rescue trying to copy the table:

create table x1 as (select * from x); I succeed to rescue one of the tables with corrupted block last week on 9i.

Seems different tools have his own criteria for corrupted blocks (especially if the corruption is logical only).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now