Re: Backup question - temp tablespaces

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 Mon Feb 12 2001 - 22:54:41 CST