Re: Backup question - temp tablespaces

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

> >

> >

> >

>

>