In the Oracle 9i concepts manual, it says,
"Sometimes, if more buffer space is needed, LGWR writes redo log entries before a transaction is committed. These entries become permanent only if the transaction is later committed."
Why does Oracle even have to write uncommitted transactions in redo logs? Can I assume it is for a huge transaction which, say, updates/inserts something like a 50,000 rows? In this case Oracle cannot have all the entries in the memory and so it writes into the redo log files along with the data files. Am I right in making this assumption? How do those entries become 'not permanent' if the transaction is not committed? Rollback?
Regarding group commits, the manual also says,
"In times of high activity, LGWR can write to the online redo log file using group commits. If requests to commit continue at a high rate, then every write (by LGWR) from the redo log buffer can contain multiple commit records."
Don't we lose the distinction between different SCNs when we do group commits? How do we perform a recovery to a particular SCN with group commit or do we have forego that luxury?
You are correct in your assumption. The Log Buffer space (determined by the LOG_BUFFER parameter) in your instance's SGA is a finite size. Typically, this SGA memory component is not large compared to other memory structures in the SGA. If a transaction is sufficiently large, then there is no way the Log Buffer can accomodate all of the redo information. When this happens, data must be written to the online redo logs before the transaction is committed. If LGWR was not allowed to write to the online redo logs until a transaction is committed, then your transactions would have a finite size to them (in terms of redo bytes).
Your assumption focuses on the effects of one large transaction on the Log Buffer and LGWR. Now focus on many transactions. What if many, small transactions fill the Log Buffer? LGWR has no choice but to write the transactions, even though they are uncommitted, to the online redo log files.
The statement from the Oracle documentation is somewhat misleading. When I read that statement, I get the sense that transactions that are rolled back are not written to the online redo logs. This is not true. All transactions, committed or rolled back, are written to the online redo logs. At the end of each transaction is a marker to indicate if the transaction was committed or rolled back. So to make a transaction "not permanent", a rollback marker is written to the online redo logs.
When LGWR gets the signal to write to the online redo logs, it will typically write many transactions at once. Each transaction has its own SCN. While group commits may occur at what seems like the same time, they are not the same time. The SCN of each individual transaction denotes the relative time the individual transaction committed.
Your final question on restoring to a particular SCN is not a valid thought because each individual transaction has their own SCN, regardless of whether or not it participated in a group commit or not.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.