Oracle Block Corruption

[Mofizur]

Hi,

I have below system

NW 731 SPS08 JAVA on Oracle 11

I was trying to perform a export of JAVA system using SWPM and It is failing due to below error

ORA-01578: ORACLE data block corrupted (file # 5, block # 214

data file 5: 'G:\ORACLE\SID\SAPDATA4\SR3DB_2\SR3DB.DATA2'

I have run dbv on the file and belwo is the output.There are many continuous segment corrupted on that file.

dbv file=G:\ORACLESID\SAPDATA4\SR3DB_2\SR3DB.DATA2 blocksize=8192

DBVERIFY - Verification complete

Total Pages Examined         : 256000

Total Pages Processed (Data) : 22660

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 15999

Total Pages Failing   (Index): 0

Total Pages Processed (Lob)  : 211772

Total Pages Failing   (Lob)  : 0

Total Pages Processed (Other): 3250

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 1806

Total Pages Marked Corrupt   : 513

Total Pages Influx           : 2

Total Pages Encrypted        : 0

Highest block SCN            : 6904259 (0.6904259)

I have an offline backup of Nov 14 and DB is in archive log mode.After that there are many changes in the DB made.

How can I fix this issue.

Thanks,

Mofizur

[former_member207186]

Hi,

To handle corruption issue, you can proceed as per SAP Notes 23345 - "Consistency check of ORACLE database" and 365481 - "Block corruptions". For more information, please also refer to SAP KBA 1559652 - "How to deal with block corruptions on Oracle".

Also for a possible way to fix block corruption with Zero Downtime, please refer to point 12 of SAP Note 540463- "FAQ: Consistency Checks + Block Corruptions".

For restore&recovery please check SAP Note 605062 - "FAQ: Restore and recovery".

Regards,
Bíborka

[tomislav_bobanovic]

Hi,

please refer to the related document:

https://oracle-base.com/articles/misc/detect-and-correct-corruption

You can run the sql command:

SELECT DISTINCT owner, segment_name

    FROM   v$database_block_corruption dbc

        JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1

    ORDER BY 1,2;

to find which objects are affected.

In case that indexes were corrupted you can recreate them.

also you can find info about datafile number and block number from V$DATABASE_BLOCK_CORRUPTION view.

Br

Tomislav