Detecting & Repairing Oracle Database Block Corruption

Database outages can be tremendously costly for data-driven organizations, bringing your business to a near standstill. One of the most common causes of database outages is data block corruption. In this article, we’ll discuss how you can detect and repair the problem of block corruption within your Oracle database.

What is a data block corruption?

Data block corruptions occur whenever data is not in its expected state. The block may have contents that are not internally consistent, or it may have been altered to be unrecognizable as part of the Oracle Database format.

Some data block corruptions are relatively minor, affecting only a single block. Others corrupt a significant portion of the database, rendering it unusable and leading to loss of critical information.

Why do data block corruptions occur?

Data block corruptions can occur any time that data is altered, and can be traced back to both hardware and software problems. Problems with any or all of the following components can result in data block corruptions:
 

• Hard drive
• Disk controller
• Operating system
• Storage area network (SAN)
• Storage device

How can you prevent data block corruptions?

Data block corruptions are difficult to prevent because they are inherently unpredictable. Indeed, corruptions are inevitable in today’s highly complex database environments.

However, you can still work to mitigate the effects of data block corruptions with a robust backup and recovery strategy. Oracle provides a variety of tools and solutions for these purposes, including:
 

• Oracle Data Guard: Protects enterprise databases in the event of disaster or corruption.
• Data Recovery Advisor: Diagnoses data loss or corruption and performs repairs.
• Oracle Flashback: Restores database tables or rows to an earlier state.
• Oracle Recovery Manager: Backs up and recovers Oracle databases.
• Oracle Secure Backup: Backs up file system data and Oracle Database files to tape.

How do you detect and monitor data block corruption?

Despite your best efforts, data block corruption can occur at any time. Fortunately, there are multiple ways that Oracle can detect and repair data block corruption:
 

• Oracle Recovery Manager (RMAN)
• DBVerify
• ANALYZE command
• DB_BLOCK_CHECKING parameter
• Block media recovery (BMR)
• DBMS_REPAIR

Oracle Recovery Manager (RMAN)

To check a database for physical and logical corruption, run the following command within Oracle RMAN:
 

• BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

 
The VALIDATE command in Oracle RMAN can be used to validate files, tablespaces, and even entire databases. For example:
 

• VALIDATE DATAFILE ‘/u01/oradata/ORCL/system01.dbf’;
• VALIDATE TABLESPACE users;
• VALIDATE DATABASE;

 
If Oracle RMAN detects any data block corruptions, they will be shown in the V$DATABASE_BLOCK_CORRUPTION view.

DBVerify

DBVerify is an external command-line utility for validating online and offline databases and files, including backups. The example below demonstrates the use of the dbv command:
 

• dbv file=/u01/oradata/ORACL/system01.dbf feedback=10000 blocksize=8192

 
Note that you cannot use DBVerify for Oracle control files or redo logs.

ANALYZE command

You can use the ANALYZE… VALIDATE STRUCTURE construction to verify the data blocks in an object. Once the analysis is complete, check the INVALID_ROWS table to see the corrupted data.

To first create the INVALID_ROWS table, run the following command within SQL:
 

• @$ORACLE_HOME/rdbms/admin/UTLVALID.SQL

 
Second, validate the table and index structure with the following command:
 

• ANALYZE TABLE scott.emp VALIDATE STRUCTURE CASCADE;

DB_BLOCK_CHECKING parameter

By setting the DB_BLOCK_CHECKING parameter to [TRUE|HIGH], you can have Oracle check database blocks for self-consistency. Note that this may add up to 10 percent overhead to the server, so only use this option if performance is not of great importance.

Block media recovery (BMR)

Block media recovery (BMR) lets you recover one or more data blocks without affecting the file as a whole. As a result, you’ll experience a lower mean time to repair (MTTR) and enjoy higher availability, since you only need to take the affected blocks offline.

This tactic is useful when only a limited number of data blocks have been corrupted. You can perform BMR only within Oracle RMAN using the BLOCKRECOVER command.

DBMS_REPAIR

Oracle’s DBMS_REPAIR package can both detect and repair corruption. To do so, you first need to create two administration tables. These tables will contain a list of corrupt blocks, and the index keys pointing to those blocks:

 
BEGIN

DBMS_REPAIR.admin_tables (

table_name => ‘REPAIR_TABLE’,

table_type => DBMS_REPAIR.repair_table,

action     => DBMS_REPAIR.create_action,

tablespace => ‘USERS’);

DBMS_REPAIR.admin_tables (

table_name => ‘ORPHAN_KEY_TABLE’,

table_type => DBMS_REPAIR.orphan_table,

action     => DBMS_REPAIR.create_action,

tablespace => ‘USERS’);

END;

/

The second step is to use the CHECK_OBJECT procedure to examine the table:

 
SET SERVEROUTPUT ON

DECLARE

v_num_corrupt INT;

BEGIN

v_num_corrupt := 0;

DBMS_REPAIR.check_object (

schema_name       => ‘SCOTT’,

object_name       => ‘DEPT’,

repair_table_name => ‘REPAIR_TABLE’,

corrupt_count     =>  v_num_corrupt);

DBMS_OUTPUT.put_line(‘number corrupt: ‘ || TO_CHAR (v_num_corrupt));

END;

/

Next, use the FIX_CORRUPT_BLOCKS procedure to mark the corrupted blocks:

 
SET SERVEROUTPUT ON

DECLARE

v_num_fix INT;

BEGIN

v_num_fix := 0;

DBMS_REPAIR.fix_corrupt_blocks (

schema_name       => ‘SCOTT’,

object_name       => ‘DEPT’,

object_type       => Dbms_Repair.table_object,

repair_table_name => ‘REPAIR_TABLE’,

fix_count         => v_num_fix);

DBMS_OUTPUT.put_line(‘num fix: ‘ || TO_CHAR(v_num_fix));

END;

/

The next step is to check for orphan keys, which are key entries that point to a corrupt data block:

 
SET SERVEROUTPUT ON

DECLARE

v_num_orphans INT;

BEGIN

v_num_orphans := 0;

DBMS_REPAIR.dump_orphan_keys (

schema_name       => ‘SCOTT’,

object_name       => ‘PK_DEPT’,

object_type       => DBMS_REPAIR.index_object,

repair_table_name => ‘REPAIR_TABLE’,

orphan_table_name => ‘ORPHAN_KEY_TABLE’,

key_count         => v_num_orphans);

DBMS_OUTPUT.put_line(‘orphan key count: ‘ || TO_CHAR(v_num_orphans));

END;

/

If there are 1 or more orphan keys, then you need to rebuild the database index with the REBUILD_FREELISTS procedure:

BEGIN

DBMS_REPAIR.rebuild_freelists (

schema_name => ‘SCOTT’,

object_name => ‘DEPT’,

object_type => DBMS_REPAIR.table_object);

END;

/

Finally, use the SKIP_CORRUPT_BLOCKS procedure to ensure that the corrupt blocks are skipped during queries:

BEGIN

DBMS_REPAIR.skip_corrupt_blocks (

schema_name => ‘SCOTT’,

object_name => ‘DEPT’,

object_type => DBMS_REPAIR.table_object,

flags       => DBMS_REPAIR.skip_flag);

END;

/

Conclusion

While data block corruption is an unfortunate fact of life, Oracle has a mature, wide-ranging suite of tools to protect and recover your enterprise data. Using software such as Oracle Data Guard and Oracle RMAN, as well as performing regular corruption checks, will help you counteract the issue of data block corruption.