Re: Tablespace lost

Some extra opinions based on experience and reading docs, attending courses and so on

The rollback tablespace (more precise a rollback segment) is protected by redo log files as anything else (except for temporary segments, no redo log is made for that, they are excluded always by Oracle). So: ALWAYS run in archivelog mode. This is the first commandment in using Oracle (as it is the second to tenth commandment). You will be able to recover from the loss of a rollback tablespace if you have all your redolog files after you made your last (cold) backup. Actually recovery executes a rollforward first and a rollback based on the redo log files you apply.
This may however get complicated (ie force you to perform a complete recovery) when you mix rollback and non-rollback segments in the same tablespace and distribute your rollback segments in several tablespaces.

A temp segment is only used for sorts: so for ORDER BY, group by, union, create index and so, and so on. So who cares when you loose it. It is not protected by redo log nor by rollback anyway, so simply recreate. You have to repeat your statements, of course. Because of this, it is useful to dedicate one tablespace for temporary segments.

I agree with the comments on index tablespaces. If you run in archivelog, you will be able to recover it. Dropping it and recreating all indexes can however be quicker in many cases. Of course you will have to know which indexes where in this tablespace.

Sybrand Bakker
Senior IS Analyst
Bentley Systems Europe

mreagan_at_fast.net wrote:
>
> In article <tok.79.000CD014_at_sni.dk>, tok_at_sni.dk (Tom
 Krühlmann) wrote:
>
> > I'm currently running through all my emergency procedures.
 And I discovered
> > that I always rely on having all of my filesystems files (
 Unix )
 representing
> > tablespaces in oracle on my backup.
> >
> > What could I do if I lost for instance my rollback
 tablespace, my index
> > tablespace or my temp tablespace ?
>
> Hmm. Interesting question. One that I have pondered many
 times.
> Everyone I ask gives a different answer. From what I have
 been able to
> determine:
>
> Rollback tablespace: If you lose it, you are completely
 screwed.
> Hypothetically, if there were NO update/insert/delete
 transactions
> occurring at the time you lost it, you should be able to
 recover.
> However, I don't think the database would go along with it.
 Might be
> interesting to test.
>
> Index tablespace: You would have to destroy the existing
 index tablespace
> by dropping it and recreating it (with REUSE). Indexes can
 then be
> rebuilt, since the data upon which they were built is
 available and is
> unaffected.
>
> Temp tablespace: I would assume that this would be possible,
 but I
> wouldn't want to try it. I believe creating temporary objects
 in the
> temporary tablespace is protected by rollbacks just like
 anything else.
> The database would probaby have a fit if you tried to recreate
 the temp
> tablespace.
>
> Anyone else have an opinion (or, god forbid, a real,
 researched answer)?
>
> Matt...
Received on Mon Oct 30 1995 - 00:00:00 CET