Part 1: Logical validatation of database

After we restore our production database from Tape backup. we found there are serveral loggical corruption in our database.

i.e

ORA-01578: ORACLE data block corrupted (file # 11, block # 4262)

ORA-01110: data file 11: '/db01/oracle/PROD/datafile//data01.dbf'

So I decided to run full datababase validatation in my way


Step 1: Create status table for datafile  

SQL> create table pradipta.rman_data_file as select file_id,null from dba_data_files;

 (This table will use as status and monitor)    

Step 2 : run_data_chk.sh  (Pilot Script)

#!/bin/sh

. ~oracle/.oenv

ORACLE_SID=mdd11g
export ORACLE_SID
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID


sqlplus -s / as sysdba <
Declare
v_input varchar2(4000);
BEGIN
for c1 in (select file_id from pradipta.rman_data_file_chk order by file_id)
LOOP
v_input :='/u02/bin/RMAN/script/logical_chk.sh '
c1.file_id;
sys.host(v_input); #(My magic OS setup) 
END LOOP;
END;
/


exit;
!EOF
exit



Step 3: Unix Script : logical_chk.sh (Slave Script)

#!/bin/sh

. ~oracle/.oenv
ORACLE_SID=mdd11g
export ORACLE_SID
datafile=$1
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
rman target / nocatalog << EOF
RUN
{
allocate channel d1 type disk;
backup check logical validate datafile $datafile;
release channel d1;
}
EOF
RSTAT=$?
if [ "$RSTAT" = "0" ]
then
LOGMSG="SUCESS"
else
LOGMSG="FAILED"
fi


sqlplus -s / as sysdba <
update pradipta.rman_data_file_chk set status='${LOGMSG}' where file_id=${datafile};
commit;
exit;
!EOF
exit $RSTAT


 Step 4: Run pilot script to identify logical corruption

$ ./run_data_chk.sh &


 Step 5: Check Status

SQL> select * from pradipta.rman_data_file;

FILE_ID           STATUS
---------       ----------
64                FAILED

68                SUCESS
69                SUCESS
79                SUCESS
81                SUCESS
82                SUCESS
84                SUCESS