Forum OpenACS Q&A: Response to Oracle hotbackup scripts?
Posted by
Andrew Piskorski
on 06/17/02 07:27 PM
Ok, here's my export script. Note that it's a little more complicated
than necessary in order to be useful from the command line as well as
from crontab. I just run it from crontab every night with the default
values, but you'll probably want to either change the defaults, or add
some command-line switches in your crontab to override the defaults.
Just run "ora-export.sh -h" to get help on the command
line options.
#!/bin/sh
#
# ora-export.sh
# $Header: /home/cvsroot/unix/usr/local/adm/bin/ora-export.sh,v 1.3 2002/06/17 17:24:02 andy Exp $
#
# by atp@piskorski.com, 2002/05/29
#
# Script to do an Oracle export using a named pipe, gzip, and split,
# as described in _Expert One-on-One Oracle_ by Thomas Kyte,
# pg. 327-328, "Export to an OS Pipe". This lets us avoid exp's
# possible 2 gig file size limit, use less work space on disk by not
# first writing out an un-compressed export file, etc.
# We assume that /etc/profile sets and exports ORACLE_SID,
# ORACLE_HOME, etc.:
. /etc/profile
# Various defult settings, most of which may be overridden on the
# command line:
# This PASSWORD_FILE must be a Bourne shell script which these
# variables like this:
# ORA_USERNAME="username"
# ORA_PASSWORD="password"
# TODO: Putting the password in a file like this keeps us from having
# to hard-code it into multiple script files, but the password STILL
# DOES show up in ps -ef output - not good.
PASSWORD_FILE="/usr/local/adm/bin/ora-system-password.sh"
PUT_IN_DIR="/web/oracle-backups"
MAX_SIZE=600
FULL_P=1
LOG_DATE_FORMAT='+%a %Y-%m-%d %H:%M:%S %Z'
# TODO: For use on multiple machines, may want to move the above
# default settings into a separate configuration file, and source that
# file here. Perhaps just use the same $PASSWORD_FILE.
CMDNAME=`basename $0`
USAGE="Do nightly Oracle export, using exp, gzip, and split.
Usage: ${CMDNAME} [-p FILE] [-d DIRECTORY] [-m SIZE] [-f {0|1}]
[-o USER] [-r LOG_FILE]
Where:
-p : Username/password file to source to set the ORA_USERNAME
and ORA_PASSWORD variables.
Default: $PASSWORD_FILE
-d : Directory where we will put the Oracle export file.
Default: $PUT_IN_DIR
-m : Max size of each dump file chunk, in megabytes.
Default: $MAX_SIZE
-f : Full instance export, 1 for yes, 0 for no.
Default: $FULL_P
-o : Oracle schema owner user name to export. Only needed if -f is no.
-r : Redirect stdout and stdin to file. Set to empty string to
turn off default redirection to the log file.
"
password_file=$PASSWORD_FILE
put_in_dir=$PUT_IN_DIR
max_size=$MAX_SIZE
full_p=$FULL_P
owner=""
redirect_p=0
direct_p=1
compress_p=1
consistent_p=1
while getopts hp:d:m:f:o:r: OPT
do
case $OPT in
h)
echo "$USAGE"
exit
;;
p) password_file=$OPTARG
;;
d) put_in_dir=$OPTARG
;;
m) max_size=$OPTARG
;;
f) full_p=$OPTARG
;;
o) owner=$OPTARG
;;
r) STDOUT=$OPTARG
STDERR=$OPTARG
redirect_p=1
;;
?)
echo 1>&2
echo "ERROR: One or more bad command line options." 1>&2
echo 1>&2
echo "$USAGE" 1>&2
exit 1
;;
esac
done
shift `expr $OPTIND - 1`
ORA_USERNAME=""
ORA_PASSWORD=""
. $password_file
weekday=`date "+%a"`
# TODO: We only have one Oracle instance on this machine. If we add
# another, we'll need to change things to optionally take the
# ORACLE_SID as a command line option, or something like that:
# --atp@piskorski.com, 2002/05/29 11:03 EDT
if [ "$full_p" -eq 1 ]
then
full_opt="full=y"
owner_opt=""
dump_file="${ORACLE_SID}-${ORA_USERNAME}-full-${weekday}.dmp.gz"
log_file="${ORACLE_SID}-${ORA_USERNAME}-full-${weekday}.log"
else
full_opt="full=n"
if [ "$owner" = "" ]
then
owner="$ORA_USERNAME"
fi
owner_opt="owner=$owner"
dump_file="${ORACLE_SID}-${owner}-${weekday}.dmp.gz"
log_file="${ORACLE_SID}-${owner}-${weekday}.log"
fi
# Redirect stdout and stderr to where? Settings STDOUT and STDERR to
# empty string will cause no redirection to occur at all:
if [ "$redirect_p" -ne 1 ]
then
STDOUT=$log_file
STDERR=$log_file
fi
# Truncate the old previous log file:
cat -s '' > $log_file
if [ "$ORA_USERNAME" = "" ]
then
echo "Error: ORA_USERNAME not set." 1>&2 1>>$STDOUT 2>>$STDERR
exit 1
fi
if [ "$ORA_PASSWORD" = "" ]
then
echo "Error: ORA_PASSWORD not set." 1>&2 1>>$STDOUT 2>>$STDERR
exit 1
fi
if [ "$direct_p" -eq 1 ]
then
direct_opt="direct=y"
else
direct_opt="direct=n"
fi
if [ "$compress_p" -eq 1 ]
then
compress_opt="compress=y"
else
compress_opt="compress=n"
fi
if [ "$consistent_p" -eq 1 ]
then
consistent_opt="consistent=y"
else
consistent_opt="consistent=n"
fi
# The process id of this running script:
pid=$$
ora_exp_maxsize="${max_size}m"
pipe="/tmp/${dump_file}-${pid}"
cd $put_in_dir
if [ -p $pipe ]
then
# We've included the PID in the pipe name, so this is pretty
# unlikely:
echo "WARNING: Named pipe $pipe already existed!" 1>&2 1>>$STDOUT 2>>$STDERR
echo `ls -l $pipe` 1>&2 1>>$STDOUT 2>>$STDERR
fi
rm -f $pipe
mknod $pipe p
if [ $? -ne 0 ]
then
echo "ERROR: Failed to create named pipe: $pipe" 1>&2 1>>$STDOUT 2>>$STDERR
exit 1
fi
echo "$CMDNAME export STARTING: `date "$LOG_DATE_FORMAT"`" 1>>$STDOUT 2>>$STDERR
# Note: If this week's export is smaller than last week's, then simply
# overwriting the files might leave one extra split file from LAST
# week, which would mess things up when we re-combine the split files
# in order to do an import. So go and delete the old dump files
# first, rather than simply overwriting them:
rm -f ${dump_file}.*
gzip < $pipe | split -b $ora_exp_maxsize - ${dump_file}. &
exp $ORA_USERNAME/$ORA_PASSWORD file=$pipe $full_opt $owner_opt $consistent_opt $direct_opt $compress_opt 1>>$STDOUT 2>>$STDERR
echo "$CMDNAME export DONE: `date "$LOG_DATE_FORMAT"`" 1>>$STDOUT 2>>$STDERR
# To import the dump files we just created, something like this will work:
#
# cat `ls -1 ${dump_file}.* | sort` | gunzip > $pipe &
# imp $ORA_USERNAME/$ORA_PASSWORD file=$pipe show=y
rm -f $pipe
