Home » RDBMS Server » Server Administration » Error while running dbms_repair (oracle 10.2.0.4.0 on windows server 2003)
Error while running dbms_repair [message #344889] Mon, 01 September 2008 10:48 Go to next message
dvishnu_apps
Messages: 34
Registered: September 2008
Member
Hi ,

We have some block corruptions in our 10g database.
so, we ran the dbms_repair package to fix those coruptions.

While running the procedure "dbms_repair.check_object() " , we still got the error as follows:

----------------------
declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_object (
schema_name => 'DATA',
object_name => 'CPT_CODES',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => rpr_count);
dbms_output.put_line('repair count: ' || to_char(rpr_count));
end;
/
declare
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 10, block # 1861641)
ORA-01110: data file 10: 'E:\ORADATA\NAVITST2\ELIG_DATA.DBF'
ORA-06512: at "SYS.DBMS_REPAIR", line 294
ORA-06512: at line 5
-----------------------

Please advise how to proceed further to fix those blocks.

Regards,
Vishnu
Re: Error while running dbms_repair [message #344891 is a reply to message #344889] Mon, 01 September 2008 11:02 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Possible procedures are described in Metalink Document 28814.1.

Excerpt from there :
Quote:

Use DBMS_REPAIR.FIX_CORRUPT_BLOCKS to mark the found problem blocks as corrupt so that they will then signal ORA-1578



Is that what you did? So you marked them as corrupt and they now signal ORA-1578, as it's supposed to work.

You still have to either extract as much data as possible and then recreate the table, or restore from backup, or do an export/import, etc.. depending on what data was in that table


Re: Error while running dbms_repair [message #345053 is a reply to message #344889] Tue, 02 September 2008 03:52 Go to previous messageGo to next message
dvishnu_apps
Messages: 34
Registered: September 2008
Member
Hi,

We ran DBMS_REPAIR.FIX_CORRUPT_BLOCKS for the object. It gave the fix count as : 0 , because already they are marked as corrupt.

Then, I ran the skip_corrupt_blocks() procedure to skip the corrupted blocks.

After this, when I select from the object, it gave the same error as follows:

---------------


SQL> select * from data.CPTDOCTOR;
select * from data.CPTDOCTOR
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 10, block # 1862409)
ORA-01110: data file 10: 'E:\ORADATA\NAVITST2\ELIG_DATA.DBF'

---------

Please provide what to do further.

Re: Error while running dbms_repair [message #345055 is a reply to message #345053] Tue, 02 September 2008 03:56 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
As I already said:
Quote:
You still have to either extract as much data as possible and then recreate the table, or restore from backup, or do an export/import, etc.. depending on what data was in that table


Have you read the Metalink document yet?
Re: Error while running dbms_repair [message #345056 is a reply to message #344889] Tue, 02 September 2008 04:02 Go to previous messageGo to next message
dvishnu_apps
Messages: 34
Registered: September 2008
Member
Hi,

As it was a production instance and we have block corruptions on 150 objects, it will consume time to recreate all the objects.

Also, backup has also block corruptions in it.

Please advise whether there is any way to solve those OR ELSE we will proceed the plan given by you.
Re: Error while running dbms_repair [message #345057 is a reply to message #345056] Tue, 02 September 2008 04:03 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Have you read the Metalink document yet?

Re: Error while running dbms_repair [message #345062 is a reply to message #344889] Tue, 02 September 2008 04:11 Go to previous messageGo to next message
dvishnu_apps
Messages: 34
Registered: September 2008
Member
Yes....I read the given doc. in the metalink ID 28814.1
Re: Error while running dbms_repair [message #345080 is a reply to message #344889] Tue, 02 September 2008 04:50 Go to previous messageGo to next message
dvishnu_apps
Messages: 34
Registered: September 2008
Member
Hi,

Can you Please let us know how to extract as much as data possible as we are getting error while selecting it.

We tried by exporting the object for extracting the info as follows:
----------

exp file=xx1.dmp log=xx2.log tables=xx.TAB1 direct=Y

----------

We are getting the same ORA-01578 error.



Re: Error while running dbms_repair [message #345095 is a reply to message #345080] Tue, 02 September 2008 05:22 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Different methods are in (4C) Salvaging Data from Tables in the document. Which one you an use still depends on what DATA is actually in the table.

Also note the "...these methods typically require much hand-holding from support..."
Re: Error while running dbms_repair [message #345188 is a reply to message #344889] Tue, 02 September 2008 09:55 Go to previous messageGo to next message
dvishnu_apps
Messages: 34
Registered: September 2008
Member
Hi,

I tried to extract the data with the ROWID from the corrupted table from the doc. id : 61685.1.

The statements which are executed as follows:
-------------
1. SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id =10
AND 1861641 between block_id AND block_id + blocks - 1 ;

no rows selected.

2. SELECT data_object_id
FROM dba_objects
WHERE object_name = 'CPT_CODES' and owner='DATA'

DATA_OBJECT_ID
--------------
1000297

3. select dbms_rowid.rowid_create(1, 1000297,7,1861642,0) from dual;

DBMS_ROWID.ROWID_CREATE(
----------------
AAD0NpAAHAAHGgJAAA

4. For extracting the data without accessing corrupted block, we got the same error as follos:

SELECT /*+ ROWID(A) */ *
FROM DATA.CPT_CODES A
WHERE rowid < 'AAD0NpAAHAAHGgJAAA';


ORA-01578: ORACLE data block corrupted (file # 10, block # 1861641)
ORA-01110: data file 10: 'E:\ORADATA\NAVITST2\ELIG_DATA.DBF'

Please suggest us the solution to it.



Re: Error while running dbms_repair [message #345207 is a reply to message #345188] Tue, 02 September 2008 10:50 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Why did you use 1861642 in the creation of the ROWID, when (one of) the corrupt block is 1861641, and then select everything below it?

Some row with a ROWID below block 1861642 will surely run into the corrupt block 1861641.

Re: Error while running dbms_repair [message #345213 is a reply to message #344889] Tue, 02 September 2008 11:01 Go to previous messageGo to next message
dvishnu_apps
Messages: 34
Registered: September 2008
Member
Sorry....It's a typo mistake.

I ran for the block 1861641 and NOT for 1861642.

Re: Error while running dbms_repair [message #345217 is a reply to message #345213] Tue, 02 September 2008 11:11 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
DON'T re-type. Copy and paste what actually happened.

It's also funny that the select from dba_extends doesn't return anything. Is there also a typo in there?

If not, run

SELECT * 
  FROM dba_extents
 WHERE file_id =10
 ORDER BY block_id


To maybe see why nothing shows up when you try to find what's at block 1861641.
Re: Error while running dbms_repair [message #345220 is a reply to message #344889] Tue, 02 September 2008 11:24 Go to previous messageGo to next message
dvishnu_apps
Messages: 34
Registered: September 2008
Member
Hi,

It's not a typing mistake while executing the query "SELECT *
FROM dba_extents".

We executed the below query.
SELECT *
FROM dba_extents
WHERE file_id =10
ORDER BY block_id


It contains 2183 rows but it doesn't contain the corrupted block id 1861641.
Re: Error while running dbms_repair [message #345349 is a reply to message #345220] Wed, 03 September 2008 03:16 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
but it doesn't contain the corrupted block id 1861641.


OK, that CAN'T be. Post the FORMATTED 10 or so rows AROUND 1861641.

If it's really not in there, then the database is in some destroyed state that I haven't heard of before.

Then you pretty much can't do anything further aside from raising a call with Oracle support.

[Updated on: Wed, 03 September 2008 03:17]

Report message to a moderator

Re: Error while running dbms_repair [message #345359 is a reply to message #344889] Wed, 03 September 2008 03:35 Go to previous messageGo to next message
dvishnu_apps
Messages: 34
Registered: September 2008
Member
Hi,

I executed the below query:
------
SELECT *
FROM dba_extents
WHERE file_id =10
and block_id between 1860000 and 1870000
ORDER BY block_id

------

The query resulted with the four rows. I am attaching the output of the above query as <<corrupted_objects.txt>>.

Please let us know any possible way to solve these block corruptions before proceeding with the oracle support.

Re: Error while running dbms_repair [message #345361 is a reply to message #345359] Wed, 03 September 2008 03:45 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Nope. The corrupt block is not in DBA_EXTENDS, hence the dictionary itself is most likely shot.

Nobody but Oracle support can help there.
Re: Error while running dbms_repair [message #345364 is a reply to message #344889] Wed, 03 September 2008 03:53 Go to previous message
dvishnu_apps
Messages: 34
Registered: September 2008
Member
Great...!!

Thank You very much for giving the support to my queries.

I will raise an S.R with the oracle support to obtain a solution to this problem.

Previous Topic: out of process memory
Next Topic: Physical structure
Goto Forum:
  


Current Time: Mon Dec 02 08:38:06 CST 2024