Database Corruption

There are two instance parameters that will assist in detecting corrupted blocks

Both db_block_checksum (set to true) and db_block_checking (set to low) for the system tablespace.

db_block_checksum

alter system set db_block_checksum = typical;
alter system set db_block_checksum = full;

Notes: additional overhead will be occurred
typical - 1-2% increase
full - 4-5% increase

db_block_checking

alter system set db_block_checking = off;
alter system set db_block_checking = low;
alter system set db_block_checking = medium;
alter system set db_block_checking = full;

alter system set db_block_checking = false;    /* same as off */
alter system set db_block_checking = true;     /* same as full */

Note: additional overhead will be occurred
low - 1-3% increase
medium - 3-6% increase
full - 6-10% increase

Normally you are informed by a corrupted block from a user trying to use the block, an alert will be sent to the alert log. An ORA-01578 indicated a corrupted block and trace files will be created, use the DBA_EXTENTS table to match up the corrupted block to an object:

Example

select owner, segment_name, segment_type from dba_extents where file_id=7 and
   5 between block_id and block_id + blocks-1;

select segment_name, file_id, block_id from dba_extents where owner = 'TEST01';

If a table has corrupted blocks but the primary key index does not, index searches may succeed depending on what columns/rows are selected, as long as the index that has the information to complete the query and not does not read the corrupted blocks.

DBverify

dbverify is a oracle binary that can detect corrupted blocks in a datafile, it is used when running user managed backups, rman performs its own verification. dbverify checks all blocks including the blocks above the high watermark. dbverify can check offline/online datafiles and data file image copies but nothing else. dbverify is used at the operating system level it checks the structural integrity of the datafiles.

help page c:\>dbv help=y
test datafile

c:\>dbv file=test01.dbf

Note: you are looking for failing pages (blocks), an influx block is one that DBWn was writing while dbverify was trying to verify it, this is not an error and dbverify will try again until it gets consistent read.

test datafile with specific blocksize c:\>dbv blocksize=8192 file=test01.dbf logfile=c:\temp\dbv_010108.log

you can also use the analyze command to check for table or index corruption, however the analyze command will only check for corruption below the high watermark, so will not check unused space, it does not identify the corrupt block but gives a ORA-01498 error.

table only analyze table <table_name> validate structure;
index only analyze index <table_name> validate structure;
table and associated indexes analyze table <table_name> validate structure cascade;

dbms_repair

Is a set of procedures that will check objects for problems and make the object useable, note that it cannot repair the data (you are required to recover it). You must create a table that dbms_repair uses to store its output.

There are 4 procedures that will be used

create the admin table

exec dbms_repair.admin_tables ( table_name => 'REPAIR_TABLE', table_type => dbms_repair.repair_table, action => dbms_repair.create_action, tablespace => 'TEST');

select owner, object_name, object_type from dba_objects where object_name like ‘%REPAIR_TABLE’;

Note: The table REPAIR_CORRUPT_TAB will store details of any problems encountered when checking a table (must have prefix REPAIR_).

check the object

declare
num_corrupt int;
begin
  num_corrupt := 0;
  exec dbms_repair.check_object(schema_name =>'TEST03',object_name      =>'TEST03',repair_table_name =>'REPAIR_TABLE',corrupt_count => num_corrupt);
end;
/

select object_name, block_id, corrupt_type, marked_corrupt, corrupt_description, repair_description from repair_table;

Note: If no rows exist in the REPAIR_TABLE then the table is ok. Referential integrity constraints on related tables can be broken, indexes can become out of sync with the table data and triggers on a table can cause logical corruption when using the DBMS_REPAIR package.

mark the block as corrupt

declare fix_block_count int;
begin
  fix_block_count := 0;
  dbms_repair.fix_corrupt_blocks(schema_name =>'TEST03',object_name =>'TEST03',      object_type => dbms_repair.table_ojbects, repair_table_name => ‘REPAIR_TABLE’,      fix_count => fix_block_count);

select object_name, block_id, marked_corrupt from repair_table;

skip the corrupted blocks exec dbms_repair.skip_corrupt_blocks(schema_name =>'TEST03',object_name =>'TEST03', object_type => dbms_repair.table_ojbects, flags => dbm_repair.noskip_flag);

Recover corrupted block with RMAN

if you are using user-managed backups then restore the file in the normal way

RMAN can be set that it can either abort a backup when corruption is detected or specify a tolerance recording any corruption with the repository. By default RMAN only checks for physical corruptions (disk problem) not logical (software corruption disk is ok but oracle does not understand the block).

BMR (block media recovery) can only be performed by RMAN and recovery either completes or fails (no incomplete recovery). It can only recover datafiles as it uses the redo log to recover. If the MAXCORRUPT setting has been set then RMAN backup will continue until this threshold, otherwise it will fail. If recovering a table while the database is up, indexes searches may succeed depending on what columns/rows are selected, if a user hits a block that is being recovered it will report an ORA-01578 error.

set corruption threshold

rman> run {
   set maxcorrupt for datafile 7 to 100;
   backup datafile 7;
}

turn off block checking rman> backup nochecksum datafile 7;
check for logical corruption rman> backup check logical datafile 7;
display corruption select * from v$database_block_corruption;
display corruption in backup set select * from v$backup_corruption;
display corruption in image copy select * from v$copy_corruption;

To recover a corrupted block using RMAN

recover one block rman> blockrecover datafile 7 block 5;
recover two datafiles and a number of blocks rman> blockrecover datafile 7 block 5,6,7 datafile 9 block 21,25;
use a specified backup

rman> blockrecover datafile 7 block 5 from backupset 1093;
rman> blockrecover datafile 7 block 5 from tag monthly_whole_backup;

recover blocks using backups made a week ago rman> blockrecover corruption list until time sysdate – 7;

Dump a block of data

find the block data select header_file, header_block from dba_segments where segment_name = 'TEST03';
dump the block data

alter system dump datafile 6 block 75;

Note: the information will be in a trace file in the user dump directory (udump)

Using dumped info comfirm its the right object

select name from sys.obj$ where obj#='52716';

Note: object number objected from dumped information in trace file

Dump the tree of an index

object object ID select object_id from user_objects where object_type = 'INDEX' & object_name = 'TEMP_INDEX';
dump the tree

alter session set events 'immediate trace name treedump level <object_id>';

Note: object ID would have been obtained above, the information again is dumped into the user dump area in a trace file