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 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.
){kind=link}