Home » RDBMS Server » Server Administration » Oracle DML/Redo Data (Any)
Oracle DML/Redo Data [message #671887] Sat, 22 September 2018 08:25 Go to next message
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 #671888 is a reply to message #671887] Sat, 22 September 2018 08:31 Go to previous messageGo to next message
BlackSwan
Messages: 26410
Registered: January 2009
Location: SoCal
Senior Member
sacharya2017 wrote on Sat, 22 September 2018 06:25
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
upon instance (re)start, Oracle uses the content of UNDO to ROLLBACK the uncommitted DML changes.
Re: Oracle DML/Redo Data [message #671889 is a reply to message #671888] Sat, 22 September 2018 08:34 Go to previous messageGo to next message
sacharya2017
Messages: 19
Registered: January 2017
Junior Member
Hi 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
Re: Oracle DML/Redo Data [message #671890 is a reply to message #671889] Sat, 22 September 2018 08:45 Go to previous messageGo to next message
BlackSwan
Messages: 26410
Registered: January 2009
Location: SoCal
Senior Member
sacharya2017 wrote on Sat, 22 September 2018 06:34
Hi 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.

Post URL to Oracle documentation that confirms above is correct that instance recovery ONLY uses REDO.
Please explain in detail exactly how Oracle uses REDO during instance recovery.
What exactly need to be redone by making use of REDO?
Re: Oracle DML/Redo Data [message #671891 is a reply to message #671890] Sat, 22 September 2018 09:16 Go to previous messageGo to next message
sacharya2017
Messages: 19
Registered: January 2017
Junior Member
When Oracle instance starts and opens the database in read/wirte mode, it successfully mounts the redo thread and holds the enqueue and update the control file. similarly when oracle instance shutdowns normally it closes the redo thread successfully and record the consistent state of the database into the control file and updates all the online datafile headers, hence next instance startup does not need recovery. Now in the case of Instance crash oracle Instance does not get time to do all these things which it's suppose to do and Redo thread is left open into the control file and datafile header also does not get updated. so next instance startup it reads the control file and sees that Redo thread is mounted open with no live instance attached to it and determines that this instance needs recovery. The first starting point of recovery is the online redo logfile where it sees the Checkpoint position, A data structure which tell the oracle that data prior to this is in consistent mode and hence does not need recovery. it starts recovering data below that position till the end of redo thread is reached. This is where rolling forward (commited data) and rolling backward (uncommitted data) proccess is done by instance SMON process.

This is the reason I want to know what happens with long running DML, which does not get committed and redo data already been archived. Oracle handles this in a better way which I don't know.
Re: Oracle DML/Redo Data [message #671892 is a reply to message #671891] Sat, 22 September 2018 09:22 Go to previous messageGo to next message
BlackSwan
Messages: 26410
Registered: January 2009
Location: SoCal
Senior Member
sacharya2017 wrote on Sat, 22 September 2018 07:16
When Oracle instance starts and opens the database in read/wirte mode, it successfully mounts the redo thread and holds the enqueue and update the control file. similarly when oracle instance shutdowns normally it closes the redo thread successfully and record the consistent state of the database into the control file and updates all the online datafile headers, hence next instance startup does not need recovery. Now in the case of Instance crash oracle Instance does not get time to do all these things which it's suppose to do and Redo thread is left open into the control file and datafile header also does not get updated. so next instance startup it reads the control file and sees that Redo thread is mounted open with no live instance attached to it and determines that this instance needs recovery. The first starting point of recovery is the online redo logfile where it sees the Checkpoint position, A data structure which tell the oracle that data prior to this is in consistent mode and hence does not need recovery. it starts recovering data below that position till the end of redo thread is reached. This is where rolling forward (commited data) and rolling backward (uncommitted data) proccess is done by instance SMON process.

This is the reason I want to know what happens with long running DML, which does not get committed and redo data already been archived. Oracle handles this in a better way which I don't know.
The uncommitted DML changes are rolled back using the UNDO content; which is one reason why the UNDO exists.
Re: Oracle DML/Redo Data [message #671893 is a reply to message #671889] Sat, 22 September 2018 09:27 Go to previous messageGo to next message
John Watson
Messages: 7777
Registered: January 2010
Location: Global Village
Senior Member
sacharya2017 wrote on Sat, 22 September 2018 14:34
Hi 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 #671894 is a reply to message #671892] Sat, 22 September 2018 09:31 Go to previous messageGo to next message
sacharya2017
Messages: 19
Registered: January 2017
Junior Member
Yes, 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.
Re: Oracle DML/Redo Data [message #671895 is a reply to message #671893] Sat, 22 September 2018 09:41 Go to previous messageGo to next message
sacharya2017
Messages: 19
Registered: January 2017
Junior Member
Hi John,
Thank you very much. Always good to hear from you.

Yes, you're correct that instance recovery and transaction recovery is two different things. can you please explain how oracle will come to know that a particular long running DML is required a recovery? Undo segment will be used to rollback the transaction but what is the first starting point for Transaction recovery.

thanks
Re: Oracle DML/Redo Data [message #671913 is a reply to message #671894] Mon, 24 September 2018 02:17 Go to previous message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
sacharya2017 wrote on Sat, 22 September 2018 15:31
Yes, 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

Previous Topic: Instance duplication, Bi-directional implementation
Next Topic: Reading Hard disk file details through plsql
Goto Forum:
  


Current Time: Sat Feb 23 13:38:37 CST 2019