- use hot backup - if your backup doesn't contain the corruption of
course - offline datafile, restore, recover etc
- 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