Identifying the content of a corrupted block in system tablespace 2005-01-07 - By Howard Latham
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@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Powell, Mark D
Sent: 07 January 2005 16:20
To: oracle-l@(protected)
Subject: RE: Identifying the content of a corrupted block in system
tablespace
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@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of Vlado Barun
Sent: Thursday, January 06, 2005 9:24 PM
To: oracle-l@(protected)
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 (See ORA-01578.ora-code.com): ORACLE data block corrupted (file # 1, block # 8002)
ORA-01110 (See ORA-01110.ora-code.com): 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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
|
|
