Oracle DML/Redo Data [message #671887] |
Sat, 22 September 2018 08:25 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/f917760ed633fb33ec4655c6d4125746?s=64&d=mm&r=g) |
sacharya2017
Messages: 19 Registered: January 2017
|
Junior Member |
|
|
Hi,
I want to get little bit clear about what happens when we run any DML, specifically an update command which updates say 1 million rows in the table, which would say run for few minutes to several hours. as we know LGWR keeps writing redo data periodically to online log. here in this case redo data we get almost written to online log and possibly it will get archived as well. once the update is over and we commit. oracle places a commit record into the log buffer and it will ensure that redo data is indeed got written on to the disk and sends the commit complete back to user. Am I correct here? please explain me bit in details what happens in this case. also what is instance crashes before the commit is fired? how would oracle rollback the transaction? I believe oracle only uses online redo log for instance recovery so what will happen here since the redo data for this particular transaction has already been archived.
thanks
SA
|
|
|
|
|
|
|
|
Re: Oracle DML/Redo Data [message #671893 is a reply to message #671889] |
Sat, 22 September 2018 09:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
sacharya2017 wrote on Sat, 22 September 2018 14:34Hi Black Swan,
thanks for quick reply. I really appreciate it. Yes you're right oracle will use undo segment to rollback the transaction but How will it come to know? since instance recovery uses only online redo log. Since in this case oracle has already archived the redo log.
thanks
SA You are confusing he stages of recovery. The online redo is used for instance recovery, in mount mode: to rebuild the buffer cache to the state it was in before the crash. This is known as forward recovery, or roll forward, and recovers committed changes, uncommitted changes, and undo segment changes. Once that is done, the database can be opened and then any uncommitted transactions are rolled back. This second stage, transacion recovery or rollback, does not use redo: it uses undo, which is in the undo segments on disc and blocks of undo in buffer cache that were recovered during he roll forward. Archived redo is not needed for either stage: archived redo is necessary only for database recovery after restoring datafiles.
|
|
|
|
|
Re: Oracle DML/Redo Data [message #671913 is a reply to message #671894] |
Mon, 24 September 2018 02:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
sacharya2017 wrote on Sat, 22 September 2018 15:31Yes, I know that it uses undo to roll back the transaction. My question is how it will know to rollback the long running DML. I can right now think of only one thing that oracle will always have information in online redo log file about the long running DML even though most of the redo data related to it been already archived, since the transaction is still running and not committed. I wanted to get clarified with my knowledge, so hoping somebody will share a bit of insight in this process.
Ok, it's pretty early for me so this will be fairly high level but this information is held in the undo segment headers.
There's a part there which keeps track of transactions, scns etc.
I'm not sure which bit of online docs this comes from, I last read it in a Johnathon Lewis book some time ago. Essential internals iirc.
[Updated on: Mon, 24 September 2018 02:18] Report message to a moderator
|
|
|