Solved

undo - redo - rollback - what is the difference?

Posted on 2011-02-21
8
1,004 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 73

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
 
LVL 73

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
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 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 73

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 73

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.

Join & Write a Comment

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now