RE: Hot backup and TEMP tablespace

On Mon, 28 Jan 2002, John Kanagaraj wrote:

> There are two kinds of media recovery : Complete and incomplete. The
> former requires that the database be mounted but not open when
> recovery is being performed. If the database was just mounted, then
> you will not be able to drop the TEMP tablespace.

She is talking about offline drop of the datafiles comprising the temporary tablespaces before you begin the recovery. You can do this in mount mode regardless of what type of recovery you are performing. You wait to drop and recreate the tablespace until *after* you are done recovering (complete or incomplete) and have opened the database. The existence of the tablespace in the dictionary has no effect on recovery.

> Add to this the fact that Oracle applies archive logs based on the
> file header SCNs. If you restore an old copy of the TEMP
> tablespace's datafiles, the recovery will require that *all* the
> archive logs since the last backup of TEMP. This implies that you
> need to be able to get these archive logs from the backup (without
> missing even one), and spend time in applying them. This is
> something you DON'T want to do in an emergen

She is not planning to restore an old copy of the temp datafiles, but just re-create them after open. With careful planning, this is a safe and clever way to conserve resources during backup. It might actually *reduce* MTR for the recovery.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton



> > -----Original Message-----

> > From: traci.l.rebman_at_rrd.com [mailto:traci.l.rebman_at_rrd.com]

> > 

> > I was wondering if anyone could offer any suggestions to this 

> > issue, or the

> > pro's and con's...

> > 

> > We are trying to shorten the time frame that it takes to complete our

> > Oracle hot backups, and the subsequent file copies to tape.  

> > I have read

> > that it is ok to skip the TEMP tablespace, and then do an 

> > off-line drop of

> > the datafile(s) in the temporary tablespace, drop the 

> > tablespace, and then

> > recreate it.

> > 

> > Our temporary tablespaces are 900mb, and they take a chunk of 

> > the total

> > time it takes to complete the hot backups.  Are there any issues or

> > ramifications of not including that tablespace in the event 

> > of a recovery?

> > It seems to me that it would be much quicker to re-create 

> > that tablespace

> > if needed.

> > 

> > Any suggestions are greatly appreciated.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeremiah Wilton
  INET: jwilton_at_speakeasy.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).