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

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 7 Jan 2005 11:20:23 -0500
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF410511E70C@usahm236.amer.corp.eds.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Vlado Barun Sent: Thursday, January 06, 2005 9:24 PM To: oracle-l_at_freelists.org
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

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

Received on Fri Jan 07 2005 - 10:23:37 CST