Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: why need to backup undo tablespace?

Re: why need to backup undo tablespace?

From: Richard Foote <>
Date: Tue, 14 Dec 2004 08:33:21 GMT
Message-ID: <lzxvd.72279$>

"niy38" <> wrote in message news:cplpte$4ua$
> "Denis Do" <> wrote in message
>>> but if you lose undo tablespace and no backup, you still can
>>> recreate the undo tablespace.
> Yes, I did some tests successfully:
> 1) change undo_management to manual
> 2) startup instance and drop original undo
> 3) create new undo
> 4) shutdown instance and change back to auto
> But just like HJR said, no way to test in every kind of scenario.
>> Have you tried it? :-)
>> Good example - take a kind of big partitioned table, put real load on it,
>> run long transaction and start collecting statistics at the same time.
>> :-)
>> Yes, it is should not be done like that - but it is happening quite
>> often.
>> And kill smon:-) (or shutdown abort)
>> And try restore without undo TS:-)
> For sure instance recovery needs rollback segments, for roll forward and
> roll back.
> You can not recover without undo TS? what's the error msg? you have an
> rollback
> segment in system, right?
>> Well, if you still looking for fun and adventures - you can try it.
>> If you want to get job done - please do not do it
>> Have a great day!
> I just want to explore why backuping undo is a must, and I could be
> persuded
> easily be an example. Thanks!


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.
  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.
  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.
  10. So we go to our backup area and attempt to restore the undo/rbs datafile(s).
  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 !!


Richard Received on Tue Dec 14 2004 - 02:33:21 CST

Original text of this message