Oracle Database COLD BACKUP Shell Script

Creating a script to take a cold backup of a database may be a time-consuming task, but creating a script to restore this cold backup later is certainly a daunting task.

The shell script I'm sharing will take a database cold backup and then will create a restore script (to be used in case you will restore the cold backup later).

Script download:
https://www.dropbox.com/s/sjibiupwic9oxt1/COLD_BACKUP.sh?dl=0

How it works:

The script checks the current running databases on the server and will ask you to select the number of the database you want to back up from the list (The database you want to back it up should be up and running in order to get the script know the database files location).

It will shut down the database, take a cold backup, create a script to restore the cold backup and finally startup the database.

This script is RAC aware, it will detect if your database is RAC or a standalone one, if it's a RAC DB the script will ensure that there is no other instances are currently running for the same database before starting the cold backup procedure.

Note: This script is not designed for databases having their files located on ASM  :-)

DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".

The script is part of the database administration bundle, this bundle includes more than 45 scripts covering day to day database administration tasks, you can download the DBA BUNDLE from here: 
[http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html.

	# ##################################################################################################
	# Database COLD Backup Script.
	# [Ver 1.6] # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 22-12-13 # # # # #
	#
	# Modified: 16-05-14 Increased linesize to avoid line breaking.
	#
	#
	# ##################################################################################################
	# ###########
	# Description:
	# ###########
	echo
	echo "==============================================="
	echo "This script Takes a COLD BACKUP for a database."
	echo "==============================================="
	echo
	sleep 1
	# #######################################
	# Excluded INSTANCES:
	# #######################################
	# Here you can mention the instances the script will IGNORE and will NOT run against:
	# Use pipe "|" as a separator between each instance name.
	# e.g. Excluding: -MGMTDB, ASM instances:
	EXL_DB="\-MGMTDB|ASM|APX" #Excluded INSTANCES [Will not get reported offline].
	# ###########################
	# Listing Available Databases:
	# ###########################
	# Count Instance Numbers:
	INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )
	# Exit if No DBs are running:
	if [ $INS_COUNT -eq 0 ]
	then
	echo No Database Running !
	exit
	fi
	# If there is ONLY one DB set it as default without prompt for selection:
	if [ $INS_COUNT -eq 1 ]
	then
	export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	# If there is more than one DB ASK the user to select:
	elif [ $INS_COUNT -gt 1 ]
	then
	echo
	echo "Select the ORACLE_SID:[Enter the number]"
	echo ---------------------
	select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	do
	if [ -z "${REPLY##[0-9]*}" ]
	then
	export ORACLE_SID=$DB_ID
	echo Selected Instance:
	echo
	echo "********"
	echo $DB_ID
	echo "********"
	break
	else
	export ORACLE_SID=${REPLY}
	break
	fi
	done
	fi
	# Exit if the user selected a Non Listed Number:
	if [ -z "${ORACLE_SID}" ]
	then
	echo "You've Entered An INVALID ORACLE_SID"
	exit
	fi
	# #########################
	# Getting ORACLE_HOME
	# #########################
	ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|grep -v "\-MGMTDB"|awk '{print $1}'|tail -1`
	USR_ORA_HOME=`grep -i "^${ORA_USER}:" /etc/passwd| cut -f6 -d ':'|tail -1`
	# SETTING ORATAB:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	export ORATAB
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	export ORATAB
	fi
	# ATTEMPT1: Get ORACLE_HOME using pwdx command:
	PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
	export PMON_PID
	ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
	export ORACLE_HOME
	# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	## If OS is Linux:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	fi
	fi
	# ATTEMPT3: If ORACLE_HOME is in /etc/oratab, use dbhome command:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`dbhome "${ORACLE_SID}"`
	export ORACLE_HOME
	fi
	# ATTEMPT4: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
	export ORACLE_HOME
	fi
	# ATTEMPT5: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' ${USR_ORA_HOME}/.bash_profile ${USR_ORA_HOME}/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
	export ORACLE_HOME
	fi
	# ATTEMPT6: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	if [ -x /usr/bin/locate ]
	then
	ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
	export ORACLE_HOME
	fi
	fi
	# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
	echo "e.g."
	echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
	exit
	fi
	# #############################################
	# Exit if the executer is not the Oracle Owner:
	# #############################################
	CURR_USER=`whoami`
	if [ ${ORA_USER} != ${CURR_USER} ]; then
	echo ""
	echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
	echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
	echo "Script Terminated!"
	exit
	fi
	# Neutralize login.sql file:
	# #########################
	# Existance of login.sql file under current working directory eliminates many functions during the execution of this script:
	if [ -f ./login.sql ]
	then
	mv ./login.sql ./login.sql_NeutralizedBy${SCRIPT_NAME}
	fi
	# ################################
	# Creating Backup & Restore Script:
	# ################################
	echo
	echo "Enter the Backup location: [Full Path]"
	echo "-------------------------"
	while read LOC1
	do
	EXTEN=${ORACLE_SID}_`date '+%F'`
	LOC2=${LOC1}/COLDBACKUP_${EXTEN}
	/bin/mkdir -p ${LOC2}
	if [ ! -d "${LOC2}" ]; then
	echo "Provided Backup Location is NOT Exist/Writable !"
	echo
	echo "Please Provide a VALID Backup Location:"
	echo "---------------------------------------"
	else
	echo
	sleep 1
	echo "Backup Location Validated."
	echo
	break
	fi
	done
	BKPSCRIPT=${LOC2}/Cold_Backup.sh
	RSTSCRIPT=${LOC2}/Restore_Cold_Backup.sh
	BKPSCRIPTLOG=${LOC2}/Cold_Backup.log
	RSTSCRIPTLOG=${LOC2}/Restore_Cold_Backup.log
	# Creating the Cold Backup script:
	echo
	echo "Creating Cold Backup and Cold Restore Scripts ..."
	sleep 1
	cd ${LOC2}
	${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set pages 0
	set termout off echo off feedback off linesize 400;
	spool Cold_Backup.sh
	PROMPT echo "Shutting Down Database $ORACLE_SID ... [Ctrl+c to CANCEL]"
	PROMPT echo "[5]"
	PROMPT sleep 1
	PROMPT echo "[4]"
	PROMPT sleep 1
	PROMPT echo "[3]"
	PROMPT sleep 1
	PROMPT echo "[2]"
	PROMPT sleep 1
	PROMPT echo "[1]"
	PROMPT sleep 1
	PROMPT echo "SHUTTING DOWN NOW ..."
	PROMPT sleep 3
	PROMPT echo ""
	PROMPT ${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	PROMPT shutdown immediate;
	PROMPT EOF
	PROMPT echo "Database SHUTDOWN SUCCESFULLY."
	PROMPT sleep 1
	PROMPT echo
	PROMPT echo "Starting DB FILES copy ..."
	PROMPT echo
	PROMPT echo "************************"
	PROMPT echo "DON'T CLOSE THIS SESSION, Once the BACKUP JOB is DONE, it will return you back to the PROMPT."
	PROMPT echo "************************"
	PROMPT echo
	PROMPT sleep 1
	PROMPT
	PROMPT echo -ne '...'
	select 'cp -vpf '||name||' ${LOC2} ; echo ' ||'-ne '''||'...''' from v\$controlfile
	union
	select 'cp -vpf '||name||' ${LOC2} ; echo ' ||'-ne '''||'...''' from v\$datafile
	union
	select 'cp -vpf '||member||' ${LOC2} ; echo ' ||'-ne '''||'...''' from v\$logfile;
	PROMPT touch ${LOC2}/verifier.log
	PROMPT echo
	spool off
	EOF
	chmod 700 ${BKPSCRIPT}
	# Creating the Restore Script:
	${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set pages 0 termout off echo off feedback off linesize 400;
	spool Restore_Cold_Backup.sh
	PROMPT echo ""
	PROMPT echo "Restoring Database [${ORACLE_SID}] from Cold Backup [${EXTEN}] ..."
	PROMPT sleep 1
	PROMPT echo ""
	PROMPT echo "ARE YOU SURE YOU WANT TO RESTORE DATABASE [${ORACLE_SID}] ? [Y|N] [N]"
	PROMPT while read ANS
	PROMPT do
	PROMPT case \$ANS in
	PROMPT y|Y|yes|YES|Yes) echo "RESTORATION JOB STARTED ...";break ;;;
	PROMPT ""|n|N|no|NO|No) echo "Script Terminated.";exit;break ;;;
	PROMPT *) echo "Please enter a VALID answer [Y|N]" ;;;
	PROMPT esac
	PROMPT done
	PROMPT ORACLE_SID=${ORACLE_SID}
	PROMPT export ORACLE_SID
	PROMPT echo "Shutting Down Database ${ORACLE_SID} ... [Ctrl+c to CANCEL]"
	PROMPT echo "[5]"
	PROMPT sleep 1
	PROMPT echo "[4]"
	PROMPT sleep 1
	PROMPT echo "[3]"
	PROMPT sleep 1
	PROMPT echo "[2]"
	PROMPT sleep 1
	PROMPT echo "[1]"
	PROMPT sleep 1
	PROMPT echo "SHUTTING DOWN NOW ..."
	PROMPT sleep 3
	PROMPT echo ""
	PROMPT ${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	PROMPT shutdown immediate;
	PROMPT EOF
	PROMPT
	PROMPT echo "Restoration Job Started ..."
	PROMPT echo ""
	PROMPT echo -ne '...'
	select 'cp -vpf ${LOC2}/'||SUBSTR(name, INSTR(name,'/', -1,1)+1)||' '||name||' ; echo ' ||'-ne '''||'...''' from v\$controlfile
	union
	select 'cp -vpf ${LOC2}/'||SUBSTR(name, INSTR(name,'/', -1,1)+1)||' '||name||' ; echo ' ||'-ne '''||'...''' from v\$datafile
	union
	select 'cp -vpf ${LOC2}/'||SUBSTR(member, INSTR(member,'/', -1,1)+1)||' '||member||' ; echo ' ||'-ne '''||'...''' from v\$logfile;
	PROMPT echo
	PROMPT ${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	PROMPT startup
	PROMPT PROMPT
	PROMPT PROMPT Adding TEMPFILES TO TEMPORARY TABLESPACES...
	select 'ALTER DATABASE TEMPFILE '''||file_name||''' DROP;' from dba_temp_files;
	select 'ALTER TABLESPACE '||tablespace_name||' ADD TEMPFILE '''||file_name||''' REUSE;' from dba_temp_files;
	PROMPT EOF
	PROMPT VAL1=\$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	PROMPT set heading off echo off feedback off termout off
	PROMPT select status from v\\\$instance;;
	PROMPT EOF
	PROMPT )
	PROMPT VAL2=\`echo \$VAL1 | perl -lpe'\$_ = reverse' |awk '{print \$1}'|perl -lpe'\$_ = reverse'\`
	PROMPT case \${VAL2} in "OPEN")
	PROMPT echo "******************************************************"
	PROMPT echo "Database [$ORACLE_SID] has been Restored Successfully."
	PROMPT echo "Database [$ORACLE_SID] is UP."
	PROMPT echo "******************************************************"
	PROMPT echo
	PROMPT echo ;;;
	PROMPT *)
	PROMPT echo "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
	PROMPT echo "Database [${ORACLE_SID}] CANNOT OPEN !"
	PROMPT echo "Please check the ALERTlOG and investigate."
	PROMPT echo "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
	PROMPT echo
	PROMPT echo ;;;
	PROMPT esac
	spool off
	EOF
	chmod 700 ${RSTSCRIPT}
	if [ ! -f "${BKPSCRIPT}" ]; then
	echo ""
	echo "Backup & Restore Scripts CANNOT be Created."
	echo "Script Failed to Create the Cold Backup job !"
	echo "Please check the Backup Location permissions."
	exit
	fi
	echo
	echo "--------------------------------------------------------"
	echo "Backup & Restore Scripts have been Created Successfully."
	echo "--------------------------------------------------------"
	echo
	echo
	sleep 1
	# ############################
	# Executing Cold Backup Script:
	# ############################
	# Checking if more than one instance is running: [RAC]
	echo "Checking Other OPEN instances [RAC]."
	sleep 1
	VAL3=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set heading off echo off feedback off termout off
	select count(*) from gv\$instance;
	EOF
	)
	VAL4=`echo ${VAL3} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
	if [ ${VAL4} -gt 1 ]
	then
	echo
	echo "WARNING:"
	echo "-------"
	echo "Please SHUTDOWN ALL other RAC INSTANCES EXCEPT the one on the CURRENT Node."
	echo "Then Re-run COLD_BACKUP.sh script Again."
	echo ""
	exit
	fi
	echo
	echo "VERIFIED: Only ONE INSTANCE is RUNNING for Database [${ORACLE_SID}]."
	echo
	sleep 1
	echo "ARE YOU SURE TO SHUTDOWN DATABASE [${ORACLE_SID}] AND START THE COLD BACKUP JOB? [Y|N] [N]"
	while read ANS
	do
	case $ANS in
	y|Y|yes|YES|Yes) echo;echo "COLD BACKUP PROCEDURE STARTED ...";break ;;
	""|n|N|no|NO|No) echo;echo "Script Terminated.";exit;break ;;
	*) echo "Please enter a VALID answer [Y|N]" ;;
	esac
	done
	echo
	echo "Database [${ORACLE_SID}] Will SHUTDOWN within [5 Seconds] ... [To CANCEL press [Ctrl+c]]"
	echo "[5]"
	sleep 1
	echo "[4]"
	sleep 1
	echo "[3]"
	sleep 1
	echo "[2]"
	sleep 1
	echo "[1]"
	sleep 1
	echo ""
	echo "Shutting Down Database [${ORACLE_SID}] ..."
	echo "Backup Files will be Copied Under: [${LOC2}] ..."
	echo
	sleep 1
	exec ${BKPSCRIPT} |tee ${BKPSCRIPTLOG}
	VAL11=${LOC2}/verifier.log
	if [ ! -f ${VAL11} ]
	then
	echo
	echo "xxxxxxxxxxxxxxxxxxx"
	echo "Backup Job Failed !"
	echo "xxxxxxxxxxxxxxxxxxx"
	echo
	else
	echo
	echo "Database Cold Backup is DONE."
	echo "Please Note that TEMP DATAFILES are NOT included in this Backup."
	echo
	echo "****************************************************************"
	echo "COLD BACKUP files located under: ${LOC2}"
	echo "****************************************************************"
	echo
	echo "****************************************************************"
	echo "Later, To restore database ${DB_ID} from this COLD BACKUP,"
	echo "use this script to do that job automatically:"
	echo "${RSTSCRIPT}"
	echo "****************************************************************"
	fi
	rm -f ${VAL11}
	echo
	echo "Do You Want to STARTUP Database [${ORACLE_SID}]? [Y|N] [Y]"
	echo "==========================================="
	while read ANS
	do
	case $ANS in
	""|y|Y|yes|YES|Yes) echo "STARTING UP DATABASE [${ORACLE_SID}] ..."
	${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	STARTUP
	EOF
	echo
	break ;;
	n|N|no|NO|No) echo;echo "Script FINISHED."
	echo "To restore this database from the COLD BACKUP, Run Script: [${RSTSCRIPT}]"
	exit
	break ;;
	*) echo "Please enter a VALID answer [Y|N]" ;;
	esac
	done
	# De-Neutralize login.sql file:
	# ############################
	# If login.sql was renamed during the execution of the script revert it back to its original name:
	if [ -f ./login.sql_NeutralizedBy${SCRIPT_NAME} ]
	then
	mv ./login.sql_NeutralizedBy${SCRIPT_NAME} ./login.sql
	fi
	# #############
	# END OF SCRIPT
	# #############
	# REPORT BUGS to: <mahmmoudadel@hotmail.com>.
	# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
	# DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM:
	# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

view raw COLD_BACKUP hosted with ❤ by GitHub