Printable View
Hey, thanks a lot buddy. I am happy there are atleast a few who found this post a worth.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 :)
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.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?
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...
Very well explained. Thanks a TON.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...
I think there is a small dent in my imagination and your understanding. Maybe you can help be decipher the information.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!!??
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]
If I understand your imaginary scenario, it goes something like this: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.
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:Quote:
Originally posted by jmodic
If I understand your imaginary scenario, it goes something like this: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.
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.
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
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. ;)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.
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.
Thank you kind Sir. Thanks for the clarity and sincere apologies on my childish ignorance.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. ;)