User Managed Offline Backups

From Oracle FAQ
Jump to: navigation, search

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 / 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 ./ rm ${COLD}/coldbackup.lock