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

  • From: "Howard Latham" <howard.latham@xxxxxxxxxx>
  • To: <mark.powell@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 7 Jan 2005 16:27:15 -0000

An export usually isolates the problem object.
And if its valuable data buy support as Oracle Support=20
have helped me out of a similar problem with a hack or two.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark D
Sent: 07 January 2005 16:20
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Identifying the content of a corrupted block in system

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

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

I have a corrupted block in the system tablespace.=20
I used the standard query to identify the object that the block but it
  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 =3D 1
  9* AND 8002 BETWEEN block_id and block_id + blocks -1 FROM
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:
select owner
       , segment_name
       , segment_type
       , tablespace_name
       , block_id
       , blocks
from dba_extents
where file_id=3D1
and block_id in (
        select max(block_id)
        from dba_extents
        where file_id=3D1
        and block_id <=3D 8002
----- ------------ ------------ ---------------- -------- ------
SYS   C_OBJ#       CLUSTER      SYSTEM           7978         25
So, the object that the corrupt block belongs to is c_obj#.=20
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. =20
So, I'm trying to identify which object is lost.=20
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?=20
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...=20
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...=20

Vlado Barun, M.Sc.

Mobile: 865 335 7652

AIM: vbarun2


Other related posts: