Identifying the content of a corrupted block in system tablespace 2005-01-07 - By Parker, Matthew
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@(protected) =
[mailto:oracle-l-bounce@(protected)] On Behalf Of Vlado Barun
Sent: Thursday, January 06, 2005 6: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
=20
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
|
|
