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.
- RMAN Backup Types.
- Backup Format Specification.
- Full Backup Script (Hot Backup).
- Archive Log Backup Script (Hot Backup)
- Example of Incremental Backup.
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>
