Identifying the content of a corrupted block in system tablespace

  • From: "Vlado Barun" <vlado@xxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 6 Jan 2005 20:23:33 -0600

I have a corrupted block in the system tablespace. 
I used the standard query to identify the object that the block but it
failed:
 
SQL> 
  1  SELECT segment_name
  2      , segment_type
  3      , owner
  4      , tablespace_name
  5      , block_id
  6      , blocks
  7  FROM sys.dba_extents
  8  WHERE file_id = 1
  9* AND 8002 BETWEEN block_id and block_id + blocks -1 FROM sys.dba_extents
         *
ERROR at line 7:
ORA-01578: ORACLE data block corrupted (file # 1, block # 8002)
ORA-01110: data file 1: '/u100/oradata/fstst/system01.dbf'
 
Then I did this:
ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
 
select owner
       , segment_name
       , segment_type
       , tablespace_name
       , block_id
       , blocks
from dba_extents
where file_id=1
and block_id in (
        select max(block_id)
        from dba_extents
        where file_id=1
        and block_id <= 8002
);
 
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME  BLOCK_ID BLOCKS
----- ------------ ------------ ---------------- -------- ------
SYS   C_OBJ#       CLUSTER      SYSTEM           7978         25
 
So, the object that the corrupt block belongs to is c_obj#. 
 
Is that correct?
 
If so, and since c_obj# stores data about objects (metadata), it basically
means that I can not access the object whose metadata is in that block.  
So, I'm trying to identify which object is lost. 
For example, if it's just an index, I should be able to export all the
objects from database into a new database and rebuild the index, and resolve
the corruption in that way. Any idea how I can identify the object whose
metadata is lost? 
 
I'm aware that Oracle support should be contacted, however a friend of mine
asked me to look into  this before they contact Oracle Support since they
don't have a support contract anymore... 
BTW, this is 8.0.6, they identified this problem 3 months ago in their
Peoplesoft application, and of course they don't have a good backup from
which to recover... 
 

Vlado Barun, M.Sc.

Mobile: 865 335 7652

AIM: vbarun2

 



--
//www.freelists.org/webpage/oracle-l

Other related posts: