Re: Data Block corruption

Just to be sure the oracle dump isn't making a mess of it (scn, seq & tail looks funny too)- could you try the following : dd if=<datafile 11> bs=<DB blocksize> skip=<block> count=1 | od -x

datafile 11 = select file_name, file_id from dba_data_files where relative_fno=11 ;

example
dd if='/db1/data01.dbf' bs=4096 skip=27874 count=1 | od -x The objectid is at 0x18, 0x19

"Rich" <rich.bevan_at_bt.com> wrote in message news:3E310B9C.1A3E6AC0_at_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 - 04:43:39 CST