Friday, July 13, 2012

Useful Tips to Recover from Oracle Data Block Corruption and Prevent Data Loss

High availability of business-critical information is a key concern for all medium and large-scale enterprises. This information usually consists of company-sensitive data, application data, customer relationships, etc. Oracle's Maximum Availability Architecture (MMA) is specially designed to safeguard your crucial information and mitigate potential risks of database outages. Despite these best practices, users face crippling data loss due to intermittent cases of Oracle block corruptions. These often result from various software and hardware defects, causing damage to your database contents or the file header. To easily work around these problems and prevent data loss in such situations, you should perform Oracle recovery through advanced third-party software.

You may receive different types of errors after facing corruption in the data blocks. One common error message that indicates Oracle data block corruption is shown below:

ORA-01578: ORACLE data block corrupted (file # string, block # string)

Additionally, you may see the following error message recorded in your alert log:

Bad check value found during buffer read

This is a typical case of Oracle block corruption that happens when the checksum contained in the block header is different from the checksum computed during the block read. Checksums are used to determine the consistency of Oracle data blocks. They can help administrators to know whether an external source modified the Oracle data block.

You can check the validity of blocks in your datafile using the external utility ‘DBVERIFY’. This tool performs an integrity check on your offline datafiles. You can run the command as follows:

C:>dbv file=C:\Oracle\oradata\TSH1\system01.dbf feedback=100 blocksize=4096

Here, ‘C:\Oracle\oradata\TSH1\system01.dbf’ is the datafile to be verified. The ‘feedback’ parameter is used to display one period on the terminal to show the progress. In this case, a progress display will be send to the terminal after the processing of every 100 pages in the file. If your datafile does not contain blocks of 2 KB, you should provide the ‘blocksize’ parameter.

If you find block corruption in your database, run ‘DBMS_REPAIR’ utility to repair the corrupt blocks. If this utility fails to fix block corruption, you should take help of commercial third-party Oracle database repair software. These software use non-destructive algorithms to scan your damaged Oracle database and safely recover all lost or inaccessible objects, including tables, views, clusters, tablespaces, indexes, etc. They facilitate selective recovery of Oracle database components and allow saving the recovered file at your desired location in the system.

No comments:

Post a Comment