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

  • From: "Parker, Matthew" <matthewp@xxxxxxxxxx>
  • To: "Vlado Barun" <vlado@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 16 Jan 2005 03:10:06 -0800

Yep, the queires were wrong. Some days it doesn't pay to multi-task.

1. The problem in dropping the objects will be the recursive sql oracle =
will issue that will probably cause the drops to fail as they try to =
touch the corrupted block for the delete of rows in the data dictionary. =
You could test on the tables, since you say they are not necessary =

2. This is the standard plain jane way to get around dictionary =
corruption if you do not have any good backups, ( of course it doesn't =
help when the people wait for long periods of time thinking the problem =
can be solved in the same way at any point in time).

3. Do the views and synonym belong to the peoplesoft application? If not =
then you could use different named objects with synonyms to get around =
the views that are bad. Getting around the synonym might be a little =
more complex depending on if it is a a public synonym or a private =
synonym. If it is a peoplesoft application then it is more complex if =
you ever try to upgrade the application.

4. The other options available would depend on you answering the =
questions in my original email and providing the appropriate data (dd =
block dump and block trace of the corrupted block to determine the type =
of corruption and whether the block can be easily repaired or whether =
data extraction and reinsertion is the only way around the problem).=20
Along the lines of the original email, when you say you don't have any =
good backups, what does that mean? (You have no backups at all? You have =
no complete backups including any copies of the system datafile? You =
have backups with no archive logs to recover it from? You have no =
backups prior to the detection of the corrupted block?)

-----Original Message-----
From: Vlado Barun [mailto:vlado@xxxxxxxxxx]=20
Sent: Saturday, January 15, 2005 1:38 PM
To: Parker, Matthew; oracle-l@xxxxxxxxxxxxx
Subject: RE: Identifying the content of a corrupted block in system =

Thanks for the queries however they returned no rows, which I accutally =
expected, since obj$ is not part of the c_obj# cluster, which is the =
cluster that has the corrupted block (see my original post). So, I =
didn't even need to use the alter session...

BTW, in the second and third query you need to adjust the index name...

So, what I did is to loop through each object in obj$ and try to access =
the data. See code below. In this way I identified 3 tables, 2 views and =
a synonym that are affected by the corrupted block. Fortunately, the =
three tables are not needed anymore, and I should be able to rebuild the =
2 views and synonym.
So, my next question is what are my options in regards to resolving this =

Currently, I see the following options:
1) As somebody mentioned before, if the row data and not the block =
header data is corrupt, then by just dropping the objects, the =
corruption might go away.=20
2) Create an empty database, export objects from corrupted database and =
import all the objects into the new database.

I like option 1, but if it doesn't work is there any other less resource =
intensive way to resolve this than option 2?

create or replace procedure access_all_data as
  l_sql_string  varchar2(2000);
  l_type        number;
  l_object_name varchar2(30);
  l_owner          varchar2(30);
  l_cursor_name INTEGER;
  l_ignore integer;
  l_sqlerrm varchar2(4000);
  l_row_count number;
cursor objs is
        select object_name
                   , o.type# type
                   , owner
        from sys.obj$ o
                 , sys.user$ u
        where o.owner# =3D u.user#
        and not in ('SYSTEM', 'SYS')
--      and rownum <11
        ORDER BY,;
        for obj_rec in objs loop
                        l_object_name:=3D obj_rec.object_name;
                        l_owner:=3D obj_rec.owner;
                        l_type:=3D obj_rec.type;
                        l_sql_string:=3D'select /*+ full(t) noparallel(t) */
count(*) from ' || l_owner || '.' || l_object_name || ' T';
                    l_cursor_name :=3D dbms_sql.open_cursor;
                                                        , l_sql_string
                                                , SYS.DBMS_SQL.native);
                        DBMS_SQL.DEFINE_COLUMN(l_cursor_name, 1, l_row_count);
                    l_ignore :=3D dbms_sql.execute(l_cursor_name);
                        l_ignore :=3D DBMS_SQL.FETCH_ROWS(l_cursor_name);
                        DBMS_SQL.COLUMN_VALUE(l_cursor_name, 1, l_row_count);
                        insert into object_avail(owner, object_name, type,
                        values (l_owner, l_object_name, l_type, l_row_count);
                         when others then
                         l_sqlerrm :=3D sqlerrm;
                         insert into object_avail(owner, object_name, type,
                         values (l_owner, l_object_name, l_type, l_sqlerrm);
        end loop;

Vlado Barun, M.Sc.
Mobile: 865 335 7652
AIM: vbarun2

-----Original Message-----
From: Parker, Matthew [mailto:matthewp@xxxxxxxxxx]
Sent: Friday, January 07, 2005 10:56 AM
To: vlado@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Identifying the content of a corrupted block in system =

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.

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) */ =
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@xxxxxxxxxxxxx =
On Behalf Of Vlado Barun
Sent: Thursday, January 06, 2005 6: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: