Re: why need to backup undo tablespace?

niy38 wrote:
>
> For the close backup, it's obvious we don't have to
> backup undo: you even can create a new one after deleting
> the original one.

You do indeed need to backup undo, whatever the openness or otherwise of your database backup, because they are merely segments in a data file, just like your tables are. SMON expects to be able to open all files mentioned in the controlfile. If it can't, it will give you the error that "File X cannot be identified". You had better be able to restore file X from backup therefore. If you do, and try and open your database, SMON will still complain that "File X needs media recovery" -being a backup, it needs redo applied to it to make it up-to-date and consistent with the rest of the database.

The UNDO tablespace is no different from any other data file or tablespace in this regard.

> This is quite understandable: since after shutdown
> (normal, transactional, immediate), all transactions
> are over. ( here I didn't consider undo_retention)

You're missing the point. Of course, if you have shutdown your database, then there can be nothing in the undo tablespace which is needed to rollback uncommitted transactions, because (assuming it is a clean shutdown) there can be no more uncommitted transactions. But SMON will expect to find the undo tablespace datafiles. And it will expect to *make use* of the undo tablespace when it is recovering any other data file.

Bear in mind that any Oracle recovery consists of replaying transactions; and if you generated undo when you first did the transaction, so Oracle is going to re-generate the undo when it re-performs those transactions. And the redo 'instructions' about *where* to generate the undo, and in which undo segment, is quite specific: it's referenced by rowid just like anything else is. So SMON had better have access to a working undo tablespace if any other recovery is ever going to work.

> So I suppose for a must of backup undo, it's hot backup.

You suppose wrong. You must *always* back up the undo tablespace. Hot or cold, it makes no difference.

If you don't believe me, try an RMAN 'backup database' and see what Oracle's own backup utility considers essential to have in the backup set. Undo will be included, online redo logs will not.

> For an tablespace USERS, after I do a hot backup,
> and its datafiles corrupt, now I do recovery, do I
> need undo?

Yes. All recoveries always generate undo. And they'd better be able to re-generate their undo in exactly the same place as they originally generated it.

HJR Received on Thu Dec 09 2004 - 13:34:52 CST