Re: Backup Unix Scripts

In article <2kqjhr$l6u_at_bach.seattleu.edu>, ldare_at_bach.seattleu.edu (Larry S. Dare) writes:

> While I am working on produceing some ORACLE7 backup scripts,
> ones which query the database to see what files need to be backup, control logs, redo logs, and the like, and then write the shell script to produce the script.I was wondering if anyone already has some, and if they would post them.  
> I should be posting the one I produce in about a week, so please no mail asking for them.
> 
> Thanks
> Larry Dare
> ldare_at_wdni

I advertized the following scripts a while back on this newsgroup, and got a lot of response. I didn't actually post them to the newsgroup at that time.

These scripts don't actually determine which files need to be backed up. It assumes that you are going to do a complete backup of the volume or directories that contain the oracle datafiles, control files, etc. It does dynamically determine which databases to back up, and whether to do a cold or hot backup.

-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285
------------------------------------------------------------------------------
If you find any bugs, I'd appreciate it if you would let me know what they are.

Each file is separated by a dashed lines that contain the filename.

The files are:

Backup			c shell script that drives the backup
backup_arch		c shell script that backs up the archived redo logs
backup_sys		c shell script that backups all mount points defined to
			our system
end_oracle_backup	c shell script that drives the process of setting
			END BACKUP for all tablespaces in an instance, backing
			up the control file, and switching the log file.
shutdown		c shell script to shut down an instance
startup			c shell script to start up an instance
start_oracle_backup	c shell script that drives the process of setting
			BEGIN BACKUP for all tablespaces in an instance
backup_control.sql	SQL script that backs up the control file to the
			archive location
build_alter_backup.sql	SQL script that builds a script that will set 
			BEGIN or END backup for all tablespaces in an
			instance
switch_logfile.sql	SQL script that switches the logfile

-----------------------------------------------------------
local/csh/Backup
-----------------------------------------------------------


#! /bin/csh -x

#

# Backup script 

# This script drives the backup procedure for all of the disks on the 

# system, including the ORACLE data disks. It does a 'hot' backup, which

# means that ORACLE is still up while the backups are going on.

#

# Assumptions:

#

# 1. All ORACLE databases are defined in /var/opt/oracle/oratab

# 2. The environment variable LLY_ARCH_ROOT points to the directory where

#    ORACLE archive logs are stored. Each database will have its own   

#    subdirectory within this directory.

# 3. The c shell scripts called by this procedure are in

#    the directory defined below as DIR.

# 4. The SQL scripts used are in $ORACLE_BASE/local/sql

# 5. The backup_sys script has been modified to back up all volumes

# 6. The logs will be placed in $ORACLE_BASE/local/log

# 7. This script needs to run as root.

# 8. An OPS$ORACLE account has been created so that the oracle unix id can

#    log into sqlplus using a /


#




# Set up initial directory for scripts



set DIR = $ORACLE_BASE/local/csh



# get instance list from /var/opt/oracle/oratab

# use awk to pull the first word (delimited by a :) that does not begin with


# a # or a space.



set INSTANCE_LIST = `awk -F: 'substr($1,1,1) != " " && substr($1,1,1) != "#" {print $1}' /var/opt/oracle/oratab`

set ARCH_LIST =
set NOARCH_LIST =

foreach INSTANCE ($INSTANCE_LIST)


#

#	check to see if the log writer process is running for this instance.

#	If it is, the instance is assumed to be up. If the instance is down

#	we don't have to do anything special to back it up.

#

#	The output from the ps command will actually include the grep command

#	that follows in the pipeline. So, the first grep will have an extra

#	line. The second grep removes the extra line.


#

    if ("`ps -ef | grep -w ora_smon_$INSTANCE | grep -v grep`" != "") then


#

#	if we got this far, the instance is up. Now check to see if it 

#	archives, by looking for the archive background process. If it does

#	archive, add the instance to the ARCH_LIST. If not, add it to

#	NOARCH_LIST.


#

	if ("`ps -ef | grep -w ora_arch_$INSTANCE | grep -v grep`" != "") then
	   set ARCH_LIST = "$ARCH_LIST $INSTANCE"
	else
	   set NOARCH_LIST = "$NOARCH_LIST $INSTANCE"
	endif
    endif
end
echo "Starting $DIR/Backup at `date`"
echo ""



# Set 'BEGIN BACKUP' for all tablespaces in all instances that do archiving



echo "Running $DIR/start_oracle_backup to prepare ORACLE instances for backup"
echo ""

foreach INSTANCE ($ARCH_LIST)
	su - oracle -c "$DIR/start_oracle_backup $INSTANCE"
end



# Shutdown instances that don't do archiving



echo "Shutting down ORACLE instances that don't archive"
echo ""

foreach INSTANCE ($NOARCH_LIST)
	su - oracle -c "$ORACLE_BASE/local/csh/shutdown $INSTANCE"
end



# Backup filesystems with backup_sys script



echo "Running $DIR/backup_sys to backups filesystems"
echo ""

$DIR/backup_sys



# Set 'END BACKUP' for all tablespaces in all instances



echo "Running $DIR/end_oracle_backup to tell ORACLE that the backup is done"
echo ""

foreach INSTANCE ($ARCH_LIST)
	su - oracle -c "$DIR/end_oracle_backup $INSTANCE"
end

echo "Starting up ORACLE instances that don't archive"
echo ""

foreach INSTANCE ($NOARCH_LIST)
	su - oracle -c "$ORACLE_BASE/local/csh/startup $INSTANCE"
end



# Run procedure to backup archive directories



echo "Running $DIR/backup_arch to backup the ORACLE archive logs"
echo ""

$DIR/backup_arch

unsetenv LLY_ARCH_ROOT



# Finished



echo "Finished at `date`"
echo ""

-----------------------------------------------------------
local/csh/backup_arch
-----------------------------------------------------------


#! /bin/csh

#

#


set LOG = $ORACLE_BASE/local/log/archlog.`date +%m%d%y`
set NRTAPE = /dev/rmt/0n
set TAPE = /dev/rmt/0

set MT = "/usr/bin/mt"
set HOSTNAME = "/usr/ucb/hostname"
set DUMP = "/usr/sbin/ufsdump"

echo "Starting backup of `$LLY_ARCH_ROOT` at `date`" >>& $LOG
echo "" >>& $LOG

echo "Dumping $LLY_ARCH_ROOT filesystem..." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE $LLY_ARCH_ROOT) >>& $LOG

echo "" >>& $LOG

echo "Rewinding tape" >>& $LOG

$MT -t $TAPE rew

echo "Removing old archive files" >>& $LOG

find $LLY_ARCH_ROOT -name 'arch*.dbf' -mtime +3 -print -exec rm {} \; >>& $LOG

echo "Removing old log files" >>& $LOG

find $ORACLE_BASE/local/log -name '*.log' -mtime +14 -print -exec rm {} \; >>& $LOG

echo "Backup completed at `date`" >>& $LOG
-----------------------------------------------------------
local/csh/backup_sys
-----------------------------------------------------------


#! /bin/csh

#

#  Backup_SYS : Simple C-shell script to call dump for the following 

#  		filesystems : / /usr /home /disk1 /disk2 /disk3 /disk4

#

#  Written by : Douglas James

#		October 27, 1993


#


set LOG = $ORACLE_BASE/local/log/dumplog.`date +%m%d%y`
set NRTAPE = /dev/rmt/0n
set TAPE = /dev/rmt/0

set MT = "/usr/bin/mt"
set HOSTNAME = "/usr/ucb/hostname"
set DUMP = "/usr/sbin/ufsdump"

$MT -t $TAPE rew

echo "Starting backup of `$HOSTNAME` at `date`" >>& $LOG
echo "" >>& $LOG

echo "Dumping root filesystem..." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /) >>& $LOG

echo "" >>& $LOG

echo "Dumping usr filesystem.." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /var) >>& $LOG

echo "" >>& $LOG

echo "Dumping usr filesystem.." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /opt) >>& $LOG

echo "" >>& $LOG

echo "Dumping usr filesystem.." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /usr) >>& $LOG

echo "" >>& $LOG

echo "Dumping disk01 filesystem.." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /disk01) >>& $LOG

echo "" >>& $LOG

echo "Dumping disk02 filesystem.." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /disk02) >>& $LOG

echo "" >>& $LOG

echo "Dumping disk03 filesystem.." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /disk03) >>& $LOG

echo "" >>& $LOG

echo "Dumping disk04 filesystem.." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /disk04) >>& $LOG

echo "" >>& $LOG

echo "Dumping disk05 filesystem.." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /disk05) >>& $LOG

echo "" >>& $LOG

echo "Dumping disk06 filesystem.." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /disk06) >>& $LOG

echo "" >>& $LOG

echo "Dumping disk07 filesystem.." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /disk07) >>& $LOG

echo "" >>& $LOG

echo "Dumping disk08 filesystem.." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /disk08) >>& $LOG

echo "" >>& $LOG

echo "Dumping disk09 filesystem.." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE /disk09) >>& $LOG

echo "" >>& $LOG

echo "Backup completed at `date`" >>& $LOG
-----------------------------------------------------------
local/csh/backup_arch~
-----------------------------------------------------------


#! /bin/csh

#

#


set LOG = $ORACLE_BASE/local/log/archlog.`date +%m%d%y`
set NRTAPE = /dev/rmt/0n
set TAPE = /dev/rmt/0

set MT = "/usr/bin/mt"
set HOSTNAME = "/usr/ucb/hostname"
set DUMP = "/usr/sbin/ufsdump"

echo "Starting backup of `$LLY_ARCH_ROOT` at `date`" >>& $LOG
echo "" >>& $LOG

echo "Dumping $LLY_ARCH_ROOT filesystem..." >>& $LOG
($DUMP 0nudsbf 54000 13000 126 $NRTAPE $LLY_ARCH_ROOT) >>& $LOG

echo "" >>& $LOG

echo "Rewinding tape" >>& $LOG

$MT -t $TAPE rew

echo "Removing old archive files" >>& $LOG

find $LLY_ARCH_ROOT -name 'arch*.dbf' -mtime +3 -print -exec rm {} \; >>& $LOG

echo "Backup completed at `date`" >>& $LOG
-----------------------------------------------------------
local/csh/end_oracle_backup
-----------------------------------------------------------


# start_oracle_backup


inst $1
setenv ARCH_LOC $LLY_ARCH_ROOT/${1}/
sqlplus / _at_${ARCH_LOC}alter_${1}_backup end
sqlplus / _at_$ORACLE_BASE/local/sql/backup_control $ARCH_LOC
sqlplus / _at_$ORACLE_BASE/local/sql/switch_logfile
-----------------------------------------------------------
local/csh/start_oracle_backup
-----------------------------------------------------------


# start_oracle_backup


inst $1
setenv ARCH_LOC $LLY_ARCH_ROOT/${1}/
sqlplus / _at_$ORACLE_BASE/local/sql/build_alter_backup $1 $ARCH_LOC
sqlplus / _at_${ARCH_LOC}alter_${1}_backup begin
-----------------------------------------------------------
local/csh/startup
-----------------------------------------------------------


#! /bin/csh


echo ""
echo "Starting up $1"
echo ""
inst $1
sqldba <<EOF
connect internal
startup
EOF
-----------------------------------------------------------
local/csh/shutdown
-----------------------------------------------------------


#! /bin/csh


echo ""
echo "Shutting down $1"
echo ""
inst $1
sqldba <<EOF
connect internal
shutdown immediate
EOF
-----------------------------------------------------------
local/sql/backup_control.sql
-----------------------------------------------------------
define location = &1
alter database backup controlfile to '&location.control.con' reuse;

set pause off
set pagesize 0
set linesize 132
set heading off
set feedback off
set verify off

spool &location.dbfile.lis
select * from dba_data_files;
spool off
exit
-----------------------------------------------------------
local/sql/build_alter_backup.sql
-----------------------------------------------------------
set pause off
set pagesize 0
set linesize 132
set heading off
set feedback off
set verify off
set escape on
define instance = &1
define arch_location = &2

spool &arch_location.alter_&instance._backup.sql
select 'set echo on' from dual;
select 'define action = \&1' from dual;
select 'spool &arch_location.\&action._&instance._backup.err' 
	from dual;
select 'alter tablespace '|| tablespace_name || ' \&action backup;'
	from sys.dba_tablespaces where status = 'ONLINE';
select 'spool off' from dual;
select 'exit' from dual;
spool off;
exit
-----------------------------------------------------------
local/sql/switch_logfile.sql
-----------------------------------------------------------
alter system switch logfile;
exit