Data Block Corruption In Oracle

November 12, 2016 by S4

Filed under Oracle

Last modified November 12, 2016

Data Block Corruption In Oracle

Case Study :- Once I faced the Data block corruption problem on DishTv site in one of our Oracle Database server. The corrupted block was referring to a table. We tried everything like rebuilding index, correcting the data corruption etc but finally it was detected as a physical data corruption and hence we raised the call with Os vendor. We have this Database Server installed on Sun solaris. Now, that problem has been permanently resolved but still i would like to share some of the exercise which i did to temporarily resolve this issue.

I hope you all will find this a very knowledgeable document especially if you are not an Oracle DBA like me.

Problem: the application encounters an ORA-01578 runtime error because there are one or more corrupt blocks in a table it is reading.

How can corrupt blocks be caused?

First of all we have two different kinds of block corruption:
– physical corruption (media corrupt)
– logical corruption (soft corrupt)
Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk;
Logical corruption can among other reasons be caused by an attempt to recover through a NOLOGGING action.
There are two initialization parameters for dealing with block corruption:
– DB_BOCK_CHECKSUM (calculates a checksum for each block before it is written to disk, every time)
causes 1-2% performance overhead
– DB_BLOCK_CHECKING (server process checks block for internal consistency after every DML)
causes 1-10% performance overhead
If performance is not a big issue then you should use these!

Normally RMAN checks only for physically corrupt blocks
with every backup it takes and every image copy it makes.
This is a common misunderstanding among a lot of DBAs.
RMAN does not automatically detect logical corruption by default!
We have to tell it to do so by using CHECK LOGICAL!
The info about corruptions can be found in the following views:

SYS @ orcl AS SYSDBA SQL > select * from v$backup_corruption;
 

 

Steps to Correct corrupted blocks problem

Once one or more corrupted blocks are detected, the DBA must resolve the issue. Below are some options available to the DBA to address block corruption:

• Drop and re-create the corrupted object – If the loss of data is not an issue, this is the preferred approach. For Data Warehouses, the data can be reloaded from external sources and the loss of data is minor. However, for OLTP tables (customer_orders), no data can be lost without a serious negative impact on the business.

If the object is an index, rebuild it. If a few blocks are corrupt, determine which object(s) are causing the corruption. This can be done in the query below by mapping the physical file location to an object(s) contained in the file.

select tablespace_name, segment_type, owner,
segment_name
from dba_extents
where file_id = <corrupted file id>
and <Block #> between block_id AND block_id + blocks-1;

• Restore the file from a backup – The tried and true method for restoring good blocks back into the datafiles.

• Use dbms_repair – Dealing with block corruption is always a risky proposition so limit the use of dbms_repair to extreme situations. dbms_repair is a package supplied by Oracle that identifies and repairs block corruption (described in next section).

If the first two options are unacceptable, using dbms_repair can resolve some block corruption issues.

dbv is a useful utility to inspect datafiles for block corruption. It should be used primarily against offline datafiles on a regular basis. In should be used in combination with other corruption detection mechanisms, including the analyze table command and init.ora parameters.

For online checking, the configuration parameter db_block_checking should be enabled, provided the overhead incurred on the database is at an acceptable level. Finally, when corrupted blocks are detected, the DBA should choose the most appropriate method of recovery – be it a restore, a rebuild of the object, or utilizing the dbms_repair utility
Dbms_Repair Details

dbms_repair is a utility that can detect and repair block corruption within Oracle.  It is provided by Oracle as part of the standard database installation.

Configuring the Environment

Two tables must first be created under the SYS schema before the dbms_repair utility can be used.  Fortunately, a procedure in the package itself (admin_tables) creates these tables and eliminates the need to hunt for a script in $ORACLE_HOME/rdbms/admin.

dbms_repair.ADMIN_TABLES (

table_name  IN   VARCHAR2,

table_type  IN   BINARY_INTEGER,

action      IN   BINARY_INTEGER,

tablespace  IN   VARCHAR2        DEFAULT NULL);

 

 

  • table_name – The name of the table to be processed, as determined by the action.
  • table_type – Either orphan_table or repair_table.
  • action – Either create_action, purge_action or drop_action. When create_action is specified, the table will be created in the SYS schema.  purge_action deletes all rows in the table that apply to objects that no longer exist.  drop_action will drop the table.
  • tablespace – The tablespace in which the newly created table will reside. This tablespace must already exist.

The following command will be used to create the two tables needed.  The command will be executed twice with different parameters, once for the repair table and once for the orphan table.

begin

dbms_repair.admin_tables(table_name => ‘REPAIR_TEST’,

table_type => dbms_repair.repair_table,

action     => dbms_repair.create_action,

tablespace => ‘SCOTTWORK’

);

end;

 

begin

dbms_repair.admin_tables(

table_name => ‘ORPHAN_TEST’,

table_type => dbms_repair.orphan_table,

action     => dbms_repair.create_action,

tablespace => ‘SCOTTWORK’

);

end;

 

Repair tables will contain those objects that have corrupted blocks.  Orphan tables, on the other hand, are used to contain indexes that point to corrupted data

Finding Corrupt Blocks

The dbms_repair utility provides a mechanism to search for corrupt database blocks.

Below is the syntax for the check_objects procedure.  Note that the only OUT parameter is the corrupt_count.

dbms_repair.CHECK_OBJECT (

schema_name       IN  VARCHAR2,

object_name       IN  VARCHAR2,

partition_name    IN  VARCHAR2       DEFAULT NULL,

object_type       IN  BINARY_INTEGER DEFAULT TABLE_OBJECT,

repair_table_name IN  VARCHAR2       DEFAULT ‘REPAIR_TABLE’,

flags             IN  BINARY_INTEGER DEFAULT NULL,

relative_fno      IN  BINARY_INTEGER DEFAULT NULL,

block_start       IN  BINARY_INTEGER DEFAULT NULL,

block_end         IN  BINARY_INTEGER DEFAULT NULL,

corrupt_count     OUT BINARY_INTEGER);

 

 

  • schema_name – Schema name of the object to be checked for corruption.
  • object_name – Name of the table or index that will be checked for corruption.
  • partition_name – Partition or sub-partition name to be checked.
  • object_type – Either TABLE_OBJECT or INDEX_OBJECT as specified as an enumeration (dbms_repair.table_object).
  • repair_table_name – The name of the repair table to be populated in the SYS schema.
  • flags – Not used.
  • relative_fno – The relative file number to be used when specifying a block range to be checked.
  • block_start – The first block in the block range to begin checking.
  • block_end – The last block in the block range to check.
  • corrupt_count – The number of corrupt blocks discovered.

 

The code below will check the scott.employee table for corruption and report the number of corrupted blocks.

 

 

set serveroutput on

declare corr_count binary_integer;

begin

corr_count := 0;

dbms_repair.CHECK_OBJECT (

schema_name       => ‘SCOTT’,

object_name       => ‘EMPLOYEE’,

partition_name    => null,

object_type       => dbms_repair.table_object,

repair_table_name => ‘REPAIR_TEST’,

flags             => null,

relative_fno      => null,

block_start       => null,

block_end         => null,

corrupt_count     => corr_count

);

dbms_output.put_line(to_char(corr_count));

end;

/

 

# Corrupt Blocks =0

 

 

Once executed, the table repair_test can be queried in order to find out more about corrupt blocks.  In this case, no rows exist in the table.  The repair table is only populated if the check_object procedure did indeed find corrupt blocks, so no rows in this table is good news!

 

Repairing Corrupt Blocks

 

The dbms_repair utility provides a mechanism to repair the corrupt database blocks, the fix_corrupt_blocks procedure.  Corrupt blocks are not really repaired, but instead are simply marked as corrupt.

 

Below is the syntax for the fix_corrupt_blocks procedure.  Note that the only OUT parameter is the fix_count.

 

dbms_repair.FIX_CORRUPT_BLOCKS (

schema_name       IN  VARCHAR2,

object_name       IN  VARCHAR2,

partition_name    IN  VARCHAR2       DEFAULT NULL,

object_type       IN  BINARY_INTEGER DEFAULT TABLE_OBJECT,

repair_table_name IN  VARCHAR2       DEFAULT ‘REPAIR_TABLE’,

flags             IN  BINARY_INTEGER DEFAULT NULL,

fix_count         OUT BINARY_INTEGER);

 

  • schema_name – The name of the schema containing the object with corrupt blocks.
  • object_name – The name of the object needing repair.
  • partition_name – The name of the partition or subpartition to process. If none is specified and the object is partitioned, all partitions will be processed.
  • object_type – Either table_object or index_object as specified as an enumeration.
  • repair_table_name – The name of the repair table.
  • flags – Not used.
  • fix_count – The number of blocks fixed. This should equal the same number of corrupt blocks reported.

If the object repaired is a table, then any corresponding index also needs to be fixed.  The dump_orphan_keys procedure will indicate if any keys are broken.  If they are, the index will need to be rebuilt.

 

Rebuilding Freelists

 

The dbms_repair utility provides a mechanism to rebuild the impacted freelists and bitmap entries after fixing block corruption.  This procedure recreates the header portion of the datafile, allowing Oracle to use the newly repaired blocks.

 

Below is the syntax for the rebuild_freelists procedure:

 

dbms_repair.REBUILD_FREELISTS (

schema_name    IN VARCHAR2,

partition_name IN VARCHAR2       DEFAULT NULL,

object_type    IN BINARY_INTEGER DEFAULT TABLE_OBJECT);

 

  • schema_name – The name of the schema containing the object whose freelists need rebuilding.
  • partition_name – The name of the partition or subpartition whose freelists are to be rebuilt.
  • object_type – Either TABLE_OBJECT or INDEX_OBJECT as specified as an enumeration.

dbms_repair provides a new method of addressing ORA-600 errors dealing with block corruption.   The utility is very easy to use and very functional.  As described earlier, it is one of many potential solutions when resolving block corruption.  dbms_repair does  basically the same thing as analyze table … validate structure.

Leave a Comment