Along with RMAN Maintenance on your RMAN catalog, it is critical to run periodic RMAN Validation on your backups. This helps to ensure there is no corruption and that valid backups are available if and when they are needed.
Any block corruptions are visible in the V$DATABASE_BLOCK_CORRUPTION view as well as the RMAN output.
To check for physical and logical corruption. The Logical keyword includes checks for logical corruption.
BACKUP VALIDATE CHECK LOGICAL DATABASE
Validates the contents of the backup files. Include commands for Controlfiles and Archivelog files.
RESTORE DATABASE VALIDATE
RESTORE CONTROLFILE VALIDATE
RESTORE ARCHIVELOG ALL VALIDATE
RUN {
BACKUP
VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
RESTORE DATABASE VALIDATE;
RESTORE CONTROLFILE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;
}
Here is the entire script. Run once a week from cron. Highlighted in yellow are lines that require updating to match your environment.
validate_backups.sh
#!/bin/sh
# ------------------------------------------------------------------
# Determine the user which is executing this script.
# ------------------------------------------------------------------
CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`
# ------------------------------------------------------------------
# Put output in .out. Change as desired.
# Note: output directory requires write permission.
# RMAN_LOG_FILE=${0}.out
RMAN_LOG_FILE=${0}.`date +%Y%m%d_%H%M%S`.out
# ------------------------------------------------------------------
# You may want to delete the output file so that
# backup information does not accumulate.
# If not, delete the
following lines.
# ------------------------------------------------------------------
if [ -f "$RMAN_LOG_FILE" ]
then
rm -f
"$RMAN_LOG_FILE"
fi
# -----------------------------------------------------------------
# Initialize the log file.
# -----------------------------------------------------------------
set NLS_DATE_FORMAT="YYYY/MM/DD HH24:MI:SS"
set echo on
echo >> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE
# ------------------------------------------------------------------
# Log the start of this script.
# ------------------------------------------------------------------
echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
# -----------------------------------------------------------------
# Replace below, with the Oracle home path.
# -----------------------------------------------------------------
ORACLE_HOME=/oracle/app
export ORACLE_HOME
# ------------------------------------------------------------------
# Replace below, with the Oracle SID of the target database.
# ------------------------------------------------------------------
ORACLE_SID=orcl
export ORACLE_SID
# ------------------------------------------------------------------
# Replace below, with the Oracle DBA user id (account).
# ------------------------------------------------------------------
ORACLE_USER=oracle
# ------------------------------------------------------------------
# Set the target connect string.
# Replace "sys/manager", below, with the target connect string.
# ------------------------------------------------------------------
TARGET_CONNECT_STR=/
CATALOG_CONNECT_STR=catalog_login/password@catalog;
# ------------------------------------------------------------------
# Set the Oracle Recovery Manager name.
# ------------------------------------------------------------------
RMAN=$ORACLE_HOME/bin/rman
# ------------------------------------------------------------------
# Print out the value of the variables set by this script.
# ------------------------------------------------------------------
echo >> $RMAN_LOG_FILE
echo "RMAN: $RMAN" >>
$RMAN_LOG_FILE
echo "ORACLE_SID:
$ORACLE_SID" >> $RMAN_LOG_FILE
echo "ORACLE_USER:
$ORACLE_USER" >> $RMAN_LOG_FILE
echo "ORACLE_HOME:
$ORACLE_HOME" >> $RMAN_LOG_FILE
# -----------------------------------------------------------------
# NOTE: This script assumes that the database is properly opened.
# If
desired, this would be the place to verify that.
# -----------------------------------------------------------------
echo >> $RMAN_LOG_FILE
#
# -----------------------------------------------------------------
# Set initial script start time
c_begin_time_sec=`date +%s`
CMD_STR="
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN target $TARGET_CONNECT_STR catalog $CATALOG_CONNECT_STR msgno append
<< EOF
RUN {
BACKUP
VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
RESTORE DATABASE VALIDATE;
RESTORE CONTROLFILE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;
}
EOF
"
# Initiate the command string
/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE
RSTAT=$?
c_end_time_sec=`date +%s`
echo >> $RMAN_LOG_FILE
v_total_execution_time_sec=`expr ${c_end_time_sec} - ${c_begin_time_sec}`
echo "Script execution time is $((v_total_execution_time_sec / 60))
Minutes" >> $RMAN_LOG_FILE
echo "Validation Script ran in record time." >>
$RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
# -----------------------------------------------------------------
# Log the completion of this script.
# ------------------------------------------------------------------
if [ "$RSTAT" = "0" ]
then
LOGMSG="ended
successfully"
else
LOGMSG="ended in
error"
fi
echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
#
# Email log file and status to DBA
cat $RMAN_LOG_FILE | mailx -s "$((v_total_execution_time_sec / 60)) Mins.
${ORACLE_SID} on `uname -n` Validation Script $LOGMSG - EXIT Status = $RSTAT" howard@email.com
exit $RSTAT
