User Managed Offline Backups

Cold backup is a process of copying the physical files (database files, redolog files and control files) of your database to an secondary storage media (may be a tape, CD, or different disk designated to hold cold backup files).

In my normal practice, I like to schedule an cold backup every Sunday midnight. This again depends on the transaction load on the database, whether customer agree for the operation, etc. The pre-requisite for the cold backup is that the database should be brought down. This may raise a question whether a 7 X 24 database can be a target. Hot Backups are always possible for such scenarios, but I would always put in my PDD (project definition documentation) the risks of not having a cold backups at least once in a while. Finally it is the duty of the DBA to convince the customer. The series of steps are:

• To perform a cold backup first you need to shut down the database with the normal option. If you perform an abort or immediate shutdown you should restart the database in restrict mode and shutdown again with the normal option.
• Then you use the OS backup utility (cp, tar, cpio, or copy) to copy all the physical files associated with the database to a secondary device. It is also advisable to backup your initialization files and archive files.

While taking a off-line backup or online backup, since the blocks (physical files are made of blocks), are physically copied to the secondary media, there is a possibility that if a block is corrupted in the physical files, it gets propagated (copied) to the secondary media, causing difficulty in recovering. t is advised to do DB Verify and other known checks to make sure that the physical files have no problems.

Here is a Shell Script (UNIX) to automate your "Cold Backups". Please test the script throughly before implementing this in you Production Environment (Unix/Linux Flavors)

#! /bin/ksh
 # The script can be tailored to meet your Environment
# Plug in appropriate values between the "<>" to meet your environment
# The following values needs to be replaced
# <ORACLE_SID>
# <ORACLE_HOME>
# <COLD_BACKUP_DIRECTORY>
# <email_address>
# <username>
# <password>
#
trap  2 3
#
# Set up environmental variables - Oracle specific
#
export ORACLE_SID=<ORACLE_SID>
export ORACLE_HOME=<ORACLE_HOME>
export PATH=$PATH:/$ORACLE_HOME/bin
#
# Set up variables to hold directory information
#
COLD=<COLD_BACKUP_DIRECTORY>
#
# Make sure that the Lock File does not exists this confirms that
# the previous cron job has finished its activity of cold backup
#
if [ -f ${COLD}/coldbackup.lock ]; then
  mail <email_address> <<EOF
ERROR - COLD BACKUP For "${ORACLE_SID}" Has Failed
Previous Cold Backup is not completed !
EOF
  exit 1
else
  touch ${COLD}/coldbackup.lock
fi
#
# Make sure that the Database Is up and running
# If not inform the concerned person
#
database_up=`ps -ef | grep pmon_${ORACLE_SID} | grep -v grep | wc -l`
if [ $database_up -eq 0 ]
then
  mail <email_address> <<EOF
ERROR - COLD BACKUP For "${ORACLE_SID}" Has Failed
Database Should be up to take a trace of the data files - Please Investigate!
EOF
  exit 1
fi

sqlplus <username>/<password> < column tablespace_name noprint
column seqn noprint
set pagesize 0
set linesize 132
set feedback off
set sqlprompt ""
Whenever SQLERROR exit FAILURE
SELECT 'compress < '||file_name||'> ${COLD}/' ||
substr(file_name,instr(file_name,'/',-1) + 1) || '.Z'
FROM sys.dba_data_files
UNION
SELECT 'compress < '||member||'> ${COLD}/' ||
substr(member,instr(member,'/',-1) + 1) || '.Z'
from sys.v$logfile
UNION
SELECT
'compress < '||name||'> ${COLD}/' ||
substr(name,instr(name,'/',-1) + 1) || '.Z'
FROM sys.v$controlfile
spool ora_COLD_backup.sh
/
spool off
exit
EOF
#
# Make sure that the Database Is Down Before you initiate the Cold Backup
# If not bring down the database
# If not successful inform the concerned Person
database_up=`ps -ef | grep pmon_${ORACLE_SID} | grep -v grep | wc -l`
if [ $ora_users_num -ne 0 ]
then
  sqlplus /nolog <<END
connect / as sysdba
shutdown
exit
END
fi
#
# Double Check if the Database is brugh down before you start cold backup
# If not inform the concerned person.
#
database_up=`ps -ef | grep pmon_${ORACLE_SID} | grep -v grep | wc -l`
if [ $database_up -ne 0 ]
then
  mail <email_address> <<EOF
ERROR - COLD BACKUP For "${ORACLE_SID}" Has Failed
Database could be brough down - Please Investigate !
EOF
  exit 1
fi
chmod +x ora_COLD_backup.sh
./ora_COLD_backup.sh
rm ${COLD}/coldbackup.lock