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

Posted on 2005-04-08
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

    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

    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.
    LVL 5

    Assisted Solution

    LVL 47

    Assisted Solution

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    Title # Comments Views Activity
    query run slowness in 12c 23 92
    SQL LOADER help 1 40
    Help on Oracle SQL queries 2 52
    Right Function in Oracle SQL Query 6 38
    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    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 video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now