We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

ORA-01578: ORACLE data block corrupted

killdurst
killdurst asked
on
Medium Priority
4,899 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.
Comment
Watch Question

Commented:
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

Author

Commented:
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

Commented:
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

Author

Commented:
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.
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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.

Author

Commented:
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...

Commented:
Have you succeded in fixing the corruption ?

Author

Commented:
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...

Author

Commented:
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...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.