
In the middle of the night, you will receive a call from the users. They receive an error, when they query product_master. 
SALES> select * from product_master;
select * from product_master
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 50)
ORA-01110: data file 8: '/home/oradata/sales/tblspc01.dbf' 
This script proactively finds out the corruptions, so that DBAs are alerted and corrective action can be taken, before users actually report the error. Recovery methods include, BLOCKRECOVER or complete recovery depending on the extent of the problem. 


#!/bin/ksh 
#******************************************************************
# File Name    - alert_corruption.ksh
# Description  - This script alerts DBAs of corruptions in datafile
#        backups from the control file, before users report the error,
#        ORA-01578: ORACLE data block corrupted (file # , block # )
# Author       - Krishna Kakatur
# Date Written - 11th Dec 2002
#****************************************************************** 
EMAIL_ADDR="dbagroup@mycompany.com"
SPOOL_FILE="spool.tmp"
ORA_USER="system"
ORA_PASS="manager"
DB_NAME="SALES" 
sqlplus -s $ORA_USER/$ORA_PASS@$DB_NAME > $SPOOL_FILE << EOF
   set pages 0
   set feedback off
   -- Read from v$backup_corruption 
   SELECT distinct 'Data Block# '|| block# || ' of Data File '
          || name || ' is corrupted.'
   FROM v$backup_corruption a, v$datafile b
   WHERE a.file# = b.file#; 
   -- Read from v$copy_corruption 
   SELECT distinct 'Data Block# '|| block# || ' of Data File '
          || name || ' is corrupted.'
   FROM v$copy_corruption a, v$datafile b
   WHERE a.file# = b.file#;
EOF 
# Send an Email, if any corruptions are found
v_corruptions="`cat $TMP_FILE | wc -l`"
if [ $v_corruptions -ne 0 ]; then
   cat $SPOOL_FILE | /usr/ucb/mail -s "Data Block Corruptions in $DB_NAME" $EMAIL_ADDR
fi
