#!/bin/sh #File: hot_backup.sh # # Description: This is a script file that performs hot backups. It takes one # parameter: the database name (DBNAME). It needs to run locally on the # database server as it uses local OS calls to copy all of the required files # to a disk. # # Environment Variables: # This script assumes it resides in the oracle user's UNIX home directory # in a sub-directory called BACKUP # DBLIST_DIR - a temporary directory in which text files can be created # # Author: Tim Moss # # Date: 8th December 1995 # # Change History # # Date Changed By Description # 12/04/96 Tim Moss Implement hard ware compression # 16/04/96 Tim Moss Change SQLDBA to SVRMGR due to forthcomming # 1997/06 B. Beaton Adapt to QC Data, Calgary # 1997/12 D. Morgan Adapt to london # 1998/1 Hari.K Adapt to Calgary # 1999/08 Hari.K Adapt to DLT and some clean up # 2002/06 D. Morgan Adapt for Anadarko # 2002/11 D. Morgan Upgraded, single file now # ############################################################################# # Tests to check the number of arguments # if [ X$1 != X ]; then DBNAME=$1 export DBNAME else echo "The database name must be passed as a parameter" exit fi # The database must be up for this to work # ERRCHK=`ps -fu oracle | grep $DBNAME |grep -c ora_` if [ "$ERRCHK" = "0" ]; then echo "Timestamp : `date` :The database is not running, unable to continue" exit fi ############################################################################# # Environment variables to change for specific sites # # Change following line if location is different # Configure settings in $DBNAME.env file to match system # # Set environment . ${HOME}/BACKUP/${DBNAME}.env # The $DBNAME.env file contains the following variables # and exports # # CONNECT_STRING=sys/the_password@EXAMPLE # ORACLE_HOME=/usr/oracle/product/8.1.7 # Oracle home for the instance # BACKUP_HOME=/usr/oracle/BACKUP # # DBLIST_DIR=${BACKUP_HOME}/DB_DIR # directory to handle temporary files # HOTBACKUP_DIR=/data/orabk/LANDPRD/hot # directory for backup files # COMPRESS=YES # Use compress instead of cp #export CONNECT_STRING #export BACKUP_HOME #export DBLIST_DIR #export HOLD_DIR #export ORACLE_HOME #export HOTBACKUP_DIR #export COMPRESS #End contents $DBNAME.env # # End of customized variables ############################################################################## #Define some commands and files SQLPLUS="${ORACLE_HOME}/bin/sqlplus -s ${CONNECT_STRING}" TIME_STAMP=`date +"%d-%b-%Y"` # Test for duplicate filenames # $SQLPLUS <<-EOF set feedback off var numfiles number; BEGIN SELECT count(*) into :numfiles FROM dba_data_files a, dba_data_files b WHERE a.file_name != b.file_name AND SUBSTR(a.file_NAME, INSTR(a.file_name, '/', -1) + 1) = SUBSTR(b.file_NAME, INSTR(b.file_name, '/', -1) + 1); END; / exit :numfiles EOF FILE_ERROR=$? echo "Number of duplicate files $FILE_ERROR" if [ $FILE_ERROR -gt 0 ]; then echo "Aborting, ... $FILE_ERROR duplicate filenames" exit fi #################################################### echo "" echo "Timestamp : `date` :Starting Backup.." echo "Timestamp : `date` : Switching logfile at start of backup.." $SQLPLUS <<-EOF alter system SWITCH LOGFILE; exit; EOF #################################################### # Create a list of tablespaces $SQLPLUS > ${DBLIST_DIR}/${DBNAME}.lis <<-EOF set pages 0 set lines 80 set head off set echo off set verify off set feedback off column tname format a50 select dt.tablespace_name tname from sys.dba_tablespaces dt where dt.status not in ('OFFLINE') and dt.tablespace_name not in ('TEMP'); exit; EOF #################################################### # Create a list of datafiles for each of the tablespaces cat $DBLIST_DIR/${DBNAME}.lis | while read TABLESPACE do $SQLPLUS >$DBLIST_DIR/${TABLESPACE}.lis <<-EOF set pages 0 set lines 80 set head off set echo off set verify off set feedback off define TSPACE=$TABLESPACE column fname format a70 select ddf.file_name fname from sys.dba_data_files ddf where ddf.tablespace_name = upper('&&TSPACE'); exit EOF done # reading of ${DBNAME}.lis #################################################### line_count=`cat $DBLIST_DIR/${DBNAME}.lis |wc -l` total_ts_count=`expr $line_count` cur_ts_count=0 echo #################################################### # Read the file, taking each tablespace off-line, backing up the file(s), # and bringing the tablespace back on line cat $DBLIST_DIR/${DBNAME}.lis | while read TABLESPACE do cur_ts_count=`expr $cur_ts_count + 1` echo "Timestamp : `date` :Backing up tablespace $TABLESPACE - [${cur_ts_count}/${total_ts_count}]" $SQLPLUS <<-EOF alter tablespace $TABLESPACE begin backup; exit EOF echo "" ################################################### # For each datafile in turn, send it to the hot backup directory cat $DBLIST_DIR/${TABLESPACE}.lis | while read DATAFILE do if [ $COMPRESS = YES ]; then BKUP_FILENAME=`basename ${DATAFILE}` compress -c $DATAFILE > ${HOTBACKUP_DIR}/${BKUP_FILENAME}.Z else cp $DATAFILE $HOTBACKUP_DIR fi done # Reading of $TABLESPACE.lis # Delete the datafile rm -f $DBLIST_DIR/${TABLESPACE}.lis # Bring the tablespace back on-line $SQLPLUS <<-EOF alter tablespace $TABLESPACE end backup; exit EOF echo "Timestamp : `date` :Completed back up of tablespace $TABLESPACE\n" done ################################################### # End of tablespace backup # aka Reading of $DBNAME.lis echo "Timestamp : `date` :Backup Miscellaneous files..\n" cp -p ${ORACLE_HOME}/dbs/*${DBNAME}*ora ${HOTBACKUP_DIR} cp -p ${ORACLE_HOME}/network/admin/*ora ${HOTBACKUP_DIR} $SQLPLUS <<-EOF > ${DBLIST_DIR}/misc_file_list.lst set verify OFF; set feedback OFF; set echo OFF; set heading OFF; set pause OFF alter database backup controlfile to '$HOTBACKUP_DIR/${DBNAME}_ctl_file.bckup' reuse; alter database backup controlfile to trace noresetlogs; select name from v\$controlfile; exit; EOF cp `cat ${DBLIST_DIR}/misc_file_list.lst` $HOTBACKUP_DIR cp `grep -l "CREATE CONTROLFILE" ${HOME}/admin/${DBNAME}/udump/*.*` ${HOTBACKUP_DIR} echo "Timestamp : `date` : Switching logfile after backup.." $SQLPLUS <<-EOF alter system SWITCH LOGFILE; exit; EOF ########################################### # Check the contents echo echo "Timestamp : `date` :Listing the contents of the Backup Directory" echo ls -l $HOTBACKUP_DIR/* echo "\nTimestamp : `date` :Hot backup process for $DBNAME completed at `date`"