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

From: Parker, Matthew <matthewp_at_amazon.com>
Date: Fri, 7 Jan 2005 08:55:46 -0800
Message-ID: <F385925F530F6C4081F1659F71640EB301CC751B@ex-mail-sea-04.ant.amazon.com>

Here are the three queries (Jeremiah was referring to, (nice to know = your still having fun jeremiah although your company name is kind of = funny)) in order to return the most data available, easily:

--So the the second part of the minus will bypass the bad block. ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10'; select /*+ INDEX (o I_OBJ_1) */ obj# from obj$ o minus
select /*+ FULL (o) */ obj# from obj$ o;

select /*+ INDEX (o I_OBJ_1) */ =
owner#,name,namespace,remoteowner,linkname,subname from obj$ o minus
select /*+ FULL (o) */ =
owner#,name,namespace,remoteowner,linkname,subname from obj$ o;

select /*+ INDEX (o I_OBJ_1) */ oid$ from obj$ o minus
select /*+ FULL (o) */ oid$ from obj$ o;

To fix this becomes more complex:
1. Does your friend have any backups of the database? How old are the = backups? Is there a continuous redo chain for the backup?

2. Actual repair of the block would require at least a trace dump and a = block dump.
alter system dump datafile '/u100/oradata/fstst/system01.dbf' block = 8002;
(You will need to fill in the appropriate database blocksize for the = bs=3D paramter in bytes)
dd if=3D/u100/oradata/fstst/system01.dbf of=3Ddd_df_1_bl_8002_curr.dd = bs=3D8192 skip=3D8002 count=3D1 conv=3Dnotrunc

Once this information is available then some choices can be made to fix = the system.

-----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.=20 I used the standard query to identify the object that the block but it failed:
=20

SQL>=20
  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 = 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'
=20

Then I did this:
ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
=20

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

);
=20

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCK_ID BLOCKS

----- ------------ ------------ ---------------- -------- ------
SYS   C_OBJ#       CLUSTER      SYSTEM           7978         25

=20

So, the object that the corrupt block belongs to is c_obj#.=20
=20

Is that correct?
=20

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

Vlado Barun, M.Sc.

Mobile: 865 335 7652

AIM: vbarun2

=20

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

Received on Fri Jan 07 2005 - 10:56:48 CST