RE: Hot backup and TEMP tablespace

Hi Traci,

You need to keep the following in mind before deciding to stop backing up TEMP. 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.

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

I would look at using RMAN as Jared has suggested. Empty blocks are not backed up, and there are opportunties for incremental backups as well.

We have a 13 Gb TEMP tablespace on our Production APPS 10.7 database and back that up everyday. Tape is cheap - time is not.

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Fear is the darkroom where Evil develops your negatives. Wanna break free of fear? Click on 'http://www.needhim.org'

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: traci.l.rebman_at_rrd.com [mailto:traci.l.rebman_at_rrd.com]
> Sent: Saturday, January 26, 2002 7:40 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Hot backup and TEMP tablespace
>
>
>
> 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.
>
> Traci Rebman
> Oracle Database Administrator
> R.R. Donnelley & Sons - Financial Division
> Lancaster, PA
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: traci.l.rebman_at_rrd.com
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

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