Solved

undo - redo - rollback - what is the difference?

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot 2 43
Difference in number of minutes between 2 timestamps 16 39
automatic email alert 1 42
SQL query question 8 40
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…
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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

895 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

14 Experts available now in Live!

Get 1:1 Help Now