Oracle RMAN Maintenance script

When using an RMAN Catalog to backup your database (Highly Recommended), it is important to run Maintenance scripts on it to clean up old backups and to maintain your catalog. This is the entire bash script called from cron one a week. Usually on a Sunday. Highlighted in yellow are lines you would need to customize to your environment.

The portion of the RMAN script is here. Below this is the entire script.

ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
LIST INCARNATION OF DATABASE dev;
REPORT UNRECOVERABLE;
REPORT SCHEMA;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP OF DATABASE;
DELETE NOPROMPT EXPIRED BACKUP OF ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP OF CONTROLFILE;
LIST BACKUP OF DATABASE SUMMARY;
REPORT NEED BACKUP;
REPORT OBSOLETE ORPHAN;
REPORT OBSOLETE;
DELETE NOPROMPT OBSOLETE;

RMAN Maintenance script.

maintenance.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
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
LIST INCARNATION OF DATABASE dev;
REPORT UNRECOVERABLE;
REPORT SCHEMA;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP OF DATABASE;
DELETE NOPROMPT EXPIRED BACKUP OF ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP OF CONTROLFILE;
LIST BACKUP OF DATABASE SUMMARY;
REPORT NEED BACKUP;
REPORT OBSOLETE ORPHAN;
REPORT OBSOLETE;
DELETE NOPROMPT OBSOLETE;
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 "Maintenance 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` Maintenance Script $LOGMSG - EXIT Status = $RSTAT" howard@email.com

exit $RSTAT