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; Notes: additional overhead will be occurred |
db_block_checking | alter system set db_block_checking = off; alter system set db_block_checking = false; /* same as off */ |
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 |
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 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; 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 { |
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; |
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 |