As a secondary backup solution, we decided to go with hot backups, using ALTER TABLESPACE ... BEGIN BACKUP. I am doing this for all permanent tablespaces, thus excluding temp and undo. My question is, is it nessecary to back up the UNDO tablespace, and if not, why? Keep in mind, I am doing a log switch, archive all and then creating a copy of my control file as a last step, before exiting. I was asked this question, and after a lot of reading, still could not find a clear answer. Thanks in advance.

    Requires Free Membership to View

It is not necessary to back up the UNDO tablespace. Think about why you would need undo information. One reason is for performing consistent reads with respect to transactions. But the database is down when you perform your restore, so you will not need undo for consistent reads. Another reason you would need the UNDO tablespace is to roll back a transaction. The redo logs contain the information to roll forward and undo any uncomitted transactions. If you need to restore your database and you do not have a backup of the UNDO tablespace, at a minimum you can STARTUP MOUNT the database, create a new UNDO tablespace, change the UNDO_TABLESPACE parameter to point to the new tablespace, and then OFFLINE DROP the old tablespace.

If you are implementing a new backup strategy and your database contains an UNDO tablespace, then you must be running either Oracle 9i or 10g. In either case, I strongly recommend that you use RMAN for your backup and recovery needs. RMAN has many more features than User-Managed Backups, which is what you are implementing. I would prefer RMAN to a User-Managed Backup solution almost every single time.

This was first published in July 2006

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: