User Managed Online Backups

If your business requires you to operate 24 Hours a day, 7 days a week, you should take online or hot backups. To use hot backups you should operate the database in ARCHIVELOG mode. As my day to day activities (for all my 7 X 24 systems), I schedule my Hot backups to fire automatically every day. The duration (interval) for the hot backups are picked up based on the low transaction (DML activities) interval for the system under consideration. Hot backups are nothing but putting your tablespaces in backup mode and backing up your online datafiles associated with your tablespaces.

Steps for Hot Backup's

• Check to see if your database is in archive log mode, by issuing

SQL> archive log list;

• If "not", put your database in archive log by following the sequence below, (assuming your database is up and running)

SQL> alter database close;
SQLPLUS>alter database archivelog;
SQL>alter database open;

• Obtain the oldest online log sequence number by issuing

SQL> archive log list;
Database log mode ARCHIVELOG
Automatic archival ENABLED
Archive destination
Oldest online log sequence
Next log sequence to archive
Current log sequence

• You need to keep all archived files starting from the sequence number specified in "Oldest online log sequence". Though recovery will start from "Current log sequence" (SCN where backup has started).

• Make a list of all tablespaces, and associated databafiles in your database

SQL> select tablespace_name,file_name from dba_data_files;

• Set the tablespaces you want to backup in hot backup mode

SQL> ALTER tablespace  BEGIN BACKUP;

• Backup the datafiles associated with the tablespaces, using OS command like "cp" or "tar".

• Set the tablespace back in normal mode

SQL> ALTER TABLESPACE  END BACKUP;

• Repeat steps for all the tablespaces.

• Force a log switch, so that Oracle will create an archived log file

SQL> ALTER SYSTEM SWITCH LOGFILE;

• Backup control file

SQL> ALTER DATABASE BACKUP CONTROLFILE TO filespec;

• Copy the backup controlfile created to an external device.

• Copy the archived log files, starting with the required sequence number to external storage.

Some points worth remembering

• You need to backup all the archived log files, these files are very important to do recovery.
• It is advisable to backup all your tablespaces (except read-only tablespaces), else complete recovery is not possible.
• Backup of online redo log files are not required, as the online log file has the end of backup marker and would cause corruption if used in recovery.
• It is Preferable to start the hot backups at low activity time.
• When hot backups are in progress you "cannot" shutdown the database in NORMAL or IMMEDIATE mode (and it is also not desirable to ABORT).

What happens during Hot Backup's

• When alter tablespace begin backup is issued, the datafiles which belongs to the tablespaces are flagged as hot-backup-in-progress.
• The command would checkpoint all data files that are in the hot backup mode. I.e DBWR is asked to write dirty database block buffers to files, and LGWR or CKPT will update the file headers with the current SCN number.
• An interesting point to pay attention to is the concept of "split blocks". For example let us say that the Oracle block size chosen is (DB_BLOCK_SIZE), by you is 8K and the OS (operating systems) block size is 2K. OS always copies files as OS Blocks (not Oracle block size). When OS is copying the first 4K of the datafile (block), there are chances that the a block (database block buffers are Oracle block size 8K in this case) may be updated in the buffers and may be written to the datafile by DBWR (basically overlaying the original 8K block in the datafile). Now when OS is copying the second 4K of the datafile (block), the two portions of the blocks are "not" consistent. To avoid this blunder this condition, Oracle tracks the blocks which are written to the files by DBWR. If a block belonging to a datafile stays in the database block buffers for a long time, then the block is only logged once (in the online redolog files). But if DBWR flushes the database block buffer to the disk and read it once again from the datafiles, there is a possibility that it may be got into the buffers for Updates. In these situation the before image copy of the block is logged into the online redo log files.
• Logging the before image of a data block to the redo log files before the first change, helps Oracle in reconstructing a fractured block (two portions of block not being consistent, because of variation between OS and Oracle block size). The undocumented parameter _LOG_BLOCKS_DURING_BACKUP which by default is "TRUE" will make sure that the logging of before image happens.
• Due to the conditions discussed above excessive REDO LOG INFORMATION is generated during HOT BACKUPS.

I have put together a Shell Script (UNIX), to automate your "Hot Backups". Please test the script throughly before implementing this in you Production Environment (Unix 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
#
HOT=<HOT_BACKUP_DIRECTORY>
LOG=<BACKGROUND_DUMP_DEST>/alert_${ORACLE_SID}.log

# 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 ${HOT}/hotbackup.lock ]; then
  mail <email_address> <<-EOF
    ERROR - HOT BACKUP For "${ORACLE_SID}" Has Failed
    Previous Hot Backup was not completely Successful !
  EOF
  exit 1
else
  touch ${HOT}/hotbackup.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

# Make sure that the Database Is running in archive log mode
# If not inform the concerned person
#
sqlplus /nolog <<-EOF
  connect / as sysdba
  spool ${HOT}/archive_mode.lst
  archive log list
  spool off
  EXIT
EOF
archive_mode=`grep -i "No Archive Mode*" ${HOT}/archive_mode.lst|grep -v grep|wc -l`
if [ $archive_mode -eq 1 ]
then
  mail <<email_address> <<-EOF
    ERROR - HOT BACKUP For "${ORACLE_SID}" Has Failed
  Database Is Not Running in Archive Log Mode as Desired
  during Hot Backup's - Please put the database in Archive Log Mode and restart!
  EOF
  exit 1
fi

# Find the count of 'alter tablespace .* begin backup' and
# 'alter tablespace .* end backup' in your alert logs
# if both are equal this signifies the previous hot backup is successful
#
if [ -f $LOG ] ; then
  Bgn_Bkp=egrep -ci '^alter tablespace .* begin backup' $LOG
  End_Bkp=egrep -ci '^alter tablespace .* end backup' $LOG
  Diff_Bkp=expr $Bgn_Bkp - $End_Bkp
else
  mail <email_address> <<-EOF
    ERROR - HOT BACKUP For "${ORACLE_SID}" Has Failed
    Could not locate Alert Log - Please Investigate !
  EOF
  exit 1
fi

# Logon to sqlplus - preferably as a DBA
# the sql script, generates a files called ora_HOT_backup.sql
# The file contains all the commands to perform hot backup's
# 1. Backup control file before starting hot backup.
# 2. Put first tablespace online.
# 3. Backup the datafile associated with the tablespaces and compress them (to save space).
# 4. Bring the first tablespace online.
# 5. Repeat steps 2 through 5 till all tablespaces are backed up
#
sqlplus <<username>/<password> <<-EOF
  column tablespace_name noprint
  column seqn noprint
  set pagesize 0
  set linesize 132
  set feedback off
  set sqlprompt ""
  Whenever SQLERROR exit FAILURE
  select chr(1) tablespace_name, -9999 seqn,
  'alter system switch logfile;'
  from dual
  UNION
  select chr(1) tablespace_name, -9998 seqn,
  'alter database backup controlfile to  ||
  '${HOT}/controlfile_${ORACLE_SID}.HOT.full.before reuse;'
  from dual
  UNION
  select tablespace_name, 0,
  'alter tablespace '||tablespace_name||' begin backup;'
  from sys.dba_tablespaces
  where status = 'ONLINE'
  UNION
  select tablespace_name, file_id,
  '!compress < '||file_name||'> ${HOT}/' ||
  substr(file_name,instr(file_name,'/',-1) + 1) || '.Z'
  from sys.dba_data_files
  where status = 'AVAILABLE'
  UNION
  select tablespace_name, 9999,
  'alter tablespace '||tablespace_name||' end backup;'
  from sys.dba_tablespaces
  where status = 'ONLINE'
  UNION
  select chr(255) tablespace_name, 9998 seqn,
  'alter database backup controlfile to  ||
  '${HOT}/controlfile_${ORACLE_SID}.HOT.full.after reuse;'
  from dual
  UNION
  select chr(255) tablespace_name, 9999 seqn,
  'alter system switch logfile;'
  from dual
  ORDER
  BY 1, 2
  spool ora_HOT_backup.sql
  /
  spool off
  start ora_HOT_backup.sql
  exit
EOF

# Find the count of 'alter tablespace .* begin backup' and
# 'alter tablespace .* end backup' in your alert logs
# if both are equal this signifies the previous hot backup is successful
# Else mail the concerned person associated with the backup's.
#
Bgn_Bkp=egrep -ci '^alter tablespace .* begin backup' $LOG
End_Bkp=egrep -ci '^alter tablespace .* end backup' $LOG
if [ $Bgn_Bkp != expr $End_Bkp + $Diff_Bkp ] ; then
  mail <email_address> <<-EOF
    ERROR : HOT BACKUP For "${ORACLE_SID}" Has Failed
    Number of "Begin Backup" statenents does not equal "End Backup"
    Please Investigate Immediately !
  EOF
  exit 1
fi