Tip

Oracle backup and recovery scripts, part I: Cold backup under Unix

This is the first of a series of excerpts from "Oracle DBA automation scripts" by Rajendra Gutta. Click for part two on hot backup under Unix and part

    Requires Free Membership to View

three on backup and recovery under Windows NT. You can purchase the book here.

The backup and recovery scripts discussed here have been tested under Sun Solaris 2.x, HP-UX 11.x and AIX 4.x. The use of a particular command is discussed if there is a difference between these operating systems. They might also work in higher versions of the same operating system. These scripts are written based on the common ground among these three Unix flavors. However, I advise that you test the scripts under your environment for both backup and recovery before using it as a regular backup script. This testing not only gives you confidence in the script, it also gives you an understanding of how to use the script in case a recovery is needed and gives you peace of mind when a crisis hits.

Backup Scripts for HP-UX, Sun Solaris, and AIX

The backup scripts provided here work for HP-UX, Sun Solaris, and AIX with one slight modification. That is, the scripts use v$parameter and v$controlfile to get the user dump destination and control file information. Because in Unix the dollar sign ($) is a special character, you have to precede it with a forward slash (\) that tells Unix to treat it as a regular character. However, this is different in each flavor of Unix. AIX and HP-UX need one forward slash, and the Sun OS needs two forward slashes to make the dollar sign a regular character.

Sun OS 5.x needs two \\

AIX 4.x needs one \

HP-UX 11.x needs one \

These scripts are presented in modular approach. Each script consists of a number of small functions and a main section. Each function is designed to meet a specific objective so that they are easy to understand and modify. These small functions are reusable and can be used in the design of your own scripts. If you want to change a script to fit to your unique needs, you can do so easily in the function where you want the change without affecting the whole script.

After the backup is complete, it is necessary to check the backup status by reviewing log and error files generated by the scripts.

Cold Backup

Cold backup program (see Listing 3.1) performs the cold backup of the database under the Unix environment. The script takes two input parameters—SID and OWNER. SID is the instance to be backed up, and OWNER is the Unix account under which Oracle is running. Figure 3.3 describes the functionality of the cold backup program. Each box represents a corresponding function in the program:

Listing 3.1 coldbackup_ux

#####################################################################
# PROGRAM NAME:coldbackup_ux

# PURPOSE:Performs cold backup of the database. Database

#should be online when you start
# the script. It will shutdown and take a cold backup and brings 
# the database up again


# USAGE:$coldbackup_ux SID OWNER

# INPUT PARAMETERS: SID(Instance name), OWNER(Owner of instance)

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

#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_verify(): Verify that database is online        
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_verify(){
 STATUS=´ps -fu ${ORA_OWNER} |grep -v grep| grep ora_pmon_${ORA_SID}´
 funct_chk_ux_cmd_stat "Database is down for given SID($ORA_SID),
Owner($ORA_OWNER). Can't generate files to be backed up"
} 


#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_verify_shutdown(): Verify that database is down
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_verify_shutdown(){
 STATUS=´ps -fu ${ORA_OWNER} |grep -v grep| grep ora_pmon_${ORA_SID}´
if [ $? = 0 ]; then
  echo "´date´" >> $LOGFILE
  echo "COLDBACKUP_FAIL: ${ORA_SID}, Database is up, can't make 
coldbackup if the database is online."|tee -a ${BACKUPLOGFILE} >> $LOGFILE
  exit 1
fi
}


#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_shutdown_i(): Shutdown database in Immediate mode
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_shutdown_i(){
${ORACLE_HOME}/bin/sqlplus -s << EOF
 / as sysdba
shutdown immediate;
exit
EOF
} 


#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_shutdown_n(): Shutdown database in Normal mode
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

funct_shutdown_n(){
${ORACLE_HOME}/bin/sqlplus -s << EOF
 / as sysdba
shutdown normal;
exit
EOF
}


#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_startup_r(): Startup database in restricted mode
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

funct_startup_r(){
${ORACLE_HOME}/bin/sqlplus -s << EOF
 / as sysdba
startup restrict;
exit
EOF
} 


#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_startup_n(): Startup database in normal mode
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_startup_n(){
${ORACLE_HOME}/bin/sqlplus -s << EOF
/ as sysdba
startup; 
exit
EOF
} 


#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_build_dynfiles(): Identify the files to backup   
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

funct_build_dynfiles(){
# Build datafile list
echo "Building datafile list ." >> ${BACKUPLOGFILE}
datafile_list=´${ORACLE_HOME}/bin/sqlplus -s <<EOF 
/ as sysdba
set heading off  feedback off
select file_name from dba_data_files order by tablespace_name;
exit
EOF´

echo "############### SQL for Temp Files " >> ${RESTOREFILE}
${ORACLE_HOME}/bin/sqlplus -s <<EOF >> ${RESTOREFILE} 
/ as sysdba
set heading off  feedback off
select 'alter tablespace '||tablespace_name||' add tempfile '||''||
file_name||''||' reuse'||';'
from dba_temp_files;
exit
EOF

echo "Backingup controlfile and trace to trace file" >>${BACKUPLOGFILE}
${ORACLE_HOME}/bin/sqlplus -s <<EOF
/ as sysdba
set heading off  feedback off
alter database backup controlfile to '${CONTROLFILE_DIR}/backup_control.ctl';
alter database backup controlfile to trace;
exit
EOF


# Backup trace of control file
CONTROL=´ls -t ${udump_dest}/*.trc |head -1´
if [ ! -z "$CONTROL" ]; then
 grep 'CONTROL' ${CONTROL} 1> /dev/null
 if test $? -eq 0; then
   cp ${CONTROL} ${CONTROLFILE_DIR}/backup_control.sql
 fi
fi 
} 
# Prepare restore file for control file
echo "###### Control File  " >> ${RESTOREFILE}
echo "# Use your own discretion to copy control file, not advised unless 
required..." >> ${RESTOREFILE}
echo " End of backup of control file" >> ${BACKUPLOGFILE}


#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_cold_backup(): Perform cold backup 
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

funct_cold_backup(){

#Copy datafiles to backup location
echo "############### Data Files " >> ${RESTOREFILE}
for datafile in ´echo $datafile_list´
do
     echo "Copying datafile ${datafile} ..." >> ${BACKUPLOGFILE}
     #Prepare a restore file to restore coldbackup in case a 
restore is necessary
     echo cp -p ${DATAFILE_DIR}/´echo $datafile|awk -F"/" '{print $NF}'´
$datafile >> ${RESTOREFILE}
     cp -p ${datafile} ${DATAFILE_DIR} 
     funct_chk_ux_cmd_stat "Failed to copy datafile file to 
backup location"
done

#Copy current init<SID>.ora file to backup directory
echo " Copying current init.ora file" >> ${BACKUPLOGFILE}
cp -p ${init_file} ${INITFILE_DIR}/init${ORA_SID}.ora
funct_chk_ux_cmd_stat "Failed to copy init.ora file to backup location"

echo "################ Init.ora File " >> ${RESTOREFILE}
echo cp -p ${INITFILE_DIR}/init${ORA_SID}.ora ${init_file} 
>> ${RESTOREFILE}
} 


#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_chk_parm(): Check for input parameters
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

funct_chk_parm() {
if [ ${NARG} -ne 2 ]; then
   echo "COLDBACKUP_FAIL: ${ORA_SID}, Not enough arguments passed"
   exit 1
fi
}


#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_chk_bkup_dir(): Create backup directories if not already existing
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

funct_chk_bkup_dir() {

 RESTOREFILE_DIR="${BACKUPDIR}/restorefile_dir"
 BACKUPLOG_DIR="${BACKUPDIR}/backuplog_dir"
 DATAFILE_DIR="${BACKUPDIR}/datafile_dir"
 CONTROLFILE_DIR="${BACKUPDIR}/controlfile_dir"
 REDOLOG_DIR="${BACKUPDIR}/redolog_dir"
 ARCLOG_DIR="${BACKUPDIR}/arclog_dir"
 INITFILE_DIR="${BACKUPDIR}/initfile_dir"

 BACKUPLOGFILE="${BACKUPLOG_DIR}/backup_log_${ORA_SID}"
 RESTOREFILE="${RESTOREFILE_DIR}/restorefile_${ORA_SID}"
 LOGFILE="${LOGDIR}/${ORA_SID}.log"

 if [ ! -d ${RESTOREFILE_DIR} ]; then mkdir -p ${RESTOREFILE_DIR}; fi
 if [ ! -d ${BACKUPLOG_DIR} ]; then mkdir -p ${BACKUPLOG_DIR}; fi
 if [ ! -d ${DATAFILE_DIR} ]; then mkdir -p ${DATAFILE_DIR}; fi
 if [ ! -d ${CONTROLFILE_DIR} ]; then mkdir -p ${CONTROLFILE_DIR}; fi
 if [ ! -d ${REDOLOG_DIR} ]; then mkdir -p ${REDOLOG_DIR}; fi
 if [ ! -d ${ARCLOG_DIR} ]; then mkdir -p ${ARCLOG_DIR}; fi
 if [ ! -d ${INITFILE_DIR} ]; then mkdir -p ${INITFILE_DIR}; fi

 if [ ! -d ${DYN_DIR} ]; then mkdir -p ${DYN_DIR}; fi
 if [ ! -d ${LOGDIR} ]; then mkdir -p ${LOGDIR}; fi

# Remove old backup
rm -f ${RESTOREFILE_DIR}/*
rm -f ${BACKUPLOG_DIR}/*
rm -f ${DATAFILE_DIR}/*
rm -f ${CONTROLFILE_DIR}/*
rm -f ${REDOLOG_DIR}/*
rm -f ${ARCLOG_DIR}/*
rm -f ${INITFILE_DIR}/*

echo "${JOBNAME}: coldbackup of ${ORA_SID} begun on ´date +\"%c\"´" > 
${BACKUPLOGFILE}
} 


#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_get_vars(): Get environment variables
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

funct_get_vars(){

ORA_HOME=´sed /#/d ${ORATABDIR}|grep -i ${ORA_SID}|nawk -F ":" 
'{print $2}'´
ORA_BASE=´echo ${ORA_HOME}|nawk -F "/" '{for (i=2; i<=NF-2; i++) print 
"/"$i}'´
ORACLE_BASE=´echo $ORA_BASE|tr -d " "´
init_file=$ORA_HOME/dbs/init$ORA_SID.ora
#log_arch_dest1=´sed /#/d $init_file|grep -i log_archive_dest|
nawk -F "=" '{print $2}'´
#log_arch_dest=´echo $log_arch_dest1|tr -d "'"|tr -d '"'´

udump_dest=´${ORACLE_HOME}/bin/sqlplus -s <<EOF
/ as sysdba
set heading off  feedback off
select value from v\\$parameter
where name='user_dump_dest';
exit
EOF´

if [ x$ORA_HOME = 'x' ]; then
  echo "COLDBACKUP_FAIL: Can't get ORACLE_HOME from oratab file 
for $ORA_SID"|tee -a ${BACKUPLOGFILE} >> ${LOGFILE}
  exit 1
fi

if [ ! -f $init_file ]; then
  echo "COLDBACKUP_FAIL: init$ORA_SID.ora does not exist in 
ORACLE_HOME/dbs"|tee -a ${BACKUPLOGFILE} >> ${LOGFILE}
  exit 1
fi

if [ x$udump_dest = 'x' ]; then
  echo "COLDBACKUP_FAIL: user_dump_dest not defined in init$ORA_SID.ora"|
tee -a ${BACKUPLOGFILE} >> ${LOGFILE}
  exit 1
fi

ORACLE_HOME=${ORA_HOME}; export ORACLE_HOME
ORACLE_SID=${ORA_SID}; export ORACLE_SID
} 


#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_chk_ux_cmd_stat(): Check the exit status of Unix command
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

funct_chk_ux_cmd_stat() {
 if [ $? != 0 ]; then
      echo "´date´" |tee -a ${BACKUPLOGFILE} >> ${LOGFILE}
      echo "COLDBACKUP_FAIL: ${1} "| tee -a ${BACKUPLOGFILE}
>> ${LOGFILE}
      exit 1
 fi
} 


############################################################
#   MAIN
############################################################

NARG=$#
ORA_SID=$1
ORA_OWNER=$2

# Set environment variables 
BACKUPDIR="/u02/${ORA_SID}/cold" 
ORATABDIR=/etc/oratab
TOOLS="/u01/oracomn/admin/my_dba"

DYN_DIR="${TOOLS}/DYN_FILES"
LOGDIR="${TOOLS}/localog"
JOBNAME="dbcoldbackup"

echo " Starting coldbackup of ${ORA_SID} "

    funct_chk_parm
    funct_chk_bkup_dir
    funct_get_vars
    funct_verify
    funct_build_dynfiles
    funct_shutdown_i
    funct_startup_r
    funct_shutdown_n
    funct_verify_shutdown
    funct_cold_backup
    funct_startup_n

echo "${ORA_SID}, Coldbackup Completed successfully on ´date +\"%c\"´" 
|tee -a ${BACKUPLOGFILE} >> ${LOGFILE}
########    END MAIN ##########################

Cold Backup Script under Unix Checklist

  • In the main function, set correct values for the BACKUPDIR, ORATABDIR, and TOOLS variables highlighted in the cold backup script. The default location of ORATABDIR is different for each flavor of Unix. For information about the default location of the ORATAB file for different flavors of Unix, refer to Chapter 13, "Unix, Windows NT, and Oracle."

  • Check for the existence of SID in oratab file. If not already there, you must add the instance.

  • Check for existence of initSID.ora file in the ORACLE_HOME/dbs directory. If it is in a different location, you can create a soft link to the ORACLE_HOME/dbs directory.

  • Pass SID and OWNER as parameters to the program.

  • The database must be running when you start the program. It gets required information by querying the database and then shuts down the database and performs cold backup.

  • main() The main function defines the variables required and calls the functions to be executed. The variables BACKUPDIR defines the backup location, ORATABDIR defines the oratab file location. oratab files maintain the list of instances and their home directories on the machine. This file is created by default when oracle is installed. If it is not there, you must create one. OWNER is the owner of Oracle software directories. A sample oratab file can be found at the end of the chapter.

  • funct_get_vars() This function gets ORACLE_HOME from the oratab file and USER_DUMP_DEST from the initSID.ora file. The value of USER_DUMP_DEST is used to back up the trace of the control file.

  • funct_build_dynfiles() This function generates a list of files from the database for backup. It also creates SQL statements for temporary files. These temporary files do not need to be backed up, but can be recreated when a restore is performed. These temporary files are session-specific and do not have any content when the database is closed.

  • funct_shutdown_i() This function shuts down the database in Immediate mode, so that any user connected to the database will be disconnected immediately.

  • funct_startup_r() This function starts up the database in Restricted mode, so that no one can connect to the database except users with Restrict privileges.

  • funct_shutdown_n() This function performs a clean shutdown of the database.

  • funct_chk_ux_cmd_stat() This function is used to check the status of Unix commands, especially after copying files to a backup location.

Restore File

A cold backup program creates a restore file that contains the commands to restore the database. This functionality is added based on the fact that a lot of DBAs perform backups but, when it comes to recovery, they will not have any procedures to make the recovery faster. With the restore file, it is easier to restore files to the original location because it has all the commands ready to restore the backup. Otherwise, you need to know the structure of the database—what files are located where. A sample restore file is shown in Listing 3.2.

Listing 3.2 Sample Restore File

######### SQL for Temp Files 
alter tablespace TEMP add tempfile '/u03/oracle/DEV/data/temp03.dbf' reuse;
alter tablespace TEMP add tempfile '/u03/oracle/DEV/data/temp04.dbf' reuse;
######### Data Files 
cp -p /bkp/DEV/cold/datafile_dir/INDX01.dbf  /u02/oracle/DEV/data/INDX01.dbf
cp -p /bkp/DEV/cold/datafile_dir/RBS01.dbf  /u02/oracle/DEV/data/RBS01.dbf
cp -p /bkp/DEV/cold/datafile_dir/SYSTEM01.dbf /u02/oracle/DEV/data/SYSTEM01.dbf
cp -p /bkp/DEV/cold/datafile_dir/TEMP01.dbf  /u02/oracle/DEV/data/TEMP01.dbf
cp -p /bkp/DEV/cold/datafile_dir/USERS01.dbf /u02/oracle/DEV/data/USERS01.dbf
######### Control Files 
cp -p /bkp/DEV/cold/controlfile_dir/cntrl01.dbf 
/u02/oracle/DEV/data/cntrl01.dbf
######### Init.ora File 
cp -p /bkp/DEV/cold/initfile_dir/initDEV.ora /u02/apps/DEV/oracle/8.1.7/
dbs/initDEV.ora

Cold Backup Troubleshooting and Status Check

The important thing here is that the backup log file defined by BACKUPLOGFILE contains detailed information about each step of the backup process. This is a very good place to start investigating why the backup failed or for related errors. This file will also have the start and end time of the backup.

A single line about the success or failure of a backup is appended to SID.log file every time a backup is performed. This file is located under the directory defined by the LOGDIR variable. This file also has the backup completion time. A separate file is created for each instance. This single file maintains the history of performed backups and their status and timing information. The messages for a cold backup are 'COLDBACKUP_FAIL' if a cold backup failed and 'Coldbackup Completed successfully' if a backup completes successfully.

Apart from the BACKUPLOGFILE and SID.log files, it is always good to capture the out-of-the-ordinary errors displayed onscreen if you are running the backup unattended. You can capture these errors by running the command shown next. The same thing can be done for hot backups. This command captures onscreen errors to the coldbackup.log file.

coldbackup_ux SID OWNER 1> coldbackup.log 2>&1 

The following is an excerpt from the SID.log file:

Tue Jul 18 16:48:46 EDT 2000
COLDBACKUP_FAIL: DEV, Failed to copy control file to backup location

BACKUPLOGFILE

Listing 3.3 Sample BACKUPLOGFILE

—dbcoldbackup: coldbackup of DEV begun on Sun May 20 21:15:27 2001
dbcoldbackup: building datafile list .
dbcoldbackup: Building controlfile list

Copying datafile /u02/oracle/DEV/data/INDX01.dbf ...
Copying datafile /u02/oracle/DEV/data/RBS01.dbf ...
Copying datafile /u02/oracle/DEV/data/SYSTEM01.dbf ...
Copying datafile /u02/oracle/DEV/data/TEMP01.dbf ...
Copying datafile /u02/oracle/DEV/data/USERS01.dbf ...
Copying control file /u02/oracle/DEV/data/cntrl01.dbf ...
Copying redolog file /u03/oracle/DEV/data/log01a.dbf ...
Copying redolog file /u03/oracle/DEV/data/log01b.dbf ...
Copying current init.ora file
DEV, Coldbackup Completed successfully on Sun May 20 21:19:38 2001

Reader Feedback

Todd B. writes: I was looking at this tip and one thing that came to mind is it neglects those who use a network backup system. I have created some scripts that test for the presence of the backup daemon. Once the backup daemon is not there, then I restart the database. This enables me to only have as much downtime as necessary to get a complete cold backup. I also have a second script that will check for the presence of the first. This is my "drop dead" script, meaning that at a certain time, regardless if the backups have finished or not, I need to have the database up. This script will kill the first script waiting on the backup daemon, start the database, then e-mail the administrators letting them know that we did not get a good backup.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in June 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.