Using RMAN in UNIX

Excerpt by Rampant Author Porus Homi Havewala, author of Oracle Enterprise Manager Grid Control by Rampant TechPress.

Oracle strongly recommends the use of Oracle Grid Control for managing sophisticated options like its fully active-active clustered database option, Oracle Real Application Clusters (Oracle RAC). This is so especially in the area of analyzing cluster performance, which is greatly aided by Oracle Grid Control.

When the earliest Oracle databases were being backed up, they were offline (“cold”) backups. The DBA manually performed the backup as an OS file copy of all database files after shutting down the database. Some enterprising administrators started writing UNIX shell scripts to do the work of shutting down the database, copying the files using OS commands, and then starting the database again.

The traditional UNIX scheduler cron was used to call the script at the appropriate time. So there was some level of automation, if one discounted the manual effort of writing, implementing, and testing the scripts. They also had to maintain the scripts for changes—the database name might change, or more databases might appear on the same server that would need to be backed up, so the scripts would need to be modified.

The traditional approach to Oracle’s RMAN utility was exactly the same as before: the use of UNIX shell scripts and cron.

In this age of sophisticated database management tools such as Oracle Grid Control, this may seem archaic, but there are a large number of companies who still use a shell script to call Oracle RMAN, and there are some who use layer upon layer of shell scripts.

Oracle purposely simplified the Oracle RMAN syntax to make the job of backup and recovery much easier, but the purpose is defeated if the Oracle RMAN commands are hidden under sublayers of OS shell scripts. Even experienced DBAs who walk into these large companies find it difficult to understand the customized shell scripts, although they know how to use Oracle RMAN.

The Traditional Approach to Backups

These steps must be followed for every database server requiring Oracle RMAN backups for its databases. If the databases are on an active-passive cluster—for example, a SUN HA cluster or any such technology—then the steps would be followed for each server.
As the root UNIX user add the line “oracle” to /etc/cron.d/cron.allow in order to allow the oracle UNIX user to use the cron utility in UNIX.

As the oracle UNIX user, add the following to the crontab:

30 21 * * * dba/scripts/rman_backup_db.sh FIN1P

As per crontabl syntax, this calls the rman_backup_db.sh script at 21:30 hours on each day. The script is asked to execute against the FIN1P database by specifying this database name as the first and only argument.

#!/bin/ksh

#

#       rman_backup_db.sh

#

#       function:       Backup Database using Oracle RMAN

#       Author:         Porus Homi Havewala

#

#       Creation - 07/06/2008 - Porus HH

#

# Get the job name from the basename of the script file name

JOBNAME="$0"

JOB=`basename $JOBNAME`

# Set the DBTOOLS directory

DBTOOLS=$HOME/dba/scripts

# Set ORATAB

ORATAB=/var/opt/oracle/oratab

# Form the Error message if required

ERRMSG="

$JOB: `date '+%H:%M:%S'` You have specified an invalid parameter. The correct syntax is:

$JOB

"

# Check if there is an argument, if yes set the DBNAME to the argument

if [ "$1" ]

then DBNAME=$1

# Otherwise show the error message showing the correct syntax and exit

else echo $ERRMSG

exit ${ERROR}

fi

# Set the Oracle Sid to the first argument

ORACLE_SID=$1

# Set this variable so that the oracle supplied oraenv script works non-interactively

ORAENV_ASK=NO; export ORAENV_ASK

# Call the oracle supplied script oraenv to set the oracle environment variables

. oraenv

# Check if successful, else show error and exit

if [ $? -ne 0 ]; then

echo "$JOB: `date '+%H:%M:%S'` Failed to set oracle environment variables. Aborting.."

exit ${ERROR}

fi

# Following code is useful if running on passive node in an active-passive cluster

echo "Verifying database $ORACLE_SID"

# Check if Oracle SID is a valid entry in the oratab file

if grep -c $ORACLE_SID $ORATAB > /dev/null ; then

# Checks if Oracle database pmon background process is running

pmon=`ps -ef | egrep pmon_$ORACLE_SID | grep -v grep`

if [ "$pmon" != "" ];

then

echo "\n"

else

echo "\n******* IT IS POSSIBLE THIS IS THE PASSIVE NODE*****"

echo "\n******* SINCE $ORACLE_SID IS NOT RUNNING *********"

exit 0

fi

else

echo "\n******* $ORACLE_SID DOES NOT EXIST ON THIS SYSTEM *******\n"

exit 1

fi

# Set the backup directoy and if doesn't exist, then exit

BACKUPDIR=/U99/$DBNAME

if [ ! -d $BACKUPDIR ]; then

echo "$JOB: `date '+%H:%M:%S'` Cannot find $BACKUPDIR directory. Aborting.."

exit ${ERROR}

fi

# Set the log directory and the name of the RMAN command file

SCRIPT=`basename $0`

LOGDIR="$HOME/dba/logs"

CMDFILE="${BACKUPDIR}/rmancmd/rman_${DBNAME}.cmd"

NODENAME=`hostname`

# Logfile is placed under backup directory so backed up along with corporate file-level backup

# Node name is also included in the logfile name for the 2 active-passive nodes

LOGFILE="${BACKUPDIR}/log/${SCRIPT}_${NODENAME}_${DBNAME}_`date '+%d%m%y-%H:%M'`.log"

# Set the NLS_LANG as per database setting

case $DBNAME in

"FIN1P") NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 ; export NLS_LANG

;;

"PAY2P") NLS_LANG=AMERICAN_AMERICA.AL32UTF8 ; export NLS_LANG

;;

esac

# Set the NLS_DATE_FORMAT

NLS_DATE_FORMAT='DD-MON-RR HH24.MI.SS' ; export NLS_DATE_FORMAT

# Now creating the RMAN commandfile

cat << EOF > $CMDFILE

#

#       Creation                      Porus Homi Havewala     07/06/2008

#

# Configure RMAN settings

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$BACKUPDIR/cf_%F';

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEVICE TYPE disk PARALLELISM 3;

CONFIGURE DEFAULT DEVICE TYPE TO disk;

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '$BACKUPDIR/b_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '$BACKUPDIR/b_%U';

CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '$BACKUPDIR/b_%U';

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$BACKUPDIR/snapcf_${DBNAME}.f';

# Perform backup of database and archivelogs, deleting backed up archivelogs

BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

# Maintainance commands for crosschecks and deleting expired backups

ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

CROSSCHECK BACKUP;

DELETE NOPROMPT EXPIRED BACKUP;

DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;

CROSSCHECK ARCHIVELOG ALL;

DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

# End of RMAN command script

EOF

# The actual rman utility is called at this point by the script. Note that the nocatalog mode is being used

rman nocatalog target=/ cmdfile=$CMDFILE log=$LOGFILE

if [ $? -ne 0 ]; then

echo "$JOB: `date '+%H:%M:%S'` RMAN has failed, see $LOGFILE for details. Aborting.." | tee -a $LOGFILE

exit ${ERROR}

fi

# Find and delete files modified before 2, 3, 4 or 5 days

# This is a safety precaution so the space in the backup directory doesn't fill up

find /U99/$DBNAME/ -name "*" -mtime 2 -exec rm {} \;
find /U99/$DBNAME/ -name "*" -mtime 3 -exec rm {} \;
find /U99/$DBNAME/ -name "*" -mtime 4 -exec rm {} \;
find /U99/$DBNAME/ -name "*" -mtime 5 -exec rm {} \;

echo "$JOB: `date '+%H:%M:%S'` $ORACLE_SID rman backup completed" | tee -a $LOGFILE

# End of rman_backup_db.sh script

Log on as a DBA to database FIN1P in SQL*Plus and create an externally identified user :

create user ops$oracle identified externally;

This has the effect that the Oracle UNIX user can log on to SQL*Plus as the Oracle database user without specifying the password—in other words, the user is identified externally. Note that SYSDBA database privileges are needed for taking RMAN backups, so the Oracle UNIX user should be a member of the dba UNIX group.

This is the technique used most often with scripts that log in to the database, especially those that require DBA rights for database-level backups. It is not a good idea to hard-code database passwords in scripts, because the scripts are UNIX files and may be readable by anyone on the computer (unless the file permissions are locked down). So as a safety precaution, an externally identified Oracle user can be used to log in as the DBA and perform the backup. This circumvents the need for specifying the database password in the script.

Under the subdirectory /home/oracle/dba/scripts, create the file rman_backup_db.sh using vi or any UNIX editor. The script, when executed every day at the specified time set in cron, will generate the following Oracle RMAN command file

/U99/FIN1P/rmancmd/rman_FIN1P.cmd:

#
# Creation Porus Homi Havewala 07/06/2008
#

# Configure RMAN settings
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$BACKUPDIR/cf_%F';
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEVICE TYPE disk PARALLELISM 3;
CONFIGURE DEFAULT DEVICE TYPE TO disk;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '$BACKUPDIR/b_%U';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '$BACKUPDIR/b_%U';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '$BACKUPDIR/b_%U';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$BACKUPDIR/snapcf_${DBNAME}.f';

# Perform backup of database and archivelogs, deleting backed up archivelogs
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

# Maintainance commands for crosschecks and deleting expired backups
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
# End of RMAN command script

As can be seen from the Oracle RMAN commands, a recovery window of three days is specified as the Oracle RMAN retention policy, which means that all backups necessary to recover up to the last three days of data will be retained. Obviously this setting may need to be changed, depending on the size of the database and the free space available in the backup mount point /U99 at any point. The DBA needs to closely monitor this space and change the recovery window if necessary.

Control file autobackup is also configured, so the control file will be automatically backed up along with every database or archive log backup. This is especially important because we are using the nocatalog mode of Oracle RMAN, so all the history of our backups is only in the control file.

After the backups are completed, maintenance jobs are performed that cross-check the existing backups and archive logs and make sure that they are present on-disk. All expired (not found) and obsolete (as per the retention policy) database, archive log, and control file backup entries are deleted from the control file records. The maintenance commands also ensure that the obsolete database backups and control file autobackups are physically deleted from the disk.

Make the rman_backup_db.sh script executable at the UNIX level by setting the user-executable file permission:

cd /home/oracle/dba/scripts
chmod u+x rman_backup_db.sh