Re: Backup question - temp tablespaces

Temp tablespace is only ever used when Users do select statements that include 'order by' and 'group by' clauses... and only then when the sort_area_size isn't big enough to contain the sort required to generate the requested report. There is only one exception that I can think of: creating an index (a piece of DDL) also requires data to be sorted, and swapped to disk.

So temp tablespace is used for select statements, and create index statements, and that's about it.

When are redo logs and rollback segments used? When users do DML (updates, inserts or deletes).

What is actually happening during a recovery of a database? DML is being re-performed. Hence recovery uses redo logs and rollback segments. Recovery does not suddenly start re-performing Users' queries, and hence temporary segments are not used during recovery.

Regards
HJR Syltrem <syltrem_at_videotron.ca> wrote in message news:4Wzi6.8616$QB4.167683_at_wagner.videotron.net...
> Maybe I did not express myself correctly.. I was more thinking about roll
> forward.
>
> But just now as I think about it, Oracle will just read off redo or
 rollback
> and make necessary changes to the user's data. It will not re-exeute any
> transactions and thus will not require any temp storage for sorting or
> anything like this.
>
> Is that it?
>
> --
>
> Sytrem
> http://pages.infinit.net/syltrem
>
> "Howard J. Rogers" <howardjr_at_www.com> a écrit dans le message news:
> 3a899f09_at_news.iprimus.com.au...
> > Rollback segments live in rollback tablespaces (or ought to).
> >
> > Rollback segments are real segments -permanent objects, that is. They
> > cannot reside in proper temporary tablespace (just like nothing else
 can).
> >
> > You are correct that recoveries get very interesting if there are no
> > rollback segments, but the absence of temporary tablespace is not a
 concern
> > at that particular time.
> >
> > Regards
> > HJR
> >
> >
> > "Syltrem" <syltrem_at_videotron.ca> wrote in message
> > news:Kzfi6.5316$Iu3.103201_at_weber.videotron.net...
> > > Thanks for the info.
> > >
> > > Is this still valid for online backups, in the case you have to
 restore
 all
> > > the db after a disaster, say? Won't Oracle sometimes want to see the
 temp
> > > segments to rollback transactions or something like this?
> > >
> > > --
> > >
> > > Sytrem
> > > http://pages.infinit.net/syltrem
> > >
> > > "Howard J. Rogers" <howardjr_at_www.com> a écrit dans le message news:
> > > Jc3i6.174$305.62763_at_inet16.us.oracle.com...
> > > > The 'create datafile blah as blah1' syntax is specifically designed
 for
> > > > re-creating a tablespace (well, a datafile really) that is subject
 to
 media
> > > > failure but which has never been included in a previous backup.
 Provided
> > > > ALL redo since time of the datafile's creation is available, the
 thing
 can
> > > > still be recovered -but the redo needs to be applied to a new, blank
> > > > datafile which looks to the database like an exact copy of the old
 one.
> > > >
> > > > In short, that bit of syntax has absolutely nothing to do with
 recovering
 or
> > > > backing up TEMP tablespaces.
> > > >
> > > > The usual recommendation for temp datafiles is to do exactly what
 you
 went
> > > > on to do. Oracle will always notice ANY missing datafiles (whether
 of
 type
> > > > TEMPORARY or PERMANENT), and will prompt you to perform recovery on
 it.
 The
> > > > thing about TEMP stuff is that you ignore the prompt: you just
 offline
 drop
> > > > the relevant datafile, open what's left of the database, drop the
 TEMP
> > > > tablespace in its entirety, and create a brand new one.
> > > >
> > > > So: short answer is (1) don't back up datafiles associated with a
 temporary
> > > > tablespace. (2) Don't recreate them before opening the database (3)
> > > > recreate the entire tablespace once the rest of the database is up
 and
> > > > running
> > > >
> > > > Regards
> > > > HJR
> > > >
> > > >
> > > > "Syltrem" <syltrem_at_videotron.ca> wrote in message
> > > > news:64Wh6.3517$Iu3.61069_at_weber.videotron.net...
> > > > > Hello!
> > > > >
> > > > > I am testing a db restore from cold backup but... I have not
 backed
 up
 the
> > > > > TEMP tablespace. Instead I have a script that says:
> > > > > ALTER DATABASE CREATE DATAFILE 'xyz' AS 'XYZ';
> > > > >
> > > > > Since the temp datafile is a TEMPORARY tablespace content, there
 is
 nothing
> > > > > in there that needs backing up and I figured I could re-create the
 file
> > > > > before ALTER DATABASE OPEN.
> > > > >
> > > > > It did not work as expected. I received the message that the file
 needed
> > > > > recovery. Why? I was finally able to do an ALTER DATABASE DATAFILE
 'xx'
> > > > > OFFLINE DROP and ALTER DATABASE OPEN, after which I dropped the
 temp
> > > > > tablespace and recreated it.
> > > > >
> > > > > So I guess my method is not so good, and the question is:
> > > > > How would you go about not backing up the temporary tablespaces
 (datafiles)?
> > > > >
> > > > > Thanks!
> > > > >
> > > > > --
> > > > >
> > > > > Sytrem
> > > > > http://pages.infinit.net/syltrem
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Wed Feb 14 2001 - 14:14:17 CST