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: Data Block corruption

Re: Data Block corruption

From: koert54 <nospam_at_nospam.com>
Date: Thu, 23 Jan 2003 19:15:48 GMT
Message-ID: <EbXX9.2314$Jd.511@afrodite.telenet-ops.be>

  1. use hot backup - if your backup doesn't contain the corruption of course - offline datafile, restore, recover etc
  2. start fiddling around : - find out the object id by :
    • try to dump the block : alter system dump datafile 11 block 27874 ;
    • go to udump and open the trace file
    • look for seg/obj: 0xs140 -> hex to dec -> your OBJECTID
    • select owner, object_name, object_type from dba_objects where object_id=<OBJECTID>
      • if OBJECT_TYPE = INDEX -> *REBUILD* index - if OBJECT_TYPE= TABLE - use of recent export
    • find out which records are stored in the corrupted block by using your PK index
      • get the rowid's surrounding the corrupted block : select dbms_rowid.rowid_create(<rowid_type> , <object_number> ,<relative_fno>, <block_number> from dual ; -> low rowid select dbms_rowid.rowid_create(<rowid_type> , <object_number> ,<relative_fno>, <block_number+1> from dual ; -> high rowid
      • now you know the rowid's - we can partially extract the corrupted data out of the indexes create table salvage as select /*+ INDEX(<corrupted table> <PKINDEX>) */ <indx_col1> x1, <indx_col2> x2 from <corrupted table> where rowid >= 'low rowid' and rowid < 'high rowid'
        • let's export the latest data and skip the corrupted block :
          • exec dbms_repair.skip_corrupt_blocks('<owner>', '<corrupted table>') ;
          • exp system/manager tables=<owner>.<corrupted table>
    • drop the corrupted table
    • import the table - the data in the corrupted block is now gone - but we know which records are missing (they are in the table salvage)
    • so now we import the table from a recent export dump into another schema (hoping the data in the corrupted block was static or hasn't changed after the export)
    • if we now put the pieces together we can insert the missing records by using the salvage table (PK) and the table in the other schema (rest of the columns)

"Rich" <rich.bevan_at_bt.com> wrote in message news:3E3021C5.70F699E0_at_bt.com...

> There was a disk errror on our system (Sun Sparc 2.6) which was repaired
> using format->analysis->read. However, before this was run the database
> was shutdown causing data block corruption - the message below was
> reported by TOAD
>
> ORA-01578: Oracle data block corruted (file# 11, block # 27874}
> ORA-01110: datafile 11: '/u03/oradata/BAA/data04.dbf'
>
> Is it possible from the above information to repair the damage either by
> deleting the segment which is corrupted and restoring the data  or do I
> have to restore the whole datafile and use the archive logs ?
>
> I am running Oracle 8.1.7.1 on 32 bit
>
> Thanks for any help
>
> Richard
>
Received on Thu Jan 23 2003 - 13:15:48 CST

Original text of this message

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