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

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.

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.

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

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.