| Error while running dbms_repair [message #344889] |
Mon, 01 September 2008 10:48  |
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  |
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 |
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 |
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 |
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 #345080 is a reply to message #344889] |
Tue, 02 September 2008 04:50 |
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 |
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 |
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 |
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 #345217 is a reply to message #345213] |
Tue, 02 September 2008 11:11 |
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 |
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 |
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 |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
