RE: Identifying the content of a corrupted block in system tablespace

From: Vlado Barun <vlado_at_cadre5.com>
Date: Tue, 8 Mar 2005 22:06:39 -0600
Message-Id: <200503090406.j2946oO70530@cadre5.com>

Just a quick summary on how I resolved this problem (see below for the problem definition).

I used a stored proc to dynamically execute a "select /*+ full(t) noparallel(t) */" against all appropriate objects to identify the objects that were affected. The result was 1 synonym, 2 views and 3 tables were affected. Fortunately the 3 tables contained old audit data and were not needed anymore, and the views and synonym could easily be recreated.

So, I set event = "10231 trace name context forever, level 10" to export all data, except the corrupted block. However, export failed with an ora-600 on the export, basically saying that it's trying to export a table for which it can't find it's location.
After further research, I added "and o$.name not in ('PS_AUDIT_0000222', 'PS_AUDIT_0000223', 'PS_AUDIT_0000224' )" to the export views sys.EXU8CLU and SYS.EXU8TAB, thus telling export not to even try to deal with the 3 affected tables. It worked and the rest is the standard exp/imp story...

Thank you for all of your help.

-----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 6: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



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

Received on Tue Mar 08 2005 - 23:10:09 CST