.../Updation in Progress
Block Corruption : Block inconsistent or Oracle is not able to recognize the block Whenever data from block is read / written to a consistency check is performed, additionally cheksum check is performed when enabled (by default it is enabled for system tablespace) Two Types of Corruption :
Logical : Internal error of oracle software corrupts the block Physical : Media underlying is corrupt, check for memory,paging space, i/o controllers Error is generated when corrupted block is found and logged in alert.log
Absolute file number and block number are also logged Common ora error for corruption ORA-01578 Tools to check for corruption
RMAN does not detect logical corruption by default, you have to
give the parameter for doing that RMAN> backup validate check logical database; \\ For checking whole db
RMAN>backup validate check logical datafile 1; \\ For checking particular file It does not backup the database, just check for corruption
feeds that information into the table v$database_block_corruption
Pull the data from the table
select * from v$database_block_corruption;
File# : File id of the datafile containing the corrupted block dbverify : External command utility that is used to check
data structure integrity of a datafile, these could be run againts online as well as offline datafile syntax
#dbv help=y #dbv file='/path/to/datafile' blocksize=<block size> analyze : Perform logical check for block corruption / validate index
and table entries/Updates statistics select * from table, is normally used to perform quick check for corruptions in a specific table data for table import the data from dr,exp backup for index create the index again after dropping for any other object ,drop and re-create To restore object in corrupted objects file object in corrupted blocks select segment_name,segment_type,relative_fno from dba_extents where file_id=<file_id> and <block_id> between block_id and block_id + blocks -1; bb_block_checking dbms_repair package ================================== Database Corruption DataBlock Corruption : Two types of block corruption are observed Software or Logical Corruption : Error in Oracle Software corrupts the the block logically Media or Physical Corruption : If the block is media corrupt the block is physically corrupted For an Media Corrupt Block the information in itis rendered useless after the read operation from the disk is performed, when an inconsistency is detected the oracle database marks the software-corrupt blocs as "corrupt" ORA-01578 errror, is well known error for Corrupted Blocks due to Hardware Problem. Whenever such corrupted datablocks are found the error gets logged in alert.log file, the error also accompanies the file number and the block number that caused the error = If ORA-01578 error appears repeatedly with identical arguments it is most likely a media-corrupt block. However if the argument vary each time there may be a hardware problem. == DBVERIFY Utility is a utility that detects database block corruption. An offline database file could also be tested for structural integrity by this utility This utility checks for block consistency after the utility is executed we get a report specifying total blocks scanned the number of blocks failed There is one columns for datablocks in flux Flux is a process when blocks are read and written at the same time and the dbverify utility fails to get a consistent image of the blocks. Normally dbverify utility is used to ensure that a backup of database or specific datafile is valid before it is restored. For an offline database the integrity checks are faster compared to online database == ==Analyze : Analyze command performs logical check for block corruptions ,validate index and table entries, also it populates table and index statistics. Analyze command is used to validate the structure of a table ,a table partition, an index or an index partition. Command : ANALYZE TABLE <table_name> VALIDATE STRUCTURE CASCADE; ANALYZE TABLE <index_name> VALIDATE STRUCTURE CASCADE; When there is media corruption Analyze command only reports the block and does not mark it as soft corrupt = you could also use select * from <table_name> to perform quick check for corruptions in your table == DB_BLOCK_CHECKING : ALTER SYSTEM set DB_BLOCK_CHECKING = OFF | LOW | MEDIUM | FULL OFF : No Block Checking is performed for blocks in the user tablespaces, block
checking for SYSTEM tablespace blocks is always turned on
LOW : Block header checks are performed after block content changes in memory MEDIUM : All LOW checks are performed,not the index-organized table blocks FULL : All LOW checks are performed,including the index blocks ( FULL=TRUE and,FALSE=OFF , For backward compatibility ) DB_BLOCK_CHECKSUM : ALTER SYSTEM set DB_BLOCK_CHECKSUM = OFF | TYPICAL | FULL Checksum is stored for every block and is verified every time block is read. dbms_repair package : Detect and repair corrupt blocks in tables and indexes. While the object is online Various Procedure in DBMS_REPAIR Packaged CHECK_OBJECT Detects and reports corruption in table or index FIX_CORRUPT_BLOCK Marks the corrupted blocks as software corrupt DUMP_ORPHAN_KEYS REBUILD_FREELISTS SEGMENT_FIX_STATUS SKIP_CORRUPT_BLOCKS : Ignores the blocks that are marked as Corrupt during table and index scans ADMIN_TABLES BMR : Block media recovery Using RMAN's BLOCKRECOVER command you could recover a corrupt block based on the time,scn or log sequence BLOCKRECOVER DATAFILE 4 BLOCK 44,66,55; = solarins pkg addition pkgadd -d . SUNWarc first go to the folder ./Product |
Database Technologies > Oracle >