Corrupt block relative dba:. how to correct, what to make?

[hhbrazil]

The error in the alert file


Corrupt block relative dba: 0x0ea1af4e (file 58, block 2207566)
Bad header found during buffer read
Data in bad block -
type: 0 format: 0 rdba: 0x00000000
last change scn: 0x0000.00000000 seq: 0x1e flg: 0x64
consistency value in tail: 0x99120606
check value in block header: 0x0, computed block checksum: 0x6e3a
spare1: 0x0, spare2: 0x0, spare3: 0x19a
***
Reread of rdba: 0x0ea1af4e (file 58, block 2207566) found same corrupted data
***
Corrupt block relative dba: 0x0ea1af4e (file 58, block 2207566)
Bad header found during buffer read
Data in bad block -
type: 0 format: 0 rdba: 0x00000000
last change scn: 0x0000.00000000 seq: 0x1e flg: 0x64
consistency value in tail: 0x99120606
check value in block header: 0x0, computed block checksum: 0x6e3a
spare1: 0x0, spare2: 0x0, spare3: 0x19a
***

The tablespace

Nome
da Tablespace FILE_ID FILE_NAME
------------------------------ ---------- ----------------------------------------
RTX_DATA 58 /datafiles/pnxtl_02/dat/rtx_data_07.dbf


How to correct?????

[stmontgo]

What version?

You can through the dilligene to determine what object in that file is corrupt if you like. Perhaps it's am index that you can just rebuild.

If it turns out to be a crucial object you will have to restore some how. Perhaps from a backup or it's a static table you could rebuild from Q/A type environemnt if that is available to you.

steve

[OracleDoc]

Run this script and it will tell you what the object is that is corrupt. Hopefully it's an index and you can just drop it and recreate it. If it's a table then there are a couple of things you can do to fix it.


/* title: Corrupt_blocks
purpose:This script will find what objects are corrupt if you receive a datablock corruption error
example: ORA-01578: ORACLE data block corrupted (file # 347, block # 13591)
ORA-01110: data file 347: '/u006/oradata/OARPTSdata4/applsysx03.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
*/
select segment_name,segment_type,owner
from sys.dba_extents
where file_id=(m)
and (z) between block_id and block_id + blocks -1;
/* note! (m) is the file number and (z) is the block number reported in the ora-01578 error message.
*/

[hhbrazil]

To Mr. stmontgo

Version 8.1.7.2.0

To Mr. OracleDoc

PNXTL02-:dbahiran> select segment_name,segment_type,owner
2 from sys.dba_extents
3 where file_id=58
4 and (2207566) between block_id and block_id + blocks -1;

SEGMENT_NAME SEGMENT_TYPE Dono
--------------------------------------------------------------------------------- ------------------ ------------
RTX_LT TABLE PARTITION SYSADM

[hhbrazil]

I agree with you

OCP-One more way for Larry to extract money from you.

[anandkl]

Since its a table corruption, you need to restore from the the most recent backup.If u dont have a good backup u can do the following which will cause oracle to skip the corrupt block while reading

1 .exec DBMS_REPAIR.ADMIN_TABLES ('REPAIR_ADMIN',1,1,null);
execute this in your schema which will create a table by name REPAIR_ADMIN.You can give your own name.This table will basically store name of objects for which corruption was blocks were skipped

2.variable a number;
exec sys.DBMS_REPAIR.CHECK_OBJECT( 'schema_owner' , table_name' , null, 1, REPAIR_ADMIN', null, null, null, null, :a );
print a;

This will check object for any block corruption and will tell u how many blocks were corrupt.

3.exec DBMS_REPAIR.FIX_CORRUPT_BLOCKS( 'schema_name' , 'table_name' , NULL, 1, 'REPAIR_ADMIN',NULL, :a );
print a;

This will mark the corrupt blocks as been corrupt and this output should be same the output of step 2

4.exec DBMS_REPAIR.skip_corrupt_blocks( 'schema_name' , 'table_name' , 1, 1);

This will mark all the corrupt blocks to be skipped when the process reads this blocks.

5. exec DBMS_REPAIR.rebuild_freelists( 'schema_name' , 'table_name' , null, 1);

This will rebuild the freelists for the table

If using this method all the data in corrupt block will be lost.

So the best way to fix the corrupt block is do drop the table and do recover from the most recent backup..

All the best in fixing the block corruption.

please come back if you have any questions.

we faced the same problem

regards
anandkl

[hhbrazil]

To anandkl


Tanks,