asked on

ORA-01578: ORACLE data block corrupted

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.

FVER

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

killdurst

ASKER

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

FVER

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

killdurst

ASKER

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.

ASKER CERTIFIED SOLUTION

FVER

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

killdurst

ASKER

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.

killdurst

ASKER

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

FVER

Have you succeded in fixing the corruption ?

killdurst

ASKER

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

killdurst

ASKER

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