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
Solved

undo - redo - rollback - what is the difference?

Posted on 2011-02-21
8
1,026 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 74

Accepted Solution

by:
sdstuber earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

791 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