?
Solved

undo - redo - rollback - what is the difference?

Posted on 2011-02-21
8
Medium Priority
?
1,051 Views
Last Modified: 2013-12-19
oracle 10 on sun solaris 10 64 bit.
I am using redo and rollback and per another issue was told the best approach is to use an undo tablespace - have been reading up on it but need some quick clarifications to proceed.
Does the undo tablespace replace redo or rollback or both?
Does the database have to be bounced to implement?
Does autoextend have to be on?
Can you turn autoextend off/on as needed?
What is the best size/next/maxsize for long running queries and can this be adjusted on the fly?
When the undo tablespace is created should I get rid of the rollback tablespace or the redo tablespace?

Thanks
0
Comment
Question by:bkreynolds48
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 34942990
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34943049
undo and rollback are related - they are conceptually the same thing, undo is the newer version of rollback and is typically managed in terms of time rather than simply size.

The purpose is, as the name implies to "undo" your changes.  If you update a row, then encounter an error or change your mind before committing, the old data stored in undo/rollback is used to put the original values back.


Redo -also as the name implies is used to record all changes  so you can do them again.  In particular, if your database crashes, you will need to restore a backup then apply all of your redo logs in order to get your backup up-to-date with the changes you made since the backup occurred.  So you will need to "re-do" all of those changes.
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34943171
Does the undo tablespace replace redo or rollback or both?

so undo replaces rollback.............

Can you answer the rest of my questions?

Does the database have to be bounced to implement?
Does autoextend have to be on?
Can you turn autoextend off/on as needed?

What is the best size/next/maxsize for long running queries and can this be adjusted on the fly?

rollback and is typically managed in terms of time rather than simply size
This is set in seconds I think so could I set this high before my long running query and then set lower when the query is complete - can this be done on the fly or do  you have to bounce the database?

When the undo tablespace is created should I get rid of the rollback tablespace or the redo tablespace?

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 34943216
have to be bounced? - no

autoextend required? no

can turn autoextend off/on ? yes, no bounce requried

size/maxsize - depends entirely on what you will be doing, number of transactions and length of them
yes you can adjust on the fly but it's not normal to do so.   So yes, you "could" set the undo size/time large then reduce it later but unless it's a one time operation it wouldn't be normal practice.  Instead, set the time/size of your undo/rollback to whatever is needed to ensure your transactions complete and then leave it.


redo and undo/rollback are different things.  You definitely should not be getting rid of redo because you have undo.  There is no "redo" tablespace unless you have created one by that name, in which case you'll have to see what it's being used for because redo doesn't write to a tablespace it writes to your online log files, and then to archive log files if you are running in archivelog mode.
0
 
LVL 1

Author Closing Comment

by:bkreynolds48
ID: 34943233
Thanks for helping me clear things up - yes I do know that redo is logs just a bit confused on the undo
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34943514
Just to be complete, based on one of your other questions.


You do not need to bounce in order to add or change your undo/rollback allocations.

You do need to bounce in order to switch from undo (AUTO) to rollback (MANUAL)
via the undo_management parameter

sorry for any confusion
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34943569
I put the following in the init.ora file...........
UNDO_MANAGEMENT = AUTO       # Default is MANUAL
UNDO_TABLESPACE = undotbs_01 # The name of the undo tablespace.
UNDO_RETENTION  = 7200       # The time undo is retained.
                             # Default is 900 seconds.
UNDO_SUPPRESS_ERRORS = TRUE  # Suppress errors when MANUAL undo admin
                             # SQL statements are issued.

after I bounce the database should I keep the rollback tablespace for a bit?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34943626
you can't use them if you're in automatic undo so no need to keep them around

but, if you think you might switch back to manual, you can leave them in place
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question