Re: Data Block corruption

From: Rich <rich.bevan_at_bt.com>
Date: Fri, 24 Jan 2003 09:47:08 +0000
Message-ID: <3E310B9C.1A3E6AC0@bt.com>

Thanks - for this it was the kind of info I was after:

I dumped the block and look at the .trc file, but could not find the

• look for seg/obj: 0xs140 as you suggested. The top of the dump is as follows
  • 2003-01-24 09:39:10.767
  • SESSION ID:(88.16349) 2003-01-24 09:39:10.750 Start dump data blocks tsn: 6 file#: 11 minblk 27874 maxblk 27874 buffer tsn: 6 rdba: 0x02c06ce2 (11/27874) scn: 0x0000.00000000 seq: 0xff flg: 0x00 tail: 0x000000ff frmt: 0x02 chkval: 0x0000 type: 0x00=unknown

Dump of memory from 0x1A65714 to 0x1A676FC 1A65710 00000000 00000000 00000000 [............]

Any suggestions?

Thanks

koert54 wrote:

> 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 Fri Jan 24 2003 - 03:47:08 CST