Work With Results Through Technology to Grow Your Business Contact Us Today

Hide
Show
Categories: Technical Tips

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

 

  1. 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

 

  1. Identify the database files to back up.
  2. Edit the generated file to make cp commands
  3. Create the scott account and some tables.
  4. Shutdown the database and copy the files to a different directory
  5. Startup the database, then drop a table ( to show that it comes back after the recovery)
  6. Shutdown the database, copy the backup files back, and restart the instance.
  7. 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

default tablespace tools_t01

temporary tablespace temp_t01

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.

 

  1. Make sure that the database is in archive log mode
  2. Put the database into backup mode
  3. Backup the data files
  4. Generate some activity
  5. Take the tablespaces out of backup mode
  6. Force a log switch
  7. Backup the controlfile to trace.
  8. Perform some more activity . Commit. Record the time.
  9. Perform some more activity.  Commit. Record the SCN.
  10. Perform some more activity. Commit.
  11. Force a log switch, then backup the archived redo logs.
  12. Shutdown the database, and delete the files from the archive location.
  13. Recover the datafiles and all archived redo log files.
  14. See what happens if you try to simply open the database
  15. Recover the database
  16. 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.

 

  1. Shutdown the database, and delete all database files and archived redo logs.
  2. Recover the datafiles and all archived redo log files.
  3. Recover the database, canceling after the 1st archived redo log.
  4. 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.

 

  1. Shutdown the database, and delete all database files and archived redo logs.
  2. Recover the datafiles and all archived redo log files.
  3. Recover the database, using UNTIL TIME.
  4. 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.

 

  1. Shutdown the database, and delete all database files and archived redo logs.
  2. Recover the datafiles and archived redo log files.
  3. Recover the database, using UNTIL CHANGE
  4. 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.

 

  1. Shutdown the database, and delete all database files and archived redo logs.
  2. Recover the datafiles and archived redo log files to different locations that their original location
  3. Edit the config.ora file to indicate the new location of the controlfile and the archive log destination
  4. 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.
  5. Remove the recovered controlfiles, since we will be recreating them.
  6. Recover the database, applying all of the logs.
  7. 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..

 

  1. Take a new backup.
  2. At the operating system, remove the tablespace file.
  3. See how the missing data file affects Oracle.
  4. Take the affected tablespaces offline.
  5. Recover the lost datafile(s) from the last hot backup
  6. Make sure that you have all of the archived redo log files created since the backup.
  7. Issue the RECOVER TABLESPACE command, and bring the tablespace back online.
  8. 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

 

  1. 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.

  1. 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.

  1. 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.

  1. 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.

 

  1. 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

  1. 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

 

  1. Put the database into backup mode
  2. Abort the instance to simulate a system crash
  3. Attempt to startup the instance, to prove that recovery is required
  4. Generate SQL to take the datafiles out of backup mode ( Oracle 7.2 or above)
  5. Run the generated script
  6. 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.

 

  1. Create a trace file with the CREATE CONTROLFILE command.
  2. Shutdown the instance
  3. Move the files
  4. Edit the trace file to remove comments and to specify the new location.
  5. Edit config.ora to specify the new location of the controlfile ( and archive location , if you move it).
  6. 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.

 

  1. Create a trace file that contains the CREATE CONTROLFILE command.
  2. Shutdown the database
  3. Rename the directories that contain the data files, control files, and online redo logs.
  4. Rename the archive directory
  5. Rename the admin directory
  6. In the pfile directory, rename the init.ora and config.ora files, and edit them to use the new instance name.
  7. Remove the old symbolic link for the init.ora file in $ORACLE_HOME/dbs and create a new one.
  8. Edit the oratab file to change the entry for this instance.
  9. Redefine ORACLE_SID.
  10. 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.
  11. Run the edited script to rename and open the database.
  12. 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.

 

  1. Create the data file and archive directories for the new database
  2. From the source database, create a trace file that contains the CREATE CONTROLFILE command.
  3. Put the source database into backup mode
  4. Copy the datafiles, control files, and online redo log files to the new data directory.
  5. Take the source database out of backup mode.
  6. Force a log switch at the source database.
  7. 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.
  8. Copy the admin directory of the source database into a new admin directory for the new database
  9. In the new pfile directory, rename the init.ora and config.ora files, and edit them to use the new instance name.
  10. Add a symbolic link for the new init.ora file in $ORACLE_HOME/dbs.
  11. Edit the oratab file to add an entry for the new instance.
  12. Redefine ORACLE_SID.
  13. 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.
  14. 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.
  15. 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.

  1. 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.

 

  1. Take a hot backup.
  2. Backup the controlfile
  3. Create a table in a different tablespace.
  4. Create a foreign key, to show how the process deals with dependencies
  5. Truncate the table
  6. Add a new table to the tablespace.
  7. Use TS_PITR_OBJECTS_TO_BE_DROPPED to identify the objects that will be lost during the recovery.
  8. Use TS_PITR_CHECK to identify constraints, then disable them
  9. Switch the logfile and take the tablespace offline
  10. Clone the database and recovery it
  11. Export the clone information
  12. Copy the recovered database datafile back to the source database directory
  13. Import the information about the recovered tablespace
  14. 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

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

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

as select * from dept;

 

Table created.

 

bob239> connect /  

Connected.

7.         bob239> select owner, name from sys.ts_pitr_objects_to_be_dropped

where tablespace_name = ‘USERS_T02’ and creation_time >

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

where ( ts1_name = ‘USERS_T02’ and ts2_name <> ‘USERS_T02’ )

   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

where ( ts1_name = ‘USERS_T02’ and ts2_name <> ‘USERS_T02’ )

   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.

 

  1. Copy the admin directory of the instance and duplicate the directory structure of the primary database.
  2. Take a hot backup ( backup directly to the standby server)
  3. Create a standby controlfile and send it to the remote site.
  4. Replace the copied control files with the standby controlfile
  5. Mount the standby database
  6. Apply logs until you have a disaster at the primary database
  7. 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

 

Spread the Word, like or share this page, your friends will also love it and thanks for it.

About the Author

Scott Nelson is an entrepreneur, Oracle wizard, consultant, businessman, filmmaker, inventor, tinkerer. He's always been willing to approach the unapproachable, attempt the impossible, reach for the brass ring in a wild dive into the darkness of the unknown because his motto is “All you have to do is do it” Currently living the good life in southern Indiana amongst the hills and pleasant valleys of Brown County.

Top