The short story: undo is used to undo changes. To recover an instance, we roll forward with redo -- which rolls UNDO forward as well. Then we look around and say "so, what transactions were in process when we crashed, let's roll them back now".
Tom, 1. In response the discussion above, for 2 you mentioned " THis is what I meant by undo protection mechanism. Is the term i used to describe not correct? Pls advise. In response to discussion 3 above, you mentioned "no undo for the TABLE indexes on table, undo, changes to dictionar, undo ". I mean to understand this : A.
UNDO will still be generated for the data dictionary changes and in case if the table has any indexes and the associated redo as well to protect the UNDO. Is this what you mean by "minimal redo Please confirm my understanding. Thanks much. You said "is this redo part of the undo protection mechanism".
I said "some of the redo is the redo for the modification to the dictionary" not part of the "undo protection mechanism " "some of the redo is for undo" so the answer is "no, all of the redo is not for undo, some of it is, some of it is not" 2a - correct, 2b - correct. August 31, - am UTC. The second one generated redo, since conventional path inserts always generate redo.
August 31, - pm UTC. CTAS did not generate any plans although I set autotrace on. September 01, - am UTC. Hi Tom, From the thread above I understand that keeping objects in nologging mode cannot be recocerved in case a media recovery is required in an event of a faliure. And involves a rework. But will it make a big difference in case of indexes? Please clearify 1 Will it be a big risk to keep some indexes NO tables in nologging mode in a production system?
I think there should be NO problem as far as recovery is concerned except that index stats would not be up to date after recovery. Is this correct? Thanks as always. September 27, - pm UTC.
Do you run a standby database, one that works off of redo? Are you using redo based replication??? Only you can tell I see no reason to keep indexes in non-logged. It will take hours, I'm aware of that. But it should be acceptable as failure is a rare event.
No, there is no standby database. Hot backup is taken every week and bitmap index rebuild is done everyday after data loading. And I'm sure it will not be acceptable to take a backup everyday after rebuild. In that case I'll be asked to find other ways to rectify the performance problem while bitmap index rebuild. You only need to backup the affected datafiles, not the entire database.
Is it acceptable to be down for hours or longer to perform a recovery? If the system fails on Saturday, you have Sun-Sat to apply You get ora-xxxx errors when accessing them, bad blocks.
You have to recreate it. Insert into as select also takes up more then 2 hours All the tables are analyzed and up-to-date. This is for a particular table and not repeated for all the CTAS.
It is very puzzling to me as I never encountered such a scenario before Rich, September 13, - pm UTC. Hi Tom, Still, few things are unclear to me. What about this scenario version is September 15, - pm UTC. You did not backup X the datafiles holding X after you performed a non-logged operation. After going throught this thread and couple of others and after a look into the doc i came up with this. This is kind of what i understood and i what was not clear to me.
Nothing happens with respect to Redo Log. January 22, - am UTC. If you issue commit, LGWR will write your commit record to the redo stream and you are done. They are all the same, the amount of redo generated will differ, but they are all the same. Ok got you. In both the case it will be logging as the purpose of redo is to help us out to recover in the case of disaster.
Once again thank you. A reader, August 29, - am UTC. Dear Tom. Backup before and after - You must take a backup, both before and after all nologging operations. Only nologging operations during the nologging window - Between the backups the nologging processing window , ONLY nologging operations should be run in the middle of this "backup sandwich". You can use nologging for batch inserts into tables and for creating indexes:. You can create indexes with nologging - The only danger with using nologging is that you must re-run the create index syntax if you perform a roll-forward database recovery.
For more information on using nologging for optimal performance, see my book Oracle Tuning: The Definitive Reference. For example, a common practice is to reorganize very large tables is to use CTAS:. The very very short answer is NO. Rating 2 ratings Is this answer out of date? You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels.
And of course, keep up to date with AskTOM via the official twitter account. They will never skip any of those at all. So, it is simply the new rows at the "end" of the table that are added that are "special". Most of their speed comes not from skipping UNDO generation -- but rather that we don't have to go through the somewhat time consuming task of finding free space for them in the existing table.
It is not doing things "in a single location" -- but could affect one row on every single block in the table. What happens if halfway through -- the system failed?
It would be corrupt -- both logically half an update as well as physically indexes out of sync with table, table out of sync with itself. Same with a delete. So, a bulk load can be treated "special", but any in place modification -- no way, not a chance. Rating 6 ratings Is this answer out of date? If it is, please let us know via a Comment Comments Comment Superb!!!!
Reader, October 25, - am UTC.
0コメント