Re: Data Block corruption

Thanks again - I ran

$ dd if='/u03/oradata/BAA/data04.dbf' bs=8192 skip=27874 count=1 | od -x | pg

The first page is as follows:

1+0 records in
1+0 records out
0000000 0000 0000 0000 0000 0000 0000 0000 0000 *
0001000 000c 7100 0200 87fb 0200 6889 12ff 0301 0001020 0100 0000 0003 00ff 0008 0018 0010 0014 0001040 0000 5c6e 0000 5c6e 0000 0006 0000 0000 0001060 0b01 2570 0000 0000 0201 0000 0000 0000 0001100 00c0 9846 000c 7000 0200 87fb 0200 6889 0001120 12ff 0301 0100 0000 0002 00ff 0008 0018 0001140 0010 0014 0000 5c6e 0000 5c6e 0000 0006 0001160 0000 0000 0b01 256f 0000 0000 0201 0000 0001200 0000 0000 00c0 9846 000c 6f00 0200 87fb 0001220 0200 6889 12ff 0301 0100 0000 0001 00ff 0001240 0008 0018 0008 0014 0000 5c6e 0000 5c6e 0001260 0000 0006 0000 0000 0b01 256e 0000 0000 0001300 0301 0000 0000 0000 0200 87fb 0200 6889 0001320 12ff 0301 0100 0000 0000 00ff 0008 0018 0001340 0010 0014 0000 5c6e 0000 5c6e 0000 0006 0001360 0000 0000 0b01 256d 0000 0000 0201 0000 0001400 0000 0000 00c0 9846 000c 6d00 0200 87fa 0001420 0200 6889 12ff 0301 0100 0000 001b 00ff 0001440 0008 0018 0010 0014 0000 5c6e 0000 5c6e 0001460 0000 0006 0000 0000 0b01 256c 0000 0000 0001500 0201 0000 0000 0000 00c0 9846 000c 6c00 0001520 0200 87fa 0200 6889 12ff 0301 0100 0000

Your mail below says the objectid is at 0x18, 0x19 - is this 18 to 19 byte inside this page ?

Richard

koert54 wrote:

> 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 - 10:10:18 CST