Types of corruption in Oracle and how to avoid them

Corruption in Oracle mostly occurs in data block on hard disk or in memory. It is easy to handle memory corruption using Oracle Kernel detection mechanism and to handle block corruption Oracle provides various methods. A data block is said to be corrupted when it is not in proper format. Block corruption can damage a single block or your whole database which can lead to loss of data. The most common reasons behind block corruption are creepy hardware, OS bug or faulty disk.

How corruption happens?

Oracle write operation goes through various I/O devices like file system, volume manager, device manager and so on. When a bug or hardware failure occurs in any of these devices then incorrect or corrupt data is written to disk.

Types of corruption

1. Physical corruption: It happens when there is an invalid checksum or when a block consists of all zeros. In this case the data block is considered as invalid block.
2. Logical corruption: It happens when the content of data block are not logically consistent.

Ways to avoid corruption

Oracle Data Guard: Oracle Data Guard is the solution to data loss problem. It is the high availability solution recommended by Oracle MAA (Maximum Availability Architecture) to prevent data loss. This high availability solution also protects Oracle data from disaster, failures and corruption. Oracle Data Guard protects database by creating one or more standby database. Standby database contain copy of your primary database which is updated continuously whenever there is change in primary database. It also validates all the changes before applying to standby database thereby preventing corruption.

To detect block corruption you need to set the Oracle Database block corruption parameters

Set following parameters for primary database and standby database

DB_BLOCK_CHECKSUM=FULL

DB_BLOCK_CHECKSUM detects corruption caused by I/O or disk. When you set its value to FULL then it will be able to detect memory corruption also and prevent them from being written to the disk. Setting the parameter to FULL may cause 4 to 5 % performance overhead.

DB_BLOCK_CHECKING= FULL or MEDIUM

It checks the logical consistency of data in the block and also prevent data and memory corruptions. The performance overhead depends on the parameter value that you set.

DB_LOST_WRITE_PROTECT= TYPICAL

It facilitates lost write detection. Lost write occurs when I/O confirms the completion of block write while in real this operation never occurred.

RMAN backup strategy

A good backup strategy is necessary for data protection. RMAN is considered as best backup solution for Oracle database. The best thing about RMAN is that it knows which files needed a backup and recovery. It also detects block corruption while performing backup.

Data Recovery Advisor: Data Recovery Advisor automatically detects data failure and also provides appropriate repair options. It can be used to troubleshoot primary database, standby database and snapshot database. This tool repairs block corruption, data dictionary corruption and undo corruption.

What if corruption still occurs?

Above methods help in preventing Oracle database from corruption but they do not guarantee that corruption will never happen. If corruption happens then you can repair your database by a professional third party Oracle recovery software. There are many third party tools available online that efficiently repair corrupt database. These softwares are read only in nature and do not harm your system. Moreover they also provide free demo of software so that you can see the preview of your corrupt database. If you are satisfied with the demo then you can go for full version of the software.