bkreynolds48
asked on
undo - redo - rollback - what is the difference?
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
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
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.
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for helping me clear things up - yes I do know that redo is logs just a bit confused on the undo
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
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
ASKER
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?
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?
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
but, if you think you might switch back to manual, you can leave them in place
https://www.experts-exchange.com/questions/23314493/Difference-between-REDO-and-UNDO-in-oracle.html