** An interesting question **

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]

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.

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.

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?

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

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.

Quote:

---

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

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

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

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 :)

Quote:

---

Originally posted by 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 :)

---

Hey, thanks a lot buddy. I am happy there are atleast a few who found this post a worth.

Quote:

---

Originally posted by 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?

---

JMODIC: 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?

But of course they are recorded in archived redologs. But it is recorded in the archived redo logs where transaction has been commited, not in the redo log where transaction has started.

Immagine this scenario:

Transaction is started at T0 and the current redo log will be archived as arclog number 100. This same transaction is commited later on at time T1. There have been 99 log switches betven T0 an T1. So when the current redo log is archived it is stored as archlog number 200. The marker that the transaction was commited is stored in archlog #200, not in #100!

Now you are performing media recovery. Recover process starts roll forward of archlog #100 and finds your transaction was started. Now what? Should it start checking all archlogs generated after that untill it finds whether it has been commited or not? For each and every started transaction!!?? Can you immagine the overhead? The recovery might take ages to complete!

Oracle has actually implemented something simmilar to what you have suggested in 9i. It is simmilar only in principle, not in implementation - they are not checking which tranasctions were commited, but which changes in redo stream have allready been written to disks, so they are not dirty anymore and need no recovery. But there is one HUGE difference: They have implemented this only for instance recovery, not for media recovery. In instance recovery Oracle has to check only changes in online redo logs, which are typicaly relatively small. But it is unfeasable to do so in media recovery where you might have thousands of archlogs to apply...

Quote:

---

Originally posted by jmodic
But of course they are recorded in archived redologs. But it is recorded in the archived redo logs where transaction has been commited, not in the redo log where transaction has started.

Immagine this scenario:

Transaction is started at T0 and the current redo log will be archived as arclog number 100. This same transaction is commited later on at time T1. There have been 99 log switches betven T0 an T1. So when the current redo log is archived it is stored as archlog number 200. The marker that the transaction was commited is stored in archlog #200, not in #100!

Now you are performing media recovery. Recover process starts roll forward of archlog #100 and finds your transaction was started. Now what? Should it start checking all archlogs generated after that untill it finds whether it has been commited or not? For each and every started transaction!!?? Can you immagine the overhead? The recovery might take ages to complete!

Oracle has actually implemented something simmilar to what you have suggested in 9i. It is simmilar only in principle, not in implementation - they are not checking which tranasctions were commited, but which changes in redo stream have allready been written to disks, so they are not dirty anymore and need no recovery. But there is one HUGE difference: They have implemented this only for instance recovery, not for media recovery. In instance recovery Oracle has to check only changes in online redo logs, which are typicaly relatively small. But it is unfeasable to do so in media recovery where you might have thousands of archlogs to apply...

---

Very well explained. Thanks a TON.

Quote:

---

Originally posted by jmodic

Now you are performing media recovery. Recover process starts roll forward of archlog #100 and finds your transaction was started. Now what? Should it start checking all archlogs generated after that untill it finds whether it has been commited or not? For each and every started transaction!!??

---

I think there is a small dent in my imagination and your understanding. Maybe you can help be decipher the information.

When you said " Recover process starts roll forward of archlog #100 and finds your transaction was started. Now what? Should it start checking all archlogs generated after that untill it finds whether it has been commited or not?"

What I meant was: Oracle need NOT search all the logs to see whether this #100 was committed; it can skip #100 as anyway it knows its not committed at that moment. But when it passes #200, it knows it got committed, thats when it can apply to datafiles. Do you see what I mean? Maybe I am mistaken of some fundas somewhere or I am missing something very significant. Your share of thoughts shall enlighten me.

Many thanks.


[Edited by quester on 09-20-2002 at 07:16 AM]

Quote:

---

Originally posted by quester
What I meant was: Oracle need NOT search all the logs to see whether this #100 was committed; it can skip #100 as anyway it knows its not committed at that moment. But when it passes #200, it knows it got committed, thats when it can apply to datafiles.

---

If I understand your imaginary scenario, it goes something like this:

Oracle finds a new transaction in log# 100. It reads the corresponding data block into buffer cache from data file and applies chenges from archlog to it. So the changes are applied to blocks in SGA, but the main improvement would be if those blocks would not be written to disks until Oracle finds the transaction has been commited in #200. Is this what you are trying to say?

If so, there are couple of problems with this scenario.

First, immagine your buffer cache can hold 500 MB of database blocks, yet the changes inclouded in archlogs from #100 to #200 would require 5GB worth of data blocks to be processed. Now what? The block with starting changes from #100 for this transaction was flushed from buffer cache long time ago!

Second problem. In archlog #150 there is a start of a new transaction that writes to the same blocks as your first transaction. Yet this second transaction will be commited afetr your first one, say in #250. So when we reach the commit of the first transaction in #200, should we write blocks that are dirty because of this transaction? But those blocks at the same time contains changes from the second transaction which has not been commited yet! So what to do, isn't this a collision of set rules? Remember that Oracle writes only complete blocks to disk, not partial changes that it finds in archlogs.

Quote:

---

Originally posted by jmodic

Quote:

---

Originally posted by quester
What I meant was: Oracle need NOT search all the logs to see whether this #100 was committed; it can skip #100 as anyway it knows its not committed at that moment. But when it passes #200, it knows it got committed, thats when it can apply to datafiles.

---

If I understand your imaginary scenario, it goes something like this:

Oracle finds a new transaction in log# 100. It reads the corresponding data block into buffer cache from data file and applies chenges from archlog to it. So the changes are applied to blocks in SGA, but the main improvement would be if those blocks would not be written to disks until Oracle finds the transaction has been commited in #200. Is this what you are trying to say?

If so, there are couple of problems with this scenario.

First, immagine your buffer cache can hold 500 MB of database blocks, yet the changes inclouded in archlogs from #100 to #200 would require 5GB worth of data blocks to be processed. Now what? The block with starting changes from #100 for this transaction was flushed from buffer cache long time ago!

Second problem. In archlog #150 there is a start of a new transaction that writes to the same blocks as your first transaction. Yet this second transaction will be commited afetr your first one, say in #250. So when we reach the commit of the first transaction in #200, should we write blocks that are dirty because of this transaction? But those blocks at the same time contains changes from the second transaction which has not been commited yet! So what to do, isn't this a collision of set rules? Remember that Oracle writes only complete blocks to disk, not partial changes that it finds in archlogs.

---

Many thanks. Your explaination was excellent, no doubts, but I am afraid whether it answered my question. I think I lack the knowledge of some fundamentals. Let me take you through my example:

A transaction begins at #100: Lets say UPDATE EMP SET SAL = 2000; At #300 this gets committed. Therefore at #300 Oracle knows "UPDATE EMP SET SAL = 2000;" this was commited and must be written to files.

Now what Oracle can do in my imagination (correct me anywhere if I am wrong) - At #100, Oracle knows "UPDATE EMP SET SAL = 2000;" this was not committed....so let Oracle not copy it to the buffers...let it skip it. At #300, Oracle knows "UPDATE EMP SET SAL = 2000;" this is committed, at that time it can read it to the buffers for write to the data files. My whole contention is only based on one thing - copy to files ONLY when you know its committed, until then you can skip the transaction.

I am sure I am missing something very fundamental. Kindly spare a few minutes to enlighten me.

Regards,
QUESTER

Perhaps no-one knew this here. I incidentally read this and thought I might share this with you all regarding this topic.


"To get round this, Oracle 9i introduces a 2-pass read of the redo logs during Instance Recovery. The first pass doesn.t actually do anything. it simply allows Oracle to determine which parts of the redo stream actually need to be applied. The second pass actually applies the relevant bits of redo to recover the database.
In theory (and especially if you have placed the redo logs onto separate, fast devices .like you.re supposed to), the time saved not attempting to recover data blocks which don.t need it will outweigh the extra time taken to read the redo logs twice.

The secret of this new mechanism is that some trivially small extra information is recorded in the redo logs any time any buffers get flushed to disk. The redo stream therefore knows whether a block is dirty (and needs recovery) or clean (and doesn.t).

This is the new default mechanism for Oracle.s Instance Recovery process. It cannot be changed (i.e., there is no parameter available to make it behave like the Oracle 8 or 8i recovery process). "












Does this give us some insight on this topic.

Regards,
Quester

Quote:

---

Originally posted by quester
Perhaps no-one knew this here. I incidentally read this and thought I might share this with you all regarding this topic.

"To get round this, Oracle 9i introduces a 2-pass read of the redo logs during Instance Recovery. ...... "

Does this give us some insight on this topic.

---

Well, anyone who has been studying (or at least reading) about the new features of 9i (not to mention those who have allready passed their 9i OCP exams) surely nows this, so your news are not realy news. ;)

So why has noone mentioned this in respect to your original question? Well, because this 2-pass redo reads are something quite different, not applicable to your scenario! Why not? Because of one tiny little detail: this new 9i feature is relevant only to instance (or crash) recovery, while your question was clearly in the context of media recovery!

In instance recovery only online redo logs are involved and those are totaly predictible, i.e. Oracle knows where they are and how many/how large they are. While archived redo logs are totaly different beasts - when you start media recovery Oracle has no idea where they actualy are stored and how many of them it will need to apply. Besides, the additional information that 9i writes into redo logs in order to enable 2-pass fast-start instance recovery is not about which transactions ahve been commited or not (which was the idea of your initial question), but which buffers from buffer cache have been written to disk and which not, meaning that those which have allready been written to disk can be skipped during instance recovery. But since checkpoint must have been completed before a redo log can be archived it is self evident that this additional information is useless in archived redo logs (all buffers involved in that archived log file were written to disks, that's for sure). So this mechanism is simply not applicable to media recovery.

Quote:

---

Originally posted by jmodic
Well, anyone who has been studying (or at least reading) about the new features of 9i (not to mention those who have allready passed their 9i OCP exams) surely nows this, so your news are not realy news. ;)

---

Thank you kind Sir. Thanks for the clarity and sincere apologies on my childish ignorance.

Hi All,
May be this is relevant since we are talking abt recoverability,
In Oracle9i, while recovering Oracle reads the Logs twice
Firtst time it decides which all relevant data it needs to recover and in the second pass it actually applies those changes.
Thought just t share these with U guys

Regards
Sudip Sarkar

Hi ,
i guess oracle has a unique way of identifying each transaction in the archive logs ( each transaction may be uniquely identified by sequence# , switch_change# ) . while recovering oracle does move archive logs contents in redolog buffer in sequence ( all the transactions , whether they are committed or not ) then in redo log buffer, flag is set, if commit is present for a particular transaction id it is further moved to SGA or datafiles . if not they are ignore in redo log buffer stage . this loop continues .

SCN is present only for commited transactions . i don't think if there is any number for uncommitted transactions ?

i guess single archival log can have without any SCN Number or a single log can have more than 1 SCN .

if anybody can post sample archival logs contents from logminer could be useful, to trace exactly what values are stored in the archival logs .

everything must have a scn otherwise I dont know how Oracle can recover uncommitted transactions in an instance recovery?

I am confused a bit with this piece of explaination I read in one of the Oracle manuals: It reads -

"Redo Logs contain both committed and uncommitted transactions. In 8i, distinguishing the committeds from the uncommitteds was a bit tricky, because the only thing tying a SQL Statement to its eventual commit was that both rows in v$logmnr_contents had the same Serial Number. You were reduced, therefore, to issuing this sort of query:

select serial#, sql_redo from v$logmnr_contents where operation='UPDATE' and serial# in (select serial# from v$logmnr_contents where operation='COMMIT');

Version 2 Oracle 9i New Features for Administrators
Copyright ©Howard Rogers 2002 5/03/2002 Page 23 of 115

Now 9i natively includes a new option for the start_logmnr procedure,
COMMITTED_DATA_ONLY, which groups transactions together by their serial number, and excludes any which don.t have a .committed. identifier. Note that this could be misleading: it.s possible to issue an Update statement whilst we.re in Log 103, and a commit once we.ve switched to Log 104. If you only analyse Log 103 with this new option, that update will not be listed, because its matching
commit is not included within the analysed log.
Note, too, that this does not cure the problem of how rolled back transactions look. A rollback is as final as a commit (you can.t roll forward after issuing that command, after all!) and hence a statement issued in Log 103 and rolled back in Log 103 will still be listed, even with the COMMITTED_DATA_ONLY option set.








Can someone please explain me this, as this is again in a way related to this topic in the eye of the Log Miner.

since you dont say what you dont understand I cant help much

in Oracle 9i you can choose to see comitted data only, in 8i not