Database Backup Script using RMAN

A backup is a copy of data. This copy can include important parts of the database, such as the control file and datafiles. A backup is a safeguard against unexpected data loss and application errors. If you lose the original data, then you can reconstruct it by using a backup. So taking database backup is one of the most important task for DBA.

We can take database backup either online (hot backup) or offline (cold backup).

RMAN Backup Types:

Full Backup: A full backup contains all used data file blocks.

Level 0 Backup: A level 0 incremental backup is equivalent is equivalent to a full backup that has been marked as level 0.

Incremental Backup:

A Cumulative level 1 incremental backup contains only blocks modified since the last level 0 incremental backup.

A differential Level 1 incremental backup contains only blocks modified since the last incremental backup.

For hot backup, database need to be configured archive log mode.

How to change archive log – Archive Log Mode

For cold backup, database downtime is required.

Backup Format Specification:

Syntax Element	Description
%a	Specifies the activation ID of the database.
%b	Specifies the file name stripped of directory paths. It is only valid for SET NEWNAME and backup when producing image copies It yields errors if used as a format specification for a backup that produces backup pieces.
%c	Specifies the copy number of the backup piece within a set of duplexed backup pieces. If you did not duplex a backup, then this variable is 1 for backup sets and 0 for proxy copies. If a command is enabled, then the variable shows the copy number. The maximum value for %c is 256.
%d	Specifies the name of the database (see Example 4-22).
%D	Specifies the current day of the month from the Gregorian calendar in format DD.
%e	Specifies the archived log sequence number.
%f	Specifies the absolute file number (see Example 4-22).
%F	Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name. This variable translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:
	
	IIIIIIIIII stands for the DBID. The DBID is printed in decimal so that it can be easily associated with the target database.
	YYYYMMDD is a time stamp in the Gregorian calendar of the day the backup is generated
	QQ is the sequence in hexadecimal number that starts with 00 and has a maximum of 'FF' (256)
	
%h	Specifies the archived redo log thread number.
%I	Specifies the DBID.
%M	Specifies the month in the Gregorian calendar in format MM.
%N	Specifies the tablespace name. This substitution variable is only valid when backing up data files as image copies.
%n	Specifies the name of the database, padded on the right with x characters to a total length of eight characters. For example, if prod1 is the database name, then the padded name is prod1xxx.
%p	Specifies the piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1 as each backup piece is created.
	Note: If you specify PROXY, then the %p variable must be included in the FORMAT string either explicitly or implicitly within %U.
%s	Specifies the backup set number. This number is a counter in the control file that is incremented for each backup set. The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then duplicate values can result. Also, CREATE CONTROLFILE initializes the counter back to 1.
%t	Specifies the backup set time stamp, which is a 4-byte value derived as the number of seconds elapsed since a fixed reference time. You can use a combination of %s and %t to form a unique name for the backup set.
%T	Specifies the year, month, and day in the Gregorian calendar in this format: YYYYMMDD.
%u	Specifies an 8-character name constituted by compressed representations of the backup set or image copy number and the time the backup set or image copy was created.
%U	Specifies a system-generated unique file name (default).
	The meaning of %U is different for image copies and backup pieces. For a backup piece, %U specifies a convenient shorthand for %u_%p_%c that guarantees uniqueness in generated backup file names. For an image copy of a data file, %U means the following:
	data-D-%d_id-%I_TS-%N_FNO-%f_%u
	For an image copy of an archived redo log, %U means the following:
	arch-D_%d-id-%I_S-%e_T-%h_A-%a_%u
	For an image copy of a control file, %U means the following:
	cf-D_%d-id-%I_%u
%Y	Specifies the year in this format: YYYY.
%%	Specifies the percent (%) character. For example, %%Y translates to the string %Y.

Full Backup Script (Hot Backup):

For Compression Backup use following line:

#!/bin/ksh
set -x
#---------------------------------------------------------------------------#
#
# FILENAME   : db_full_backup.ksh
# DESCRIPTION: The Script does the following
#              1. Applies the retention policy to delete obsolete backups
#              2. Retention policy is set to recovery window of 30 days for this db
#              3. Deletes all archive logs backed up to Disk two times
#              4. backup plus archive log performs following operations in sequence
#                 a. alter system archive log current
#                 b. backup archive log all
#                 c. alter system archive log current
#                 d. backup archives generated during backup#
#---------------------------------------------------------------------------#

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/12.1.0
export ORACLE_SID=orcl
export DBA=${ORACLE_BASE}/admin
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:/usr/lib:/lib
export LIBPATH=${ORACLE_HOME}/lib:/usr/lib:/lib

export BACKUP_MEDIA=DISK
export BACKUP_TYPE=FULL_DB_BACKUP
export MAXPIECESIZE=8G

export DBAEMAIL="dba_team@alldba.com"
export FAILURE_EMAIL="dba_team@alldba.com"
export TIMESTAMP=`date +%T-%m-%d-%Y`
export NLS_DATE_FORMAT="MM/DD/YYYY HH24:MI:SS"
export HOST_NAME=ole7orcl12

export LOG_DIR=${DBA}/${ORACLE_SID}/logs
export TMPDIR=/tmp
export LOGFILE=${LOG_DIR}/${ORACLE_SID}_${BACKUP_TYPE}_${BACKUP_MEDIA}_${TIMESTAMP}.log
export TMPLOG=$TMPDIR/tmplog.$$

export TARGET=" TARGET / "
# using catalog db
#export CATALOG_CONN=" CATALOG rcat/*******@CAT_DB_TNSNAME"
# no catalog db
export CATALOG_CONN=" NOCATALOG"

export LOCKFILE=$DBA/rman/${ORACLE_SID}_${BACKUP_TYPE}_${BACKUP_MEDIA}.lck
export NUM_OF_DAYS=3

if [ -f $LOCKFILE ]; then
        echo `date` "Script running. Exiting ..... " >> ${LOGFILE}
else
        echo "Do NOT delete this file. Used for RMAN locking" > $LOCKFILE
fi

#/usr/bin/find /backup*/orcl_backupset/backupset/ -name "*.rman" -mtime +$NUM_OF_DAYS | xargs rm;

${ORACLE_HOME}/bin/rman ${TARGET} ${CATALOG_CONN} log=$TMPLOG << EOF

run
{
allocate channel ch01 type disk format '/backup01/orcl_backupset/backupset/ch01_${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s_%c.rman' ;
allocate channel ch02 type disk format '/backup02/orcl_backupset/backupset/ch02_${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s_%c.rman' ;
allocate channel ch03 type disk format '/backup03/orcl_backupset/backupset/ch03_${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s_%c.rman' ;
allocate channel ch04 type disk format '/backup04/orcl_backupset/backupset/ch04_${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s_%c.rman' ;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup01/orcl_backupset/backupset/cf_%F';
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
delete noprompt obsolete;
delete noprompt archivelog all backed up 2 times to disk;
CROSSCHECK BACKUP;
CROSSCHECK archivelog all;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
backup tag = 'LVL0_DB_BACKUP' as backupset database INCLUDE CURRENT CONTROLFILE plus archivelog;
# For compression backup
#backup tag = 'LVL0_DB_BACKUP' as compressed backupset database INCLUDE CURRENT CONTROLFILE plus archivelog;
backup current controlfile;
release channel ch01;
release channel ch02;
release channel ch03;
release channel ch04;
}
exit
EOF

RC=$?

cat $TMPLOG >> $LOGFILE
rm $TMPLOG
rm $LOCKFILE

echo `date` "Script lock file removed" >> $LOGFILE

if [ $RC -ne "0" ]; then
        mailx -s "ORCL: orcl ${BACKUP_TYPE} ${ORACLE_SID} ${BACKUP_MEDIA} Failed -  ${HOST_NAME}  " ${FAILURE_EMAIL} < ${LOGFILE}
else
        mailx -s "ORCL: RMAN orcl ${BACKUP_TYPE} ${ORACLE_SID} ${BACKUP_MEDIA} Success -  ${HOST_NAME} " ${DBAEMAIL} < ${LOGFILE}
fi

Archive Log Backup Script (Hot Backup):

#!/bin/ksh
set -x
#---------------------------------------------------------------------------#
#
# FILENAME   : db_full_backup.ksh
# DESCRIPTION: The Script does the following
#              1. Applies the retention policy to delete obsolete backups
#              2. Retention policy is set to recovery window of 30 days for this db
#              3. Deletes all archive logs backed up to Disk two times
#              4. backup plus archive log performs following operations in sequence
#                 a. alter system archive log current
#                 b. backup archive log all
#                 c. alter system archive log current
#                 d. backup archives generated during backup#
#---------------------------------------------------------------------------#

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/12.1.0
export ORACLE_SID=orcl
export DBA=${ORACLE_BASE}/admin
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:/usr/lib:/lib
export LIBPATH=${ORACLE_HOME}/lib:/usr/lib:/lib

export BACKUP_MEDIA=DISK
export BACKUP_TYPE=ARCHIVELOG_DB_BACKUP
export MAXPIECESIZE=8G

export DBAEMAIL="dba_team@alldba.com"
export FAILURE_EMAIL="dba_team@alldba.com"
export TIMESTAMP=`date +%T-%m-%d-%Y`
export NLS_DATE_FORMAT="MM/DD/YYYY HH24:MI:SS"
export HOST_NAME=ole7orcl12

export LOG_DIR=${DBA}/${ORACLE_SID}/logs
export TMPDIR=/tmp
export LOGFILE=${LOG_DIR}/${ORACLE_SID}_${BACKUP_TYPE}_${BACKUP_MEDIA}_${TIMESTAMP}.log
export TMPLOG=$TMPDIR/tmplog.$$

export TARGET=" TARGET / "
# using catalog db
#export CATALOG_CONN=" CATALOG rcat/*******@CAT_DB_TNSNAME"
# no catalog db
export CATALOG_CONN=" NOCATALOG"

export LOCKFILE=$DBA/rman/${ORACLE_SID}_${BACKUP_TYPE}_${BACKUP_MEDIA}.lck
export NUM_OF_DAYS=3

if [ -f $LOCKFILE ]; then
        echo `date` "Script running. Exiting ..... " >> ${LOGFILE}
else
        echo "Do NOT delete this file. Used for RMAN locking" > $LOCKFILE
fi

#/usr/bin/find /backup*/orcl_backupset/backupset/ -name "*.rman" -mtime +$NUM_OF_DAYS | xargs rm;

${ORACLE_HOME}/bin/rman ${TARGET} ${CATALOG_CONN} log=$TMPLOG << EOF

run
{
allocate channel ch01 type disk format '/backup01/orcl_backupset/backupset/ch01_${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s_%c.rman' ;
allocate channel ch02 type disk format '/backup02/orcl_backupset/backupset/ch02_${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s_%c.rman' ;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
delete noprompt obsolete;
delete noprompt archivelog all backed up 2 times to disk;
CROSSCHECK BACKUP;
CROSSCHECK archivelog all;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
backup tag = 'ARCHLOG_BACKUP' archivelog all;
release channel ch01;
release channel ch02;
}
exit
EOF

RC=$?

cat $TMPLOG >> $LOGFILE
rm $TMPLOG
rm $LOCKFILE

echo `date` "Script lock file removed" >> $LOGFILE

if [ $RC -ne "0" ]; then
        mailx -s "ORCL: orcl ${BACKUP_TYPE} ${ORACLE_SID} ${BACKUP_MEDIA} Failed -  ${HOST_NAME}  " ${FAILURE_EMAIL} < ${LOGFILE}
else
        mailx -s "ORCL: RMAN orcl ${BACKUP_TYPE} ${ORACLE_SID} ${BACKUP_MEDIA} Success -  ${HOST_NAME} " ${DBAEMAIL} < ${LOGFILE}
fi

Example of Incremental Backup: (We may use below command on above shell script for cron job)

To perform an incremental backup at level 0, use the following command:

RMAN>
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
RMAN>

To perform a differential incremental backup at level 0, use the following command:

RMAN>
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
RMAN>

To perform a cumulative incremental backup at level 0, use the following command:

RMAN>
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
RMAN>

Leave a Reply