Q

Necessary to back up UNDO tablespace?

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?

This Content Component encountered an error
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.

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

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close