"niy38" <niy38_at_yahoo.com> wrote in message
news:cplpte$4ua$1_at_hisatsinom.cs.arizona.edu...
>
> "Denis Do" <nospam.denisdo_at_yahoo.com> wrote in message
> news:slrncro61g.rc.nospam.denisdo_at_denisdo.news.google.com...
>>> 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!
Hi
Why is backing up your undo/rollback tablespace a must. Hummmmm, here's a
"typical" scenario:
- Your database is currently up and running and all looks good. You have
heaps of happy users making numerous changes to the database.
- 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.
- 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.
- 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.
- 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.
- Question is, how do we make it all happy again ?
- 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.
- How, how, how ?
- 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.
- So we go to our backup area and attempt to restore the undo/rbs
datafile(s).
- 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 .......
- We slowly drift down an unpleasant smelling creek without our little
paddle ......
Hint: Backup your undo tablespace.
Always !!
Cheers
Richard
Received on Tue Dec 14 2004 - 02:33:21 CST
