Backup and Recovery University
Backup and Recovery
Setup
Lets Start with the “readme file”. This gives a high level definition of the structures that need to be configured and how the various scripts are linked together during execution. We will then go through each of the scripts step by step to document exactly how they accomplish a backup.
The README file
Backup Scripts in a UNIX Environment
Various text files are provided for UNIX backups. The main backup script
reads the schedule to determine the kind of backup to be taken. Then it calls
other procedures to actually take a cold backup, a hot backup, or an export of
the database. The scripts should give you an idea on the kind of logic to use
while automating backup procedures.
We have tried not to hardcode any values in the scripts. We have tried
to obtain information from the database whenever possible, or used environment
variables which can be set to suit your needs. You should customize the scripts
for your business needs and test them before using them in a production
environment.
The main procedure is called dbbackup. This procedure always does the
following:
o Sets up the environment variables specific to the database by running the
shell script $TOOLS/db_admin/common/crontab.env.
o Reads the backup schedule from $TOOLs/db_admin/common/dbbackup_sched.dat
file to determine whether to take a hot backup, cold backup or an export
of the database
o Calls the procedure dbbackup_begin to do a hot or cold backup
o Calls the procedure dbexport_begin to do a full database export
Description of dbbackup_begin and dbexport_begin script
The procedure dbbackup_begin does the following:
o Builds a dynamic listing of database files, control files and location of
archive log files for use by hot and cold backups
o Performs hot backup by executing the following steps:
- Each tablespace is put into hot backup mode
- Data files are copied
- Ends hot backup mode for that tablespace
- A log switch is forced before the archive logs are copied
- Only a single control file backup is made
o Performs cold backups by executing the following steps:
- Send warning messages to users notifying them of the impending
database shutdown
- The database is shutdown
- Copies all database files, control files and archive logs
- Optionally, starts the database in restricted mode and performs DBA
tasks, and shuts down again
- Restarts the database, and sends message to users
The procedure dbexport_begin does the following:
- Takes a backup of the previous export file by copying it from the
current location to a backup location
- Deletes the export file in the current location
- Performs the export using the parameter file
$TOOLS/db_admin/db_<dbname>/export.par
List of the scripts files and directories
Related files:
$TOOLS/backup/dbbackup main routine
$TOOLS/backup/dbbackup_begin called by dbbackup
$TOOLS/ /backup/dbexport_begin called by dbbackup
$TOOLS/db_admin/common/dbbackup_sched.dat backup schedule file
$TOOLS/db_admin/common/crontab.env sets environment variables
$TOOLS/db_admin/db_dbname/log directory containing logs
written by the backup and
export scripts
$TOOLS/db_admin/db_dbname/export.par export parameter file
Directory structure requirements
The directory structure that is required by these scripts is:
$TOOLS - top level directory, needs to be set in the
environment before the scripts are executed
$TOOLS/backup – location of backup scripts
$TOOLS/db_admin/common – Administrative files common to all databases
$TOOLS/db_admin/db_dbname – Administrative files specific to each
database
$TOOLS/db_admin/db_dbname/log – Log files
While creating scripts in UNIX always remember the following general rules:
Never use cd in a script; always use absolute paths of files.
Never refer to files with wildcards.
Always verify file copies using the cksum command.
Always check the return status of shell commands to verify their
success or failure.
Always check whether a file or directory exists with the -f option.
Caveats
We assume that all the backup files are kept in the same directory.
You may want to modify this such that the directory structure of the original
database files is maintained. You also need to determine whether you want to
copy all data files to one disk, to more than one disk, or to tape, and
customize the scripts accordingly.
$
Walkthrough of the “dbbackup” script
The first action of the script is to run the crontab.env file to setup the environment variables specific to the passed DBNAME parameter. The crontab.env will need to be updated whenever a change is made to ORACLE_HOME or you want to backup a new database.
# ……………………………………………………..
# set environment
# ……………………………………………………..
# This should define ORACLE_HOME, ORACLE_SID, PATH, BACKUPDIR
# BACKUPDIROLD, EXPORTDIR, EXPORTDIROLD, ARCOLD
#
. $TOOLS/db_admin/common/crontab.env $DBNAME
This section can be used if needed but is not required to perform a backup and is commented out.
# ADMIN_FILE is a file that can be used to run some administrative commands
# on a given database. One such task could be changing a parameter in
# init<sid>.ora. Uncomment the line if required.
#
# ADMIN_FILE=”$TOOLS/db_admin/db_$DBNAME/tools/${DBNAME}_backup_admin.sh”
Now that the database environment variables have been set and any admin scripts have completed, it’s time to set the parameters for the backup script itself. Note that you may need make a modification here to use the appropriate nawk for your operating system.
#
# SCHED_FILE is the file containing backup schedule. A typical line in
# this file is:
# V7PROD Sun hot export /V7PROD/db_management/restrict1.sh
# where V7PROD is name of the database, Sun is for Sunday, hot is the
# backup type, and export is the additinal backup procedure. The last
# column is a special tak you want to perform, for example cleanup
# of some files etc.
#
# JOBNAME is the current script.
# DBBACKUP_BEGIN is the backup script that performs hot or cold backup
# based on the schedule of the day.
# DBEXPORT_BEGIN is the script for exporting the database.
# dbbackup_begin and dbexport_begin are shown in this section.
#
SCHED_FILE=”$TOOLS/db_admin/common/dbbackup_sched.dat”
JOBNAME=”$TOOLS/backup/dbbackup”
DBBACKUP_BEGIN=”$TOOLS/backup/dbbackup_begin”
DBEXPORT_BEGIN=”$TOOLS/backup/dbexport_begin”
TODAY=”`date`”
THIS_DAY=”`date ‘+%a’`”
MSG=”$DBNAME Backup succeeded at `date`”
# This has to be nawk for most paltforms, except HP-UX
AWK=/usr/bin/nawk
# bdf works on HP-UX, df -k on Solaris 2.x, df on SunOS 4.x
# You might also want to add other options, like ‘df -kF ufs’ for Solaris.
DF=”/usr/bin/df -k”
The first backup step is to save a copy of an existing error file. Additional cat commands could be used to save more copies is desired.
# ……………………………………………………..
# begin
# ……………………………………………………..
if [ -f "$ERRFILE" ]; then
#
# Save old log file
#
cat $ERRFILE >> $ERRFILE2
fi
The next action the script takes is to look at the schedule file to determine what if anything should be done during this execution. Using AWK to parse the file the for actions to be performed during this execution
#
# Read backup schedule
#
$AWK -v dbname=$DBNAME -v this_day=$THIS_DAY ‘{
#
# get a record
#
cmd=$0
sizeofarray=split(cmd,rec,” “)
dbname2=rec[1]
day_of_week=rec[2]
backup=rec[3]
export=rec[4]
special_task=rec[5]
if (( dbname2 == dbname ) && ( this_day == day_of_week ))
print ” ” backup ” ” export ” ” special_task
}’ $SCHED_FILE | while read BACKUP EXPORT SPECIAL_TASK
do
Now that we know what type of backup is going to be performed print the parameters to the backup’s logfile and error file for identification purposes.
#
# Print all parameters before beginning backup.
#
PARAMETER_MSG=”
\n…………………………………………………………….
\nBackup Job Parameters:
\n
\nDatabase Name = $DBNAME
\nBackup Type = $BACKUP
\nExport Type = $EXPORT
\nSpecial Task = $SPECIAL_TASK
\n
\nEnvironment Variables:
\nORACLE_HOME = $ORACLE_HOME
\nORACLE_SID = $ORACLE_SID
\nPATH = $PATH
\n…………………………………………………………….
\n
”
echo $PARAMETER_MSG >> $LOGFILE 2> $ERRFILE
echo $PARAMETER_MSG >> $MSGFILE
$DF >> $LOGFILE 2> $ERRFILE
echo ” ” >> $LOGFILE 2> $ERRFILE
If a backup is going to be performed print seperator lines to the logs then call the DBBACKUP_BEGIN file to perform either a hot or cold backup.
#
# Backup
#
if [ "$BACKUP" != "nobackup" ]; then
echo “……………………………..” >> $LOGFILE 2>> $ERRFILE
echo “Begin backup at `date`” >> $LOGFILE 2>> $ERRFILE
echo “……………………………..” >> $LOGFILE 2>> $ERRFILE
$DBBACKUP_BEGIN $DBNAME $BACKUP $SPECIAL_TASK >> $LOGFILE 2>> $ERRFILE
echo “$DBBACKUP_BEGIN $DBNAME $BACKUP $SPECIAL_TASK >> $LOGFILE 2>> $ERRFILE”
echo
echo
echo “……………………………..” >> $LOGFILE 2>> $ERRFILE
echo “End backup at `date`” >> $LOGFILE 2>> $ERRFILE
echo “……………………………..” >> $LOGFILE 2>> $ERRFILE
fi
If an export is going to be performed print seperator lines to the logs then call the DBEXPORT_BEGIN file to perform an export.
#
# Export
#
if [ "$EXPORT" != "noexport" ]; then
echo “……………………………..” >> $LOGFILE 2>> $ERRFILE
echo “Begin export at `date`” >> $LOGFILE 2>> $ERRFILE
echo “……………………………..” >> $LOGFILE 2>> $ERRFILE
$DBEXPORT_BEGIN $DBNAME $EXPORT $SPECIAL_TASK >> $LOGFILE 2>> $ERRFILE
echo “$DBEXPORT_BEGIN $DBNAME $EXPORT $SPECIAL_TASK >> $LOGFILE 2>> $ERRFILE”
echo
echo
echo “……………………………..” >> $LOGFILE 2>> $ERRFILE
echo “End export at `date`” >> $LOGFILE 2>> $ERRFILE
echo “……………………………..” >> $LOGFILE 2>> $ERRFILE
fi
The backup portion of this script is complete. Well not quite, we still need to document the any error that may have occurred.. Additional commands and actions can be added to the “if statement” to trigger whatever other events you may want to happen in the event of a failure.
echo ” ” >> $MSGFILE
echo “Backup log file errors and warnings:” >> $MSGFILE
echo ” ” >> $LOGFILE 2>> $ERRFILE
$DF >> $LOGFILE 2>> $ERRFILE
echo ” ” >> $LOGFILE 2>> $ERRFILE
#
# Errors
#
egrep -e error -e warning -e ORA- -e EXP- -e fatal $LOGFILE | grep -v “No errors.” >> $MSGFILE
ERRCNT=`egrep -e error -e ORA- -e EXP- -e fatal $LOGFILE | grep -c -v “No errors.”`
egrep -e error -e warning -e ORA- -e EXP- -e fatal $ERRFILE | grep -v “Export terminated successfully” >> $MSGFILE
ERRCNT2=`egrep -e error -e ORA- -e EXP- -e fatal $ERRFILE | grep -c -v “Export terminated successfully”`
END_JOB=”`date`”
if [ "$ERRCNT" -gt 0 -o "$ERRCNT2" -gt 0 ]
then MSG=”$DBNAME backup failed at ${END_JOB}”
else MSG=”$DBNAME backup succeeded at ${END_JOB}”
fi
echo ” ” >> $MSGFILE
echo “Log files: ” >> $MSGFILE
echo “Log file=$LOGFILE” >> $MSGFILE
echo “Error file=$ERRFILE” >> $MSGFILE
echo “Message file=$MSGFILE” >> $MSGFILE
This section creates a space report but it is not necessary to run this for the backup to complete successfully. It is prudent however to keep an eye on disk utilizition in some fashion so if your not watching it here, you need to via some other medium.
# Create database Space Report
# The space_report script should contain SQL statements needed to get
# information about the available space in the database. One example:
#
# svrmgrl <<EOF
# connect internal
# spool <file_name>
# select tablespace_name, sum(bytes) from dba_free_space
# group by tablespace_name;
# exit
# EOF
#
# Uncomment the next line if you have set up the script.
# $TOOLS/sql/space_report $DBNAME
Time to let the DBA know that the backup succeded or that it has experienced a problem and failed. I typically only mail the MSGFILE file due to the size of the LOGFILE which contains all of the detail nitty-gritty generated during the script’s execution. If you decide to not mail the logfile it need to be reviewed periodically for any unforseen error’s or messages that may have slipped through the error checking routine. The LOGFILE should always be reviewed whenever any changes are made to any of the backup script files and reports or if the database or operating system has been upgraded or if something of consequance has happened to the Oracle account. (i.e. group designation change…)
#
# Send mail to all DBAs
# Uncomment the next line if you want a mail to be sent to all DBA’s
# at the end of a backup job. We assume that a mailing list or alias
# “${DBNAME}_dbas” exists in your system. If not, modify the sender name
# appropriately. The mail command also may have to be changed depending
# on the platform.
#
# Uncomment the next line to send success/failure mail message
/usr/bin/mailx -s “$MSG” oracle < $MSGFILE
#
# uncomment the next line to mail the logfile
# /usr/bin/mailx -s “LOGFILE” oracle < $LOGFILE
#
# Uncomment the next line if you have set the ADMIN_FILE variable.
# . $ADMIN_FILE
done
That’s it, we’re out of here!
Walkthrough of the “dbbackup_begin” script
Now we get down to the nitty gritty. This single script handles hot or cold backups. From startup to shutdown every details is accommodated. There is a lot of shelling out of the script to execute svrmgr but it is really not as complicated as it’s size would lead you to believe due to generous messages being written to the log files.
Kick things off by verifying the script was called with all necessary parameters then set the booleans and other variables needed by this script.
#! /bin/sh
# name $TOOLS/backup/dbbackup_begin
#
# purpose Performs a hot or cold backup of the database.
# This script is called by dbbackup.
#
# usage $TOOLS/backup/dbbackup_begin <dbname> <backup>
# <special task>
#
# parameters $1=dbname
# $2=backup type
# $3=special task
#
#
# ……………………………………………………..
# set local variables
# ……………………………………………………..
ERRMSG=”
`basename $0`: syntax error:
dbbackup_begin <dbname> <hot|cold|nobackup> <special task>.
”
#
# parameters
#
if [ "$1" ]
then DBNAME=$1
else echo $ERRMSG >&2
exit 1
fi
if [ "$2" ]
then BACKUP=$2
else echo $ERRMSG >&2
exit 1
fi
if [ "$3" ]
then SPECIAL_TASK=$3
else SPECIAL_TASK=” ”
fi
#
# booleans
#
TRUE=0
FALSE=1
SHUTDOWN_FAILED_B=1
RESTART_FAILED_B=1
#
# local variables
#
JOBNAME=”$TOOLS/backup/dbbackup_begin”
JOBNAME_SHORT=”dbbackup_begin”
LOGDIR=”$TOOLS/db_admin/db_$DBNAME/log”
DBBACKUP=”$LOGDIR/datafile_`date ‘+%y%m%d’.log”
CTLFILES=”$LOGDIR/control_`date ‘+%y%m%d’.log”
ARCHLOGS=”$LOGDIR/archlog_`date ‘+%y%m%d’.log”
ALERTLOG=”$LOGDIR/alertlog_`date ‘+%y%m%d’.log”
SPECIALOG=”$LOGDIR/special_`date ‘+%y%m%d’.log”
# Location of these commands is system dependent
WALL=”/usr/sbin/wall”
CKSUM=”/bin/cksum”
CMP=”/bin/cmp”
BANNER=”$TOOLS/db_admin/db_${DBNAME}/banner/status”
CKSUM_SIZE_ERR=”${JOBNAME_SHORT}: fatal error in cksum size comparison.”
CKSUM_VALUE_ERR=”${JOBNAME_SHORT}: fatal error in cksum value comparison.”
CKSUM_VALUE_WAR=”${JOBNAME_SHORT}: warning in cksum value comparison.”
CMP_ERR=”${JOBNAME_SHORT}: fatal error in cmp.”
ARCERR=”${JOBNAME_SHORT}: fatal error in archive log copy.”
THISNODE=`uname -n`
Everything’s setup so here goes. Begin by checking if the database is up and a hot can be done or if it’s down and a cold will be done. Note: If a hot is specified and the database is down the type of backup will be switched to cold. The relevance here is that is if the database is down then leave it down and do a cold backup even if a hot backup was specified.
# ……………………………………………………..
# begin
# ……………………………………………………..
# check for database online
#
STATUS=`ps -fu oracle | grep “ora_[a-z]*_${DBNAME}” `
if [ $? != 0 ]; then
# There are no background processes running, or database is down
if [ "$BACKUP" = "hot" ]; then
# hot backup does not make sense.
echo “${JOBNAME_SHORT}: Database is not online. Switching to cold backup”
echo “${JOBNAME_SHORT}: Database is not online. Switching to cold backup” >&2
BACKUP=”cold”
else
# for cold backup this is fine
echo “${JOBNAME_SHORT}: Database is already down. Continuing.”
fi
else
# since database is already up
if [ "$BACKUP" = "cold" ]; then
#
# broadcast shutdowns, write your own banners
#
$WALL $TOOLS/db_admin/db_${DBNAME}/banner/shutdown_15min.banner
sleep 1500
$WALL $TOOLS/db_admin/db_${DBNAME}/banner/shutdown_5min.banner
sleep 300
$WALL $TOOLS/db_admin/db_${DBNAME}/banner/shutdown_1min.banner
sleep 60
#
# shutdown using appropriate shutdown scripts
# Before the shutdown ensure that the logfile being used is archived
echo “${JOBNAME_SHORT}: Shutting down immediate.”
svrmgrl >/dev/null <<EOF
connect internal
alter system switch logfile;
shutdown immediate;
exit
EOF
Verify that the database is in fact down before continuing with the cold backup. A cold backup performed when the database is up is, in almost all cases, worthless.
STATUS=`ps -fu oracle | grep “ora_[a-z]*_${DBNAME}” `
if [ $? = 0 ]; then
echo “${JOBNAME_SHORT}: Error in database shutdown. Exiting.”
exit
fi
If necessary clean up any zombie sqlnet processes.
#
# kill sqlnet processes
#
echo “${JOBNAME_SHORT}: killing sqlnet v2 processes.”
PROCS=`ps -ef |grep oracle${DBNAME} |grep -v grep |awk ‘{print $2}’`
if [ x$PROCS = x ]; then
echo “${JOBNAME_SHORT}: no processes to kill”
else
echo “${JOBNAME_SHORT}: killing processes $PROCS”
kill $PROCS
fi
echo ” ”
fi
fi
If this is a cold backup and since we shutdown other thna normal, startup the database in restricted mode to perform the necessary recovery.
if [ "$BACKUP" = "cold" ]; then
echo “${JOBNAME_SHORT}: Starting up restrict.”
svrmgrl >/dev/null <<EOF
connect internal
startup mount restrict
exit
EOF
STATUS=`ps -fu oracle | grep “ora_[a-z]*_${DBNAME}” `
if [ $? != 0 ]; then
echo “${JOBNAME_SHORT}: Error in database startup. Exiting.”
exit
fi
fi
Prior to continuing, get the initialization parameters, archive log destination, alert log file name and the names of the control files from the data dictionary. Besides the recovery performed getting this information is the other reason to startup the database prior to performing a cold backup.
# …………………………………………………………….
# begin backup
# …………………………………………………………….
# build database file list
echo “${JOBNAME_SHORT}: building dynamic parameter file.”
if [ "$BACKUP" = "hot" ]; then
svrmgrl >/dev/null <<EOF
connect internal
set termout off;
spool $DBBACKUP
select file_name,tablespace_name from sys.dba_data_files
order by tablespace_name,file_name;
spool off;
exit;
EOF
else
svrmgrl >/dev/null <<EOF
connect internal
set termout off;
spool $DBBACKUP;
select name from v\$datafile;
spool off;
exit;
EOF
fi
svrmgrl >/dev/null <<EOF
connect internal
set termout off
spool $CTLFILES;
select name from v\$controlfile;
spool off;
spool $ARCHLOGS;
select value from v\$parameter where name=’log_archive_dest’;
spool off;
spool $ALERTLOG;
select value from v\$parameter where name=’background_dump_dest’;
spool off;
exit
EOF
# Check the dynamic parameter file size
if [ -z $DBBACKUP ]; then
echo “${JOBNAME_SHORT}: fatal error during backup file creation. Backup aborting.”
return
fi
If this execution is for a cold backup then bring the database down print a message to the logfile and move the alert log to its backup destination. This way all of the backup information in the alert log will commence at the top of the file and is easily located when diagnosing errors. If the database is still up then a cold backup would be invalid so print an error message and set then SHUTDOWN_FAILED_B variable to TRUE to bypass other steps in the script effectively aborting the backup.
#
# shutdown
#
if [ $BACKUP = "cold" ]; then
echo “${JOBNAME_SHORT}: Shutting down immediate.”
svrmgrl >/dev/null <<EOF
connect internal
shutdown immediate
exit
EOF
STATUS=`ps -fu oracle | grep “ora_[a-z]*_${DBNAME}” `
if [ $? = 0 ]; then
echo “${JOBNAME_SHORT}: error in shutdown. Cold backup aborting.”
SHUTDOWN_FAILED_B=”$TRUE”
else
echo “${JOBNAME_SHORT}: Database is shutdown.”
echo “${JOBNAME_SHORT}: move alert log.”
ALERT=`sed -e ‘s/[ ]*$//’ -e ‘/selected\.$/d’ -e ‘/^—.*–$/d’ -e ‘/^VALUE/d’ $ALERTLOG`
mv ${ALERT}/alert_${DBNAME}.log ${ALERT}/alert_${DBNAME}.log_`date ‘+%y%m%d’`
fi
fi
This code moves any existing backup datafiles file to an “old” directory structure and copies the existing alert log file to the current backup directory.
#
# Begin backup
#
if [ $SHUTDOWN_FAILED_B = $FALSE ]; then
echo ” ”
echo “${JOBNAME_SHORT}: Starting $BACKUP backup using $DBBACKUP…”
#
# Move BACKUPDIR to BACKUPDIROLD
# Files from the old backup are moved to a different location
# before starting another backup job.
# If you have backed up these files to tape (recommended), you
# might want to remove these files instead of moving.
#
if [ `ls ${BACKUPDIR} | wc -l` != 0 ]; then
for FILE in ${BACKUPDIR}/*
do
mv $FILE $BACKUPDIROLD
done
fi
#
# Make a backup copy of alert log
#
if [ "$BACKUP" = "cold" ]; then
cp ${ALERT}/alert_${DBNAME}.log_`date ‘+%y%m%d’` ${BACKUPDIR}
fi
#
# check hot backup status
#
if [ $BACKUP = "hot" ]; then
svrmgrl >/dev/null <<EOF
connect internal
select * from v\$backup;
exit
EOF
Fi
Using the sed utility to parse the list of datafiles and their tablespace from the TABLESPACE file spooled earlier. As each tablespace is determined it is altered into backup mode and a loop is begun to copy and compare each of the datafiles in the tablespace.
#
# begin reading dynamic parameter file
#
if [ "$BACKUP" = "hot" ]; then
SED=”sed -e ‘/selected\.$/d’ -e ‘/^—.*–$/d’ -e ‘/^FILE_NAME/d’ $DBBACKUP”
else
SED=”sed -e ‘/selected\.$/d’ -e ‘/^—.*–$/d’ -e ‘/^NAME/d’ $DBBACKUP”
fi
eval $SED | while read FILE TABLESPACE
do
if [ "$BACKUP" = "hot" ]; then
svrmgrl >/dev/null <<EOF
connect internal
alter tablespace $TABLESPACE begin backup;
exit
EOF
fi
Still in the loop of datafiles within the tablespace this is the code that performs the copy and compare.
#
# copy a database file
#
echo “${JOBNAME_SHORT}: cp $FILE $BACKUPDIR”
DATAFILE=`basename $FILE`
# It is possible that database files under different directories have the
# same name. We have to take care that we don’t accidentally overwrite
# a file because of this. One possible way to do this is:
# if [ -f ${BACKUPDIR}/${DATAFILE} ]; then
# NEWNAME=`dirname $FILE | tr / _`
# cp $FILE ${BACKUPDIR}/${DATAFILE}${NEWNAME}
# fi
cp $FILE $BACKUPDIR
STATUS=$?
if [ "$STATUS" != 0 ]; then
echo “${JOBNAME_SHORT}: error during file copy $FILE.”
fi
if [ "$BACKUP" = "hot" ]; then
echo “${JOBNAME_SHORT}: $CKSUM $FILE $BACKUPDIR/$DATAFILE”
$CKSUM $FILE $BACKUPDIR/$DATAFILE
CKSUM_OUT=`$CKSUM $FILE $BACKUPDIR/$DATAFILE`
echo $CKSUM_OUT | read VALUE1 SIZE1 NAME1 VALUE2 SIZE2 NAME2
if [ "$VALUE1" != "$VALUE2" ]; then
echo “$CKSUM_VALUE_WAR”
fi
if [ "$SIZE1" != "$SIZE2" ]; then
echo “$CKSUM_SIZE_ERR”
fi
else
echo “${JOBNAME_SHORT}: $CMP $FILE $BACKUPDIR/$DATAFILE”
$CMP $FILE $BACKUPDIR/$DATAFILE
STATUS=”$?”
if [ "$STATUS" != 0 ]; then
echo “$CMP_ERR”
fi
fi
if [ "$BACKUP" = "hot" ]; then
svrmgrl >/dev/null <<EOF
connect internal
alter tablespace $TABLESPACE end backup;
exit
EOF
fi
done
Check and report the status of the datafiles if this is a hot backup
#
# check hot backup status
#
if [ "$BACKUP" = "hot" ]; then
svrmgrl >/dev/null <<EOF
connect internal
select * from v\$backup
exit
EOF
fi
Backup the control file if this is a hot backup, if a cold backup just copy it.
#
# Backup control files
#
# control files
#
if [ $BACKUP = "hot" ]; then
echo “${JOBNAME_SHORT}: backing up controlfile to ${BACKUPDIR}/${DBNAME}_control01.ctl”
svrmgrl >/dev/null <<EOF
connect internal
alter database backup controlfile to ‘${BACKUPDIR}/control01.ctl’;
exit
EOF
#
else
sed -e ‘/rows selected\.$/d’ -e ‘/^—.*–$/d’ -e ‘/^NAME/d’ $CTLFILES | while read FILE
do
#
# copy a control file
#
echo “${JOBNAME_SHORT}: cp $FILE $BACKUPDIR”
DATAFILE=`basename $FILE`
# It is possible that control files under different directories have the
# same name. We have to take care that we don’t accidentally overwrite
# a file because of this. One possible way to do this is:
# if [ -f ${BACKUPDIR}/${DATAFILE} ]; then
# NEWNAME=`dirname $FILE | tr / _`
# cp $FILE ${BACKUPDIR}/${DATAFILE}${NEWNAME}
# fi
cp $FILE $BACKUPDIR
STATUS=$?
if [ "$STATUS" != 0 ]; then
echo “${JOBNAME_SHORT}: error during file copy $FILE.”
fi
echo “${JOBNAME_SHORT}: $CMP $FILE $BACKUPDIR/$DATAFILE”
$CMP $FILE $BACKUPDIR/$DATAFILE
STATUS=”$?”
if [ "$STATUS" != 0 ]; then
echo “$CMP_ERR”
fi
done
fi
Backup then delete the archive logs (note the purposeful avoidance of backing up the online redo’s)
#
# archive logs
#
# Make sure that you backup archive logs only and not the online
# redo logs.
#
# force a log switch
#
if [ $BACKUP = "hot" ]; then
svrmgrl >/dev/null <<EOF
connect internal
alter system switch logfile;
exit
EOF
# Sleep for sometime, till the log file is copied.
sleep 120
fi
# copy archive logs
#
ARC=`sed -e ‘s/\/arch.log//’ -e ‘s/[ ]*$//’ -e ‘/selected\.$/d’ -e ‘/^—.*–$/d’ -e ‘/^VALUE/d’ $ARCHLOGS`
if [ -f $ARCOLD/* ]; then
echo ” ”
echo “${JOBNAME_SHORT}: Delete previous backup archive logs…”
for I in $ARCOLD/*
do
ls -l $I
ARCNAME=`basename $I`
rm $ARCOLD/$ARCNAME
STATUS=”$?”
if [ "$STATUS" != 0 ]; then
echo “${JOBNAME_SHORT}: error deleting old archive log: $ARCOLD/$ARCNAME”
fi
done
else echo ” ”
echo “${JOBNAME_SHORT}: No old archive logs to delete.”
fi
if [ -f $ARC/* ]; then
echo ” ”
echo “${JOBNAME_SHORT}: Copying archive logs…”
for I in $ARC/*
do
ls -l $I
ARCNAME=`basename $I`
echo “${JOBNAME_SHORT}: cp $ARC/$ARCNAME $ARCOLD”
cp $ARC/$ARCNAME $ARCOLD
STATUS=”$?”
if [ "$STATUS" != 0 ]; then
echo “$ARCERR”
fi
echo “${JOBNAME_SHORT}: $CMP $ARC/$ARCNAME $ARCOLD/$ARCNAME”
$CMP $ARC/$ARCNAME $ARCOLD/$ARCNAME
STATUS=”$?”
if [ "$STATUS" != 0 ]; then
echo “$CMP_ERR”
echo “${JOBNAME_SHORT}: Archive log deletion skipped.”
else echo “${JOBNAME_SHORT}: $CKSUM $ARC/$ARCNAME $ARCOLD/$ARCNAME”
$CKSUM $ARC/$ARCNAME $ARCOLD/$ARCNAME
CKSUM_OUT=`$CKSUM $ARC/$ARCNAME $ARCOLD/$ARCNAME`
echo $CKSUM_OUT | read VALUE1 SIZE1 NAME1 VALUE2 SIZE2 NAME2
if [ "$VALUE1" != "$VALUE2" -o "$SIZE1" != "$SIZE2" ]; then
echo “$DIFFERR”
echo “${JOBNAME_SHORT}: Archive log deletion skipped.”
else rm $ARC/$ARCNAME
if [ $? != 0 ]; then
echo “${JOBNAME_SHORT}: Archive deletion failed.”
fi
fi
fi
done
else echo “${JOBNAME_SHORT}: Found no archives to copy.”
fi
If this is a cold backup then restart the database
if [ $BACKUP = "cold" ]; then
# startup
#
#
# dba mode tasks
#
if [ "$SPECIAL_TASK" != " " ]; then
echo “${JOBNAME_SHORT}: Running DBA mode task…”
svrmgrl >/dev/null <<EOF
connect internal
startup restrict
exit
EOF
STATUS=`ps -fu oracle | grep “ora_[a-z]*_${DBNAME}” `
if [ $? != 0 ]; then
echo “${JOBNAME_SHORT}: error in database startup.” >&2
echo “ Skipping ${SPECIAL_TASK}.” >&2
else
. ${SPECIAL_TASK} > $SPECIALOG 2>&1
svrmgrl >/dev/null <<EOF
connect internal
shutdown
exit
EOF
fi
fi
#
# startup
#
svrmgrl >/dev/null <<EOF
connect internal
startup nomount;
alter database archivelog;
alter database mount;
alter database open;
EOF
STATUS=`ps -fu oracle | grep “ora_[a-z]*_${DBNAME}” `
if [ $? != 0 ]; then
echo “${JOBNAME_SHORT}: error in database startup.”
echo “${JOBNAME_SHORT}: error in database startup.” >&2
RESTART_FAILED_B=0
else
echo “${JOBNAME_SHORT}: Database restarted.”
$WALL $TOOLS/db_admin/db_${DBNAME}/banner/db_online.banner
fi
fi
fi
Walkthrough of the “dbexport_begin” script
#! /bin/sh
# name $TOOLS/backup/dbexport_begin
#
# purpose Performs a database export. This script is
# called by dbbackup. This scripts looks up
# ‘export.par’ file for export parameters.
#
# usage $TOOLS/backup/dbexport_begin <dbname>
# <export> <special task>
#
# parameters $1=dbname
# $2=export
# $3=special task
#
Set up Parameters
# ……………………………………………………..
# local variables
# ……………………………………………………..
ERRMSG=’
$0: syntax error:
dbexport_begin <dbname> <export|noexport> <special task>.
#
# parameters
#
if [ "$1" ]
then DBNAME=$1
export DBNAME
else echo $ERRMSG >&2
exit 1
fi
if [ "$2" ]
then EXPORT=$2
else echo $ERRMSG >&2
exit 1
fi
if [ "$3" ]
then SPECIAL_TASK=$3
else SPECIAL_TASK=” ”
fi
#
# booleans
#
TRUE=0
FALSE=1
SHUTDOWN_FAILED_B=1
RESTART_FAILED_B=1
#
# local variables
#
JOBNAME=”$TOOLS/backup/dbexport_begin”
JOBNAME_SHORT=”dbexport_begin”
CMP=”/bin/cmp”
PARFILE=”$TOOLS/db_admin/db_$DBNAME/export.par”
CMP_ERR=”${JOBNAME_SHORT}: fatal error in cmp.”
EXPERR=”${JOBNAME_SHORT}: fatal error in export file copy.”
Make sure the database is up
# ……………………………………………………..
# begin
# ……………………………………………………..
#
# check for database online
#
STATUS=`ps -fu oracle | grep “ora_[a-z]*_${DBNAME}” `
if [ $? != 0 ]; then
echo “${JOBNAME_SHORT}: error – database not online.”
echo “${JOBNAME_SHORT}: error – database not online.” >&2
echo “${JOBNAME_SHORT}: process listing is to follow…” >&2
echo “${JOBNAME_SHORT}: ps -fu oracle | grep \”ora_[a-z]*_$DBNAME\”" >&2
ps -fu oracle | grep “ora_[a-z]*_${DBNAME}” >&2
echo “${JOBNAME_SHORT}: exiting.” >&2
exit 1
fi
Delete old export then copy current export file to the aged export directory. This way if the export fails we will still have a copy of the last export file.
# …………………………………………………………….
# Export
# …………………………………………………………….
echo ” ”
echo “${JOBNAME_SHORT}: List previous export files…”
ls -l $EXPORTDIR/${DBNAME}.exp*
ls -l $EXPORTDIROLD/${DBNAME}.exp*
#
# delete old export
#
if [ -f $EXPORTDIROLD/${DBNAME}.exp_old ]; then
rm $EXPORTDIROLD/${DBNAME}.exp_old
if [ $? != 0 ]; then
echo “${JOBNAME_SHORT}: error deleting previous export.”
else
echo “${JOBNAME_SHORT}: Deleted previous export file.”
fi
else echo “${JOBNAME_SHORT}: Found no previous export file.”
fi
#
# copy current export to old
#
if [ -f $EXPORTDIR/${DBNAME}.exp ]; then
chmod 644 $EXPORTDIR/${DBNAME}.exp
echo “${JOBNAME_SHORT}: cp $EXPORTDIR/${DBNAME}.exp $EXPORTDIROLD”
cp $EXPORTDIR/${DBNAME}.exp $EXPORTDIROLD
if [ $? != 0 ]; then
echo “$EXPERR”
else echo “${JOBNAME_SHORT}: $CMP $EXPORTDIR/${DBNAME}.exp $EXPORTDIROLD/${DBNAME}.exp”
$CMP $EXPORTDIR/${DBNAME}.exp $EXPORTDIROLD/${DBNAME}.exp
STATUS=”$?”
if [ "$STATUS" != 0 ]; then
echo “$CMP_ERR”
fi
echo “${JOBNAME_SHORT}: mv ${EXPORTDIROLD}/${DBNAME}.exp ${EXPORTDIROLD}/${DBNAME}.exp_old”
mv ${EXPORTDIROLD}/${DBNAME}.exp ${EXPORTDIROLD}/${DBNAME}.exp_old
if [ $? != 0 ]; then
echo “$EXPERR”
fi
rm $EXPORTDIR/${DBNAME}.exp
if [ $? != 0 ]; then
echo “${JOBNAME_SHORT}: error deleting export file.”
exit
fi
fi
else echo “${JOBNAME_SHORT}: Found no current export file to copy.”
fi
Perform a full export with the consistant and compress options.
# Begin export
#
exp system/manager file=$EXPORTDIR/${DBNAME}.exp full=y CONSISTENT=y compress=y
echo ” ”
echo “${JOBNAME_SHORT}: Export complete. ”
ls -l $EXPORTDIR/${DBNAME}.exp*
ls -l $EXPORTDIROLD/${DBNAME}.exp*
$
Sample spooled cold backup log file listing
Backup Job Parameters:
Database Name = ioug
Backup Type = cold
Export Type = noexport
Special Task =
Environment Variables:
ORACLE_HOME = /u01/app/oracle/product/8.0.4
ORACLE_SID = ioug
PATH = /usr/bin:/usr/sbin:/u01/app/oracle/product/8.0.4/bin
…………………………………………………………….
Filesystem kbytes used avail capacity Mounted on
/dev/dsk/c0t3d0s0 915166 720914 193337 79% /
/proc 0 0 0 0% /proc
fd 0 0 0 0% /dev/fd
swap 116472 12 116460 1% /tmp
/dev/dsk/c0t2d0s0 989827 826767 162071 84% /u01
/dev/dsk/c0t4d0s0 979951 693213 285759 71% /u02
……………………………..
Begin backup at Sun Mar 21 01:00:03 EST 1999
……………………………..
dbbackup_begin: Shutting down immediate.
dbbackup_begin: killing sqlnet v2 processes.
dbbackup_begin: no processes to kill
dbbackup_begin: Starting up restrict.
dbbackup_begin: building dynamic parameter file.
dbbackup_begin: Shutting down immediate.
dbbackup_begin: Database is shutdown.
dbbackup_begin: move alert log.
dbbackup_begin: Starting cold backup using /export/home/oracle/tools/db_admin/db_ioug/log/datafile_990321.log…
dbbackup_begin: cp /u01/oradata/ioug/system01.dbf /u02/bkup/db_ioug/backup.dir
dbbackup_begin: /bin/cmp /u01/oradata/ioug/system01.dbf /u02/bkup/db_ioug/backup.dir/system01.dbf
dbbackup_begin: cp /u01/oradata/ioug/rbs01.dbf /u02/bkup/db_ioug/backup.dir
dbbackup_begin: /bin/cmp /u01/oradata/ioug/rbs01.dbf /u02/bkup/db_ioug/backup.dir/rbs01.dbf
dbbackup_begin: cp /u01/oradata/ioug/temp01.dbf /u02/bkup/db_ioug/backup.dir
dbbackup_begin: /bin/cmp /u01/oradata/ioug/temp01.dbf /u02/bkup/db_ioug/backup.dir/temp01.dbf
dbbackup_begin: cp /u01/oradata/ioug/tools01.dbf /u02/bkup/db_ioug/backup.dir
dbbackup_begin: /bin/cmp /u01/oradata/ioug/tools01.dbf /u02/bkup/db_ioug/backup.dir/tools01.dbf
dbbackup_begin: cp /u01/oradata/ioug/users01.dbf /u02/bkup/db_ioug/backup.dir
dbbackup_begin: /bin/cmp /u01/oradata/ioug/users01.dbf /u02/bkup/db_ioug/backup.dir/users01.dbf
dbbackup_begin: cp /u01/oradata/ioug/control01.ctl /u02/bkup/db_ioug/backup.dir
dbbackup_begin: /bin/cmp /u01/oradata/ioug/control01.ctl /u02/bkup/db_ioug/backup.dir/control01.ctl
dbbackup_begin: cp /u01/oradata/ioug/control02.ctl /u02/bkup/db_ioug/backup.dir
dbbackup_begin: /bin/cmp /u01/oradata/ioug/control02.ctl /u02/bkup/db_ioug/backup.dir/control02.ctl
dbbackup_begin: cp /u01/oradata/ioug/control03.ctl /u02/bkup/db_ioug/backup.dir
dbbackup_begin: /bin/cmp /u01/oradata/ioug/control03.ctl /u02/bkup/db_ioug/backup.dir/control03.ctl
dbbackup_begin: Delete previous backup archive logs…
-rw-r—– 1 oracle dba 233472 Mar 20 01:03 /u02/bkup/db_ioug/archive.dir/arch.log1_5.dbf
dbbackup_begin: Copying archive logs…
-rw-r—– 1 oracle dba 445440 Mar 21 01:00 /u01/app/oracle/admin/ioug/arch/arch.log1_6.dbf
dbbackup_begin: cp /u01/app/oracle/admin/ioug/arch/arch.log1_6.dbf /u02/bkup/db_ioug/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/ioug/arch/arch.log1_6.dbf /u02/bkup/db_ioug/archive.dir/arch.log1_6.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/ioug/arch/arch.log1_6.dbf /u02/bkup/db_ioug/archive.dir/arch.log1_6.dbf
2994139733 445440 /u01/app/oracle/admin/ioug/arch/arch.log1_6.dbf
2994139733 445440 /u02/bkup/db_ioug/archive.dir/arch.log1_6.dbf
dbbackup_begin: Database restarted.
……………………………..
End backup at Sun Mar 21 01:03:39 EST 1999
……………………………..
……………………………..
Begin export at Sun Mar 21 01:03:39 EST 1999
……………………………..
dbexport_begin: List previous export files…
-rw-r–r– 1 oracle dba 982016 Mar 18 01:09 /u02/bkup/db_ioug/export.dir/ioug.exp
dbexport_begin: Found no previous export file.
dbexport_begin: cp /u02/bkup/db_ioug/export.dir/ioug.exp /u02/bkup/db_ioug/old_export.dir
dbexport_begin: /bin/cmp /u02/bkup/db_ioug/export.dir/ioug.exp /u02/bkup/db_ioug/old_export.dir/ioug.exp
dbexport_begin: mv /u02/bkup/db_ioug/old_export.dir/ioug.exp /u02/bkup/db_ioug/old_export.dir/ioug.exp_old
dbexport_begin: Export complete.
-rw-r–r– 1 oracle dba 982016 Mar 21 01:07 /u02/bkup/db_ioug/export.dir/ioug.exp
-rw-r–r– 1 oracle dba 982016 Mar 21 01:03 /u02/bkup/db_ioug/old_export.dir/ioug.exp_old
……………………………..
End export at Sun Mar 21 01:07:33 EST 1999
……………………………..
Filesystem kbytes used avail capacity Mounted on
/dev/dsk/c0t3d0s0 915166 720927 193324 79% /
/proc 0 0 0 0% /proc
fd 0 0 0 0% /dev/fd
swap 116696 12 116684 1% /tmp
/dev/dsk/c0t2d0s0 989827 826826 162012 84% /u01
/dev/dsk/c0t4d0s0 979951 694250 284722 71% /u02
Sample spooled hot backup log file listing
…………………………………………………………….
Backup Job Parameters:
Database Name = orcl734
Backup Type = hot
Export Type = export
Special Task =
Environment Variables:
ORACLE_HOME = /u01/app/oracle/product/7.3.4
ORACLE_SID = orcl734
PATH = /usr/bin:/usr/sbin:/u01/app/oracle/product/7.3.4/bin
…………………………………………………………….
Filesystem kbytes used avail capacity Mounted on
/dev/dsk/c0t3d0s0 915166 372809 450847 46% /
/proc 0 0 0 0% /proc
fd 0 0 0 0% /dev/fd
swap 61832 8 61824 1% /tmp
/dev/dsk/c0t2d0s0 989827 852982 37865 96% /u01
/dev/dsk/c0t4d0s0 979951 608586 273375 70% /u02
……………………………..
Begin backup at Wed Feb 17 03:00:03 EST 1999
……………………………..
dbbackup_begin: building dynamic parameter file.
dbbackup_begin: Starting hot backup using /export/home/oracle/tools/db_admin/db_orcl734/log/datafile_990217.log…
dbbackup_begin: cp /u01/oradata/orcl734/rbs01.dbf /u02/bkup/db_orcl734/backup.dir
dbbackup_begin: /bin/cksum /u01/oradata/orcl734/rbs01.dbf /u02/bkup/db_orcl734/backup.dir/rbs01.dbf
2728711926 26218496 /u01/oradata/orcl734/rbs01.dbf
3937276462 26218496 /u02/bkup/db_orcl734/backup.dir/rbs01.dbf
dbbackup_begin: cp /u01/oradata/orcl734/system01.dbf /u02/bkup/db_orcl734/backup.dir
dbbackup_begin: /bin/cksum /u01/oradata/orcl734/system01.dbf /u02/bkup/db_orcl734/backup.dir/system01.dbf
4274272546 41947136 /u01/oradata/orcl734/system01.dbf
1160819795 41947136 /u02/bkup/db_orcl734/backup.dir/system01.dbf
dbbackup_begin: cp /u01/oradata/orcl734/temp01.dbf /u02/bkup/db_orcl734/backup.dir
dbbackup_begin: /bin/cksum /u01/oradata/orcl734/temp01.dbf /u02/bkup/db_orcl734/backup.dir/temp01.dbf
1606438030 10489856 /u01/oradata/orcl734/temp01.dbf
1606438030 10489856 /u02/bkup/db_orcl734/backup.dir/temp01.dbf
dbbackup_begin: cp /u01/oradata/orcl734/tools01.dbf /u02/bkup/db_orcl734/backup.dir
dbbackup_begin: /bin/cksum /u01/oradata/orcl734/tools01.dbf /u02/bkup/db_orcl734/backup.dir/tools01.dbf
3655137326 15732736 /u01/oradata/orcl734/tools01.dbf
3655137326 15732736 /u02/bkup/db_orcl734/backup.dir/tools01.dbf
dbbackup_begin: cp /u01/oradata/orcl734/tools02.dbf /u02/bkup/db_orcl734/backup.dir
dbbackup_begin: /bin/cksum /u01/oradata/orcl734/tools02.dbf /u02/bkup/db_orcl734/backup.dir/tools02.dbf
3893844661 1052672 /u01/oradata/orcl734/tools02.dbf
3893844661 1052672 /u02/bkup/db_orcl734/backup.dir/tools02.dbf
dbbackup_begin: cp /u01/oradata/orcl734/users01.dbf /u02/bkup/db_orcl734/backup.dir
dbbackup_begin: /bin/cksum /u01/oradata/orcl734/users01.dbf /u02/bkup/db_orcl734/backup.dir/users01.dbf
3342356124 1052672 /u01/oradata/orcl734/users01.dbf
3342356124 1052672 /u02/bkup/db_orcl734/backup.dir/users01.dbf
dbbackup_begin: backing up controlfile to /u02/bkup/db_orcl734/backup.dir/orcl734_control01.ctl
dbbackup_begin: Delete previous backup archive logs…
-rw-r—– 1 oracle dba 512512 Feb 16 03:11 /u02/bkup/db_orcl734/archive.dir/arch.log1_836.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 03:11 /u02/bkup/db_orcl734/archive.dir/arch.log1_837.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 03:12 /u02/bkup/db_orcl734/archive.dir/arch.log1_838.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 03:12 /u02/bkup/db_orcl734/archive.dir/arch.log1_839.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 03:12 /u02/bkup/db_orcl734/archive.dir/arch.log1_840.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 03:12 /u02/bkup/db_orcl734/archive.dir/arch.log1_841.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 03:12 /u02/bkup/db_orcl734/archive.dir/arch.log1_842.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 03:12 /u02/bkup/db_orcl734/archive.dir/arch.log1_843.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 03:12 /u02/bkup/db_orcl734/archive.dir/arch.log1_844.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 03:12 /u02/bkup/db_orcl734/archive.dir/arch.log1_845.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 03:12 /u02/bkup/db_orcl734/archive.dir/arch.log1_846.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 03:12 /u02/bkup/db_orcl734/archive.dir/arch.log1_847.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 03:12 /u02/bkup/db_orcl734/archive.dir/arch.log1_848.dbf
dbbackup_begin: Copying archive logs…
-rw-r—– 1 oracle dba 512512 Feb 16 04:59 /u01/app/oracle/admin/orcl734/arch/arch.log1_849.dbf
dbbackup_begin: cp /u01/app/oracle/admin/orcl734/arch/arch.log1_849.dbf /u02/bkup/db_orcl734/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/orcl734/arch/arch.log1_849.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_849.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/orcl734/arch/arch.log1_849.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_849.dbf
3670464749 512512 /u01/app/oracle/admin/orcl734/arch/arch.log1_849.dbf
3670464749 512512 /u02/bkup/db_orcl734/archive.dir/arch.log1_849.dbf
-rw-r—– 1 oracle dba 508928 Feb 16 05:03 /u01/app/oracle/admin/orcl734/arch/arch.log1_850.dbf
dbbackup_begin: cp /u01/app/oracle/admin/orcl734/arch/arch.log1_850.dbf /u02/bkup/db_orcl734/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/orcl734/arch/arch.log1_850.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_850.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/orcl734/arch/arch.log1_850.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_850.dbf
898718538 508928 /u01/app/oracle/admin/orcl734/arch/arch.log1_850.dbf
898718538 508928 /u02/bkup/db_orcl734/archive.dir/arch.log1_850.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 06:41 /u01/app/oracle/admin/orcl734/arch/arch.log1_851.dbf
dbbackup_begin: cp /u01/app/oracle/admin/orcl734/arch/arch.log1_851.dbf /u02/bkup/db_orcl734/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/orcl734/arch/arch.log1_851.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_851.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/orcl734/arch/arch.log1_851.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_851.dbf
3242124285 512512 /u01/app/oracle/admin/orcl734/arch/arch.log1_851.dbf
3242124285 512512 /u02/bkup/db_orcl734/archive.dir/arch.log1_851.dbf
-rw-r—– 1 oracle dba 470016 Feb 16 09:33 /u01/app/oracle/admin/orcl734/arch/arch.log1_852.dbf
dbbackup_begin: cp /u01/app/oracle/admin/orcl734/arch/arch.log1_852.dbf /u02/bkup/db_orcl734/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/orcl734/arch/arch.log1_852.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_852.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/orcl734/arch/arch.log1_852.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_852.dbf
1621112692 470016 /u01/app/oracle/admin/orcl734/arch/arch.log1_852.dbf
1621112692 470016 /u02/bkup/db_orcl734/archive.dir/arch.log1_852.dbf
-rw-r—– 1 oracle dba 70656 Feb 16 10:16 /u01/app/oracle/admin/orcl734/arch/arch.log1_853.dbf
dbbackup_begin: cp /u01/app/oracle/admin/orcl734/arch/arch.log1_853.dbf /u02/bkup/db_orcl734/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/orcl734/arch/arch.log1_853.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_853.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/orcl734/arch/arch.log1_853.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_853.dbf
124012636 70656 /u01/app/oracle/admin/orcl734/arch/arch.log1_853.dbf
124012636 70656 /u02/bkup/db_orcl734/archive.dir/arch.log1_853.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 16:12 /u01/app/oracle/admin/orcl734/arch/arch.log1_854.dbf
dbbackup_begin: cp /u01/app/oracle/admin/orcl734/arch/arch.log1_854.dbf /u02/bkup/db_orcl734/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/orcl734/arch/arch.log1_854.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_854.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/orcl734/arch/arch.log1_854.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_854.dbf
4001873873 512512 /u01/app/oracle/admin/orcl734/arch/arch.log1_854.dbf
4001873873 512512 /u02/bkup/db_orcl734/archive.dir/arch.log1_854.dbf
-rw-r—– 1 oracle dba 52736 Feb 16 17:13 /u01/app/oracle/admin/orcl734/arch/arch.log1_855.dbf
dbbackup_begin: cp /u01/app/oracle/admin/orcl734/arch/arch.log1_855.dbf /u02/bkup/db_orcl734/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/orcl734/arch/arch.log1_855.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_855.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/orcl734/arch/arch.log1_855.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_855.dbf
2185153682 52736 /u01/app/oracle/admin/orcl734/arch/arch.log1_855.dbf
2185153682 52736 /u02/bkup/db_orcl734/archive.dir/arch.log1_855.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 21:01 /u01/app/oracle/admin/orcl734/arch/arch.log1_856.dbf
dbbackup_begin: cp /u01/app/oracle/admin/orcl734/arch/arch.log1_856.dbf /u02/bkup/db_orcl734/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/orcl734/arch/arch.log1_856.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_856.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/orcl734/arch/arch.log1_856.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_856.dbf
146707822 512512 /u01/app/oracle/admin/orcl734/arch/arch.log1_856.dbf
146707822 512512 /u02/bkup/db_orcl734/archive.dir/arch.log1_856.dbf
-rw-r—– 1 oracle dba 512512 Feb 16 23:05 /u01/app/oracle/admin/orcl734/arch/arch.log1_857.dbf
dbbackup_begin: cp /u01/app/oracle/admin/orcl734/arch/arch.log1_857.dbf /u02/bkup/db_orcl734/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/orcl734/arch/arch.log1_857.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_857.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/orcl734/arch/arch.log1_857.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_857.dbf
357062744 512512 /u01/app/oracle/admin/orcl734/arch/arch.log1_857.dbf
357062744 512512 /u02/bkup/db_orcl734/archive.dir/arch.log1_857.dbf
-rw-r—– 1 oracle dba 512512 Feb 17 01:09 /u01/app/oracle/admin/orcl734/arch/arch.log1_858.dbf
dbbackup_begin: cp /u01/app/oracle/admin/orcl734/arch/arch.log1_858.dbf /u02/bkup/db_orcl734/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/orcl734/arch/arch.log1_858.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_858.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/orcl734/arch/arch.log1_858.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_858.dbf
66978209 512512 /u01/app/oracle/admin/orcl734/arch/arch.log1_858.dbf
66978209 512512 /u02/bkup/db_orcl734/archive.dir/arch.log1_858.dbf
-rw-r—– 1 oracle dba 511488 Feb 17 03:03 /u01/app/oracle/admin/orcl734/arch/arch.log1_859.dbf
dbbackup_begin: cp /u01/app/oracle/admin/orcl734/arch/arch.log1_859.dbf /u02/bkup/db_orcl734/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/orcl734/arch/arch.log1_859.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_859.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/orcl734/arch/arch.log1_859.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_859.dbf
2400058003 511488 /u01/app/oracle/admin/orcl734/arch/arch.log1_859.dbf
2400058003 511488 /u02/bkup/db_orcl734/archive.dir/arch.log1_859.dbf
-rw-r—– 1 oracle dba 70144 Feb 17 03:09 /u01/app/oracle/admin/orcl734/arch/arch.log1_860.dbf
dbbackup_begin: cp /u01/app/oracle/admin/orcl734/arch/arch.log1_860.dbf /u02/bkup/db_orcl734/archive.dir
dbbackup_begin: /bin/cmp /u01/app/oracle/admin/orcl734/arch/arch.log1_860.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_860.dbf
dbbackup_begin: /bin/cksum /u01/app/oracle/admin/orcl734/arch/arch.log1_860.dbf /u02/bkup/db_orcl734/archive.dir/arch.log1_860.dbf
1786051313 70144 /u01/app/oracle/admin/orcl734/arch/arch.log1_860.dbf
1786051313 70144 /u02/bkup/db_orcl734/archive.dir/arch.log1_860.dbf
……………………………..
End backup at Wed Feb 17 03:12:35 EST 1999
……………………………..
……………………………..
Begin export at Wed Feb 17 03:12:35 EST 1999
……………………………..
dbexport_begin: List previous export files…
-rw-r–r– 1 oracle dba 6898688 Feb 16 03:20 /u02/bkup/db_orcl734/export.dir/orcl734.exp
-rw-r–r– 1 oracle dba 6838272 Feb 16 03:12 /u02/bkup/db_orcl734/old_export.dir/orcl734.exp_old
dbexport_begin: Deleted previous export file.
dbexport_begin: cp /u02/bkup/db_orcl734/export.dir/orcl734.exp /u02/bkup/db_orcl734/old_export.dir
dbexport_begin: /bin/cmp /u02/bkup/db_orcl734/export.dir/orcl734.exp /u02/bkup/db_orcl734/old_export.dir/orcl734.exp
dbexport_begin: mv /u02/bkup/db_orcl734/old_export.dir/orcl734.exp /u02/bkup/db_orcl734/old_export.dir/orcl734.exp_old
Export: Release 7.3.4.0.0 – Production on Wed Feb 17 03:13:01 1999
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
Connected to: Oracle7 Server Release 7.3.4.0.1 – Production
With the distributed and parallel query options
PL/SQL Release 2.3.4.0.0 – Production
Export done in US7ASCII character set
About to export the entire database …
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting cluster definitions
. about to export SYSTEM’s tables via Conventional Path …
. . exporting table DEF$_CALL 0 rows exported
. . exporting table DEF$_CALLDEST 0 rows exported
. . exporting table DEF$_DEFAULTDEST 0 rows exported
. . exporting table DEF$_DESTINATION 0 rows exported
. . exporting table DEF$_ERROR 0 rows exported
. . exporting table EMBARCADERO_EXPLAIN_PLAN 12 rows exported
. . exporting table PRODUCT_PROFILE 0 rows exported
. . exporting table USER_PROFILE 0 rows exported
. about to export DBSNMP’s tables via Conventional Path …
. about to export RTT’s tables via Conventional Path …
. . exporting table MAXFREE 5 rows exported
. . exporting table PLAN_TABLE 0 rows exported
. . exporting table SEGSIZE 140 rows exported
. . exporting table STAT_EXT 36576 rows exported
. . exporting table TEST 0 rows exported
. . exporting table TEST_EXTENTS 15174 rows exported
. about to export OPS$ORACLE’s tables via Conventional Path …
. about to export OPM’s tables via Conventional Path …
. about to export QDBA’s tables via Conventional Path …
. . exporting table Q$BGP_CONFIG 2 rows exported
. . exporting table Q$BGP_DEBUG 26 rows exported
. . exporting table Q$DICACHE_DETL 1230 rows exported
. . exporting table Q$INSTAT_DETL 41 rows exported
. . exporting table Q$INSTAT_LOG 3495 rows exported
. . exporting table Q$IOWAITS_DETL 363 rows exported
. . exporting table Q$LATCHSTAT_DETL 2173 rows exported
. . exporting table Q$LIBCACHE_DETL 328 rows exported
. . exporting table Q$MTSDISP_DETL 0 rows exported
. . exporting table Q$MTSSERV_DETL 0 rows exported
. . exporting table Q$PLAN_TABLE 0 rows exported
. . exporting table Q$PQSERV_DETL 0 rows exported
. . exporting table Q$SEG 0 rows exported
. . exporting table Q$SEGFAIL_DETL 0 rows exported
. . exporting table Q$SEGWATCH 3 rows exported
. . exporting table Q$SESSIONWAIT_DETL 369 rows exported
. . exporting table Q$TBS 0 rows exported
. . exporting table Q$TREND_LOG 182 rows exported
. exporting referential integrity constraints
. exporting posttables actions
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting triggers
. exporting default and system auditing options
Export terminated successfully without warnings.
dbexport_begin: Export complete.
-rw-r–r– 1 oracle dba 6951936 Feb 17 03:20 /u02/bkup/db_orcl734/export.dir/orcl734.exp
-rw-r–r– 1 oracle dba 6898688 Feb 17 03:12 /u02/bkup/db_orcl734/old_export.dir/orcl734.exp_old
……………………………..
End export at Wed Feb 17 03:20:48 EST 1999
……………………………..
Filesystem kbytes used avail capacity Mounted on
/dev/dsk/c0t3d0s0 915166 372831 450825 46% /
/proc 0 0 0 0% /proc
fd 0 0 0 0% /dev/fd
swap 61484 8 61476 1% /tmp
/dev/dsk/c0t2d0s0 989827 848825 42022 96% /u01
/dev/dsk/c0t4d0s0 979951 606800 275161 69% /u02
Sample spooled backup message file listing (suitable for mailing)
…………………………………………………………….
Backup Job Parameters:
Database Name = orcl734
Backup Type = hot
Export Type = export
Special Task =
Environment Variables:
ORACLE_HOME = /u01/app/oracle/product/7.3.4
ORACLE_SID = orcl734
PATH = /usr/bin:/usr/sbin:/u01/app/oracle/product/7.3.4/bin
…………………………………………………………….
Backup log file errors and warnings:
Log files:
Log file=/export/home/oracle/tools/db_admin/db_orcl734/log/backup_990217.log
Error file=/export/home/oracle/tools/db_admin/db_orcl734/log/backup_990217.err
Message file=/export/home/oracle/tools/db_admin/db_orcl734/log/backup_990217.msg
User level export
exp scott/tiger file=user.dmp
Export: Release 8.0.4.0.0 – Production on Wed Mar 31 16:4:45 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to: Oracle8 Release 8.0.4.0.0 – Production
PL/SQL Release 8.0.4.0.0 – Production
Export done in US7ASCII character set and US7ASCII NCHAR character set
About to export specified users …
. exporting foreign function library names for user SCOTT
. exporting object type definitions for user SCOTT
About to export SCOTT’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT’s tables via Conventional Path …
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table DUMMY 1 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting referential integrity constraints
. exporting triggers
. exporting posttables actions
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
Export terminated successfully without warnings.
Import Dropped Table spooled file listing
Sqlplus scott/tiger
SQL> drop table emp;
Table dropped.
SQL> exit
Disconnected from Oracle8 Release 8.0.4.0.0 – Production
PL/SQL Release 8.0.4.0.0 – Production
$ imp scott/tiger file=user.dmp tables=emp
Import: Release 8.0.4.0.0 – Production on Wed Mar 31 16:8:45 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to: Oracle8 Release 8.0.4.0.0 – Production
PL/SQL Release 8.0.4.0.0 – Production
Export file created by EXPORT:V08.00.04 via conventional path
. importing SCOTT’s objects into SCOTT
. . importing table “EMP” 14 rows imported
Import terminated successfully without warnings.
RMAN Commands
allocateForDelete
allocate
archivelogRecordSpecifier
atClause
backup
backupSpec
catalog
change
connect
copy
copyOption
createScript
deleteScript
host
inputfile
list
listObjList
needBackupOperand
primary_key
printScript
recover
register
release
replaceScript
replicate
report
reportObject
reset
restoreObject
restore
restoreSpecOperand
resync
releaseForDelete
rmanCmd
reportObsoleteOperand
run
RMAN database objects
|
OBJECT_TYPE |
OBJECT_NAME |
OBJECT_TYPE |
OBJECT_NAME |
|
INDEX |
AL_I_2 |
PACKAGE |
DBMS_RCVCAT |
|
INDEX |
AL_I_FNAME_STATUS |
PACKAGE |
DBMS_RCVMAN |
|
INDEX |
AL_P |
PACKAGE BODY |
DBMS_RCVCAT |
|
INDEX |
AL_U1 |
PACKAGE BODY |
DBMS_RCVMAN |
|
INDEX |
BCB_U1 |
SEQUENCE |
RMAN_SEQ |
|
INDEX |
BCF_P |
TABLE |
AL |
|
INDEX |
BCF_U1 |
TABLE |
BCB |
|
INDEX |
BCF_U2 |
TABLE |
BCF |
|
INDEX |
BDF_I_DF_KEY |
TABLE |
BDF |
|
INDEX |
BDF_P |
TABLE |
BP |
|
INDEX |
BDF_U1 |
TABLE |
BRL |
|
INDEX |
BP_I_DEVICE_HANDLE_STATUS |
TABLE |
BS |
|
INDEX |
BP_P |
TABLE |
CCB |
|
INDEX |
BP_U1 |
TABLE |
CCF |
|
INDEX |
BRL_I_BS_KEY |
TABLE |
CDF |
|
INDEX |
BRL_P |
TABLE |
CKP |
|
INDEX |
BRL_U1 |
TABLE |
DB |
|
INDEX |
BS_P |
TABLE |
DBINC |
|
INDEX |
BS_U1 |
TABLE |
DF |
|
INDEX |
BS_U2 |
TABLE |
DFATT |
|
INDEX |
CCB_U1 |
TABLE |
OFFR |
|
INDEX |
CCF_I_FNAME_STATUS |
TABLE |
ORL |
|
INDEX |
CCF_P |
TABLE |
RCVER |
|
INDEX |
CCF_U1 |
TABLE |
RLH |
|
INDEX |
CDF_I_DF_KEY |
TABLE |
RR |
|
INDEX |
CDF_I_FNAME_STATUS |
TABLE |
RT |
|
INDEX |
CDF_P |
TABLE |
SCR |
|
INDEX |
CDF_U1 |
TABLE |
SCRL |
|
INDEX |
CKP_P |
TABLE |
TS |
|
INDEX |
CKP_U1 |
TABLE |
TSATT |
|
INDEX |
DBINC_P |
VIEW |
RC_ARCHIVED_LOG |
|
INDEX |
DBINC_U1 |
VIEW |
RC_BACKUP_CONTROLFILE |
|
INDEX |
DB_P |
VIEW |
RC_BACKUP_CORRUPTION |
|
INDEX |
DB_U1 |
VIEW |
RC_BACKUP_DATAFILE |
|
INDEX |
DFATT_U1 |
VIEW |
RC_BACKUP_PIECE |
|
INDEX |
DF_P |
VIEW |
RC_BACKUP_REDOLOG |
|
INDEX |
DF_U1 |
VIEW |
RC_BACKUP_SET |
|
INDEX |
DF_U2 |
VIEW |
RC_CHECKPOINT |
|
INDEX |
OFFR_P |
VIEW |
RC_CONTROLFILE_COPY |
|
INDEX |
OFFR_U1 |
VIEW |
RC_COPY_CORRUPTION |
|
INDEX |
OFFR_U2 |
VIEW |
RC_DATABASE |
|
INDEX |
ORL_I_1 |
VIEW |
RC_DATABASE_INCARNATION |
|
INDEX |
RLH_P |
VIEW |
RC_DATAFILE |
|
INDEX |
RLH_U1 |
VIEW |
RC_DATAFILE_COPY |
|
INDEX |
RLH_U2 |
VIEW |
RC_LOG_HISTORY |
|
INDEX |
RR_P |
VIEW |
RC_OFFLINE_RANGE |
|
INDEX |
RT_P |
VIEW |
RC_REDO_LOG |
|
INDEX |
SCRL_U1 |
VIEW |
RC_REDO_THREAD |
|
INDEX |
SCR_P |
VIEW |
RC_RESYNC |
|
INDEX |
SCR_U1 |
VIEW |
RC_STORED_SCRIPT |
|
INDEX |
TSATT_U1 |
VIEW |
RC_STORED_SCRIPT_LINE |
|
INDEX |
TS_P |
VIEW |
RC_TABLESPACE |
|
INDEX |
TS_U1 |
|
|
|
INDEX |
TS_U2 |
|
|
Creating an RMAN stored script for a full backup of the ioug database
$ rman target internal/change_on_install@ioug rcvcat entman/entman@ioug cmdfile ioug_full.rcv
Recovery Manager: Release 8.0.4.0.0 – Production
RMAN-06005: connected to target database: IOUG
RMAN-06008: connected to recovery catalog database
RMAN> replace script ioug_full {
2> allocate channel d1 type disk;
3> backup
4> full
5> filesperset 4
6> format ‘/u02/rman_demo/bkup/IOUG.FULL.%n.%s.%p’
7> (database);
8> }
RMAN-03022: compiling command: replace script
RMAN-03023: executing command: replace script
RMAN-08086: replaced script ioug_full
Recovery Manager complete.
$
Executing the ioug_full.rcv stored script
$ rman target internal/change_on_install@ioug rcvcat entman/entman@ioug
Recovery Manager: Release 8.0.4.0.0 – Production
RMAN-06005: connected to target database: IOUG
RMAN-06008: connected to recovery catalog database
RMAN> run {execute script ioug_full;}
RMAN-03021: executing script: ioug_full
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: d1
RMAN-08500: channel d1: sid=13 devtype=DISK
RMAN-03022: compiling command: backup
RMAN-03024: performing implicit full resync of recovery catalog
RMAN-03023: executing command: full resync
RMAN-08002: starting full resync of recovery catalog
RMAN-08004: full resync complete
RMAN-03023: executing command: backup
RMAN-08008: channel d1: starting datafile backupset
RMAN-08502: set_count=7 set_stamp=360789795
RMAN-08010: channel d1: including datafile 2 in backupset
RMAN-08010: channel d1: including datafile 3 in backupset
RMAN-08010: channel d1: including datafile 4 in backupset
RMAN-08010: channel d1: including datafile 5 in backupset
RMAN-08013: channel d1: piece 1 created
RMAN-08503: piece handle=/u01/app/oracle/product/8.0.4/dbs/IOUG.FULL.IOUGxxxx.7.1 comment=NONE
RMAN-08008: channel d1: starting datafile backupset
RMAN-08502: set_count=8 set_stamp=360789825
RMAN-08010: channel d1: including datafile 1 in backupset
RMAN-08011: channel d1: including current controlfile in backupset
RMAN-08010: channel d1: including datafile 6 in backupset
RMAN-08013: channel d1: piece 1 created
RMAN-08503: piece handle=/u01/app/oracle/product/8.0.4/dbs/IOUG.FULL.IOUGxxxx.8.1 comment=NONE
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-08031: released channel: d1
Creating an RMAN stored script to backup the archived log files
$ rman target internal/change_on_install@ioug rcvcat entman/entman@ioug cmdfile ioug_archs.rcv
Recovery Manager: Release 8.0.4.0.0 – Production
RMAN-06005: connected to target database: IOUG
RMAN-06008: connected to recovery catalog database
RMAN> replace script ioug_archs {
2> allocate channel d1 type disk;
3> sql “alter system archive log current”;
4> backup
5> (archivelog from time ‘SYSDATE-1′ all
6> format ‘/u02/rman_demo/bkup/IOUG.FULL.%n.%s.%p’);
7> }
8>
RMAN-03022: compiling command: replace script
RMAN-03023: executing command: replace script
RMAN-08086: replaced script ioug_archs
Recovery Manager complete.
$
Executing the ioug_archs.rcv stored script
$ rman target internal/change_on_install@ioug rcvcat entman/entman@ioug
Recovery Manager: Release 8.0.4.0.0 – Production
RMAN-06005: connected to target database: IOUG
RMAN-06008: connected to recovery catalog database
RMAN> run {execute script ioug_archs;}
RMAN-03021: executing script: ioug_archs
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: d1
RMAN-08500: channel d1: sid=13 devtype=DISK
RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter system archive log current
RMAN-03023: executing command: sql
RMAN-03022: compiling command: backup
RMAN-03025: performing implicit partial resync of recovery catalog
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03023: executing command: backup
RMAN-08009: channel d1: starting archivelog backupset
RMAN-08502: set_count=9 set_stamp=360832181
RMAN-08014: channel d1: including archivelog in backup set
RMAN-08504: input archivelog thread=1 sequence=8 recid=235 stamp=360784306
RMAN-08014: channel d1: including archivelog in backup set
RMAN-08504: input archivelog thread=1 sequence=9 recid=236 stamp=360810216
RMAN-08014: channel d1: including archivelog in backup set
RMAN-08504: input archivelog thread=1 sequence=10 recid=237 stamp=360831892
RMAN-08014: channel d1: including archivelog in backup set
RMAN-08504: input archivelog thread=1 sequence=11 recid=238 stamp=360831983
RMAN-08014: channel d1: including archivelog in backup set
RMAN-08504: input archivelog thread=1 sequence=12 recid=239 stamp=360832147
RMAN-08013: channel d1: piece 1 created
RMAN-08503: piece handle=/u02/rman_demo/bkup/IOUG.FULL.IOUGxxxx.9.1 comment=NONE
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-08031: released channel: d1
RMAN>
Creating RMAN stored scripts for incremental backups
$ rman target internal/change_on_install@ioug rcvcat entman/entman@ioug cmdfile ioug_incremental.rcv
Recovery Manager: Release 8.0.4.0.0 – Production
RMAN-06005: connected to target database: IOUG
RMAN-06008: connected to recovery catalog database
RMAN> replace script ioug_level0 {
2> allocate channel d1 type disk;
3> backup incremental level 0
4> (database
5> format ‘/u02/rman_demo/bkup/IOUG.LEVEL0.%s.%p’);
6> }
7>
8> replace script ioug_level1 {
9> allocate channel d1 type disk;
10> backup incremental level 1
11> (database
12> format ‘/u02/rman_demo/bkup/IOUG.LEVEL1.%s.%p’);
13> }
14>
15> replace script ioug_level2 {
16> allocate channel d1 type disk;
17> backup incremental level 2
18> (database
19> format ‘/u02/rman_demo/bkup/IOUG.LEVEL2.%s.%p’);
20> }
21>
RMAN-03022: compiling command: replace script
RMAN-03023: executing command: replace script
RMAN-08086: replaced script ioug_level0
RMAN-03022: compiling command: replace script
RMAN-03023: executing command: replace script
RMAN-08086: replaced script ioug_level1
RMAN-03022: compiling command: replace script
RMAN-03023: executing command: replace script
RMAN-08086: replaced script ioug_level2
Recovery Manager complete.
Executing the ioug_incremental.rcv stored scripts
Level 0
$ rman target internal/change_on_install@ioug rcvcat entman/entman@ioug
Recovery Manager: Release 8.0.4.0.0 – Production
RMAN-06005: connected to target database: IOUG
RMAN-06008: connected to recovery catalog database
RMAN> run {execute script ioug_level0;}
RMAN-03021: executing script: ioug_level0
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: d1
RMAN-08500: channel d1: sid=13 devtype=DISK
RMAN-03022: compiling command: backup
RMAN-03023: executing command: backup
RMAN-08008: channel d1: starting datafile backupset
RMAN-08502: set_count=10 set_stamp=360833063
RMAN-08010: channel d1: including datafile 1 in backupset
RMAN-08011: channel d1: including current controlfile in backupset
RMAN-08010: channel d1: including datafile 2 in backupset
RMAN-08010: channel d1: including datafile 3 in backupset
RMAN-08010: channel d1: including datafile 4 in backupset
RMAN-08010: channel d1: including datafile 5 in backupset
RMAN-08010: channel d1: including datafile 6 in backupset
RMAN-08013: channel d1: piece 1 created
RMAN-08503: piece handle=/u02/rman_demo/bkup/IOUG.LEVEL0.10.1 comment=NONE
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-08031: released channel: d1
Level 1
$ rman target internal/change_on_install@ioug rcvcat entman/entman@ioug
Recovery Manager: Release 8.0.4.0.0 – Production
RMAN-06005: connected to target database: IOUG
RMAN-06008: connected to recovery catalog database
- RMAN> run {execute script ioug_level1;}
RMAN-03021: executing script: ioug_level1
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: d1
RMAN-08500: channel d1: sid=13 devtype=DISK
RMAN-03022: compiling command: backup
RMAN-03026: error recovery releasing channel resources
RMAN-08031: released channel: d1
RMAN-00569: ================error message stack follows================
RMAN-03015: error occurred in stored script ioug_level1
RMAN-03002: failure during compilation of command
RMAN-03013: command type: backup
RMAN-06180: incremental backups require Oracle8 Enterprise Edition
Recovery Scenario 1 – Recovering from a Cold backup
- Identify the database files to back up.
- Edit the generated file to make cp commands
- Create the scott account and some tables.
- Shutdown the database and copy the files to a different directory
- Startup the database, then drop a table ( to show that it comes back after the recovery)
- Shutdown the database, copy the backup files back, and restart the instance.
- Look for the table that you dropped.
1. $ cat bld_orafiles.sql
set verify off
set echo off
set feedback off
set recsep off
set escape on
ttitle off
btitle off
set termout off
set pagesize 0
spool &1
select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
/
spool off
exit
$ sqlplus -s / @bld_orafiles.sql bob239_files.sql
$ cat bob239_files.sql
/oracle/data/ora01/bob239/control01.con
/oracle/data/ora01/bob239/control02.con
/oracle/data/ora01/bob239/control03.con
/oracle/data/ora01/bob239/ora_log_01_01.rdo
/oracle/data/ora01/bob239/ora_log_02_01.rdo
/oracle/data/ora01/bob239/ora_log_03_01.rdo
/oracle/data/ora01/bob239/roll_t01_01.dbf
/oracle/data/ora01/bob239/system_01.dbf
/oracle/data/ora01/bob239/temp_t01_01.dbf
/oracle/data/ora01/bob239/tools_t01_01.dbf
/oracle/data/ora01/bob239/users_t01_01.dbf
2. $ vi bob239_files.sql # Edit the file to make the cp commands.
$ cat bob239_files.sql
cp /oracle/data/ora01/bob239/control01.con .
cp /oracle/data/ora01/bob239/control02.con .
cp /oracle/data/ora01/bob239/control03.con .
cp /oracle/data/ora01/bob239/ora_log_01_01.rdo .
cp /oracle/data/ora01/bob239/ora_log_02_01.rdo .
cp /oracle/data/ora01/bob239/ora_log_03_01.rdo .
cp /oracle/data/ora01/bob239/roll_t01_01.dbf .
cp /oracle/data/ora01/bob239/system_01.dbf .
cp /oracle/data/ora01/bob239/temp_t01_01.dbf .
cp /oracle/data/ora01/bob239/tools_t01_01.dbf .
cp /oracle/data/ora01/bob239/users_t01_01.dbf .
3. $ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Sun Feb 7 14:0:5 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> create user scott identified by tiger123
2 default tablespace tools_t01
3 temporary tablespace temp_t01
4 quota unlimited on tools_t01;
User created.
bob239> grant connect, resource to scott;
Grant succeeded.
bob239> connect scott/tiger123
Connected.
bob239> @$ORACLE_HOME/sqlplus/demo/demobld
Building demonstration tables. Please wait.
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
$ sqlplus scott/tiger123
SQL*Plus: Release 8.0.4.0.0 – Production on Sun Feb 7 14:1:19 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> select * from cat;
TABLE_NAME TABLE_TYPE
—————————— ———–
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
4. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
$ mkdir /oracle/data/ora02/bob239_backup
$ cd /oracle/data/ora02/bob239_backup
$ ls
$ . /oracle/local/sql/bob239_files.sql
$ ls
control01.con ora_log_01_01.rdo roll_t01_01.dbf tools_t01_01.dbf
control02.con ora_log_02_01.rdo system_01.dbf users_t01_01.dbf
control03.con ora_log_03_01.rdo temp_t01_01.dbf
5. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
Database mounted.
Database opened.
SVRMGR> connect scott/tiger123
Connected.
SVRMGR> select * from cat;
TABLE_NAME TABLE_TYPE
—————————— ———–
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
5 rows selected.
SVRMGR> drop table dept;
Statement processed.
6. SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
$ pwd
/oracle/data/ora02/bob239_backup
$ cp * /oracle/data/ora01/bob239
$ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
Database mounted.
Database opened.
SVRMGR> connect scott/tiger123
Connected.
SVRMGR> select * from cat;
TABLE_NAME TABLE_TYPE
—————————— ———–
BONUS TABLE
DEPT TABLE < —- the table has been restored.
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
5 rows selected.
SVRMGR> select count(*) from dept;
COUNT(*)
———-
4
1 row selected.
Recovery Scenario 2 – Recovery from a Hot backup
We will be performing some additional steps in this scenario to prepare for the other recovery examples. This will allow us to use the files from a single hot backup for many of the recovery scenarios that follow.
- Make sure that the database is in archive log mode
- Put the database into backup mode
- Backup the data files
- Generate some activity
- Take the tablespaces out of backup mode
- Force a log switch
- Backup the controlfile to trace.
- Perform some more activity . Commit. Record the time.
- Perform some more activity. Commit. Record the SCN.
- Perform some more activity. Commit.
- Force a log switch, then backup the archived redo logs.
- Shutdown the database, and delete the files from the archive location.
- Recover the datafiles and all archived redo log files.
- See what happens if you try to simply open the database
- Recover the database
- Since we are applying all of the logs, all of the operations should have been recovered.
1. $ cd /oracle/admin/bob239/pfile
$ grep -i log_archive *
configbob239.ora:log_archive_dest = /oracle/data/ora03/arch/bob239/arch.log
initbob239.ora:log_archive_start = true # if you want automatic archiving
initbob239.ora:log_archive_format=%t_%s.arc
$ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup mount
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
Database mounted.
SVRMGR> alter database archivelog;
Statement processed.
SVRMGR> alter database open;
Statement processed.
SVRMGR> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/data/ora03/arch/bob239/arch.log
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
SVRMGR> exit
Server Manager complete.
2. $ cd /oracle/data/ora03/arch/bob239
$ cat /oracle/local/sql/build_begin_backup.sql
rem
rem This script builds an sql script to put all
rem of the tablespaces into backup mode.
rem
set pause off
set pagesize 0
set linesize 80
set heading off
set feedback off
set verify off
set termout off
@get_instance.sql
set escape on
spool begin_&instance_name._backup.sql
select ‘set echo on’ from dual;
select ‘spool begin_&instance_name._backup.err’ from dual;
select distinct ‘alter tablespace ‘|| t.tablespace_name || ‘ begin backup;’
from sys.dba_tablespaces t, sys.dba_data_files d, v$backup b
where b.file# = d.file_id
and d.tablespace_name = t.tablespace_name
and t.status = ‘ONLINE’
and b.status = ‘NOT ACTIVE’;
select ‘spool off’ from dual;
select ‘exit’ from dual;
spool off;
exit
$ cat /oracle/local/sql/get_instance.sql
rem
rem This script defines the sql*plus variable ‘instance name’
rem to contain the instance name.
rem
define instance_name = “”
column instance_name_xxx noprint new_value instance_name
select instance instance_name_xxx
from v$thread t, v$parameter p
where t.thread# = decode(to_number(p.value),0,1,to_number(p.value))
and p.name = ‘thread’;
column instance_name_xxx clear
define instance_name = &instance_name
$ sqlplus –s / @/oracle/local/sql/build_begin_backup.sql
$ ls
begin_bob239_backup.sql
$ cat begin_bob239_backup.sql
set echo on
spool begin_bob239_backup.err
alter tablespace ROLL_T01 begin backup;
alter tablespace SYSTEM begin backup;
alter tablespace TEMP_T01 begin backup;
alter tablespace TOOLS_T01 begin backup;
alter tablespace USERS_T01 begin backup;
spool off
exit
$ sqlplus -s / @begin_bob239_backup
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
3. $ cd /oracle/data/ora02/bob239_backup
$ rm *
$ set -x
$ . /oracle/local/sql/bob239_files.sql
+ . /oracle/local/sql/bob239_files.sql
+ cp /oracle/data/ora01/bob239/control01.con .
+ cp /oracle/data/ora01/bob239/control02.con .
+ cp /oracle/data/ora01/bob239/control03.con .
+ cp /oracle/data/ora01/bob239/ora_log_01_01.rdo .
+ cp /oracle/data/ora01/bob239/ora_log_02_01.rdo .
+ cp /oracle/data/ora01/bob239/ora_log_03_01.rdo .
+ cp /oracle/data/ora01/bob239/roll_t01_01.dbf .
+ cp /oracle/data/ora01/bob239/system_01.dbf .
+ cp /oracle/data/ora01/bob239/temp_t01_01.dbf .
+ cp /oracle/data/ora01/bob239/tools_t01_01.dbf .
+ cp /oracle/data/ora01/bob239/users_t01_01.dbf .
$ set +x
4. $ sqlplus scott/tiger123
SQL*Plus: Release 8.0.4.0.0 – Production on Sun Feb 7 19:55:28 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> create table dept_copy as select * from dept;
Table created.
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
5. $ cd /oracle/data/ora03/arch/bob239
$ cat /oracle/local/sql/build_end_backup.sql
rem
rem This script builds a script to set END BACKUP
rem for each tablespace.
rem
set pause off
set pagesize 0
set linesize 80
set heading off
set feedback off
set verify off
set termout off
@get_instance.sql
set escape on
spool end_&instance_name._backup.sql
select ‘set echo on’ from dual;
select ‘spool end_&instance_name._backup.err’ from dual;
select distinct ‘alter tablespace ‘|| t.tablespace_name || ‘ end backup;’
from sys.dba_tablespaces t, sys.dba_data_files d, v$backup b
where b.file# = d.file_id
and d.tablespace_name = t.tablespace_name
and t.status = ‘ONLINE’
and b.status = ‘ACTIVE’;
select ‘spool off’ from dual;
select ‘exit’ from dual;
spool off;
exit
$ sqlplus -s / @build_end_backup.sql
$ ls
begin_bob239_backup.err begin_bob239_backup.sql end_bob239_backup.sql
$ cat end_bob239_backup.sql
set echo on
spool end_bob239_backup.err
alter tablespace ROLL_T01 end backup;
alter tablespace SYSTEM end backup;
alter tablespace TEMP_T01 end backup;
alter tablespace TOOLS_T01 end backup;
alter tablespace USERS_T01 end backup;
spool off
exit
$ sqlplus -s / @end_bob239_backup
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
6. $ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Sun Feb 7 19:57:13 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> alter system switch logfile;
System altered.
7. bob239> alter database backup controlfile to trace;
Database altered.
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
$ cd /oracle/admin/bob239/udump
$ ls
bob239_ora_29485.trc
$ mv bob239_ora_29485.trc controlfile.sql
8. $ sqlplus scott/tiger123
SQL*Plus: Release 8.0.4.0.0 – Production on Sun Feb 7 19:57:53 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> create table emp_copy as select * from emp;
Table created.
bob239> ! date
Sun Feb 7 19:58:43 EST 1999
9. bob239> create table bonus_copy as select * from bonus;
Table created.
bob239> alter system checkpoint;
System altered.
bob239> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
——————
13296
10. bob239> create table salgrade_copy as select * from salgrade;
Table created.
bob239> select * from cat;
TABLE_NAME TABLE_TYPE
—————————— ———–
BONUS TABLE
BONUS_COPY TABLE
DEPT TABLE
DEPT_COPY TABLE
DUMMY TABLE
EMP TABLE
EMP_COPY TABLE
SALGRADE TABLE
SALGRADE_COPY TABLE
9 rows selected.
11. bob239> connect /
Connected.
bob239> alter system switch logfile;
System altered.
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
$ cd /oracle/data/ora03/arch/bob239
$ ls
arch.log1_11.arc arch.log1_14.arc end_bob239_backup.err
arch.log1_12.arc begin_bob239_backup.err end_bob239_backup.sql
arch.log1_13.arc begin_bob239_backup.sql
$ cp *.arc /oracle/data/ora02/bob239_backup
12. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
$ rm /oracle/data/ora01/bob239/*
$ rm /oracle/data/ora03/arch/bob239/*
13. $ cd /oracle/data/ora02/bob239_backup
$ ls
arch.log1_11.arc control01.con ora_log_02_01.rdo temp_t01_01.dbf
arch.log1_12.arc control02.con ora_log_03_01.rdo tools_t01_01.dbf
arch.log1_13.arc control03.con roll_t01_01.dbf users_t01_01.dbf
arch.log1_14.arc ora_log_01_01.rdo system_01.dbf
$ cp arch* /oracle/data/ora03/arch/bob239
$ cp * /oracle/data/ora01/bob239
14. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/oracle/data/ora01/bob239/system_01.dbf’
15. SVRMGR> set autorecovery on
Autorecovery ON
SVRMGR> recover database using backup controlfile until cancel
ORA-00279: change 13257 generated at 02/07/99 19:51:26 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_11.arc
ORA-00280: change 13257 for thread 1 is in sequence #11
Log applied.
ORA-00279: change 13273 generated at 02/07/99 19:57:17 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_12.arc
ORA-00280: change 13273 for thread 1 is in sequence #12
ORA-00278: log file ‘/oracle/data/ora03/arch/bob239/arch.log1_11.arc’ no longer
needed for this recovery
Log applied.
ORA-00279: change 13294 generated at 02/07/99 20:00:09 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_13.arc
ORA-00280: change 13294 for thread 1 is in sequence #13
ORA-00278: log file ‘/oracle/data/ora03/arch/bob239/arch.log1_12.arc’ no longer
needed for this recovery
Log applied.
ORA-00279: change 13296 generated at 02/07/99 20:00:09 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_14.arc
ORA-00280: change 13296 for thread 1 is in sequence #14
ORA-00278: log file ‘/oracle/data/ora03/arch/bob239/arch.log1_13.arc’ no longer
needed for this recovery
Log applied.
ORA-00279: change 13306 generated at 02/07/99 20:01:12 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_15.arc
ORA-00280: change 13306 for thread 1 is in sequence #15
ORA-00278: log file ‘/oracle/data/ora03/arch/bob239/arch.log1_14.arc’ no longer
needed for this recovery
ORA-00308: cannot open archived log ‘/oracle/data/ora03/arch/bob239/arch.log1_15
.arc’
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
SVRMGR> alter database open resetlogs;
Statement processed.
SVRMGR> connect scott/tiger123
Connected.
16. SVRMGR> select * from cat;
TABLE_NAME TABLE_TYPE
—————————— ———–
BONUS TABLE
BONUS_COPY TABLE
DEPT TABLE
DEPT_COPY TABLE
DUMMY TABLE
EMP TABLE
EMP_COPY TABLE
SALGRADE TABLE
SALGRADE_COPY TABLE
9 rows selected.
Recovery Scenario 3 – Cancel Based Recovery from a Hot backup
We will be using the backups taken in Scenario 2, but will only apply some of the redo logs.
- Shutdown the database, and delete all database files and archived redo logs.
- Recover the datafiles and all archived redo log files.
- Recover the database, canceling after the 1st archived redo log.
- Since we are applying only the first log after the hot backup, we should see only the DEPT_COPY table.
1. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
$ rm /oracle/data/ora01/bob239/*
$ rm /oracle/data/ora03/arch/bob239/*
2. $ cd /oracle/data/ora02/bob239_backup/
$ ls
arch.log1_11.arc control01.con ora_log_02_01.rdo temp_t01_01.dbf
arch.log1_12.arc control02.con ora_log_03_01.rdo tools_t01_01.dbf
arch.log1_13.arc control03.con roll_t01_01.dbf users_t01_01.dbf
arch.log1_14.arc ora_log_01_01.rdo system_01.dbf
$ cp arch* /oracle/data/ora03/arch/bob239
$ cp * /oracle/data/ora01/bob239
3. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> startup mount
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
Database mounted.
SVRMGR> recover database using backup controlfile until cancel
ORA-00279: change 13257 generated at 02/07/99 19:51:26 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_11.arc
ORA-00280: change 13257 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
ORA-00279: change 13273 generated at 02/07/99 19:57:17 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_12.arc
ORA-00280: change 13273 for thread 1 is in sequence #12
ORA-00278: log file ‘/oracle/data/ora03/arch/bob239/arch.log1_11.arc’ no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SVRMGR> alter database open resetlogs;
Statement processed.
4. SVRMGR> connect scott/tiger123
Connected.
SVRMGR> select * from cat;
TABLE_NAME TABLE_TYPE
—————————— ———–
BONUS TABLE
DEPT TABLE
DEPT_COPY TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
6 rows selected.
Recovery Scenario 4 – Time Based Recovery from a Hot backup
We will be using the backups taken in Scenario 2, but only some of the redo logs will be applied by Oracle.
- Shutdown the database, and delete all database files and archived redo logs.
- Recover the datafiles and all archived redo log files.
- Recover the database, using UNTIL TIME.
- Since we are using the timestamp from right after we created the EMP_COPY table, we should see both the DEPT_COPY and EMP_COPY tables.
1. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
$ rm /oracle/data/ora01/bob239/*
$ rm /oracle/data/ora03/arch/bob239/*
2. $ cd /oracle/data/ora02/bob239_backup/
$ ls
arch.log1_11.arc control01.con ora_log_02_01.rdo temp_t01_01.dbf
arch.log1_12.arc control02.con ora_log_03_01.rdo tools_t01_01.dbf
arch.log1_13.arc control03.con roll_t01_01.dbf users_t01_01.dbf
arch.log1_14.arc ora_log_01_01.rdo system_01.dbf
$ cp arch* /oracle/data/ora03/arch/bob239
$ cp * /oracle/data/ora01/bob239
3. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> startup mount
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
Database mounted.
SVRMGR> recover database using backup controlfile until time ’1999-02-07 19:58:43′
ORA-00279: change 13257 generated at 02/07/99 19:51:26 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_11.arc
ORA-00280: change 13257 for thread 1 is in sequence #11
Log applied.
ORA-00279: change 13273 generated at 02/07/99 19:57:17 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_12.arc
ORA-00280: change 13273 for thread 1 is in sequence #12
ORA-00278: log file ‘/oracle/data/ora03/arch/bob239/arch.log1_11.arc’ no longer
needed for this recovery
Log applied.
Media recovery complete.
SVRMGR> alter database open resetlogs;
Statement processed.
4. SVRMGR> connect scott/tiger123
Connected.
SVRMGR> select * from cat;
TABLE_NAME TABLE_TYPE
—————————— ———–
BONUS TABLE
DEPT TABLE
DEPT_COPY TABLE
DUMMY TABLE
EMP TABLE
EMP_COPY TABLE
SALGRADE TABLE
7 rows selected.
Recovery Scenario 5 – SCN Based Recovery from a Hot backup
We will be using the backups taken in Scenario 2, but only some of the redo logs will be applied by Oracle.
- Shutdown the database, and delete all database files and archived redo logs.
- Recover the datafiles and archived redo log files.
- Recover the database, using UNTIL CHANGE
- Because of the SCN that we used, we should see all of the copy tables except SALGRADE_COPY..
1. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
$ rm /oracle/data/ora01/bob239/*
$ rm /oracle/data/ora03/arch/bob239/*
2. $ cd /oracle/data/ora02/bob239_backup/
$ ls
arch.log1_11.arc control01.con ora_log_02_01.rdo temp_t01_01.dbf
arch.log1_12.arc control02.con ora_log_03_01.rdo tools_t01_01.dbf
arch.log1_13.arc control03.con roll_t01_01.dbf users_t01_01.dbf
arch.log1_14.arc ora_log_01_01.rdo system_01.dbf
$ cp arch* /oracle/data/ora03/arch/bob239
$ cp * /oracle/data/ora01/bob239
3. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> startup mount
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
Database mounted.
SVRMGR> set autorecovery on
SVRMGR> recover database using backup controlfile until change 13296
ORA-00279: change 13257 generated at 02/07/99 19:51:26 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_11.arc
ORA-00280: change 13257 for thread 1 is in sequence #11
Log applied.
ORA-00279: change 13273 generated at 02/07/99 19:57:17 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_12.arc
ORA-00280: change 13273 for thread 1 is in sequence #12
ORA-00278: log file ‘/oracle/data/ora03/arch/bob239/arch.log1_11.arc’ no longer
needed for this recovery
Log applied.
ORA-00279: change 13294 generated at 02/07/99 20:00:09 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_13.arc
ORA-00280: change 13294 for thread 1 is in sequence #13
ORA-00278: log file ‘/oracle/data/ora03/arch/bob239/arch.log1_12.arc’ no longer
needed for this recovery
Log applied.
Media recovery complete.
SVRMGR> alter database open resetlogs;
Statement processed.
4. SVRMGR> connect scott/tiger123
Connected.
SVRMGR> select * from cat;
TABLE_NAME TABLE_TYPE
—————————— ———–
BONUS TABLE
BONUS_COPY TABLE
DEPT TABLE
DEPT_COPY TABLE
DUMMY TABLE
EMP TABLE
EMP_COPY TABLE
SALGRADE TABLE
8 rows selected.
SVRMGR>
Recovery Scenario 6 – Relocating files during a recovery
We will be using the backups taken in Scenario 2.
- Shutdown the database, and delete all database files and archived redo logs.
- Recover the datafiles and archived redo log files to different locations that their original location
- Edit the config.ora file to indicate the new location of the controlfile and the archive log destination
- Edit the controlfile.sql script created during Scenario 2 to indicate the location of the data files and online redo logs, and to include the recover command.
- Remove the recovered controlfiles, since we will be recreating them.
- Recover the database, applying all of the logs.
- We should see all of the tables that we copied.
1. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
$ rm /oracle/data/ora01/bob239/*
$ rm /oracle/data/ora03/arch/bob239/*
2. $ cd /oracle/data/ora02/bob239_backup/
$ mkdir /oracle/data/ora04/bob239
$ mkdir -p /oracle/data/ora05/arch/bob239
$ cp arch* /oracle/data/ora05/arch/bob239
$ cp * /oracle/data/ora04/bob239
3. $ cd /oracle/admin/bob239/pfile
$ vi configbob239.ora
$ egrep ‘control|log_archive_dest’ configbob239.ora
control_files = (/oracle/data/ora04/bob239/control01.con,
/oracle/data/ora04/bob239/control02.con,
/oracle/data/ora04/bob239/control03.con)
log_archive_dest = /oracle/data/ora05/arch/bob239/arch.log
4. $ cd /oracle/admin/bob239/udump
$ ls
controlfile.sql
$ vi controlfile.sql
$ cat controlfile.sql # The bolded portions of the following file have been modified.
set echo on
connect internal
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “BOB239″ RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 843
LOGFILE
GROUP 1 ‘/oracle/data/ora04/bob239/ora_log_01_01.rdo’ SIZE 1M,
GROUP 2 ‘/oracle/data/ora04/bob239/ora_log_02_01.rdo’ SIZE 1M,
GROUP 3 ‘/oracle/data/ora04/bob239/ora_log_03_01.rdo’ SIZE 1M
DATAFILE
‘/oracle/data/ora04/bob239/system_01.dbf’,
‘/oracle/data/ora04/bob239/roll_t01_01.dbf’,
‘/oracle/data/ora04/bob239/temp_t01_01.dbf’,
‘/oracle/data/ora04/bob239/tools_t01_01.dbf’,
‘/oracle/data/ora04/bob239/users_t01_01.dbf’
;
set autorecovery on
RECOVER DATABASE using backup controlfile until cancel
ALTER DATABASE OPEN resetlogs;
5. $ rm /oracle/data/ora04/bob239/control*
6.. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> @controlfile
Echo ON
SVRMGR> connect internal
Connected.
SVRMGR> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
SVRMGR> CREATE CONTROLFILE REUSE DATABASE “BOB239″ RESETLOGS ARCHIVELOG
2> MAXLOGFILES 32
3> MAXLOGMEMBERS 2
4> MAXDATAFILES 512
5> MAXINSTANCES 8
6> MAXLOGHISTORY 843
7> LOGFILE
8> GROUP 1 ‘/oracle/data/ora04/bob239/ora_log_01_01.rdo’ SIZE 1M,
9> GROUP 2 ‘/oracle/data/ora04/bob239/ora_log_02_01.rdo’ SIZE 1M,
10> GROUP 3 ‘/oracle/data/ora04/bob239/ora_log_03_01.rdo’ SIZE 1M
11> DATAFILE
12> ‘/oracle/data/ora04/bob239/system_01.dbf’,
13> ‘/oracle/data/ora04/bob239/roll_t01_01.dbf’,
14> ‘/oracle/data/ora04/bob239/temp_t01_01.dbf’,
15> ‘/oracle/data/ora04/bob239/tools_t01_01.dbf’,
16> ‘/oracle/data/ora04/bob239/users_t01_01.dbf’
17> ;
Statement processed.
SVRMGR> set autorecovery on
Autorecovery ON
SVRMGR> RECOVER DATABASE using backup controlfile until cancel
ORA-00279: change 13257 generated at 02/07/99 19:51:26 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora05/arch/bob239/arch.log1_11.arc
ORA-00280: change 13257 for thread 1 is in sequence #11
Log applied.
ORA-00279: change 13273 generated at 02/07/99 19:57:17 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora05/arch/bob239/arch.log1_12.arc
ORA-00280: change 13273 for thread 1 is in sequence #12
ORA-00278: log file ‘/oracle/data/ora05/arch/bob239/arch.log1_11.arc’ no longer
needed for this recovery
Log applied.
ORA-00279: change 13294 generated at 02/07/99 20:00:09 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora05/arch/bob239/arch.log1_13.arc
ORA-00280: change 13294 for thread 1 is in sequence #13
ORA-00278: log file ‘/oracle/data/ora05/arch/bob239/arch.log1_12.arc’ no longer
needed for this recovery
Log applied.
ORA-00279: change 13296 generated at 02/07/99 20:00:09 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora05/arch/bob239/arch.log1_14.arc
ORA-00280: change 13296 for thread 1 is in sequence #14
ORA-00278: log file ‘/oracle/data/ora05/arch/bob239/arch.log1_13.arc’ no longer
needed for this recovery
Log applied.
ORA-00279: change 13306 generated at 02/07/99 20:01:12 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora05/arch/bob239/arch.log1_15.arc
ORA-00280: change 13306 for thread 1 is in sequence #15
ORA-00278: log file ‘/oracle/data/ora05/arch/bob239/arch.log1_14.arc’ no longer
needed for this recovery
ORA-00308: cannot open archived log ‘/oracle/data/ora05/arch/bob239/arch.log1_15
.arc’
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
SVRMGR> ALTER DATABASE OPEN resetlogs;
Statement processed.
7. SVRMGR> connect scott/tiger123
Connected.
SVRMGR> select * from cat;
TABLE_NAME TABLE_TYPE
—————————— ———–
BONUS TABLE
BONUS_COPY TABLE
DEPT TABLE
DEPT_COPY TABLE
DUMMY TABLE
EMP TABLE
EMP_COPY TABLE
SALGRADE TABLE
SALGRADE_COPY TABLE
9 rows selected.
SVRMGR>
Recovery Scenario 7 – Recovering from a lost datafile.
If you are not in archivelog mode, you must do a recovery from your last cold backup, as we did in Scenario 1. You will lose any data that was entered after the backup was taken.
This example shows what you can do if you are running in archivelog mode. Because we have recovered the database and used RESETLOGS, we can’t use the backup taken in scenario 2. We must take a fresh backup, then generate the error and recover from the new backup..
- Take a new backup.
- At the operating system, remove the tablespace file.
- See how the missing data file affects Oracle.
- Take the affected tablespaces offline.
- Recover the lost datafile(s) from the last hot backup
- Make sure that you have all of the archived redo log files created since the backup.
- Issue the RECOVER TABLESPACE command, and bring the tablespace back online.
- We should see all of the tables that we copied.
1. $ cd /oracle/data/ora05/arch/bob239
$ ls
arch.log1_11.arc arch.log1_12.arc arch.log1_13.arc arch.log1_14.arc
$ rm *
$ sqlplus -s / @/oracle/local/sql/build_begin_backup.sql
$ sqlplus -s / @begin_bob239_backup.sql
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
$ cd /oracle/data/ora02/bob239_backup/
$ rm *
$ vi /oracle/local/sql/bob239_files.sql
$ cat /oracle/local/sql/bob239_files.sql
cp /oracle/data/ora04/bob239/control01.con .
cp /oracle/data/ora04/bob239/control02.con .
cp /oracle/data/ora04/bob239/control03.con .
cp /oracle/data/ora04/bob239/ora_log_01_01.rdo .
cp /oracle/data/ora04/bob239/ora_log_02_01.rdo .
cp /oracle/data/ora04/bob239/ora_log_03_01.rdo .
cp /oracle/data/ora04/bob239/roll_t01_01.dbf .
cp /oracle/data/ora04/bob239/system_01.dbf .
cp /oracle/data/ora04/bob239/temp_t01_01.dbf .
cp /oracle/data/ora04/bob239/tools_t01_01.dbf .
cp /oracle/data/ora04/bob239/users_t01_01.dbf .
$ set -x
$ . /oracle/local/sql/bob239_files.sql
+ . /oracle/local/sql/bob239_files.sql
+ cp /oracle/data/ora04/bob239/control01.con .
+ cp /oracle/data/ora04/bob239/control02.con .
+ cp /oracle/data/ora04/bob239/control03.con .
+ cp /oracle/data/ora04/bob239/ora_log_01_01.rdo .
+ cp /oracle/data/ora04/bob239/ora_log_02_01.rdo .
+ cp /oracle/data/ora04/bob239/ora_log_03_01.rdo .
+ cp /oracle/data/ora04/bob239/roll_t01_01.dbf .
+ cp /oracle/data/ora04/bob239/system_01.dbf .
+ cp /oracle/data/ora04/bob239/temp_t01_01.dbf .
+ cp /oracle/data/ora04/bob239/tools_t01_01.dbf .
+ cp /oracle/data/ora04/bob239/users_t01_01.dbf .
$ set +x
$ cd /oracle/data/ora05/arch/bob239
$ sqlplus -s / @/oracle/local/sql/build_end_backup.sql
$ sqlplus -s / @end_bob239_backup.sql
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
$ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Mon Feb 8 7:55:34 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> alter system switch logfile;
System altered.
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
2. $ cd /oracle/data/ora04/bob239
$ ls
arch.log1_11.arc control01.con ora_log_02_01.rdo temp_t01_01.dbf
arch.log1_12.arc control02.con ora_log_03_01.rdo tools_t01_01.dbf
arch.log1_13.arc control03.con roll_t01_01.dbf users_t01_01.dbf
arch.log1_14.arc ora_log_01_01.rdo system_01.dbf
$ rm tools_t01_01.dbf
3. $ sqlplus scott/tiger123
SQL*Plus: Release 8.0.4.0.0 – Production on Mon Feb 8 7:56:28 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> select * from dept;
select * from dept
*
ERROR at line 1:
ORA-01115: IO error reading block from file 4 (block # 1548)
ORA-01110: data file 4: ‘/oracle/data/ora04/bob239/tools_t01_01.dbf’
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
4. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> alter tablespace tools_t01 offline immediate;
Statement processed.
SVRMGR> exit
Server Manager complete.
5. $ cp /oracle/data/ora02/bob239_backup/tools_t01_01.dbf .
7. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> set autorecovery on
Autorecovery ON
SVRMGR> recover tablespace tools_t01
Media recovery complete.
SVRMGR> alter tablespace tools_t01 online;
Statement processed.
8. SVRMGR> connect scott/tiger123;
Connected.
SVRMGR> select count(*) from dept;
COUNT(*)
———-
4
1 row selected.
SVRMGR>
Recovery Scenario 8 – Recovering from a lost online redo log.
First of all, I am assuming that you will be multiplexing your on-line redo logs. This will better protect you from this type of loss.
I will describe 4 different sub-scenarios
- Loss of a single member of a non-active group
1.1. You will normally be notified of the error when the log becomes active, so follow the procedure below.
- Loss of a single member of the active log group – normal activity continues
2.1. Identify the current logfile and remove one of its members.
2.2. Do some activity and look at the alert log for errors.
2.3. Switch the logfile
2.4. Drop the missing member from the logfile
2.5. Add the member back to the logfile
2.6. Issue enough switch logfile commands to make sure that the new file is used.
- Loss of both members of a non-active group
3.1. Identify a log file that is not current and then delete all of the files from that group.
3.2. The instance will crash when it attempts to make the logfile active.
3.3. STARTUP MOUNT the instance
3.4. Drop the logfile
3.5. Bring the instance up and recreate the logfile.
- Loss of both members of the active log group
4.1. The next time that Oracle tries to write to the log, an error will occur, and a log switch will occur
4.2. The archiver will never be able to get past that log file, and will eventually hang the instance
4.3. Shutdown the database
4.4. STARTUP MOUNT
4.5. Take the database out of archive log mode, then put it back into archivelog mode.
4.6. Drop and recreate the lost log group
4.7. Open the database
4.8. Take a backup
$ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> alter database add logfile member
2> ‘/oracle/data/ora04/bob239/ora_log_01_02.rdo’ to group 1;
Statement processed.
SVRMGR> alter database add logfile member
2> ‘/oracle/data/ora04/bob239/ora_log_02_02.rdo’ to group 2;
Statement processed.
SVRMGR> alter database add logfile member
2> ‘/oracle/data/ora04/bob239/ora_log_03_02.rdo’ to group 3;
Statement processed.
SVRMGR> select * from v$logfile;
GROUP# STATUS MEMBER
———- ——- ————————————————————-
——————-
3 /oracle/data/ora04/bob239/ora_log_03_01.rdo
2 /oracle/data/ora04/bob239/ora_log_02_01.rdo
1 /oracle/data/ora04/bob239/ora_log_01_01.rdo
1 INVALID /oracle/data/ora04/bob239/ora_log_01_02.rdo
2 INVALID /oracle/data/ora04/bob239/ora_log_02_02.rdo
3 INVALID /oracle/data/ora04/bob239/ora_log_03_02.rdo
6 rows selected.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> select * from v$logfile;
GROUP# STATUS MEMBER
———- ——- ————————————————————-
——————-
3 /oracle/data/ora04/bob239/ora_log_03_01.rdo
2 /oracle/data/ora04/bob239/ora_log_02_01.rdo
1 /oracle/data/ora04/bob239/ora_log_01_01.rdo
1 /oracle/data/ora04/bob239/ora_log_01_02.rdo
2 /oracle/data/ora04/bob239/ora_log_02_02.rdo
3 /oracle/data/ora04/bob239/ora_log_03_02.rdo
6 rows selected.
2.1 bob239> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
1 1 16 1048576 2 NO CURRENT
13376 08-FEB-99
2 1 15 1048576 2 YES INACTIVE
13375 08-FEB-99
3 1 14 1048576 2 YES INACTIVE
13374 08-FEB-99
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
$ rm /oracle/data/ora04/bob239/ora_log_01_01.rdo
2.2 $ sqlplus scott/tiger123
SQL*Plus: Release 8.0.4.0.0 – Production on Mon Feb 8 9:30:30 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
$ tail /oracle/admin/bob239/bdump/alert_bob239.log
Errors in file /oracle/admin/bob239/bdump/bob239_lgwr_20857.trc:
ORA-00345: redo log write error block 2 count 3
ORA-00312: online log 1 thread 1: ‘/oracle/data/ora04/bob239/ora_log_01_01.rdo’
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
Mon Feb 8 09:30:30 1999
Errors in file /oracle/admin/bob239/bdump/bob239_lgwr_20857.trc:
ORA-00346: log member marked as STALE
ORA-00312: online log 1 thread 1: ‘/oracle/data/ora04/bob239/ora_log_01_01.rdo’
$ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> select * from v$logfile;
GROUP# STATUS MEMBER
———- ——- ————————————————————-
——————-
3 /oracle/data/ora04/bob239/ora_log_03_01.rdo
2 /oracle/data/ora04/bob239/ora_log_02_01.rdo
1 STALE /oracle/data/ora04/bob239/ora_log_01_01.rdo
1 /oracle/data/ora04/bob239/ora_log_01_02.rdo
2 /oracle/data/ora04/bob239/ora_log_02_02.rdo
3 /oracle/data/ora04/bob239/ora_log_03_02.rdo
6 rows selected.
2.3 SVRMGR> alter system switch logfile;
Statement processed.
2.4 SVRMGR> alter database drop logfile member
2> ‘/oracle/data/ora04/bob239/ora_log_01_01.rdo’;
Statement processed.
2.5 SVRMGR> alter database add logfile member
2> ‘/oracle/data/ora04/bob239/ora_log_01_01.rdo’ to group 1;
Statement processed.
2.6 SVRMGR> select * from v$logfile;
GROUP# STATUS MEMBER
———- ——- ————————————————————-
——————-
3 /oracle/data/ora04/bob239/ora_log_03_01.rdo
2 /oracle/data/ora04/bob239/ora_log_02_01.rdo
1 INVALID /oracle/data/ora04/bob239/ora_log_01_01.rdo
1 /oracle/data/ora04/bob239/ora_log_01_02.rdo
2 /oracle/data/ora04/bob239/ora_log_02_02.rdo
3 /oracle/data/ora04/bob239/ora_log_03_02.rdo
6 rows selected.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> select * from v$logfile;
GROUP# STATUS MEMBER
———- ——- ————————————————————-
——————-
3 /oracle/data/ora04/bob239/ora_log_03_01.rdo
2 /oracle/data/ora04/bob239/ora_log_02_01.rdo
1 /oracle/data/ora04/bob239/ora_log_01_01.rdo
1 /oracle/data/ora04/bob239/ora_log_01_02.rdo
2 /oracle/data/ora04/bob239/ora_log_02_02.rdo
3 /oracle/data/ora04/bob239/ora_log_03_02.rdo
6 rows selected.
3. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
3.1 SVRMGR> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS
T_CHAN FIRST_TIM
———- ———- ———- ———- ———- — —————- —-
—— ———
1 1 28 1048576 2 NO CURRENT
33411 08-FEB-99
2 1 26 1048576 2 YES INACTIVE
33392 08-FEB-99
3 1 27 1048576 2 YES INACTIVE
33410 08-FEB-99
3 rows selected.
SVRMGR> ! rm /oracle/data/ora04/bob239/ora_log_02*
3.3 SVRMGR> alter system switch logfile;
ORA-03113: end-of-file on communication channel
SVRMGR> exit
Server Manager complete.
$ tail /oracle/admin/bob239/bdump/alert_bob239.log
Mon Feb 8 10:42:18 1999
Thread 1 advanced to log sequence 27
Current log# 3 seq# 27 mem# 0: /oracle/data/ora04/bob239/ora_log_03_01.rdo
Current log# 3 seq# 27 mem# 1: /oracle/data/ora04/bob239/ora_log_03_02.rdo
Thread 1 advanced to log sequence 28
Current log# 1 seq# 28 mem# 0: /oracle/data/ora04/bob239/ora_log_01_02.rdo
Current log# 1 seq# 28 mem# 1: /oracle/data/ora04/bob239/ora_log_01_01.rdo
Instance terminating due to error 313
Instance terminated by LGWR, pid = 13676
3.4 $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: ‘/oracle/data/ora04/bob239/ora_log_02_02.rdo’
ORA-00312: online log 2 thread 1: ‘/oracle/data/ora04/bob239/ora_log_02_01.rdo’
3.4 SVRMGR> alter database drop logfile group 2;
Statement processed.
3.5 SVRMGR> alter database open;
Statement processed.
SVRMGR> alter database add logfile group 2
2> (‘/oracle/data/ora04/bob239/ora_log_02_01.rdo’,
3> ‘/oracle/data/ora04/bob239/ora_log_02_02.rdo’) size 1m;
Statement processed.
Recovery Scenario 9 – Recovering from a lost control file.
- Loss of a single control file
1.1. Remove a control file
1.2. Perform an activity that will generate an error
1.3. Shutdown the database
1.4. Copy a control file to the lost control file
1.5. Restart the instance
- Loss of all control files
2.1. Issue ALTER DATABASE BACKUP CONTROLFILE TO TRACE. This should be done periodically, possibly as part of your backup process.
2.2. Remove all control files
2.3. Perform an activity that will generate an error
2.4. Shutdown the database
2.5. Edit the CREATE CONTROLFILE script and run it to recreate the controlfile and open the database
1.1 $ cd /oracle/data/ora04/bob239
$ ls
control01.con ora_log_01_02.rdo ora_log_03_02.rdo tools_t01_01.dbf
control02.con ora_log_02_01.rdo roll_t01_01.dbf users_t01_01.dbf
control03.con ora_log_02_02.rdo system_01.dbf
ora_log_01_01.rdo ora_log_03_01.rdo temp_t01_01.dbf
$ rm control02.con
1.2 $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> select name from v$datafile;
NAME
——————————————————————————–
ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile: ‘/oracle/data/ora04/bob239/control02.con’
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
1.3 SVRMGR> shutdown
ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile: ‘/oracle/data/ora04/bob239/control02.con’
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
SVRMGR> shutdown abort
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
1.4 $ ls
control01.con ora_log_02_01.rdo roll_t01_01.dbf users_t01_01.dbf
control03.con ora_log_02_02.rdo system_01.dbf
ora_log_01_01.rdo ora_log_03_01.rdo temp_t01_01.dbf
ora_log_01_02.rdo ora_log_03_02.rdo tools_t01_01.dbf
$ cp control01.con control02.con
1.5 $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
Database mounted.
Database opened.
SVRMGR> exit
Server Manager complete.
$ tail /oracle/admin/bob239/bdump/alert_bob239.log
Tue Feb 9 07:32:06 1999
Thread 1 advanced to log sequence 74
Thread 1 opened at log sequence 74
Current log# 3 seq# 74 mem# 0: /oracle/data/ora04/bob239/ora_log_03_01.rdo
Successful open of redo thread 1.
Tue Feb 9 07:32:06 1999
SMON: enabling cache recovery
SMON: enabling tx recovery
Tue Feb 9 07:32:07 1999
Completed: alter database open
2.1 $ rm /oracle/admin/bob239/udump/*
$ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Tue Feb 9 7:36:9 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> alter database backup controlfile to trace;
Database altered.
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
2.2 $ cd /oracle/data/ora04/bob239
$ ls
control01.con ora_log_01_02.rdo ora_log_03_02.rdo tools_t01_01.dbf
control02.con ora_log_02_01.rdo roll_t01_01.dbf users_t01_01.dbf
control03.con ora_log_02_02.rdo system_01.dbf
ora_log_01_01.rdo ora_log_03_01.rdo temp_t01_01.dbf
$ rm control*
2.3 $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> select name from v$database;
NAME
———
ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile: ‘/oracle/data/ora04/bob239/control01.con’
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
2.4 SVRMGR> shutdown abort
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
2.5 $ cd /oracle/admin/bob239/udump
$ ls
bob239_ora_17692.trc
$ cp bob239_ora_17692.trc controlfile.sql
$ vi controlfile.sql
$ cat controlfile.sql
set echo on
connect internal
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “BOB239″ NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 843
LOGFILE
GROUP 1 ‘/oracle/data/ora04/bob239/ora_log_01_01.rdo’ SIZE 1M,
GROUP 2 ‘/oracle/data/ora04/bob239/ora_log_02_01.rdo’ SIZE 1M,
GROUP 3 ‘/oracle/data/ora04/bob239/ora_log_03_01.rdo’ SIZE 1M
DATAFILE
‘/oracle/data/ora04/bob239/system_01.dbf’,
‘/oracle/data/ora04/bob239/roll_t01_01.dbf’,
‘/oracle/data/ora04/bob239/temp_t01_01.dbf’,
‘/oracle/data/ora04/bob239/tools_t01_01.dbf’,
‘/oracle/data/ora04/bob239/users_t01_01.dbf’
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
$ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> @controlfile.sql
Echo ON
SVRMGR> connect internal
Connected.
SVRMGR> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
SVRMGR> CREATE CONTROLFILE REUSE DATABASE “BOB239″ NORESETLOGS ARCHIVELOG
2> MAXLOGFILES 32
3> MAXLOGMEMBERS 2
4> MAXDATAFILES 512
5> MAXINSTANCES 8
6> MAXLOGHISTORY 843
7> LOGFILE
8> GROUP 1 ‘/oracle/data/ora04/bob239/ora_log_01_01.rdo’ SIZE 1M,
9> GROUP 2 ‘/oracle/data/ora04/bob239/ora_log_02_01.rdo’ SIZE 1M,
10> GROUP 3 ‘/oracle/data/ora04/bob239/ora_log_03_01.rdo’ SIZE 1M
11> DATAFILE
12> ‘/oracle/data/ora04/bob239/system_01.dbf’,
13> ‘/oracle/data/ora04/bob239/roll_t01_01.dbf’,
14> ‘/oracle/data/ora04/bob239/temp_t01_01.dbf’,
15> ‘/oracle/data/ora04/bob239/tools_t01_01.dbf’,
16> ‘/oracle/data/ora04/bob239/users_t01_01.dbf’
17> ;
Statement processed.
SVRMGR> # Recovery is required if any of the datafiles are restored backups,
SVRMGR> # or if the last shutdown was not normal or immediate.
SVRMGR> RECOVER DATABASE
Media recovery complete.
SVRMGR> # All logs need archiving and a log switch is needed.
SVRMGR> ALTER SYSTEM ARCHIVE LOG ALL;
Statement processed.
SVRMGR> # Database can now be opened normally.
SVRMGR> ALTER DATABASE OPEN;
Statement processed.
Recovery Scenario 10 – Recovering from a system crash while in backup mode
- Put the database into backup mode
- Abort the instance to simulate a system crash
- Attempt to startup the instance, to prove that recovery is required
- Generate SQL to take the datafiles out of backup mode ( Oracle 7.2 or above)
- Run the generated script
- Open the database
Prior to 7.2, you will have to issue the RECOVER DATABASE command after you STARTUP MOUNT the instance. You will need to have all archived redo logs created since the time that the database was put into backup mode.
1. $ cd /oracle/data/ora05/arch/bob239
$ ls
arch.log1_71.arc.Z arch.log1_74.arc end_bob239_backup.err
arch.log1_72.arc arch.log1_75.arc end_bob239_backup.sql
arch.log1_72.arc.Z begin_bob239_backup.err
arch.log1_73.arc begin_bob239_backup.sql
$ sqlplus -s / @begin_bob239_backup.sql
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
2. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
. SVRMGR> connect internal
Connected.
SVRMGR> shutdown abort
ORACLE instance shut down.
3. SVRMGR> startup
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/oracle/data/ora04/bob239/system_01.dbf’
SVRMGR> select * from v$backup;
FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 ACTIVE 133634 09-FEB-99
2 ACTIVE 133633 09-FEB-99
3 ACTIVE 133635 09-FEB-99
4 ACTIVE 133636 09-FEB-99
5 ACTIVE 133637 09-FEB-99
5 rows selected.
4. SVRMGR> spool end_backup.sql
SVRMGR> select ‘alter database datafile ”’ || name || ”’ end backup;’
2> from v$datafile;
‘ALTERDATABASEDATAFILE”’||NAME||”’ENDBACKUP;’
——————————————————————————–
alter database datafile ‘/oracle/data/ora04/bob239/system_01.dbf’ end backup;
alter database datafile ‘/oracle/data/ora04/bob239/roll_t01_01.dbf’ end backup;
alter database datafile ‘/oracle/data/ora04/bob239/temp_t01_01.dbf’ end backup;
alter database datafile ‘/oracle/data/ora04/bob239/tools_t01_01.dbf’ end backup;
alter database datafile ‘/oracle/data/ora04/bob239/users_t01_01.dbf’ end backup;
5 rows selected.
SVRMGR> spool off
SVRMGR> ! vi end_backup.sql
SVRMGR> ! cat end_backup.sql
alter database datafile ‘/oracle/data/ora04/bob239/system_01.dbf’ end backup;
alter database datafile ‘/oracle/data/ora04/bob239/roll_t01_01.dbf’ end backup;
alter database datafile ‘/oracle/data/ora04/bob239/temp_t01_01.dbf’ end backup;
alter database datafile ‘/oracle/data/ora04/bob239/tools_t01_01.dbf’ end backup;
alter database datafile ‘/oracle/data/ora04/bob239/users_t01_01.dbf’ end backup;
5. SVRMGR> @end_backup.sql
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
6. SVRMGR> alter database open;
Statement processed.
SVRMGR>
Relocating Data files
Normally when you want to move a datafile, you take the tablespace offline, move the file, and use ALTER DATABASE RENAME FILE, and bring the tablespace back on line. This can be very tedious if you have to relocate many or all of the datafiles in the database.
In Recovery Scenario 6, we learned how to use the CREATE CONTROLFILE command to tell Oracle that we have recovered the files to alternate locations. We can use a similar procedure to move the datafiles without actually having to perform a recovery.
In this example, we will move the datafiles back to the original directory on /oracle/data/ora01.
- Create a trace file with the CREATE CONTROLFILE command.
- Shutdown the instance
- Move the files
- Edit the trace file to remove comments and to specify the new location.
- Edit config.ora to specify the new location of the controlfile ( and archive location , if you move it).
- Run the edited file to recreate the controlfile and open the database.
1. $ rm /oracle/admin/bob239/udump/*
$ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> alter database backup controlfile to trace;
Statement processed.
2. SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
3. $ cd /oracle/data/ora04/bob239
$ ls
control01.con ora_log_01_02.rdo ora_log_03_02.rdo tools_t01_01.dbf
control02.con ora_log_02_01.rdo roll_t01_01.dbf users_t01_01.dbf
control03.con ora_log_02_02.rdo system_01.dbf
ora_log_01_01.rdo ora_log_03_01.rdo temp_t01_01.dbf
$ mv * /oracle/data/ora01/bob239
$ cd /oracle/data/ora01/bob239
$ ls
control01.con ora_log_01_02.rdo ora_log_03_02.rdo tools_t01_01.dbf
control02.con ora_log_02_01.rdo roll_t01_01.dbf users_t01_01.dbf
control03.con ora_log_02_02.rdo system_01.dbf
ora_log_01_01.rdo ora_log_03_01.rdo temp_t01_01.dbf
4. $ cd /oracle/admin/bob239/udump
$ ls
bob239_ora_28851.trc
$ mv bob239_ora_28851.trc controlfile.sql
$ vi controlfile.sql
$ cat controlfile.sql
set echo on
connect internal
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “BOB239″ NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 843
LOGFILE
GROUP 1 ‘/oracle/data/ora01/bob239/ora_log_01_01.rdo’ SIZE 1M,
GROUP 2 ‘/oracle/data/ora01/bob239/ora_log_02_01.rdo’ SIZE 1M,
GROUP 3 ‘/oracle/data/ora01/bob239/ora_log_03_01.rdo’ SIZE 1M
DATAFILE
‘/oracle/data/ora01/bob239/system_01.dbf’,
‘/oracle/data/ora01/bob239/roll_t01_01.dbf’,
‘/oracle/data/ora01/bob239/temp_t01_01.dbf’,
‘/oracle/data/ora01/bob239/tools_t01_01.dbf’,
‘/oracle/data/ora01/bob239/users_t01_01.dbf’
;
# Database can now be opened normally.
ALTER DATABASE OPEN;
5. $ cd /oracle/admin/bob239/pfile
$ vi configbob239.ora
$ grep control configbob239.ora
control_files = (/oracle/data/ora01/bob239/control01.con,
/oracle/data/ora01/bob239/control02.con,
/oracle/data/ora01/bob239/control03.con)
6. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> @controlfile
Echo ON
SVRMGR> connect internal
Connected.
SVRMGR> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
SVRMGR> CREATE CONTROLFILE REUSE DATABASE “BOB239″ NORESETLOGS ARCHIVELOG
2> MAXLOGFILES 32
3> MAXLOGMEMBERS 2
4> MAXDATAFILES 512
5> MAXINSTANCES 8
6> MAXLOGHISTORY 843
7> LOGFILE
8> GROUP 1 ‘/oracle/data/ora01/bob239/ora_log_01_01.rdo’ SIZE 1M,
9> GROUP 2 ‘/oracle/data/ora01/bob239/ora_log_02_01.rdo’ SIZE 1M,
10> GROUP 3 ‘/oracle/data/ora01/bob239/ora_log_03_01.rdo’ SIZE 1M
11> DATAFILE
12> ‘/oracle/data/ora01/bob239/system_01.dbf’,
13> ‘/oracle/data/ora01/bob239/roll_t01_01.dbf’,
14> ‘/oracle/data/ora01/bob239/temp_t01_01.dbf’,
15> ‘/oracle/data/ora01/bob239/tools_t01_01.dbf’,
16> ‘/oracle/data/ora01/bob239/users_t01_01.dbf’
17> ;
Statement processed.
SVRMGR> # Database can now be opened normally.
SVRMGR> ALTER DATABASE OPEN;
Statement processed.
Renaming an instance and database
It is occasionally necessary to rename an instance. Technically, you can do this without renaming the database that the instance accesses. However, it is generally better to keep the two names the same.
This example shows you how to use the CREATE CONTROLFILE command to rename the database, and all of the steps that are required to rename the instance.
- Create a trace file that contains the CREATE CONTROLFILE command.
- Shutdown the database
- Rename the directories that contain the data files, control files, and online redo logs.
- Rename the archive directory
- Rename the admin directory
- In the pfile directory, rename the init.ora and config.ora files, and edit them to use the new instance name.
- Remove the old symbolic link for the init.ora file in $ORACLE_HOME/dbs and create a new one.
- Edit the oratab file to change the entry for this instance.
- Redefine ORACLE_SID.
- Edit the trace file ( now in the admin directory for the ‘new’ instance) to rename the database, locate the files, and change the global name of the database. You will need to use RESETLOGS.
- Run the edited script to rename and open the database.
- Update your listener.ora and tnsnames.ora files, and restart your listener.
1. $ rm /oracle/admin/bob239/udump/*
$ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> alter database backup controlfile to trace;
Statement processed.
2. SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
3. $ mv /oracle/data/ora01/bob239 /oracle/data/ora01/bob999
4. $ mv /oracle/data/ora05/arch/bob239 /oracle/data/ora05/arch/bob999
5. $ mv /oracle/admin/bob239 /oracle/admin/bob999
6. $ cd /oracle/admin/bob999/pfile
$ ls
configbob239.ora initbob239.ora
$ mv configbob239.ora configbob999.ora
$ mv initbob239.ora initbob999.ora
$ grep bob239 *
configbob999.ora:control_files = (/oracle/data/ora01/bob239/control01.con,
configbob999.ora: /oracle/data/ora01/bob239/control02.con,
configbob999.ora: /oracle/data/ora01/bob239/control03.con)
configbob999.ora:background_dump_dest = /oracle/admin/bob239/bdump
configbob999.ora:core_dump_dest = /oracle/admin/bob239/cdump
configbob999.ora:user_dump_dest = /oracle/admin/bob239/udump
configbob999.ora:log_archive_dest = /oracle/data/ora05/arch/bob239/arch.log
configbob999.ora:db_name = bob239
initbob999.ora:ifile=/oracle/admin/bob239/pfile/configbob239.ora
initbob999.ora:#mts_listener_address=”(ADDRESS=(PROTOCOL=ipc)(KEY=bob239))”
$ vi configbob999.ora
$ vi initbob999.ora
$ grep bob999 *
configbob999.ora:control_files = (/oracle/data/ora01/bob999/control01.con,
configbob999.ora: /oracle/data/ora01/bob999/control02.con,
configbob999.ora: /oracle/data/ora01/bob999/control03.con)
configbob999.ora:background_dump_dest = /oracle/admin/bob999/bdump
configbob999.ora:core_dump_dest = /oracle/admin/bob999/cdump
configbob999.ora:user_dump_dest = /oracle/admin/bob999/udump
configbob999.ora:log_archive_dest = /oracle/data/ora05/arch/bob999/arch.log
configbob999.ora:db_name = bob999
initbob999.ora:ifile=/oracle/admin/bob999/pfile/configbob999.ora
initbob999.ora:#mts_listener_address=”(ADDRESS=(PROTOCOL=ipc)(KEY=bob999))”
7. $ cd $ORACLE_HOME/dbs
$ ls -l initbob239.ora
lrwxrwxrwx 1 oracle dba 41 Feb 7 13:42 initbob239.ora -> /oracle/
admin/bob239/pfile/initbob239.ora
$ rm initbob239.ora
$ ln -s /oracle/admin/bob999/pfile/initbob999.ora ./initbob999.ora
8. $ vi /var/opt/oracle/oratab
$ grep bob999 /var/opt/oracle/oratab
bob999:/oracle/product/root3:N
9. $ export ORACLE_SID=bob999
10. $ cd /oracle/admin/bob999/udump
$ ls
bob239_ora_3997.trc
$ mv bob239_ora_3997.trc controlfile.sql
$ vi controlfile.sql
$ cat controlfile.sql
#
# Note the SET in front of DATABASE.
# Note the use of RESETLOGS
# Note how we change the global name of the database.
#
set echo on
connect internal
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE “BOB999″ RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 843
LOGFILE
GROUP 1 ‘/oracle/data/ora01/bob999/ora_log_01_01.rdo’ SIZE 1M,
GROUP 2 ‘/oracle/data/ora01/bob999/ora_log_02_01.rdo’ SIZE 1M,
GROUP 3 ‘/oracle/data/ora01/bob999/ora_log_03_01.rdo’ SIZE 1M
DATAFILE
‘/oracle/data/ora01/bob999/system_01.dbf’,
‘/oracle/data/ora01/bob999/roll_t01_01.dbf’,
‘/oracle/data/ora01/bob999/temp_t01_01.dbf’,
‘/oracle/data/ora01/bob999/tools_t01_01.dbf’,
‘/oracle/data/ora01/bob999/users_t01_01.dbf’
;
# Database can now be opened normally.
ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE RENAME GLOBAL_NAME TO BOB999;
11. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> @controlfile
Echo ON
SVRMGR> connect internal
Connected.
SVRMGR> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
SVRMGR> CREATE CONTROLFILE REUSE SET DATABASE “BOB999″ RESETLOGS ARCHIVELOG
2> MAXLOGFILES 32
3> MAXLOGMEMBERS 2
4> MAXDATAFILES 512
5> MAXINSTANCES 8
6> MAXLOGHISTORY 843
7> LOGFILE
8> GROUP 1 ‘/oracle/data/ora01/bob999/ora_log_01_01.rdo’ SIZE 1M,
9> GROUP 2 ‘/oracle/data/ora01/bob999/ora_log_02_01.rdo’ SIZE 1M,
10> GROUP 3 ‘/oracle/data/ora01/bob999/ora_log_03_01.rdo’ SIZE 1M
11> DATAFILE
12> ‘/oracle/data/ora01/bob999/system_01.dbf’,
13> ‘/oracle/data/ora01/bob999/roll_t01_01.dbf’,
14> ‘/oracle/data/ora01/bob999/temp_t01_01.dbf’,
15> ‘/oracle/data/ora01/bob999/tools_t01_01.dbf’,
16> ‘/oracle/data/ora01/bob999/users_t01_01.dbf’
17> ;
Statement processed.
SVRMGR> # Database can now be opened normally.
SVRMGR> ALTER DATABASE OPEN RESETLOGS;
Statement processed.
SVRMGR> ALTER DATABASE RENAME GLOBAL_NAME TO BOB999;
Statement processed.
12. I did not have a listener configured for this instance, so I don’t have anything to show you. However, the edits are pretty straightforward.
Cloning a database
Cloning, or copying, a database can be accomplished by following a similar procedure to renaming the database. The difference will be that instead of moving the files, we will be copying the files.
You can shutdown the source database for the copy, or, if you are in archivelog mode, put the tablespaces in backup mode before copying the files. Our example will show how to do the copy when the database is up. We will be copying bob999 back to bob239.
- Create the data file and archive directories for the new database
- From the source database, create a trace file that contains the CREATE CONTROLFILE command.
- Put the source database into backup mode
- Copy the datafiles, control files, and online redo log files to the new data directory.
- Take the source database out of backup mode.
- Force a log switch at the source database.
- Copy the archived redo logs from the source database to the archive directory for the new database. Rename the files to match the new instance name, if necessary.
- Copy the admin directory of the source database into a new admin directory for the new database
- In the new pfile directory, rename the init.ora and config.ora files, and edit them to use the new instance name.
- Add a symbolic link for the new init.ora file in $ORACLE_HOME/dbs.
- Edit the oratab file to add an entry for the new instance.
- Redefine ORACLE_SID.
- Edit the trace file ( now in the admin directory for the new instance) to rename the database, locate the files, perform the recovery, and change the global name of the database. You will need to use RESETLOGS.
- Run the edited script to rename, recover and open the database. NOTE: On some operating systems and versions of Oracle, if the clone is on the same server as the source instance, you cannot perform this step unless you briefly shutdown the source instance.
- Update your listener.ora and tnsnames.ora files to define the new instance, and restart your listener.
1. $ mkdir /oracle/data/ora01/bob239
$ mkdir /oracle/data/ora05/arch/bob239
2. $ rm /oracle/admin/bob999/udump/*
$ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Tue Feb 9 22:54:17 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob999> alter database backup controlfile to trace;
Database altered.
bob999> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
3. $ cd /oracle/data/ora05/arch/bob999
$ rm *
$ sqlplus -s / @/oracle/local/sql/build_begin_backup.sql
$ sqlplus -s / @begin_bob999_backup.sql
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
4. $ cp /oracle/data/ora01/bob999/* /oracle/data/ora01/bob239
5. $ sqlplus -s / @/oracle/local/sql/build_end_backup.sql
$ sqlplus -s / @end_bob999_backup.sql
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
6. $ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Tue Feb 9 22:57:55 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob999> alter system switch logfile;
System altered.
bob999> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
7. $ cp *.arc /oracle/data/ora05/arch/bob239
8. $ cd /oracle/admin
$ cp -R bob999 bob239
9. $ cd /oracle/admin/bob239/pfile
$ ls
configbob999.ora initbob999.ora
$ mv configbob999.ora configbob239.ora
$ mv initbob999.ora initbob239.ora
$ vi configbob239.ora
$ vi initbob239.ora
$ grep bob239 *
configbob239.ora:control_files = (/oracle/data/ora01/bob239/control01.con,
configbob239.ora: /oracle/data/ora01/bob239/control02.con,
configbob239.ora: /oracle/data/ora01/bob239/control03.con)
configbob239.ora:background_dump_dest = /oracle/admin/bob239/bdump
configbob239.ora:core_dump_dest = /oracle/admin/bob239/cdump
configbob239.ora:user_dump_dest = /oracle/admin/bob239/udump
configbob239.ora:log_archive_dest = /oracle/data/ora05/arch/bob239/arch.log
configbob239.ora:db_name = bob239
initbob239.ora:ifile=/oracle/admin/bob239/pfile/configbob239.ora
initbob239.ora:#mts_listener_address=”(ADDRESS=(PROTOCOL=ipc)(KEY=bob239))”
10. $ cd $ORACLE_HOME/dbs
$ ln -s /oracle/admin/bob239/pfile/initbob239.ora ./initbob239.ora
11. $ vi /var/opt/oracle/oratab
$ grep bob239 /var/opt/oracle/oratab
bob239:/oracle/product/root3:N
12. $ export ORACLE_SID=bob239
13. $ cd /oracle/admin/bob239/udump
$ ls
bob999_ora_17994.trc
$ mv bob999_ora_17994.trc controlfile.sql
$ vi controlfile.sql
$ cat controlfile.sql
set echo on
connect internal
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE set DATABASE “BOB239″ RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 843
LOGFILE
GROUP 1 ‘/oracle/data/ora01/bob239/ora_log_01_01.rdo’ SIZE 1M,
GROUP 2 ‘/oracle/data/ora01/bob239/ora_log_02_01.rdo’ SIZE 1M,
GROUP 3 ‘/oracle/data/ora01/bob239/ora_log_03_01.rdo’ SIZE 1M
DATAFILE
‘/oracle/data/ora01/bob239/system_01.dbf’,
‘/oracle/data/ora01/bob239/roll_t01_01.dbf’,
‘/oracle/data/ora01/bob239/temp_t01_01.dbf’,
‘/oracle/data/ora01/bob239/tools_t01_01.dbf’,
‘/oracle/data/ora01/bob239/users_t01_01.dbf’
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
set autorecovery on
RECOVER DATABASE using backup controlfile until cancel
# Database can now be opened normally.
ALTER DATABASE OPEN resetlogs;
alter database rename global_name to bob239;
14. $ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> @controlfile
Echo ON
SVRMGR> connect internal
Connected.
SVRMGR> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
SVRMGR> CREATE CONTROLFILE REUSE set DATABASE “BOB239″ RESETLOGS ARCHIVELOG
2> MAXLOGFILES 32
3> MAXLOGMEMBERS 2
4> MAXDATAFILES 512
5> MAXINSTANCES 8
6> MAXLOGHISTORY 843
7> LOGFILE
8> GROUP 1 ‘/oracle/data/ora01/bob239/ora_log_01_01.rdo’ SIZE 1M,
9> GROUP 2 ‘/oracle/data/ora01/bob239/ora_log_02_01.rdo’ SIZE 1M,
10> GROUP 3 ‘/oracle/data/ora01/bob239/ora_log_03_01.rdo’ SIZE 1M
11> DATAFILE
12> ‘/oracle/data/ora01/bob239/system_01.dbf’,
13> ‘/oracle/data/ora01/bob239/roll_t01_01.dbf’,
14> ‘/oracle/data/ora01/bob239/temp_t01_01.dbf’,
15> ‘/oracle/data/ora01/bob239/tools_t01_01.dbf’,
16> ‘/oracle/data/ora01/bob239/users_t01_01.dbf’
17> ;
Statement processed.
SVRMGR> # Recovery is required if any of the datafiles are restored backups,
SVRMGR> # or if the last shutdown was not normal or immediate.
SVRMGR> set autorecovery on
Autorecovery ON
SVRMGR> RECOVER DATABASE using backup controlfile until cancel
ORA-00279: change 153758 generated at 02/09/99 22:56:06 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora05/arch/bob239/arch.log1_1.arc
ORA-00280: change 153758 for thread 1 is in sequence #1
Log applied.
ORA-00279: change 153763 generated at 02/09/99 22:57:59 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora05/arch/bob239/arch.log1_2.arc
ORA-00280: change 153763 for thread 1 is in sequence #2
ORA-00278: log file ‘/oracle/data/ora05/arch/bob239/arch.log1_1.arc’ no longer n
eeded for this recovery
ORA-00308: cannot open archived log ‘/oracle/data/ora05/arch/bob239/arch.log1_2.
arc’
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
SVRMGR> # Database can now be opened normally.
SVRMGR> ALTER DATABASE OPEN resetlogs;
Statement processed.
SVRMGR> alter database rename global_name to bob239;
Statement processed.
SVRMGR> exit
Server Manager complete.
$ ps -ef | egrep ‘bob239|bob999′
oracle 12479 1 0 22:14:25 ? 0:00 ora_lgwr_bob999
oracle 12483 1 0 22:14:25 ? 0:00 ora_smon_bob999
oracle 26321 28502 0 23:12:00 pts/0 0:00 egrep bob239|bob999
oracle 26248 1 0 23:11:21 ? 0:00 ora_smon_bob239
oracle 12485 1 0 22:14:25 ? 0:00 ora_reco_bob999
oracle 12481 1 0 22:14:25 ? 0:00 ora_ckpt_bob999
oracle 26250 1 0 23:11:21 ? 0:00 ora_reco_bob239
oracle 26238 1 0 23:11:20 ? 0:00 ora_pmon_bob239
oracle 12477 1 0 22:14:25 ? 0:00 ora_arch_bob999
oracle 26240 1 0 23:11:20 ? 0:00 ora_dbw0_bob239
oracle 12473 1 0 22:14:24 ? 0:00 ora_pmon_bob999
oracle 12475 1 0 22:14:25 ? 0:00 ora_dbw0_bob999
oracle 26242 1 0 23:11:20 ? 0:00 ora_arch_bob239
oracle 26244 1 0 23:11:21 ? 0:00 ora_lgwr_bob239
oracle 26246 1 0 23:11:21 ? 0:00 ora_ckpt_bob239
Partial Point in Time Recovery
Oracle does not have any method to rollback committed transactions. So, if someone makes a mistake in the database that is important enough that it has to be reversed, you must recover the database from a hot backup, and use time based recovery to roll the database forward in time until right before the error occurred.
This might be acceptable if the application is in an instance all by itself. However, if you have multiple applications in the instance, it is very unlikely that the other applications are going to want to lose any data that they have entered after the error was made in the other application.
Now that we know how to clone database and to do time based recoveries, we have all of the tools that we need to be able to solve this problem. Basically, we will recover the source database to a clone, and roll the clone forward to the point in time before the error. After that, we will use exp to export the lost data from the recovered database, and imp to import it into the source database.
You will also see that we only need to recover the system tablespaces and the tablespace(s) that contain the data that we are restoring. You will not need to recover tablespaces used by other applications.
Follow the steps shown in the Cloning a Database scenario above, with the following exceptions:
3. Instead of putting the source database into backup mode, locate a hot backup of that database that occurred before the errors were made.
4. You will not need to recover all of the datafiles. Recover the control files, the online redo logs, and the data files for the system, rollback, and temp tablespaces, along with the data files for the tablespace that contains the data that you are looking for.
14. When you edit the script that contains the CREATE CONTROLFILE command, remove the lines for the data files that you did not restore. Modify the CREATE CONTROLFILE script to use time based recovery.
- After you have opened the cloned database, export the data and import it back into the source database.
$ export ORACLE_SID=bob999
$ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
$ rm -rf /oracle/data/ora01/bob999
$ rm -rf /oracle/data/ora05/arch/bob999
$ rm -rf /oracle/admin/bob999
$ rm /oracle/data/ora02/bob239_backup/*
$ export ORACLE_SID=bob239
$ rm /oracle/data/ora05/arch/bob239/*
$ cd /oracle/data/ora05/arch/bob239
$ sqlplus -s / @/oracle/local/sql/build_begin_backup.sql
$ sqlplus -s / @begin_bob239_backup.sql
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
$ cd /oracle/data/ora02/bob239_backup/
$ set -x
$ . /oracle/local/sql/bob239_files.sql
+ . /oracle/local/sql/bob239_files.sql
+ cp /oracle/data/ora01/bob239/control01.con .
+ cp /oracle/data/ora01/bob239/control02.con .
+ cp /oracle/data/ora01/bob239/control03.con .
+ cp /oracle/data/ora01/bob239/ora_log_01_01.rdo .
+ cp /oracle/data/ora01/bob239/ora_log_02_01.rdo .
+ cp /oracle/data/ora01/bob239/ora_log_03_01.rdo .
+ cp /oracle/data/ora01/bob239/roll_t01_01.dbf .
+ cp /oracle/data/ora01/bob239/system_01.dbf .
+ cp /oracle/data/ora01/bob239/temp_t01_01.dbf .
+ cp /oracle/data/ora01/bob239/tools_t01_01.dbf .
+ cp /oracle/data/ora01/bob239/users_t01_01.dbf .
$ set +x
$ cd /oracle/data/ora05/arch/bob239
$ sqlplus -s / @/oracle/local/sql/build_end_backup.sql
$ sqlplus -s / @end_bob239_backup.sql
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
$ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Wed Feb 10 6:54:44 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> alter system switch logfile;
System altered.
bob239> connect scott/tiger123
Connected.
bob239> select * from cat;
TABLE_NAME TABLE_TYPE
—————————— ———–
BOBTEMP TABLE
BONUS TABLE
BONUS_COPY TABLE
DEPT TABLE
DEPT_COPY TABLE
DUMMY TABLE
EMP TABLE
EMP_COPY TABLE
SALGRADE TABLE
SALGRADE_COPY TABLE
10 rows selected.
bob239> ! date
Wed Feb 10 06:56:09 EST 1999
bob239> drop table emp;
Table dropped.
bob239> connect /
Connected.
bob239> alter system switch logfile;
System altered.
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
$ mkdir /oracle/data/ora01/bob999
$ mkdir /oracle/data/ora05/arch/bob999
$ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Wed Feb 10 6:58:11 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
$ rm /oracle/admin/bob239/udump/*
$ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Wed Feb 10 6:58:32 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> alter database backup controlfile to trace;
Database altered.
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
$ cd /oracle/data/ora02/bob239_backup
$ ls
control01.con ora_log_01_01.rdo roll_t01_01.dbf tools_t01_01.dbf
control02.con ora_log_02_01.rdo system_01.dbf users_t01_01.dbf
control03.con ora_log_03_01.rdo temp_t01_01.dbf
$ cp control* ora_log* roll_t01_01.dbf system_01.dbf \
> temp_t01_01.dbf tools_t01_01.dbf /oracle/data/ora01/bob999
$ cd /oracle/data/ora05/arch/bob239
$ ls
arch.log1_1.arc arch.log1_4.arc end_bob239_backup.err
arch.log1_2.arc begin_bob239_backup.err end_bob239_backup.sql
arch.log1_3.arc begin_bob239_backup.sql
$ cp * /oracle/data/ora05/arch/bob999
$ cd /oracle/admin
$ cp -R bob239 bob999
$ cd /oracle/admin/bob999/pfile
$ ls
configbob239.ora initbob239.ora
$ mv configbob239.ora configbob999.ora
$ mv initbob239.ora initbob999.ora
$ vi configbob999.ora
$ vi initbob999.ora
$ grep bob999 *
configbob999.ora:control_files = (/oracle/data/ora01/bob999/control01.con,
configbob999.ora: /oracle/data/ora01/bob999/control02.con,
configbob999.ora: /oracle/data/ora01/bob999/control03.con)
configbob999.ora:background_dump_dest = /oracle/admin/bob999/bdump
configbob999.ora:core_dump_dest = /oracle/admin/bob999/cdump
configbob999.ora:user_dump_dest = /oracle/admin/bob999/udump
configbob999.ora:log_archive_dest = /oracle/data/ora05/arch/bob999/arch.log
configbob999.ora:db_name = bob999
initbob999.ora:ifile=/oracle/admin/bob999/pfile/configbob999.ora
initbob999.ora:#mts_listener_address=”(ADDRESS=(PROTOCOL=ipc)(KEY=bob999))”
$ ls -l $ORACLE_HOME/dbs/initbob999.ora
lrwxrwxrwx 1 oracle dba 41 Feb 9 22:00 /oracle/product/root3/dbs/
initbob999.ora -> /oracle/admin/bob999/pfile/initbob999.ora
$ grep bob999 /var/opt/oracle/oratab
bob999:/oracle/product/root3:N
$ export ORACLE_SID=bob999
$ cd /oracle/admin/bob999/udump
$ ls
bob239_ora_15863.trc
$ cp bob239_ora_15863.trc controlfile.sql
$ vi controlfile.sql
$ cat controlfile.sql
set echo on
connect internal
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE “BOB999″ RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 843
LOGFILE
GROUP 1 ‘/oracle/data/ora01/bob999/ora_log_01_01.rdo’ SIZE 1M,
GROUP 2 ‘/oracle/data/ora01/bob999/ora_log_02_01.rdo’ SIZE 1M,
GROUP 3 ‘/oracle/data/ora01/bob999/ora_log_03_01.rdo’ SIZE 1M
DATAFILE
‘/oracle/data/ora01/bob999/system_01.dbf’,
‘/oracle/data/ora01/bob999/roll_t01_01.dbf’,
‘/oracle/data/ora01/bob999/temp_t01_01.dbf’,
‘/oracle/data/ora01/bob999/tools_t01_01.dbf’
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
set autorecovery on
RECOVER DATABASE using backup controlfile until time ’1999-02-10 06:56:09′
ALTER DATABASE OPEN resetlogs;
alter database rename global_name to bob999;
$ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> @controlfile
Echo ON
SVRMGR> connect internal
Connected.
SVRMGR> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
SVRMGR> CREATE CONTROLFILE REUSE SET DATABASE “BOB999″ RESETLOGS ARCHIVELOG
2> MAXLOGFILES 32
3> MAXLOGMEMBERS 2
4> MAXDATAFILES 512
5> MAXINSTANCES 8
6> MAXLOGHISTORY 843
7> LOGFILE
8> GROUP 1 ‘/oracle/data/ora01/bob999/ora_log_01_01.rdo’ SIZE 1M,
9> GROUP 2 ‘/oracle/data/ora01/bob999/ora_log_02_01.rdo’ SIZE 1M,
10> GROUP 3 ‘/oracle/data/ora01/bob999/ora_log_03_01.rdo’ SIZE 1M
11> DATAFILE
12> ‘/oracle/data/ora01/bob999/system_01.dbf’,
13> ‘/oracle/data/ora01/bob999/roll_t01_01.dbf’,
14> ‘/oracle/data/ora01/bob999/temp_t01_01.dbf’,
15> ‘/oracle/data/ora01/bob999/tools_t01_01.dbf’
16> ;
Statement processed.
SVRMGR> # Recovery is required if any of the datafiles are restored backups,
SVRMGR> # or if the last shutdown was not normal or immediate.
SVRMGR> set autorecovery on
Autorecovery ON
SVRMGR> RECOVER DATABASE using backup controlfile until time ’1999-02-10 06:56:0
9′
ORA-00279: change 153799 generated at 02/10/99 06:51:05 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora05/arch/bob999/arch.log1_1.arc
ORA-00280: change 153799 for thread 1 is in sequence #1
Log applied.
ORA-00279: change 153804 generated at 02/10/99 06:54:49 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora05/arch/bob999/arch.log1_2.arc
ORA-00280: change 153804 for thread 1 is in sequence #2
ORA-00278: log file ‘/oracle/data/ora05/arch/bob999/arch.log1_1.arc’ no longer n
eeded for this recovery
Log applied.
Media recovery complete.
SVRMGR> ALTER DATABASE OPEN resetlogs;
Statement processed.
SVRMGR> alter database rename global_name to bob999;
Statement processed.
SVRMGR> select * from dba_data_files
2> where tablespace_name = ‘USERS_T01′;
FILE_NAME
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELAT
IVE_F AUT MAXBYTES MAXBLOCKS INCREMENT_
——————————————————————————–
———- —————————— ———- ———- ——— —–
—– — ———- ———- ———-
/oracle/product/root3/dbs/MISSING00005
5 USERS_T01 10485760 5120 AVAILABLE
5 NO 0 0 0
1 row selected.
SVRMGR> exit
Server Manager complete.
$ exp / file=emp.dmp tables=scott.emp
Export: Release 8.0.4.1.0 – Production on Wed Feb 10 7:12:35 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to: Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
About to export specified tables via Conventional Path …
Current user changed to SCOTT
. . exporting table EMP 14 rows exported
Export terminated successfully without warnings.
$ export ORACLE_SID=bob239
$ imp / file=emp.dmp full=y
Import: Release 8.0.4.1.0 – Production on Wed Feb 10 7:12:54 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to: Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
Export file created by EXPORT:V08.00.04 via conventional path
. importing OPS$ORACLE’s objects into OPS$ORACLE
. importing SCOTT’s objects into SCOTT
. . importing table “EMP” 14 rows imported
Import terminated successfully without warnings.
Home Grown Standby Database
You don’t have to wait until the version of Oracle that offers standby databases to have a standby database. We have shown you all of the tools that you need to be able to create your own standby database.
Basically, you clone the database, then continually apply the archived redo logs as they are generated. There will be some activities that will need to be dealt with manually ( such as adding a file to a tablespace ), and some activities that will invalidate the standby copy ( such as using the UNRECOVERABLE option).
Tablespace Point In Time Recovery ( TSPITR )
This feature, available in Oracle8, is similar to the ‘Partial Point In Time Recovery’ example that I showed earlier. A major difference when using TSPITR is that you actually re-attached the recovered tablespace datafile from the clone database back to the original database, avoiding the need to use exp and imp to move the data. This can save a considerable amount of time if you have a large database.
- Take a hot backup.
- Backup the controlfile
- Create a table in a different tablespace.
- Create a foreign key, to show how the process deals with dependencies
- Truncate the table
- Add a new table to the tablespace.
- Use TS_PITR_OBJECTS_TO_BE_DROPPED to identify the objects that will be lost during the recovery.
- Use TS_PITR_CHECK to identify constraints, then disable them
- Switch the logfile and take the tablespace offline
- Clone the database and recovery it
- Export the clone information
- Copy the recovered database datafile back to the source database directory
- Import the information about the recovered tablespace
- Bring the recovered tablespace online
1. $ cd /oracle/data/ora03/arch/bob239
$ sqlplus -s / @/oracle/local/sql/build_begin_backup.sql
$ sqlplus -s / @begin_bob239_backup.sql
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
$ mkdir /oracle/data/ora01/bob239c
$ cp /oracle/data/ora01/bob239/* /oracle/data/ora01/bob239c
$ sqlplus -s / @/oracle/local/sql/build_end_backup.sql
$ sqlplus -s / @end_bob239_backup.sql
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
$ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Fri Feb 12 7:3:22 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> alter system switch logfile;
System altered.
2. bob239> alter database backup controlfile
2 to ‘/oracle/data/ora01/bob239c/backup.con’;
Database altered.
3. bob239> connect scott/tiger123
Connected.
bob239> select * from user_ts_quotas;
TABLESPACE_NAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS
—————————— ———- ———- ———- ———-
USERS_T02 0 -1 0 -1
USERS_T01 51200 -1 25 -1
bob239> alter table dept add constraint dept_p primary key (deptno);
Table altered.
bob239> create table emp_copy tablespace users_t02
2 as select * from emp;
Table created.
4. bob239> alter table emp_copy add constraint emp_copy_r01
2 foreign key (deptno) references dept;
Table altered.
bob239> ! date
Fri Feb 12 08:018:50 EST 1999
5. bob239> truncate table emp_copy;
Table truncated.
bob239> truncate table emp;
Table truncated.
6. bob239> create table dept_copy tablespace users_t02
2 as select * from dept;
Table created.
bob239> connect /
Connected.
7. bob239> select owner, name from sys.ts_pitr_objects_to_be_dropped
2 where tablespace_name = ‘USERS_T02′ and creation_time >
3 to_date(’12-feb-99 08:02:40′,’dd-mon-yy hh24:mi:ss’);
OWNER NAME
—————————— ——————————
SCOTT DEPT_COPY
8. bob239> select * from sys.ts_pitr_check
2 where ( ts1_name = ‘USERS_T02′ and ts2_name <> ‘USERS_T02′ )
3 or ( ts1_name <> ‘USERS_T02′ and ts2_name = ‘USERS_T02′);
OBJ1_OWNER OBJ1_NAME
—————————— ——————————
OBJ1_SUBNAME OBJ1_TYPE TS1_NAME
—————————— ————— ——————————
OBJ2_NAME OBJ2_SUBNAME OBJ2_TYPE
—————————— —————————— —————
OBJ2_OWNER TS2_NAME
—————————— ——————————
CONSTRAINT_NAME
——————————
REASON
——————————————————————————
SCOTT DEPT
TABLE USERS_T01
EMP_COPY TABLE
SCOTT USERS_T02
SYS_C00567
constraint between tables not contained in recovery set
SCOTT DEPT
TABLE USERS_T01
EMP_COPY TABLE
SCOTT USERS_T02
EMP_COPY_R01
constraint between tables not contained in recovery set
bob239> alter table scott.emp_copy disable constraint emp_copy_r01;
Table altered.
bob239> select * from sys.ts_pitr_check
2 where ( ts1_name = ‘USERS_T02′ and ts2_name <> ‘USERS_T02′ )
3 or ( ts1_name <> ‘USERS_T02′ and ts2_name = ‘USERS_T02′);
no rows selected
9. bob239> alter system switch logfile;
System altered.
bob239> alter tablespace users_t02 offline;
Tablespace altered.
bob239> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
10. $ cd /oracle/admin/bob239/pfile
$ cp configbob239.ora configbob239c.ora
$ cp initbob239.ora initbob239c.ora
$ vi configbob239c.ora
$ vi initbob239c.ora
$ egrep -ih ‘control|ifile|convert|name_space’ *bob239c*
control_files = (/oracle/data/ora01/bob239c/backup.con)
ifile=/oracle/admin/bob239/pfile/configbob239c.ora
# of these files point to a master file using the IFILE command.
# your own. Then place an IFILE= line into each instance-specific
lock_name_space=CLONE
db_file_name_convert=”bob239″,”bob239c”
log_file_name_convert=”bob239″,”bob239c”
$ export ORACLE_SID=bob239c
$ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> startup nomount pfile=/oracle/admin/bob239/pfile/initbob239c.ora
ORACLE instance started.
Total System Global Area 4954304 bytes
Fixed Size 47296 bytes
Variable Size 4415488 bytes
Database Buffers 409600 bytes
Redo Buffers 81920 bytes
SVRMGR> alter database mount clone database;
Statement processed.
SVRMGR> select member from v$logfile;
MEMBER
——————————————————————————–
/oracle/data/ora01/bob239c/ora_log_01_01.rdo
/oracle/data/ora01/bob239c/ora_log_02_01.rdo
/oracle/data/ora01/bob239c/ora_log_03_01.rdo
3 rows selected.
SVRMGR> select name from v$datafile;
NAME
——————————————————————————–
/oracle/data/ora01/bob239c/system_01.dbf
/oracle/data/ora01/bob239c/roll_t01_01.dbf
/oracle/data/ora01/bob239c/temp_t01_01.dbf
/oracle/data/ora01/bob239c/tools_t01_01.dbf
/oracle/data/ora01/bob239c/users_t01_01.dbf
/oracle/data/ora01/bob239c/users_t02_01.dbf
6 rows selected.
SVRMGR> select ‘alter database datafile ”’ || name || ”’ online;’
2> from v$datafile where name not like ‘%users_t01%’;
‘ALTERDATABASEDATAFILE”’||NAME||”’ONLINE;’
——————————————————————————–
alter database datafile ‘/oracle/data/ora01/bob239c/system_01.dbf’ online;
alter database datafile ‘/oracle/data/ora01/bob239c/roll_t01_01.dbf’ online;
alter database datafile ‘/oracle/data/ora01/bob239c/temp_t01_01.dbf’ online;
alter database datafile ‘/oracle/data/ora01/bob239c/tools_t01_01.dbf’ online;
alter database datafile ‘/oracle/data/ora01/bob239c/users_t02_01.dbf’ online;
5 rows selected.
SVRMGR> alter database datafile ‘/oracle/data/ora01/bob239c/system_01.dbf’ onlin
e;
Statement processed.
SVRMGR> alter database datafile ‘/oracle/data/ora01/bob239c/roll_t01_01.dbf’ onl
ine;
Statement processed.
SVRMGR> alter database datafile ‘/oracle/data/ora01/bob239c/temp_t01_01.dbf’ onl
ine;
Statement processed.
SVRMGR> alter database datafile ‘/oracle/data/ora01/bob239c/tools_t01_01.dbf’ on
line;
Statement processed.
SVRMGR> alter database datafile ‘/oracle/data/ora01/bob239c/users_t02_01.dbf’ on
line;
Statement processed.
SVRMGR> set autorecovery on
Autorecovery ON
SVRMGR> recover database using backup controlfile until time ’1999-02-12 08:18:50′
ORA-00279: change 13568 generated at 02/12/99 08:15:28 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_18.dbf
ORA-00280: change 13568 for thread 1 is in sequence #18
Log applied.
ORA-00279: change 13574 generated at 02/12/99 08:17:04 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora03/arch/bob239/arch.log1_19.dbf
ORA-00280: change 13574 for thread 1 is in sequence #19
ORA-00278: log file ‘/oracle/data/ora03/arch/bob239/arch.log1_18.dbf’ no longer
needed for this recovery
Log applied.
Media recovery complete.
SVRMGR> alter database open resetlogs;
Statement processed.
SVRMGR> alter table scott.emp_copy disable constraint emp_copy_r01;
Statement processed.
SVRMGR> exit
Server Manager complete.
11. $ exp sys/manager123 file=tspitr.dmp point_in_time_recover=y \
> recovery_tablespaces=users_t02
Export: Release 8.0.4.1.0 – Production on Fri Feb 12 8:25:3 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to: Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects…
For tablespace USERS_T02 …
. exporting cluster definitions
. exporting table definitions
. . exporting table EMP_COPY
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
12. $ cp /oracle/data/ora01/bob239c/users_t02_01.dbf /oracle/data/ora01/bob239
13. $ export ORACLE_SID=bob239
$ imp sys/manager123 file=tspitr.dmp point_in_time_recover=true
Import: Release 8.0.4.1.0 – Production on Fri Feb 12 8:45:40 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to: Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
Export file created by EXPORT:V08.00.04 via conventional path
About to import Tablespace Point-in-time Recovery objects…
. importing SCOTT’s objects into SCOTT
. . importing table ”EMP_COPY”
. importing SYSTEM’s objects into SYSTEM
. importing SCOTT’s objects into SCOTT
Import terminated successfully without warnings.
14. $ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Fri Feb 12 8:46:3 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.1.0 – Production
bob239> alter tablespace users_t02 online;
Tablespace altered.
bob239> connect scott/tiger123
Connected.
bob239> select * from cat;
TABLE_NAME TABLE_TYPE
—————————— ———–
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
EMP_COPY TABLE
SALGRADE TABLE
6 rows selected.
bob239> select count(*) from emp_copy;
COUNT(*)
———-
14
bob239> select count(*) from emp;
COUNT(*)
———-
0
Standby Database
This feature, available in Oracle7, is similar to the ‘Cloning a Database’ example that I showed earlier. The basic idea is that you recover the database to another location, apply your archived redo logs to it until you have a disaster, the activate the standby database.
- Copy the admin directory of the instance and duplicate the directory structure of the primary database.
- Take a hot backup ( backup directly to the standby server)
- Create a standby controlfile and send it to the remote site.
- Replace the copied control files with the standby controlfile
- Mount the standby database
- Apply logs until you have a disaster at the primary database
- Activate the standby database
$ cd /oracle/admin
$ tar -cvf sbd.tar SBD
a SBD/ 0K
a SBD/bdump/ 0K
a SBD/bdump/alert_SBD.log 11K
a SBD/bdump/alert_SBD.log_19990224 15K
a SBD/bdump/alert_SBD.log_19990303 23K
a SBD/bdump/alert_SBD.log_19990304 16K
a SBD/bdump/alert_SBD.log_199903040831 6K
a SBD/bdump/alert_SBD.log_19990305 13K
a SBD/bdump/alert_SBD.log_19990306 13K
a SBD/bdump/alert_SBD.log_19990307 13K
a SBD/bdump/alert_SBD.log_19990308 13K
a SBD/bdump/alert_SBD.log_199903080731 5K
a SBD/bdump/alert_SBD.log_19990309 10K
a SBD/bdump/alert_SBD.log_19990310 13K
a SBD/cdump/ 0K
a SBD/udump/ 0K
a SBD/udump/sbd_ora_21088.trc 2K
a SBD/create/ 0K
a SBD/create/usrdfl_SBD.ksh 2K
a SBD/create/crdbSBD.sql 2K
a SBD/create/crdb2SBD.sql 5K
a SBD/create/newinst_SBD.ksh 3K
a SBD/create/crdbSBD.log 1K
a SBD/create/crdbSBD.lst 1K
a SBD/create/crdb2SBD.log 173K
a SBD/create/crdb2SBD.lst 173K
a SBD/create/newinst_SBD.log 14K
a SBD/pfile/ 0K
a SBD/pfile/initSBD.ora 6K
a SBD/pfile/configSBD.ora 2K
$ rsh gbip-pc2.d52.lilly.com
Last login: Wed Mar 10 22:26:54 from icuxb3.d52.lilly
Sun Microsystems Inc. SunOS 5.6 Generic August 1997
You have mail.
$ exit
Connection closed.
$ cp sbd.tar gbip-pc2.d52.lilly.com:$PWD
cp: cannot create gbip-pc2.d52.lilly.com:/oracle/admin: No such file or director
y
$ rcp sbd.tar gbip-pc2.d52.lilly.com:$PWD
$ rsh
usage: rsh [ -l login ] [ -n ] host command
$ rsh gbip-pc2.d52.lilly.com
Last login: Wed Mar 10 22:29:35 from icuxb3.d52.lilly
Sun Microsystems Inc. SunOS 5.6 Generic August 1997
You have mail.
$ cd /oracle/admin
$ hostname
gbip-pc2
$ tar -xvf sbd.tar
x SBD, 0 bytes, 0 tape blocks
x SBD/bdump, 0 bytes, 0 tape blocks
x SBD/bdump/alert_SBD.log, 10644 bytes, 21 tape blocks
x SBD/bdump/alert_SBD.log_19990224, 14457 bytes, 29 tape blocks
x SBD/bdump/alert_SBD.log_19990303, 22780 bytes, 45 tape blocks
x SBD/bdump/alert_SBD.log_19990304, 15839 bytes, 31 tape blocks
x SBD/bdump/alert_SBD.log_199903040831, 5330 bytes, 11 tape blocks
x SBD/bdump/alert_SBD.log_19990305, 13002 bytes, 26 tape blocks
x SBD/bdump/alert_SBD.log_19990306, 12849 bytes, 26 tape blocks
x SBD/bdump/alert_SBD.log_19990307, 12586 bytes, 25 tape blocks
x SBD/bdump/alert_SBD.log_19990308, 12429 bytes, 25 tape blocks
x SBD/bdump/alert_SBD.log_199903080731, 4845 bytes, 10 tape blocks
x SBD/bdump/alert_SBD.log_19990309, 9715 bytes, 19 tape blocks
x SBD/bdump/alert_SBD.log_19990310, 12429 bytes, 25 tape blocks
x SBD/cdump, 0 bytes, 0 tape blocks
x SBD/udump, 0 bytes, 0 tape blocks
x SBD/udump/sbd_ora_21088.trc, 2040 bytes, 4 tape blocks
x SBD/create, 0 bytes, 0 tape blocks
x SBD/create/usrdfl_SBD.ksh, 1672 bytes, 4 tape blocks
x SBD/create/crdbSBD.sql, 1536 bytes, 3 tape blocks
x SBD/create/crdb2SBD.sql, 5068 bytes, 10 tape blocks
x SBD/create/newinst_SBD.ksh, 2730 bytes, 6 tape blocks
x SBD/create/crdbSBD.log, 370 bytes, 1 tape blocks
x SBD/create/crdbSBD.lst, 114 bytes, 1 tape blocks
x SBD/create/crdb2SBD.log, 176755 bytes, 346 tape blocks
x SBD/create/crdb2SBD.lst, 176499 bytes, 345 tape blocks
x SBD/create/newinst_SBD.log, 13546 bytes, 27 tape blocks
x SBD/pfile, 0 bytes, 0 tape blocks
x SBD/pfile/initSBD.ora, 5305 bytes, 11 tape blocks
x SBD/pfile/configSBD.ora, 1483 bytes, 3 tape blocks
$ mkdir /oracle/data/ora01/SBD
$ mkdir /oracle/data/ora02/SBD
$ mkdir /oracle/data/ora03/SBD
$ mkdir -p /oracle/data/ora04/SBD/arch
$ rm /oracle/product/root1/dbs/initSBD.ora
$ ln -s /oracle/admin/SBD/pfile/initSBD.ora /oracle/product/root1/dbs/initSBD.or
a
$ exit
Connection closed.
$ hostname
b3-dv_db
$ cd /oracle/data/ora04/SBD/arch
$ sqlplus / @build_begin_backup.sql
SQL*Plus: Release 8.0.4.0.0 – Production on Wed Mar 10 22:33:40 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
$ sqlplus / @begin_SBD_backup
SQL*Plus: Release 8.0.4.0.0 – Production on Wed Mar 10 22:34:9 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
SBD> spool begin_SBD_backup.err
SBD> alter tablespace NATE_T01 begin backup;
Tablespace altered.
SBD> alter tablespace ROLL_T01 begin backup;
Tablespace altered.
SBD> alter tablespace SYSTEM begin backup;
Tablespace altered.
SBD> alter tablespace TEMP_T01 begin backup;
Tablespace altered.
SBD> alter tablespace TOOLS_T01 begin backup;
Tablespace altered.
SBD> alter tablespace USERS_T01 begin backup;
Tablespace altered.
SBD> spool off
SBD> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
$ rcp /oracle/data/ora01/SBD/* gbip-pc2.d52.lilly.com:/oracle/data/ora01/SBD
$ sqlplus / @build_end_backup
SQL*Plus: Release 8.0.4.0.0 – Production on Wed Mar 10 22:39:18 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
$ sqlplus / @end_SBD_backup
SQL*Plus: Release 8.0.4.0.0 – Production on Wed Mar 10 22:39:31 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
SBD> spool end_SBD_backup.err
SBD> alter tablespace NATE_T01 end backup;
Tablespace altered.
SBD> alter tablespace ROLL_T01 end backup;
Tablespace altered.
SBD> alter tablespace SYSTEM end backup;
Tablespace altered.
SBD> alter tablespace TEMP_T01 end backup;
Tablespace altered.
SBD> alter tablespace TOOLS_T01 end backup;
Tablespace altered.
SBD> alter tablespace USERS_T01 end backup;
Tablespace altered.
SBD> spool off
SBD> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
$ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Wed Mar 10 22:39:43 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
SBD> alter database create standby controlfile as ‘standby.con’;
Database altered.
SBD> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
$ cd $ORACLE_HOME/dbs
$ ls *.con
standby.con standby_control_SBD.con
$ rm standby_control_SBD.con
$ ls *.con
standby.con
$ rcp standby.con gbip-pc2.d52.lilly.com:$PWD
$ rsh gbip-pc2.d52.lilly.com
Last login: Wed Mar 10 22:30:29 from icuxb3.d52.lilly
Sun Microsystems Inc. SunOS 5.6 Generic August 1997
You have mail.
$ hostname
gbip-pc2
$ cp /oracle/product/root1/dbs/standby.con /oracle/data/ora01/SBD/control01.con
$ cp /oracle/product/root1/dbs/standby.con /oracle/data/ora02/SBD/control02.c>
$ cp /oracle/product/root1/dbs/standby.con /oracle/data/ora03/SBD/control03.c>
$ inst sbd
Connected to: 8.0.4.1.0 root on the IBM catcher system
$ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> startup nomount
ORACLE instance started.
Total System Global Area 6158528 bytes
Fixed Size 47296 bytes
Variable Size 4390912 bytes
Database Buffers 1638400 bytes
Redo Buffers 81920 bytes
SVRMGR> alter database open standby database;
alter database open standby database
*
ORA-02288: invalid OPEN mode
SVRMGR> alter database mount standby database;
Statement processed.
SVRMGR> exit
Server Manager complete.
$ exit
Connection closed.
$ hostname
b3-dv_db
$ sqlplus /
SQL*Plus: Release 8.0.4.0.0 – Production on Wed Mar 10 22:43:53 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
SBD> create table bob_test as select * from dba_tables;
Table created.
SBD> select count(*) from bob_test;
COUNT(*)
———-
124
SBD> alter system switch logfile;
System altered.
SBD> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
$ cd /oracle/data/ora04/SBD/arch
$ ls
SBD_arch1_4471.dbf begin_SBD_backup.err dbfile_SBD.lis
SBD_arch1_4472.dbf begin_SBD_backup.sql end_SBD_backup.err
SBD_arch1_4473.dbf control_SBD.con end_SBD_backup.sql
alter_sbd_backup.sql create_controlfile.sql
$ rcp *.dbf gbip-pc2.d52.lilly.com:$PWD
$ rsh gbip-pc2.d52.lilly.com
Last login: Wed Mar 10 22:41:27 from icuxb3.d52.lilly
Sun Microsystems Inc. SunOS 5.6 Generic August 1997
You have mail.
$ inst sbd
Connected to: 8.0.4.1.0 root on the IBM catcher system
$ svrmgrl
Oracle Server Manager Release 3.0.4.0.0 – Production
(c) Copyright 1997, Oracle Corporation. All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.4.1.0 – Production
PL/SQL Release 8.0.4.1.0 – Production
SVRMGR> connect internal
Connected.
SVRMGR> set autorecovery on
Autorecovery ON
SVRMGR> recover standby database until cancel
ORA-00279: change 42575 generated at 03/10/99 22:34:10 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora04/SBD/arch/SBD_arch1_4473.dbf
ORA-00280: change 42575 for thread 1 is in sequence #4473
Log applied.
ORA-00279: change 42594 generated at 03/10/99 22:44:23 needed for thread 1
ORA-00289: suggestion : /oracle/data/ora04/SBD/arch/SBD_arch1_4474.dbf
ORA-00280: change 42594 for thread 1 is in sequence #4474
ORA-00278: log file ‘/oracle/data/ora04/SBD/arch/SBD_arch1_4473.dbf’ no longer n
eeded for this recovery
ORA-00308: cannot open archived log ‘/oracle/data/ora04/SBD/arch/SBD_arch1_4474.
dbf’
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
SVRMGR> alter database activate standby database;
Statement processed.
SVRMGR> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 6158528 bytes
Fixed Size 47296 bytes
Variable Size 4390912 bytes
Database Buffers 1638400 bytes
Redo Buffers 81920 bytes
Database mounted.
Database opened.
SVRMGR> connect /
Connected.
SVRMGR> select count(*) from bob_test;
COUNT(*)
———-
124
1 row selected.
SVRMGR> archive log list
ORA-01031: insufficient privileges
SVRMGR> connect internal
Connected.
SVRMGR> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/data/ora04/SBD/arch/SBD_arch
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
Filed under: Technical Tips
Like this post? Subscribe to my RSS feed and get loads more!
