Abdul Hafeez Kalsekar -- Oracle database tuning case studies

    Since we had many  corruption issues reported  thought of keeping this handy . In this blog we will try to  Oracle database  corruption insights  We will Try to cover below topics  1) Type of Block Corruption  2) How to detect Block Corruption and How to Fix  Block Corruption  3) Options to prevent Block Corruption  4)  How to manually corrupt Database Block for testing  5)  What caused block corruption    ############################################# Type of Block Corruption  ############################################# 1) Physical  Block Corruption  2) Logical  Block Corruption  PHYSICAL CORRUPTION Also called media corruption Inconsistency between header and footer is one of the symptom of physical corruption. There can be an invalid checksum or header, or when the block contains all zeroes. Generally the result of infrastructure problems like OS/Storage issues, faulty d...

   prompt** /* Current Active sql   */ -- active  col sql_id format a20 SELECT     s.sql_id,     ss.elapsed_time / 1000000 AS elapsed_seconds,     ss.cpu_time / 1000000 AS cpu_seconds,     ss.disk_reads,     ss.buffer_gets,     ss.executions,     ROUND(ss.elapsed_time / 1000000 / NULLIF(ss.executions, 0), 4) AS avg_elapsed_seconds,     ROUND(ss.cpu_time / 1000000 / NULLIF(ss.executions, 0), 4) AS avg_cpu_seconds FROM     v$sql s      JOIN v$sqlstats ss ON s.sql_id = ss.sql_id WHERE     ss.elapsed_time > 1000000 -- Adjust the threshold as needed (microseconds)   and s.sql_id in ( select sql_id from v$session where status='ACTIVE')  ORDER BY     ss.elapsed_time DESC; set line 200 col username for a20 select * from (   select a.sid , a.username  , round ( ( b.consistent_gets+b.block_gets-b.physical_reads) / ( b....