** An interesting question **

[quester]

During recovery, the Archive Logs are applied on datafiles to ROLL-FORWARD all changes and ROLL-BACKWARD uncommitted transactions to bring the database to a consistent state.

Since Oracle can identify the committed and uncommitted in the archive logs, why didn't Oracle design the operations in such a way that only the committed get applied to the datafiles? I mean if Oracle only applied the committed transactiosn, it can save the work of rolling back the uncommitted ones, isn't it? Correct me if I am wrong anywhere in my understanding.

I know I have discussed this topic before, but this question still haunts me. Anyone interested to share their thoughts?

QUESTER

[Edited by quester on 09-18-2002 at 08:04 AM]

[jmodic]

Oracle can not find out which transactions are commited and which are not from the archive log he is currently applying. The transaction might start in the current log, but it might actually be commited in the next archive log (or in any archive log that is far ahead of the current one). So how would recovery process know which transaction to start recovering and which not?

That is why after the roll-forward phase is completed Oracle has to rollback any uncommited transaction. And it finds uncommited transactions from rollback segments, not from archived or online redo logs.

[ales]

There are many reasons to do that in this way.
I think the most obvious is the FAST COMMIT. Oracle expects most of the transaction will be commited. Commit only takes the time the logwriter writes a piece of log buffer to redo log file. All other things have been done, data have been changed in the buffer.

Thus, in most cases we enjoy the fast commit, sometimes we have to wait for long running rollback and in the rare case of recovery we have to wait for roll backward of uncommited transactions. Anyway, if I remember that well, database is opened just after all commited transactions are rolled forward and roll backward doesn't prevent using the database.

[quester]

Thanks for the response guys. That was really useful. BTW, I was thinking on these lines...since SCNs are generated for every committed transaction, Oracle can recognize those committed transactions; but then, SCNs are only written to data file headers and control file I guess. So incase committed data is just in the archive logs, perhaps there is no way to know whether it was committed or not as jmodic says.

But if there is a flag in the archive logs that suggests that the data was committed (which I guess must be there), Oracle can apply only when it identifies the flag. But if the flag is in one archive log and the data in another, then it makes complete sense the way Oracle functions.

Any thoughts / inferences?

[virajvk]

dear sir
I found everybody's response very good.

But In last reply u r talking about storing Flag in archive log file that is not possible as said transactions occupies multiple archive log.

Probabally we are asking Oracle to change there style of Recovery in which at a time one archive log file is applied.
What we are expecting is Oracle shuld scan all the archive log file simultatnously and mark uncommited transactions.
Which in turn mean following things

1.No incomplete Recovery is possible
2.And Recovery time is increased MTTR is getting higher and higher

Viraj
----------------------------------
9i OCA

[Shestakov]

Oracle has 2 modes of write into log.
-- sync mode (in most cases this is end of transaction)
-- background writes ( each 3 sec or 1/3 of fill of a log buffer)
when oracle write in sync mode (with commit) it generate and write commit marker.
transaction is unrecovereble and has to be rollback during recover process
until log block with commit marker has been write to log file.

[quester]

Originally posted by virajvk
dear sir
I found everybody's response very good.

But In last reply u r talking about storing Flag in archive log file that is not possible as said transactions occupies multiple archive log.

Probabally we are asking Oracle to change there style of Recovery in which at a time one archive log file is applied.
What we are expecting is Oracle shuld scan all the archive log file simultatnously and mark uncommited transactions.
Which in turn mean following things

1.No incomplete Recovery is possible
2.And Recovery time is increased MTTR is getting higher and higher

Viraj
----------------------------------
9i OCA

That was a wonderful way of looking at it. Thanks a ton, really learning a lot of things here.

P.S. Some maybe feeling I am asking silly questions and wasting time. May be it is. Since I am a baby growing in Oracle, I will have lot of inquisitive ness until I get a little matured. Kindly bear with me in case of any questions that may seem immatured.

Many thanks and kind regards.

QUESTER

[virajvk]

dear sir

This was not silly question indeed many times I had gone through such questions. That is why this is real good site for upcoming DBAs like me.

I am having such similar Interesting question which I will be starting with new thread.

Can some explain more about SYNC mode of Oracle

viraj
---------------
9i OCP

[quester]

I just incidently read that SCNs do get recorded in the archived log files; otherwise how is "Change-Based" recovery possible, which can recover to a SCN before the failure occured. So now we are sure that Oracle has SCNs (what I call commit flags) within the Archive Logs to identify which transactions were committed and which were not.
So now, does it mean that Oracle can definitely identify just the committed transactions from Archive Logs, but applying just that would effect in an increased adverse overhaead as Vijay explained?

Am I getting any clarity here to my original question?

QUESTER

[mrchrispy]

i think threads like this are a great deal of help to people just trying to figure out the internals of Oracle.

I've learnt something today children