[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2005-04-08
Medium Priority
Last Modified: 2008-02-26
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)

Question by:ismellmyhand
LVL 25

Accepted Solution

jrb1 earned 672 total points
ID: 13741219
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.

Author Comment

ID: 13741227
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.

Assisted Solution

helpneed earned 664 total points
ID: 13750771
LVL 48

Assisted Solution

schwertner earned 664 total points
ID: 13750989
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).

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question