Identifying the content of a corrupted block in system tablespace 2005-01-16 - By Parker, Matthew
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 =
anymore.
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@(protected)]=20
Sent: Saturday, January 15, 2005 1:38 PM
To: Parker, Matthew; oracle-l@(protected)
Subject: RE: Identifying the content of a corrupted block in system =
tablespace
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.
=20
So, my next question is what are my options in regards to resolving this =
problem?
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 o.name object_name
, o.type# type
, u.name owner
from sys.obj$ o
, sys.user$ u
where o.owner# =3D u.user#
and u.name not in ('SYSTEM', 'SYS')
-- and rownum <11
ORDER BY u.name, o.name;
begin
for obj_rec in objs loop
begin
l_object_name:=3D obj_rec.object_name;
l_owner:=3D obj_rec.owner;
l_type:=3D obj_rec.type;
=09
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;
dbms_sql.parse(l_cursor_name
, 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);
=09
dbms_sql.close_cursor(l_cursor_name);
insert into object_avail(owner, object_name, type,
row_count)=20
values (l_owner, l_object_name, l_type, l_row_count);
exception
when others then
l_sqlerrm :=3D sqlerrm;
insert into object_avail(owner, object_name, type,
error_msg)=20
values (l_owner, l_object_name, l_type, l_sqlerrm);
dbms_sql.close_cursor(l_cursor_name);
end;
end loop;
end;
/
Vlado Barun, M.Sc.
Mobile: 865 335 7652
AIM: vbarun2
-- --Original Message-- --
From: Parker, Matthew [mailto:matthewp@(protected)]
Sent: Friday, January 07, 2005 10:56 AM
To: vlado@(protected); oracle-l@(protected)
Subject: RE: Identifying the content of a corrupted block in system =
tablespace
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
|
|
