Thanks

Kurtz - Thanks for your help

koert54 wrote:

> > 0000000 0000 0000 0000 0000 0000 0000 0000 0000
> > *
> > 0001000 000c 7100 0200 87fb 0200 6889 12ff 0301 -----> offset 0x1000 (or
> byte 4096)
> > 0001020 0100 0000 0003 00ff 0008 0018 0010 0014
>
> Well ... it looks like the first 4096 bytes of your block are gone !
> As you see from the dump offset 0x0000 to 0x1000 are filled with *zeros* -
> (od just shows *)...
> The first non-zero data is at offset 0x1000 (4096)
> That explains why the Oracle dump itself showed scn = 0000...00 seq = 0 etc
>
> > > > scn: 0x0000.00000000 seq: 0xff flg: 0x00 tail: 0x000000ff
> > > > frmt: 0x02 chkval: 0x0000 type: 0x00=unknown
>
> Conclusion - that data is *gone*.
> Reason - probably a soft error on disk (guess your using 4K OS blocks -
> explaining the
> first 4K zeroed out) and the OS tried to repair its block - in addition,
> disk repair utitilies can
> cause this zeroing out effect (fschk).
>
> To see the object that's affected - dump the next or previous block and look
> for the object id
>
> Guess you should start looking for your hot backup...
>
> Sorry
> Kurt
>
> "Rich" <rich.bevan_at_bt.com> wrote in message news:3E31656A.A940DB59_at_bt.com...
> > 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 Mon Jan 27 2003 - 04:29:37 CST