LATEST TOPICS

Script to Introduce Manual Block Corruption in Oracle Database

A block corruption in a database is a rare and unwanted situation. However, for testing corruption recovery scenario a DBA needs to have a actual corruption at the block level.

I have written a wrapper script that can be used to introduce artificial block corruption in a Oracle Database for the purpose of testing block media recovery scenarios.

Few words of Caution:


Do Not execute the script on a Production Environment
Ensure that you have valid database backup available before simulating block corruption

#!/bin/sh
######################################################################
# Script : manual_block_corrupt.sh
# Author : Abu Fazal Mohammed Abbas
# Version : 1.2
# Description : Script to induce artificial block corruption
######################################################################

clear
# Check existance of ORATAB
if [ -f /etc/oratab ] ; then
        OTAB="/etc/oratab"
elif [ -f /var/opt/oracle/oratab ] ; then
        OTAB="/var/opt/oracle/oratab"
else
        echo "Could not locate ORATAB. Terminating program"
        exit 1
fi

# Prompt the list of Running databases
echo "Script to introduce artificial database block corruption"
echo "Developed By : Abu Fazal Abbas"
echo
echo "Select database from the list"
echo `ps -ef | grep pmon | grep -v ASM | grep -v grep | awk '{print $NF}' | cut -c 10-`
echo
echo -e "Enter your choice:\c "
read dbname

# Set Oracle Database environment
set_env()
{
ORACLE_SID=$dbname ; export ORACLE_SID
ORAENV_ASK=1; export ORAENV_ASK
ORACLE_HOME=`cat $OTAB | grep -v "^#" | grep "^[A-Za-z,+]" | grep -w $ORACLE_SID | awk -F: '{print $2}'`
export ORACLE_HOME
PATH=${ORACLE_HOME}/bin:${PATH}; export PATH
if [ "x$LD_LIBRARY_PATH" != "x" ]
then
       LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${ORACLE_HOME}/lib64:${LD_LIBRARY_PATH}; export LD_LIBRARY_PATH
fi
}


# Unset Oracle Database environment
unset_env()
{
PATH=`echo $PATH | sed "s;:$ORACLE_HOME/bin;;g"`; export PATH
if [ "x$LD_LIBRARY_PATH" != "x" ]
then
        LD_LIBRARY_PATH=`echo $LD_LIBRARY_PATH | sed "s;:$ORACLE_HOME/lib;;g"`; export LD_LIBRARY_PATH
        LD_LIBRARY_PATH=`echo $LD_LIBRARY_PATH | sed "s;:$ORACLE_HOME/lib64;;g"`; export LD_LIBRARY_PATH
fi
}

# Corrupt Block module
corrupt_database()
{
echo
echo -e "Introduce artificial corruption[Y/N]: \c"
read opt
case $opt in
y|Y)
     echo
     echo "Corrupting block $header_b, `expr $header_b + 1`, `expr $header_b + 2` in '$cdf'"
     dd if=/dev/zero of=$cdf bs=$bsize conv=notrunc seek=$header_b count=3
     echo "Validating Block corruption from database. Please wait"
     $ORACLE_HOME/bin/rman << EOF
     connect target /
     backup validate datafile '$cdf';
     exit
EOF
     $ORACLE_HOME/bin/sqlplus -S << EOF > /tmp/chk_bc.log
     / as sysdba
     select * from v\$database_block_corruption;
     exit;
EOF
     cat /tmp/chk_bc.log
     rm -f /tmp/chk_bc.log
     ;;
n|N)
     echo
     echo "You have decided, not to proceed. Terminating Program"
     unset_env
     exit 1
     ;;
*)
     echo
     echo "Invalid Option. Terminating Program"
     unset_env
     exit 1
     ;;
esac
}


# Main Program
if [ "`ps -ef | grep pmon | grep -v ASM | grep -v grep | awk '{print $NF}' | cut -c 10- | grep -w $dbname`" = "" ]; then
        echo "Invalid Database Name.. Terminating Program"
        exit 1
else
        echo "1. Corrupt a Datafile"
        echo "2. Corrupt a Table"
        echo -e "Select your option: \c"
        read c_choice

        if [ $c_choice -eq 1 ]; then
        set_env
        echo
        echo "Scanning datafiles for $ORACLE_SID. Please wait..."
        echo
        df_sql="select name from v\$datafile;"
        df_list=`$ORACLE_HOME/bin/sqlplus -S << EOF
        / as sysdba
        set feedback off head off
        $df_sql
        exit
EOF`
        echo "Datafile"
        echo "------------------------------------------------------------------"
        for df in $df_list
        do
        echo $df
        done
        echo
        echo -e "Select the datafile for artificial corruption: \c"
        read cdf

        if [ "`echo $df_list | grep -w $cdf`" = "" ]; then
                echo "Invalid datafile selection. Terminating Program..."
                unset_env
                exit 1
        else
                echo
                echo "Scanning datafile segments to find a header block"
                header_sql="select header_block from dba_segments where header_file=(select file# from v\$datafile where name='$cdf') and rownum <2;"
                header_b=`$ORACLE_HOME/bin/sqlplus -S << EOF
                / as sysdba
                set feed off head off
                $header_sql
                exit
EOF`
                header_b=`echo $header_b | tr -d " "`
                if [ "$header_b" = "" ]; then
                        echo
                        echo "Datafile '$cdf' has no segments for corruption. Terminating program..."
                        unset_env
                        exit 1
                else
                        echo
                        echo "Retrieving datafile block size for '$cdf'"
                        bsize_sql="select block_size from v\$datafile where name='$cdf';"
                        bsize=`$ORACLE_HOME/bin/sqlplus -S << EOF
                        / as sysdba
                        set feed off head off
                        $bsize_sql
                        exit
EOF`
                        bsize=`echo $bsize | tr -d " "`
                        echo
                        echo "Final Selection:-"
                        echo "Datafile: $cdf"
                        echo "Block Size: $bsize"
                        echo "Blocks to be corrupted: $header_b, `expr $header_b + 1`, `expr $header_b + 2`"
                        echo
                        corrupt_database
                fi
        unset_env
        fi
        elif [ $c_choice -eq 2 ]; then
                echo
                echo -e "Enter Schema Name: \c"
                read s_name
                echo -e "Enter Table Name: \c"
                read t_name
                set_env
                chk_table_sql="select table_name from dba_tables where owner=upper('$s_name') and table_name=upper('$t_name');"
                chk_tabseq_sql="select count(*) from dba_segments where segment_name=upper('$t_name') and owner=upper('$s_name');"
                chk_table=`$ORACLE_HOME/bin/sqlplus -S << EOF
                / as sysdba
                set feed off head off
                $chk_table_sql
                exit
EOF`
                chk_tabseq=`$ORACLE_HOME/bin/sqlplus -S << EOF
                / as sysdba
                set feed off head off
                $chk_tabseq_sql
                exit
EOF`
                chk_tabseq=`echo $chk_tabseq | tr -d " "`

                if [ "$chk_table" == "" ]; then
                        echo "Specified table doesn't exist. Terminating program"
                        unset_env
                        exit
                elif [ $chk_tabseq -eq 0 ]; then
                        echo "Table segment doesn't exist for table '$s_name.$t_name'. Terminating program"
                        unset_env
                        exit 1
                else
                        t_header_b_sql="select header_block from dba_segments  where SEGMENT_NAME=upper('$t_name') and owner=upper('$s_name');"
                        t_dbf_sql="select name from v\$datafile where file#=(select header_file from dba_segments  where SEGMENT_NAME=upper('$t_name') and owner=upper('$s_name'));"
                        echo
                        echo "Retrieving Table Header..."
                        header_b=`$ORACLE_HOME/bin/sqlplus -S << EOF
                        / as sysdba
                        set feed off head off
                        $t_header_b_sql
                        exit
EOF`
                        echo
                        echo "Retrieving datafile details..."
                        cdf=`$ORACLE_HOME/bin/sqlplus -S << EOF
                        / as sysdba
                        set feed off head off
                        $t_dbf_sql
                        exit
EOF`
                        header_b=`echo $header_b | tr -d " "`
                        cdf=`echo $cdf | tr -d " "`

                        g_bsize_sql="select block_size from v\$datafile where name='$cdf';"
                        bsize=`$ORACLE_HOME/bin/sqlplus -S << EOF
                        / as sysdba
                        set feed off head off
                        $g_bsize_sql
                        exit
EOF`
                        bsize=`echo $bsize | tr -d " "`
                        echo
                        echo "Final Selection:-"
                        echo "Schema: $s_name"
                        echo "Table Name: $t_name"
                        echo "Datafile: $cdf"
                        echo "Block Size: $bsize"
                        echo "Blocks to be corrupted: $header_b, `expr $header_b + 1`, `expr $header_b + 2`"
                        echo
                        corrupt_database
                        echo
                        echo "Crosschecking table access"
                        $ORACLE_HOME/bin/sqlplus -S << EOF
                        / as sysdba
                        select count(*) from $s_name.$t_name;
                        exit
EOF
                fi

        else
                echo "Invalid Choice. Terminating"
                exit 1
        fi

fi

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed