?
Solved

ORA-01578: ORACLE data block corrupted

Posted on 2009-02-18
10
Medium Priority
?
4,728 Views
Last Modified: 2013-12-19
Hi, when I executed the following query:

select count(*) from voucher;

I got the following:

ORA-01578: ORACLE data block corrupted (file # 10, block 124389)
ORA-01110: data file 10: ppvms/oracle/oradata/ppvmsdb/DATA.dbf

So I executed the following query:

select segment_name, segment_type, block_id, blocks
from dba_extents
where block_id between 124389 and (124389 + blocks - 1) and file_id =10;

And I got the following:

segment_name: VOUCHER_CARDNUMBER_PK
segment_type: INDEX
block_id: 125321
blocks: 1024

So, since it's an index, I decided to drop it, and then re-create it, but I got the following error:

ORA-02429: cannot drop index used for enforcement of unique/primary key

After searching around, found out that:

Cause: user attempted to drop an index that is being used as the enforcement mechanism for unique or primary key.
Action: drop the constraint instead of the index.

So I decided to drop the constraint by executing the following query:

ALTER TABLE VOUCHER DROP CONSTRAINT VOUCHER_CARDNUMBER_PK;

And got a message that looked something like the following:

Table altered.

So now I tried to re-add the constraint:

ALTER TABLE VOUCHER ADD CONSTRAINT VOUCHER_CARDNUMBER_PK PRIMARY KEY (VOUCHER_CARDNUMBER);

But I got back the original error message:

ORA-01578: ORACLE data block corrupted (file # 10, block 124389)
ORA-01110: data file 10: ppvms/oracle/oradata/ppvmsdb/DATA.dbf

I executed the following query again:

select segment_name, segment_type, block_id, blocks
from dba_extents
where block_id between 124389 and (124389 + blocks - 1) and file_id =10;

And got the following result:

no rows selected

-----

So now my VOUCHER table is index-less in addition to having a corrupted block. Can someone guide me on how to solve this issue please? Thanks.
0
Comment
Question by:killdurst
  • 6
  • 4
10 Comments
 
LVL 6

Expert Comment

by:FVER
ID: 23670603
your query to retrieve corrupted segment name is not correct.
try this :

select segment_name, segment_type, block_id, blocks
from dba_extents
where 124389 between block_id and (block_id+ blocks - 1)
  and file_id =10;

Open in new window

0
 
LVL 1

Author Comment

by:killdurst
ID: 23678343
When I executed your query, I got the following:

SEGMENT_NAME: VOUCHER
SEGMENT_TYPE: TABLE
BLOCK_ID: 124297
BLOCKS: 1024

So I guess the object that is in the corrupted block is the "voucher" table. But it seems that we can still insert records into this table. So now that we've determined that the corrupted block contains this table, what can I do? I tried to log in as sys and executed the following command:

desc DBMS_REPAIR

But I got the following error:

ORA-04043: object DBMS_REPAIR does not exist

0
 
LVL 6

Expert Comment

by:FVER
ID: 23679082
Hi killdurst,

DBMS_REPAIR is created by script {ORACLE_HOME}/rdbms/admin/dbmsrpr.sql
metalink note 28814.1 is the bible on data block corruption. Choose your solution between 4C and 4D

Hope this helps

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 1

Author Comment

by:killdurst
ID: 23679233
Hi,

May I know how to run that script?

And may I also know if it's possible to access the contents of metalink note 28814.1 without a username and password?

Thanks.
0
 
LVL 6

Accepted Solution

by:
FVER earned 1500 total points
ID: 23679439
Use sqlplus to run that script :
sqlplus user/pwd@database @{ORACLE_HOME}/rdbms/admin/dbmsrpr.sql

for information about using DBMS_REPAIR
http://www.oracleutilities.com/Packages/dbms_repair.html
http://www.psoug.org/reference/dbms_repair.html
0
 
LVL 1

Author Comment

by:killdurst
ID: 23687938
Hi,

I've ran the script and then connected using the "sys" account as "sysdba" and then I executed the following query:

desc DBMS_REPAIR

But I got the following error message:

ORA-24372: invalid object for describe

May I know how to solve this please? Thanks.
0
 
LVL 1

Author Comment

by:killdurst
ID: 23689735
Ok, I've managed to run the script successfully and now I can describe DBMS_REPAIR... I'm now trying the fix corrupted blocks procedure... will keep u updated...
0
 
LVL 6

Expert Comment

by:FVER
ID: 23724028
Have you succeded in fixing the corruption ?
0
 
LVL 1

Author Comment

by:killdurst
ID: 23728599
The DBA wants to try this DBMS_REPAIR utility on one of his test databases first, so he's currently trying to simulate a corrupt block in the database. I gave him the following script:

SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
     SCHEMA_NAME => 'JJDEV',
     OBJECT_NAME=> 'VOUCHER',
     OBJECT_TYPE => dbms_repair.table_object,
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
END;
/

And asked him to paste it into a script file and then execute it from SQLPLUS but he wants to test it out on a test database first. Will keep you guys updated...
0
 
LVL 1

Author Comment

by:killdurst
ID: 23802472
Update: The DBA has successfully ran the script above. We are now trying to re-create the index that we dropped earlier... will keep u guys updated...
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question