RMAN backup script

#!/bin/ksh

##########################################################################################

#

# Script Name : rman_backup.ksh

#

# Written By  : 

#

# Date        : 

#

# Usage       : rman_backup.ksh -M <mode> -A <archivelog retention days>

#

# Parameters  :

#               M = Mode; DATABASE, ARCHIVELOG, ADHOC

#

# Description : 

##########################################################################################

#

# FUNCTIONS

#

##########################################################################################

InitialiseVariables ()

{

export ORACLE_SID="DB Name as appropriate"

export ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/dbhome_1

export PATH=$PATH:$ORACLE_HOME/bin

LogfileDir=/u02/backups/rman_logfiles

WrapperLogfile=${LogfileDir}/rman_backup_wrapper.log

Today=$(date +'%Y%m%d')

Now=$(date +'%Y%m%d_%H%M%S')

NLS_DATE_FORMAT='DD-Mon-YYYY Dy HH24:MI:SS'

export NLS_DATE_FORMAT

BACKUP_AREA=/u02/backups/

if [ ! -d $BACKUP_AREA ]

then

   mkdir ${BACKUP_AREA}

fi

ArcBackupArea=${BACKUP_AREA}/rman_archivelog_backups

DBBackupArea=${BACKUP_AREA}/rman_database_backups

ReportName=${LogfileDir}/rman_report_${Today}.txt

CrossscheckLogfile=${LogfileDir}/crosscheck.log

ArchiveLogRetention=7 #was previously 14

NumBackupsKeep=2

}

##########################################################################################

Usage ()

{

  echo "Usage: $0 -M <Mode> -A <archivelog retention days>"

}

##########################################################################################

ValidateParameters ()

{

#Script Mode:

case $ScriptMode in

'DATABASE') return ;;

'ARCHIVELOG') return ;;

'ADHOC') return ;;

*)    Usage; exit -1;;

esac

}

##########################################################################################

DeleteOldBackups ()

{

NumBackups=$(ls -d ${DBBackupArea}/20[0-9][0-9][0-9][0-9][0-9][0-9] | wc -l)

echo "Number of DB backups = ${NumBackups}."  >> ${WrapperLogfile}

if [ ${NumBackups} -lt ${NumBackupsKeep} ]

then

echo "No need to delete any backups."  >> ${WrapperLogfile}

else

Oldest=$(ls -d1 ${DBBackupArea}/20[0-9][0-9][0-9][0-9][0-9][0-9] | head -1 | xargs basename)

echo "Deleting backup ${Oldest}" >> ${WrapperLogfile}

rm -rf ${DBBackupArea}/${Oldest}

NumBackups=$(ls -d ${DBBackupArea}/20[0-9][0-9][0-9][0-9][0-9][0-9] | wc -l)

echo "There are now ${NumBackups} backups." >> ${WrapperLogfile}

fi

}

##########################################################################################

RMANReport ()

{

rman nocatalog target / << EOF > ${ReportName}

set echo on;

show all ;

list backupset of database summary ;

list backupset of database ;

list backupset of controlfile summary ;

list backupset of controlfile ;

exit ;

EOF

sqlplus -s "/ as sysdba" << FIN >> ${ReportName}

PROMPT

PROMPT ARCHIVELOG Summary:

PROMPT ===================

set pages 25

column date1 format a12 hea "Date Created"

select count(*) "No. Backupsets", to_char(completion_time,'YYYY/MM/DD') date1

  from v\$backup_set

where backup_type = 'L'

group by to_char(completion_time,'YYYY/MM/DD')

order by to_char(completion_time,'YYYY/MM/DD');

PROMPT

PROMPT END OF REPORT

PROMPT

exit

FIN

}

##########################################################################################

BackupArch ()

{

echo "Starting backup ..."  >> ${WrapperLogfile}

BackupTag="ARCLG_${Today}"

LogFile=${LogfileDir}/rman_arc_${Now}.log

rman nocatalog target / <<EOF > ${LogFile}

set echo on;

run {

SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${ArcBackupArea}/controlf_autobackup_%F';

allocate channel d1  type disk ;

allocate channel d2  type disk ;

allocate channel d3  type disk ;

allocate channel d4  type disk ;

allocate channel d5  type disk ;

allocate channel d6  type disk ;

delete noprompt archivelog until time 'trunc(sysdate - ${ArchiveLogRetention})' ;

delete noprompt backup of archivelog all completed before 'trunc(sysdate - ${ArchiveLogRetention})' ;

sql 'alter system archive log current';

backup as compressed backupset

       tag=${BackupTag}

       format '${ArcBackupArea}/rman_arc_%d_%t_%s_%p'

       (archivelog all not backed up 2 times) ;

release channel d1 ;

release channel d2 ;

release channel d3 ;

release channel d4 ;

release channel d5 ;

release channel d6 ;

}

exit;

EOF

}

#######################################

ArchivelogReport ()

{

sqlplus -s "/ as sysdba" << FIN >> ${ReportName}

PROMPT

PROMPT ARCHIVELOG Summary:

PROMPT ===================

column date1 format a12 hea "Date Created"

select count(*) "No. Backupsets", to_char(completion_time,'YYYY/MM/DD') date1

  from v\$backup_set

where backup_type = 'L'

group by to_char(completion_time,'YYYY/MM/DD')

order by to_char(completion_time,'YYYY/MM/DD');

PROMPT

PROMPT END OF REPORT

PROMPT

exit

FIN

}

##########################################################################################

CheckErrors ()

{

NumErrors=$(grep -c "RMAN-" ${LogFile})

if [ ${NumErrors} -ne 0 ]

then

echo "ERRORS were encountered !!!" >> ${WrapperLogfile}

else

echo "No errors encountered." >> ${WrapperLogfile}

fi

}

##########################################################################################

CrosscheckBackups ()

{

echo "Performing crosscheck ..."  >> ${WrapperLogfile}

rman nocatalog target / <<EOF > ${CrossscheckLogfile}

set echo on;

run {

crosscheck backupset ;

delete noprompt expired backupset ;

}

exit;

EOF

}

##########################################################################################

BackupDatabase ()

{

echo "Starting backup ..."  >> ${WrapperLogfile}

BACKUP_DIR=${DBBackupArea}/${Today}

export BACKUP_DIR

if [ ! -d $BACKUP_DIR ]

then

mkdir ${BACKUP_DIR}

fi

BackupTag="REGDB_$(date +'%Y%m%d')"

LogFile=${LogfileDir}/rman_dbf_${Now}.log

rman nocatalog target / <<EOF > ${LogFile}

set echo on;

run {

CONFIGURE CONTROLFILE AUTOBACKUP ON;

SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKUP_DIR}/controlf_autobackup_%F';

allocate channel d1  type disk ;

allocate channel d2  type disk ;

allocate channel d3  type disk ;

allocate channel d4  type disk ;

allocate channel d5  type disk ;

allocate channel d6  type disk ;

backup as compressed backupset

       section size 51200M

       tag=${BackupTag}

       format '${BACKUP_DIR}/rman_dbf_%d_%t_%s_%p'

       (database include current controlfile);

sql 'alter system archive log current';

backup as compressed backupset

       format '${ArcBackupArea}/rman_arc_%d_%t_%s_%p'

       (archivelog all not backed up 2 times) ;

release channel d1 ;

release channel d2 ;

release channel d3 ;

release channel d4 ;

release channel d5 ;

release channel d6 ;

}

exit;

EOF

BackupSize=$(du -sh ${BACKUP_DIR})

echo "Size of backup =  ${BackupSize}"  >> ${WrapperLogfile}

}

##########################################################################################

BackupAdhoc ()

{

echo "Starting backup ..."  >> ${WrapperLogfile}

BACKUP_DIR=${DBBackupArea}/ADHOC

export BACKUP_DIR

if [ ! -d $BACKUP_DIR ]

then

mkdir ${BACKUP_DIR}

fi

BackupTag="ADHOC_$(date +'%Y%m%d')"

LogFile=${LogfileDir}/rman_dbf_${Now}.log

rman nocatalog target / <<EOF > ${LogFile}

set echo on;

run {

CONFIGURE CONTROLFILE AUTOBACKUP ON;

SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKUP_DIR}/controlf_autobackup_%F';

allocate channel d1  type disk ;

allocate channel d2  type disk ;

allocate channel d3  type disk ;

allocate channel d4  type disk ;

allocate channel d5  type disk ;

allocate channel d6  type disk ;

allocate channel d7  type disk ;

allocate channel d8  type disk ;

sql 'alter system archive log current';

backup as compressed backupset database

       section size 51200M

       tag=${BackupTag}

       format '${BACKUP_DIR}/rman_adh_%d_%t_%s_%p'

       keep until time 'sysdate+182'

       restore point ${BackupTag} ;

sql 'alter system archive log current';

release channel d1 ;

release channel d2 ;

release channel d3 ;

release channel d4 ;

release channel d5 ;

release channel d6 ;

release channel d7 ;

release channel d8 ;

}

exit;

EOF

BackupSize=$(du -sh ${BACKUP_DIR})

echo "Size of backup =  ${BackupSize}"  >> ${WrapperLogfile}

}

##########################################################################################

ExitIfAlreadyRunning ()

{

NUM=$(ps -ef |grep rman_backup.ksh|grep -v $$|grep -v grep|wc -l)

if [ $NUM -ne 0 ]

then

echo "RMAN backup is already running.  Therefore, exiting here ..." >> ${WrapperLogfile}

echo "Script completed at `date`" | tee -a ${WrapperLogfile}

exit

fi

}

##########################################################################################

HousekeepArchivelogs ()

{

ARC_COUNT=$(ls /u02/backups/rman_archivelog_backups/rman_arc* | wc -l)

echo "Number of archivelogs before deletion: ${ARC_COUNT}" >> ${WrapperLogfile}

find /u02/backups/rman_archivelog_backups -name "rman_arc*" -mtime +18 -exec rm {} \;

ARC_COUNT=$(ls /u02/backups/rman_archivelog_backups/rman_arc* | wc -l)

echo "Number of archivelogs after deletion: ${ARC_COUNT}" >> ${WrapperLogfile}

}

##########################################################################################

#

# MAIN PROCESSING

#

##########################################################################################

# Source profile, as this will run from cron

#. ~/.profile > /dev/null  2>&1

while getopts M: flag

do

  case $flag in

  M) ScriptMode=$OPTARG ;;

  *) Usage; exit -1;;

  esac

done

if [ "$ScriptMode" = "" ]

then

  Usage

  exit -1

fi

ValidateParameters

InitialiseVariables

echo "-------------------------------------------------" | tee -a ${WrapperLogfile}

#echo " " | tee -a ${WrapperLogfile}

echo "Script starting at `date`" | tee -a ${WrapperLogfile}

echo "Mode = ${ScriptMode}." >> ${WrapperLogfile}

ExitIfAlreadyRunning

if [ "$ScriptMode" = "ARCHIVELOG" ]

then

BackupArch

fi

if [ "$ScriptMode" = "DATABASE" ]

then

DeleteOldBackups

CrosscheckBackups

BackupDatabase

RMANReport

HousekeepArchivelogs

fi

if [ "$ScriptMode" = "ADHOC" ]

then

BackupAdhoc

fi

CheckErrors

echo "Script completed at `date`" | tee -a ${WrapperLogfile}

##########################################################################################

exit

##########################################################################################

ASM compatibility

Alter compatibility on ASM disk groups :

SQL> ALTER DISKGROUP DATA SET ATTRIBUTE 'compatible.rdbms' = '12.2.0.1.0'

SQL> ALTER DISKGROUP DATA SET ATTRIBUTE 'compatible.asm' = '12.2.0.1.0';

SQL> l
1* SELECT group_number, name, compatibility, database_compatibility FROM v$asm_diskgroup

SQL> /

GROUP NAME COMPATIBILITY DATABASE_COMPATIBILITY
----- -------------------- ------------------------------------------------------------ ------------------------------------------------------------
1 FRA 12.2.0.1.0 12.2.0.0.0
2 GGDATA 12.2.0.1.0 12.2.0.0.0
3ORADATA 12.2.0.1.0 12.2.0.0.0

Two column match in Excel

Quick note to show how to check two columns in Excel to show matches.

The Excel formula is (change as appropriate):

=IF(A4=B4,"Match","")

Example output :