Re: Backup of Oracle databases
Date: 1996/07/01
Message-ID: <NttdMDAXPE2xEwuI_at_firstwrd.demon.co.uk>
In article <4q5t84$p60_at_mozart.wg.icl.co.uk>, Simon Fawkes <simon_at_openplus.co.uk> writes
>I need some information about backing up Oracle databases. I want to >perform a file level backup of an oracle database whilst the database >is running. Whilst this backup is being performed there will be no >updates. Is there anyway of locking the database so no updates can be >performed but reads are still allowed. If there is is this a safe way >of performing the backup. > >TIA > >Simon Fawkes >
Hope i'm not too late - just browsing thru - hope this helps ..
#!/bin/sh
trap 'exit' 1 2 3
#
# USAGE ......
#
# Generic hot backup script for UNIX boxes -
# .. Extract from the database all datafiles available for hot backup
# .. Write these files to the destination (usually tape)
#
# WHO WHEN WHAT
# ================================================================
# N.Atkins November 1995 Written.
# N.Atkins December 1995 Count the amount of bytes
written and
# if the next df exceeds 'slimit'
# (currently 5G), call for a new
tape
# ================================================================
# MAIN EVENTS:
# 1) Extract all ts and df identified as available from within the DB.
# 2) Loop through all df and backup to the destination. The df's are
backed up
# in alpha order ..
# 3) switch logfile, archive all logs and extract control file backup.
# 4) Stop auto archive whilst extracting a list of archived redo logs
(ARL).
# 5) Capture a list of ARL.
# 6) Restart auto archive.
# 7) All ARL captured above, written to the destination.
# 8) Control file backup, written to the destination.
# 9) Generate a list of all files on tape.
#10) Loop through and verify that :
# individual ARL exist, if true then delete from disk
#11) Control file backup exists : if true then delete from disk
#12) Report any ts or df left OFFLINE or UNAVAILABLE, or still ACTIVE in
backup
#13) Mail the FILENME to whoever is interested ..
#
#NB: The df are not checked for exist on tape - because basically if the
ARL and# control file backup are present then recovery will be a
certainty ..
# : The script could be enhanced to loop through oratab, but it was
felt that
# as the only change required will be to change the sid, I left this
out ..
# ================================================================
# REQUIREMENTS:
# 1) Solaris 2 or higher
# 2) Oracle Version 7.1 or higher
# 3) All datafiles & Redo logs begin with the SID. I.e. FINX.... .dbf or
# (This is only a 'nice'to have, and will make the reports better ..)
# 4) All archived redo logs are in ORACLE_HOME/dbs/arch ..
# 5) The account this is run from i.e. oracle, has an ops$ login.
# 6) The ops$ account has at least:
# select from V$backup
# V$logfile
# sys.sys.dba_data_files
# sys.sys.tablespaces
# create session system priv ...
# manage tablespace system priv ...
# alter system system priv ...
# alter database system priv ...
# The role RL_DB_ADMIN should exist and be granted to the ops$ account
# ..(Script is fluorine::/usr1/oracle/dba/dbcheck/bld/io_view.sql
# and is required for each database ..)
#
# SYNOPSIS:
# 1) To use on different databases - just change the sid ..
# ================================================================
# ================================================================
ORACLE_SID=FINL ; export ORACLE_SID
#
echo `uname -n`_HOT_${ORACLE_SID}_`date +%d%m%y` begins _at_ `date` .. echo .. setting up the environment ..
#
ORACLE_HOME=/usr2/oracle7/ver713 ; export
ORACLE_HOME
ORACLE_ARCH=${ORACLE_HOME}/dbs/arch ; export
ORACLE_ARCH
ORACLE_TRCE=${ORACLE_HOME}/dbs/trace_files ; export
ORACLE_TRCE
ORACLE_DEST=/dev/rmt/0n ; export
ORACLE_DEST
CONTRL=${ORACLE_ARCH}/${ORACLE_SID}controlfile.backup; export CONTRL
#
# Create filename based on this machine SID and date ..
# .. this will be the destination for all output and sent to the
# .. DBA ..
#
# Clean out files older than ..
#
find ${ORACLE_TRCE}/`uname -n`_HOT_${ORACLE_SID}* -mtime +3 -exec /bin/rm {} \;
#
FILENME=${ORACLE_TRCE}/`uname -n`_HOT_${ORACLE_SID}_`date +%d%m%y`.out HOTTAB=${ORACLE_TRCE}/`uname -n`_HOT_${ORACLE_SID}_`date +%d%m%y`.hotTAB CHKSQL=${ORACLE_TRCE}/`uname -n`_HOT_${ORACLE_SID}_`date +%d%m%y`.SQLPLUSOK
CONTNT=${ORACLE_TRCE}/`uname -n`_HOT_${ORACLE_SID}_`date +%d%m%y`.CONTENTS
#
# Mail section
#
COMP_STAT=999 ; export COMP_STAT
#
TEXTOK="`uname -n`:${ORACLE_SID}:hotbackup_at_`date +%d%m%y`::SUCCESS" export TEXTOK
TEXTFL="`uname -n`:${ORACLE_SID}:hotbackup_at_`date +%d%m%y`::FILE_ERRORS" export TEXTFL
TEXTXX="`uname -n`:${ORACLE_SID}:hotbackup_at_`date +%d%m%y`::FAILED" export TEXTXX
#
MAILOK="/usr/ucb/mail -s ${TEXTOK} atkins_neill/_at_cscpor"; export MAILOK
MAILFL="/usr/ucb/mail -s ${TEXTFL} atkins_neill/_at_cscpor"; export MAILFL
MAILXX="/usr/ucb/mail -s ${TEXTXX} atkins_neill/_at_cscpor"; export MAILXX
#
# Ensure that today's file exist, and good protections , to make testing
# a little easier later and certain ..
#
rm -f ${FILENME} ${HOTTAB} ${CHKSQL} ${CONTNT} touch ${FILENME} ${HOTTAB} ${CHKSQL} ${CONTNT} chmod 755 ${FILENME} ${HOTTAB} ${CHKSQL} ${CONTNT}
#
echo `uname -n`_HOT_${ORACLE_SID}_`date +%d%m%y` begins _at_ `date` .. >${FILENME}
echo >> ${FILENME}
#
# Check we have a .. whatever .. in ORACLE_DEST ..
#
mt -f ${ORACLE_DEST} status
if [ $? -ne 0 ]
then
echo .. cannot write to ${ORACLE_DEST} ..>> ${FILENME}
echo .. cannot write to ${ORACLE_DEST} ..
echo .. process exits _at_ `date` .. >> ${FILENME}
echo .. process exits _at_ `date` ..
${MAILXX} < ${FILENME}
exit
else
echo .. ${ORACLE_DEST} status is o.k. .. >>${FILENME}
echo .. ${ORACLE_DEST} status is o.k. ..
mt -f ${ORACLE_DEST} rewind
fi
#
# Check the Oracle account is valid by logging into sqlplus
#
$ORACLE_HOME/bin/sqlplus / <<END > ${CHKSQL}
END
#
COUNT=`grep -c "Connected to" ${CHKSQL}`
#
if [ $COUNT -eq 0 ]
then
echo .. Unable to log into Oracle >> ${FILENME}
echo .. Unable to log into Oracle
echo .. Suspect DB Down >> ${FILENME}
echo .. Suspect DB Down
echo .. process will exit >> ${FILENME}
echo .. process will exit
${MAILXX} < ${FILENME}
exit
fi
#
# Extract files required for this run and show other worthwhile
information ..
#
$ORACLE_HOME/bin/sqlplus -s / <<EOF >> ${FILENME}
set heading on
set pages 60
set lines 132
set feedback off
set echo off
set termout on
column gr_number format 9999 heading 'Group|Number'
column gr_state format a10 heading 'Group|Status'
column gr_name format a20 heading 'RDO Log Name'
column tablespace format a10 heading 'Tablespace'
column tsp_status format a7 heading 'Tablespace|Status'
column filenm format a27 heading 'Datafile|Name'
column fil_status format a9 heading 'File|Status'
column bck_status format a10 heading 'Backup|Status'
column timestamp format a17 heading 'Backup|Timestamp'
column meg format 9999.99 heading 'Megabytes'
prompt
prompt Check REDO log Members are O.K.
prompt
select group# gr_number,
nvl(status,'O.K.') gr_state,
substr(substr(member,(instr(member,'${ORACLE_SID}'))),1,2
0)
gr_name
from v\$logfile
/
prompt
prompt Check the status of the logical and physical components
prompt
select ts.tablespace_name TABLESPACE,
ts.status TSP_STATUS,
substr(df.file_name,
(instr(df.file_name,'${ORACLE_SID}'))) FILENM,
bytes/1024/1024 MEG,
df.status FIL_STATUS,
bu.status BCK_STATUS,
bu.time TIMESTAMP
from sys.dba_tablespaces ts,
sys.dba_data_files df,
v\$backup bu
where ts.tablespace_name = df.tablespace_name (+)
and df.file_id = bu.file# (+)
order by ts.tablespace_name,
df.file_name
/
prompt
prompt Construct a list of datafiles to be backed up ..
prompt .. remember ..
prompt .. - the tablespace must be ONLINE
prompt .. - the data_file must be AVAILABLE
prompt .. - the backup state must be not active ..
prompt
set heading off
set termout off
set pages 0
spool ${HOTTAB}
select ts.tablespace_name||':'||file_name||':'||bytes/1024/1024
from sys.dba_tablespaces ts,
sys.dba_data_files df,
v\$backup bu
where ts.tablespace_name = df.tablespace_name (+)
and df.file_id = bu.file# (+)
and ts.status = 'ONLINE'
and df.status = 'AVAILABLE'
and bu.status = 'NOT ACTIVE'
order by ts.tablespace_name,
file_name
/
spool off
EOF
#
if [ ! -s ${HOTTAB} ]
then
echo .. HOTTAB file is empty .. no sys.tablespaces to backup ?? >> ${FILENME}
echo .. HOTTAB file is empty .. no sys.tablespaces to backup ??
echo .. process will exit _at_ `date` >> ${FILENME}
echo .. process will exit _at_ `date`
${MAILXX} < ${FILENME}
exit
fi
#
# Set total value of megabytes written to 0 ..
# Set limit to 5.5 Gigabytes per tape - maximum ..
# Set counter to identify number of tapes used ..
#
totval=0
slimit=5000
nooftapes=1
#
cat ${HOTTAB} | while read LINE
do
case $LINE in
*) ORACLE_TS=`echo ${LINE} | awk -F: '{print $1}'`; export
ORACLE_TS
ORACLE_DF=`echo ${LINE} | awk -F: '{print $2}'`; export
ORACLE_DF
ORACLE_SZ=`echo ${LINE} | awk -F: '{print $3}'`; export
ORACLE_SZ
# Check plus is ok ..
rm -f ${CHKSQL}
touch ${CHKSQL}
chmod 755 ${CHKSQL}
$ORACLE_HOME/bin/sqlplus / <<END > ${CHKSQL}
END
#
COUNT=`grep -c "Connected to" ${CHKSQL}`
#
if [ $COUNT -eq 0 ]
then
echo .. Unable to log into Oracle >> ${FILENME}
echo .. Unable to log into Oracle
echo .. .. datafile ${ORACLE_DF} >> ${FILENME}
echo .. .. datafile ${ORACLE_DF}
echo .. process will exit _at_ `date` >> ${FILENME}
echo .. process will exit _at_ `date`
${MAILXX} < ${FILENME}
exit
else
#
if [ `expr ${totval} + ${ORACLE_SZ}` -gt ${slimit} ]
then
echo
echo .. new tape requested at `date` .. >> ${FILENME}
echo .. new tape requested at `date` ..
echo .. .. tape limit set at ${slimit} - megabytes >>
${FILENME}
echo .. .. tape limit set at ${slimit} - megabytes
echo .. .. written on this tape ${totval} - megabytes
>>${FILENME}
echo .. .. written on this tape ${totval} - megabytes
#
# New tape please ..
#
mt -f ${ORACLE_DEST} rewoff
sleep 120
totval=0
nooftapes=`expr ${nooftapes} + 1`
#
# Check we have a .. whatever .. in ORACLE_DEST ..
#
mt -f ${ORACLE_DEST} status
if [ $? -ne 0 ]
then
echo .. cannot write to ${ORACLE_DEST} ..>> ${FILENME}
echo .. cannot write to ${ORACLE_DEST} ..
echo .. process exits _at_ `date` .. >> ${FILENME}
echo .. process exits _at_ `date` ..
${MAILXX} < ${FILENME}
exit
else
echo .. ${ORACLE_DEST} status is o.k. .. >>${FILENME}
echo .. ${ORACLE_DEST} status is o.k. ..
echo .. starting tape number : ${nooftapes} .. >>
${FILENME}
echo .. starting tape number : ${nooftapes} ..
fi
fi
totval=`expr ${totval} + ${ORACLE_SZ}`
echo
echo "Starting datafile : ${ORACLE_DF} .." >>${FILENME}
echo "Starting datafile : ${ORACLE_DF} .."
echo " tablespace : ${ORACLE_TS} .." >>${FILENME}
echo " tablespace : ${ORACLE_TS} .."
echo " size - Meg : ${ORACLE_SZ} .." >>${FILENME}
echo " size - Meg : ${ORACLE_SZ} .."
echo " time is : `date`" >> ${FILENME}
echo " time is : `date`"
echo " tape number: ${nooftapes}" >>${FILENME}
echo " tape number: ${nooftapes}"
fi
#
$ORACLE_HOME/bin/sqlplus -s / << EOF >> ${FILENME}
set termout on
set echo off
set heading off
set pages 0
set verify off
set feedback off
alter tablespace ${ORACLE_TS} begin backup;
host tar cf ${ORACLE_DEST} ${ORACLE_DF}
alter tablespace ${ORACLE_TS} end backup;
EOF
;;
esac
done
#
# All sys.tablespaces and their datafiles have been backed up.
# This next section will take a copy of the controlfile and
# sweep up all the archived redo logs completed during the last
# period ..
#
# Check plus is ok ..
#
rm -f ${CHKSQL}
touch ${CHKSQL}
chmod 755 ${CHKSQL}
$ORACLE_HOME/bin/sqlplus / <<END > ${CHKSQL} END
#
COUNT=`grep -c "Connected to" ${CHKSQL}`
#
if [ $COUNT -eq 0 ]
then
echo .. Unable to log into Oracle >> ${FILENME}
echo .. Unable to log into Oracle
echo .. process will exit _at_ `date` >> ${FILENME}
echo .. process will exit _at_ `date`
${MAILXX} < ${FILENME}
exit
else
echo
echo .. switching logfile and creating controlfile backup ..
fi
#
$ORACLE_HOME/bin/sqlplus -s / << EOF >> ${FILENME}
set termout on
set echo off
set heading off
set pages 0
set verify off
set feedback off
remark
select '=> switch logfile and archive to disk _at_'
||to_char(sysdate,'DD-MON-YY HH24:MI')
from dual;
remark
alter system switch logfile;
alter system archive log all;
select '=> backup controlfile _at_'||to_char(sysdate,'DD-MON-YY
HH24:MI')
from dual;
alter database backup controlfile to
'${CONTRL}' reuse;
select '=> auto archive log stop to gather redo files for tape
_at_'
||to_char(sysdate,'DD-MON-YY HH24:MI')
from dual;
EOF
#
# Into sqldba to stop automatic archive logging :: An exact list of
CURRENT
# archived redo logs is imperative, if future checking is to be relied
on ..
#
echo .. stopping auto archive of logfiles to gather redo list .. >> ${FILENME}
echo .. stopping auto archive of logfiles to gather redo list .. $ORACLE_HOME/bin/sqldba lmode=y << EOF >> ${FILENME} connect internal
archive log stop
exit
EOF
#
# What redo log files are on disk .. Get all of them for safety ..(all
sids)
#
FILES=`ls ${ORACLE_ARCH}/*.arc`; export FILES
#
echo .. redo list gathered ..>> ${FILENME} echo .. redo list gathered ..
#
# O.K. list obtained .. reactivate automatic archiving before the DB
stops !
#
echo .. reactivate automatic archiving ..>> ${FILENME} echo .. reactivate automatic archiving ..
#
$ORACLE_HOME/bin/sqldba lmode=y << EOF >> ${FILENME} connect internal
archive log start
archive log list
exit
EOF
#
# New tape for the redo logs please ..
#
echo .. tablespace backup complete .. loading new tape .. >> ${FILENME} echo .. tablespace backup complete .. loading new tape .. mt -f ${ORACLE_DEST} rewoff
sleep 120
#
# Check we have a .. whatever .. in ORACLE_DEST ..
#
mt -f ${ORACLE_DEST} status
if [ $? -ne 0 ]
then
echo .. cannot write to ${ORACLE_DEST} ..>> ${FILENME}
echo .. cannot write to ${ORACLE_DEST} ..
echo .. process exits _at_ `date` .. >> ${FILENME}
echo .. process exits _at_ `date` ..
${MAILXX} < ${FILENME}
exit
else
echo .. ${ORACLE_DEST} status is o.k. .. >>${FILENME}
echo .. ${ORACLE_DEST} status is o.k. ..
fi
########################################################################
#######
#
# Move structures to tape with the sys.tablespaces ..
#
########################################################################
#######
#
# .. Check controlfile backup exists,then to tape else error ..
#
if [ -s ${CONTRL} ]
then
echo .. controlfile backup located .. writing to tape .. >>
${FILENME}
echo .. controlfile backup located .. writing to tape ..
tar cvf ${ORACLE_DEST} ${CONTRL}
else
echo .. ERROR Cannot locate control file backup ..>> ${FILENME}
echo .. ERROR Cannot locate control file backup ..
echo .. process continues ..
echo .. process continues ..
fi
#
# .. Check redo logs go down to tape ..
#
if [ -n {FILES} ]
then
echo .. archived redo logs located .. writing to tape .. >>
${FILENME}
echo .. archived redo logs located .. writing to tape ..
tar cvf ${ORACLE_DEST} ${FILES}
else
echo .. ERROR Cannot located archived redo logs in var FILES .. >>${FILENME}
echo .. ERROR Cannot located archived redo logs in var FILES ..
echo .. process will exit _at_ `date` >> ${FILENME}
echo .. process will exit _at_ `date`
${MAILXX} < ${FILENME}
exit
fi
# Create a listing of the tapes contents ..
#
if [ $? -eq 0 ]
then
echo .. starting to verify tape contents .. >> ${FILENME}
mt -f ${ORACLE_DEST} rewind
while
[ $? -eq 0 ]
do
tar tf ${ORACLE_DEST} >> ${CONTNT} 2>/dev/null
done
# -s true if exists and > 0
if [ -s ${CONTNT} ]
then
counter=0
while
true
do
counter=`expr $counter + 1`
filename=`echo $FILES | awk '{print $'$counter'}'`
# -n true if length is not zero
if [ -n "${filename}" ]
then
unset test
test=`grep ${filename} ${CONTNT}`
# -n true if length is zero
if [ -n "${test}" ]
then
echo ".. backup success for : ${filename}" >> ${FILENME}
echo ".. backup success for : ${filename}"
rm -f ${filename}
else
COMP_STAT=5; export COMP_STAT
echo ".. ERROR Failed to backup: ${filename} .." >>
${FILENME}
echo ".. ERROR Failed to backup: ${filename} .."
fi
else
echo ".. controlfile backup .." >> ${FILENME}
echo ".. controlfile backup .."
unset test
test=`grep ${CONTRL} ${CONTNT}`
if [ -n "${test}" ]
then
echo ".. backup success for : ${CONTRL}" >> ${FILENME}
echo ".. backup success for : ${CONTRL}"
rm -f ${CONTRL}
else
COMP_STAT=5; export COMP_STAT
echo ".. ERROR Failed to backup: ${CONTRL} .."
>>${FILENME}
echo ".. ERROR Failed to backup: ${CONTRL} .."
fi
#
echo .. rewind and eject .. >>${FILENME}
#
# If COMP_STAT = 999 (default set at top) then all files on
tape
# and O.K. -- If it is 5 then at least one file has failed
..
# Use these values to inform the DBA ..
#
if [ ${COMP_STAT} -eq 999 ]
then
COMP_STAT=0; export COMP_STAT
fi
mt -f ${ORACLE_DEST} rewoff
break;
fi
done
else
echo .. ERROR Could not create tape contents list ..>> ${FILENME}
echo .. ERROR Could not create tape contents list ..
echo .. process exits _at_ `date` .. >> ${FILENME}
echo .. process exits _at_ `date` ..
${MAILXX} < ${FILENME}
exit
fi
else
echo .. ERROR Could not write to tape .. >> ${FILENME}
echo .. ERROR Could not write to tape ..
echo .. process exits _at_ `date` .. >>${FILENME}
echo .. process exits _at_ `date` ..
${MAILXX} < ${FILENME}
exit
fi
########################################################################
#######
# AND FINALLY
#
########################################################################
#######
# Back into SQL to check the database is O.K. ELSE INFORM ..
#
$ORACLE_HOME/bin/sqlplus -s / <<EOF >> ${FILENME}
set feedback off
set echo off
set termout on
column tablespace format a10 heading 'Tablespace' column tsp_status format a7 heading 'Tablespace|Status' column filenm format a20 heading 'Datafile|Name' column fil_status format a9 heading 'File|Status' column bck_status format a10 heading 'Backup|Status' column timestamp format a17 heading 'Backup|Timestamp'
prompt
prompt .. Tablespaces not ONLINE
prompt
select ts.tablespace_name from sys.dba_tablespaces ts
where ts.status != 'ONLINE'
order by ts.tablespace_name
/
prompt
prompt .. Backups still ACTIVE
prompt
select ts.tablespace_name TABLESPACE,
ts.status TSP_STATUS,
substr(df.file_name,
(instr(df.file_name,'${ORACLE_SID}'))) FILENM,
df.status FIL_STATUS,
bu.status BCK_STATUS,
bu.time TIMESTAMP
from sys.dba_tablespaces ts,
sys.dba_data_files df,
v\$backup bu
where ts.tablespace_name = df.tablespace_name (+)
and df.file_id = bu.file# (+)
and bu.status != 'NOT ACTIVE'
order by ts.tablespace_name,
df.file_name
/
EOF
#
echo `uname -n`_HOT_${ORACLE_SID}_`date +%d%m%y` ends _at_ `date` ..
>>${FILENME}
echo `uname -n`_HOT_${ORACLE_SID}_`date +%d%m%y` ends _at_ `date` ..
#
if [ ${COMP_STAT} -eq 0 ]
then
${MAILOK} < ${FILENME}
else if [ ${COMP_STAT} -eq 5 ]
then
${MAILFL} < ${FILENME}
else
${MAILXX} < ${FILENME}
fi
fi
/usr/ucb/mail -s "`uname -n`:${ORACLE_SID}:hotbackup_at_`date +%d%m%y::Notification" vallis_tony/_at_cscpor < ${FILENME} /usr/ucb/mail -s "`uname -n`:${ORACLE_SID}:hotbackup_at_`date +%d%m%y::Notification" braithwaite_sharon/_at_cscpor < ${FILENME}
#
exit
Neill Atkins
First Word Software Limited
Portsmouth
UK.
neill_at_firstwrd.demon.co.uk
Received on Mon Jul 01 1996 - 00:00:00 CEST