ORA-01578: ORACLE data block corrupted

[Former Member]

hi everyone..

I have a serious issue on one of our servers Workprocesses are ending

and dispatcher is shutting down.

i have checked the DEV_W0 trace file and it is showing me an error

***********************

ORA-01578: ORACLE data block corrupted

***********************

I have searched for solution on the net..few site suggest to check wheather the

corrupted block belong to index or table segment..

Before i follow any of those step i wants to check in the sdn whether any one has any other alternative .

DEV_W0 trace file info

++++++++++++++++++++++++++++++++++++++

X ES initialized.

C *** ERROR => ORA-1578 occured when executing SQL statement (parse error offset = 0)

[dbsloci.c 11244]

C sc_p=043080A4,no=1,idc_p=0758EA58,con=0,act=1,slen=66,smax=256,#vars=0,stmt=0764B048,table=DDNTT

C prep=0,lit=0,nsql=0,lobret=0,#exec=1,dbcnt=0,upsh_p=00000000,ocistmth_p=0763DD2C

C IN : cols=0,rmax=0,xcnt=0,rpc=0,rowi=0,rtot=0,upto=-1,rsize=0,vmax=0,bound=0,iobuf_p=00000000,vda_p=00000000

C lobs=0,lmax=0,lpcnt=0,larr=00000000,lcurr_p=00000000,rret=0

C OUT: cols=1,rmax=1,xcnt=1,rpc=0,rowi=0,rtot=0,upto=-1,rsize=4,vmax=32,bound=1,iobuf_p=0764A988,vda_p=0764B2B8

C lobs=0,lmax=0,lpcnt=0,larr=00000000,lcurr_p=00000000,rret=0

C SELECT COUNT(*) FROM DDNTT WHERE TABFORM <> 'J' AND TABFORM <> 'V';

B ***LOG BYL=> DBQ action required because of database error [dbsh#2 @ 1096] [dbsh 1096 ]

B SQL code: 1578, SQL text: ORA-01578: ORACLE data block corrupted (file # 4, block # 20481)

B ORA-01110: data file 4: 'E:\ORACLE\TST\SAPDATA4\TST_2\TST.DATA2'

B ***LOG BY4=> sql error 1578 performing SEL on table DDNTT [dbstat#1 @ 690] [dbstat 0690 ]

B ***LOG BY0=> ORA-01578: ORACLE data block corrupted (file # 4, block # 20481)

ORA-01110: data file 4: 'E:\ORACLE\TST\SAPDATA4\TST_2\TST.DATA2' [dbstat#1 @ 690] [dbstat 0690 ]

B init: Got no entries from DDNTT !

B init failed

B db_stinit failed

++++++++++++++++++++++++++++++++++++++

Thank you.

Kiran kumar

[Former Member]

Hi Kiran how are you doing? I also believe there is no alternative. You probably have the steps to check to see if the corrupted blocks belong to index or table segment if not here are the steps ones again.

(1) Execute the following query to Identify the object owning the

corrupted block.

SELECT tablespace_name, segment_type, owner, segment_name

FROM dba_extents

WHERE file_id = 7

and 451 between block_id AND block_id + blocks - 1;

(2) Run analyze on the object returned by the above query.

If table then

sql> Analyze table <table name> validate structure cascade;

If index then

sql> Analyze Index <Index name> validate structure ;

(4) Run dbverify on the corrupted file and upload the output.

dbv file=<corrupted file anme> blocksize=<database block size>

Also Kiran you should set up ways to check Logical Corruption and Physical Corruption if you have not already done so. Here is what I have put into place:

In rare circumstances, small bits of data in an Oracle database can become corrupt and damage the integrity of the database. In general, two types of data corruptions are possible: logical corruptions and physical corruptions.

A logical data corruption is undesirable, nonsense data. Logical data corruptions most often occur due to software bugs, and are very difficult to repair because they are probably woven into the database's thread of redo entries.

A physical data corruption is a lost bit of data, usually due to faulty storage media.

To address Logical Corruption:

The initialization parameter DB_BLOCK_CHECKING must be set to TRUE. Oracle checks a block by going through the data on the block, making sure it is self-consistent. Block checking can often prevent memory and data corruption in tables and indexes. The only drawback to setting this parameter to TRUE is that it typically causes 1% to 10% overhead, depending on workload. I believe that in our case 1% to 10% performance overhead would be acceptable.

The second method of addressing logical corruption in a table, index, partition or subpartition of a table or index, or data cluster, is to use the VALIDATE STRUCTURE clause of the SQL command ANALYZE. In general, Oracle returns error messages if any corruptions are found; otherwise, Oracle returns a simple message that the structure was analyzed successfully.

To address Physical Corruption:

The initialization parameter DB_BLOCK_CHECKSUM must be set to TRUE. This parameter allows Oracle to detect corruption caused by underlying disks, storage system, or I/O systems. Turning on this feature typically causes only an additional 1% to 2% overhead.

The second method of addressing physical corruption is to set up and use Oracles DBVERIFY utility. DBVERIFY is a command-line utility that accepts a few runtime parameters to control it's behavior. This utility will validate the physical integrity of an Oracle database's data files on demand.

Take care

[former_member204746]

no alternative... you need to check which block is corrupted, to which table/index it belongs to and try to repair it.

read SAP Note 365481 - Block corruptions