SAP ORACLE BLOCK CORRUPTION TEST FOR TABLE AND RESOLUTIONS

   ORACLE BLOCK CORRUPTION TEST FOR TABLE AND RESOLUTIONS


Summary:- 
This document contain the details about the various test performed for table block corruption without a validate backup to fix the issue. Oracle classifies the Data File Block corruptions as Physical and Logical.  This is also referred as intra block corruptions.

Table of content for test

1. Describe about block corruption
2. Types of block corruption
3. Test scenario
4. Online re-org
5. DBMS_REPAIR
6. Table copy
7. Export and import Data pump
8. BALDAT export and import in SAP level

About Causes of corrupted blocks
Block corruptions can be caused by various failures including, but not limited to the following:
Faulty disks and disk controllers

• Faulty memory
• Faulty network components
• Firmware, operating system, volume manager, NFS or third party software defects
• Oracle Database software defects

Block corruptions can be also be caused by operator errors such as copying backups over existing data files or restoring inconsistent database backups.
The oracle Block corruption thrown error in some case like using no logging, some bad disk sectors or in file system, using huge volume of data kept, header footer issue in tablespace. etc..
The oracle block corruption issue can be fixed easily if you have validate RMAN Backup during the system in fly conditions.  Suppose if we have only backint or some other backup can be recover the data by using only affected data-file in offline system mode.  If the block corruption in index level, it can be fix by dropping and recreating the index. If it’s from LOB segment or LOB INDEX, Solution: Rebuild the LOB segment or Index (or) DBMS_REPAIR “(or) export the table with LOB index.
Physical and Logical Block Corruptions
Physical Block Corruptions
This kind of block corruptions are normally reported by Oracle with error ORA-1578 and the detailed corruption description is printed in the alert log.
Corruption Examples are:

• Bad header – the beginning of the block (cache header) is corrupt with invalid values
• The block is Fractured/Incomplete – header and footer of the block do not match
• The block checksum is invalid
• The block is misplaced
• Zeroed out blocks / ORA-8103

Logical Block Corruptions

When block contains a valid checksum and the structure below the beginning of the block is corrupt (Block content is corrupt). It may cause different ORA-600 errors.
The detailed corruption description for Logical Corruptions are not normally printed in the alert.log. DBVerify will report what is logically corrupted in the block.
Corruption Examples are:

• row locked by non-existent transaction – ORA-600 [4512],etc
• the amount of space used is not equal to block size
• avsp bad..etc..

When db_block_checking is enabled, it may produce the internal errors ORA-600 [kddummy_blkchk] or ORA-600 [kdBlkCheckError].


Step1
1)  Create one table space”TEST_CORRUPT with table “emp” as well.



2) Insert some entries in table “emp”







3) Now we are having manually corrupted the file by editing the block and damage the same for table “emp” in offline;




4) We have start the database and executed the below command. Note: – we have not taken any backup.




Now we got a table block corruption and we are not able to view the table


we are going to fix the issue without valid backup.

Action first method:-
1) Table copy.
We copy the table with some other name, then we can rename it.  Caution: if the table is copied successfully then it’s not an issue in table level, its seems in tablespace of datafile corruption issue. Tablespace corruption issue also can be fixed.


We performed the activity .we got an issue while copy the table emp to emp_new. Because the table is already corrupted and unable to copy.
We can copy the table by using marked is corrupted. Caution: This may cause loss of data.
Action second method:-
2) DBMS REPAIR
We have performed the DBMS REPAR for the table “emp”;








To detecting the block corruption execute the below.



Detected one file as corrupted.
To repair the table, execute the below command.

SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT, CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION OBJECT_NAME BLOCK_ID;
CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIPTION REPAIR_DESCRIPTION
——————————————————————————
DEPT 3 1                   FALSE
kdbchk: row locked by non-existent transaction
table=0 slot=0
lockid=32 ktbbhitc=1 FROM REPAIR_TABLE;

Note :  The repair show FALSE then it possible to fix the corruption, if it is true then not possible.

There is no fix happened and still the issue occurred. This means the DBMS will work for only minor table block corruption issues. SAP also not recommended to perform this activity.

Action Third method:-
Online reorg the table.  Useless waste of time, sometime the issue will fix if we done archiving for the table and then perform the online reorg.
Last but not least export &import data pump simple and powerful.
Action Fourth method:-
We have performed the table export and import method. It’s worked perfectly.
Export successfully.



Dropped the table.



Import the table and it successful.


We are now able to view the table “emp” with less row.  This means ORACLE omitted the corrupted the rows and headers. This happened while export the table got get compressed. But 100% data available.


Successfully fixed the block corruption!!!!!!!!!!!!! 

Now checking the Block corruption using the below query.

Now the table started view the data, but the corruption still shows.
We have fixed the block corruption issue for table “emp”, But , still the issue show the same table”emp”.
Because we have manually corrupted the tables pace “Test_Corrupt” by edit.
So performing the same step to fix the issue in tablespace level. (Export and Import data pump)



Drop the table space “TEST_CORRUPT”.



Import the table-space “TEST_CORRUPT”.




Now the tablespace block corruption issue has been fixed without issue. We have successfully bring back data as it is like before.
DB_BLOCK_CHECKSUM- Background
This parameter determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block and redo log when writing to disk. The checksum is used to validate that a block is not physically corrupt, detecting corruptions caused by underlying disks, storage systems, or I/O systems. If checksum validation fails when it is set to FULL, Oracle will attempt to recover the block by reading it from disk (or from another instance) and applying the redo needed to fix the block. Corruptions are recorded as ORA-600 or ORA-01578 in the database or ASM alert logs. 
RMAN: – using RMAN the block corruption can be fixed in on line and very simple.




Using RMAN Block corruption issue can be fixed during the system in fly condition.



Export and import table “BALDAT” in sap level environment with oracle database.





Drop the table “sapsr3.baldat”


Import the table “sapsr3.baldat”







The export and import done during the system in fly there is no downtime.
If you don’t have valid backup and it affecting the important business table.  Then need to contact SAP and ORACLE TEAM to fix the issue using Block Editor.  I have the knowledge on this but unable to share and confidential.   

Note: DBMS is not supported by SAP.
Reference Link:-

https://wiki.scn.sap.com/wiki/display/ORA/Oracles+Data+Pump+Utility+EXPDP+and+IMPDP