RE: Identifying the content of a corrupted block in system tables pace

From: "Powell, Mark D" <mark.powell@xxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Fri, 7 Jan 2005 11:20:23 -0500
If you are lucky the corruption is limited to row data rather than block
header/overhead entries and if so dropping the corrupt object and recreating
it may well work.  I know because I have lived through this experience where
the base table for sequences was corrupted, but c_obj# is a cluster and
there are around 16 tables assigned to it on my 9.2.0.5 system so your fiend
may not be so lucky.

Try dumping the block to see what a block dump shows.

You could try a select * from each table in the cluster and see how many of
them error off.  If only one or two of the selects fail then you can look
for PK on these tables and use a pl/sql routine to fetch each row by PK and
trap errors.  This would find you the row(s) affected by the corruption.

If nothing else this would give you more data to provide support.

HTH -- Mark D Powell --


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Vlado Barun
Sent: Thursday, January 06, 2005 9:24 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Identifying the content of a corrupted block in system
tablespace


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
RE: Identifying the content of a corrupted block in system tables pace

Other related posts:
