Re: why need to backup undo tablespace?

>
> Hi
>
> Why is backing up your undo/rollback tablespace a must. Hummmmm, here's a
> "typical" scenario:
>
> 1) Your database is currently up and running and all looks good. You have
> heaps of happy users making numerous changes to the database.

usual situations.

>
> 2) Focusing on just *one* of these transactions, it happens to be quite a
> lengthy little bugger that either makes heaps of changes or takes heaps of
> time making it's changes. Note that all these changes are written to it's
> specified undo/rollback segment.
>
> 3) Because it's such a long transaction, the undo/rollback block(s) in
> question that have been loaded and changed in memory have been unable to
> remain cached in the buffer cache. Therefore Oracle has been forced to write
> these blocks to disk, the datafile(s) associated with the undo/rbs
> tablespace. Note the transaction in question has *not* yet been committed.
>

Even this long transaction has not been commited, before anything is written to datafiles, changes must be recorded in logfiles.

> 4) Disaster now strikes. The disk associated with your undo/rbs tablespace
> has died a horrible death. Maybe you haven't quite got to mirroring the
> thing, maybe you disk controller has packed it in, maybe it's some form of
> nasty corruption. Whatever the case, you have just lost your rbs tablespace.
>
> 5) Depending on DB version, platform and actual cause of problem, the
> database may simply crash or become inoperable or suffer so many errors that
> your dump directory is finding it difficult to keep up. Point is, it ain't
> happy and neither are your users who can't make changes to the database
> anymore and if the database is lucky enough to remain up, keep getting all
> these weird errors. Also many reads are failing as well as the database is
> no longer able to make the required read consistent operations.
>
> 6) Question is, how do we make it all happy again ?
>
> 7) Let's reflect on the undo of our focused transaction. The transaction had
> not committed when the database had crashed, therefore we *must* somehow
> recover the undo data and restore the previous values of this long running
> transaction (as indeed we must for all uncommitted transactions). The
> database is simply in an awful inconsistent state until all these
> uncommitted transaction have been restored.
>
> 8) How, how, how ?
>
> 9) In a manner, I disagree with Denis on the 2 stages of recovery, I believe
> there are 3 and he missed out the crucial first step, *restore*. In order to
> reconstruct the associated undo/rollback segment so that we can recover it,
> we must restore a backup of the datafiles associated with this stuffed rbs
> tablespace. We first restore, then apply the redo from the time of backup to
> reconstruct the exact contents as required (in this case, use the redo to
> reconstruct the rbs/undo segments that contain these vital missing previous
> values of the non completed transactions) and then rollback all those that
> weren't indeed committed. Note these recover operations must be performed
> with a mounted (non open) database because the files themselves are required
> for recovery preventing you from simply taking them offline while the
> database is open.

"restore", this is the key step, actually.

Redo log recorded everything oracle needed for recovery, but it need a proper "place" for re-play. For example, if that long transaction used an 20 extents rollback segment, then oracle will not be happy if you re-create an undo tablespace with new rollback segments, or using system rollback segments.

>
> 10) So we go to our backup area and attempt to restore the undo/rbs
> datafile(s).

here, by applying logs, we can extend an 2 extents rollback segments, to 20 extents rollback segs, resume to the state when instance is down.

>
> 11) Unfortunately, we weren't "persuaded" enough to backup the undo
> datafiles, therefore we can't restore the files, therefore can't recover the
> files, therefore can't recover the database to any satisfactory state.
> Oooooppppppssssss .......
>
> 12) We slowly drift down an unpleasant smelling creek without our little
> paddle ......
>
> Hint: Backup your undo tablespace.
>
> Always !!
>
> Cheers
>
> Richard
>
>

Thanks Richard, your example is persuading! can u point me where is the oracle details for the rollback segs in recovery? Received on Tue Dec 14 2004 - 17:42:14 CST