Q1 DBWR Process writes dirty bufers to Data files. Consider a situation in which DBWR writes uncommitted data to data files and instance then crashes then how its recovery will happen next time when instance starts?
During recovery, the data in the (online) redo log files will be used.
For simplicity, assume that the DB will insert log records as follows:
where, Tx(i) is the transaction ID, X is the data item that being access, Vold is the old value of data item X, Vnew is the new value of data item X.
Thus, if we use the simplest recovery algorithm, we scan the redo log once, we can have a set of Tx(m) where they are committed, and we can have another set of Tx(n) where they are not committed (those transactions without the operations commit are regarded as not committed, and will be rollback). And we can first undo those uncommitted transactions inside the datafiles, and the redo those committed transactions.
Q2 LGRW write log entries to Log files..similarly if LGRW writes uncommited redo log entries to redo log files and then instnce crashes what will happen?
Based on the above discussion, if the transactions do not committed, the operation commit will NOT appear inside the redo log files, and thus, we know which transactions to be redo and which to be undo.
Q3 How SCN is tackled at instance recovery procedure?
Whenever there is a new log record, a SCN will be assigned to it. In normal operations, if the transaction commits, the committed transaction, together with its corresponding SCN in the log will be recorded. Thus, it can improve a bit with our simplest algorithm. If the SCN in the redo log file is already committed, it imples that the data MUST appear in the data file, and we do not need to cater it during recovery.
Q4 How at instance recovery phase oracle db come to know that this data in data files belongs to uncommitted transaction adn these redo lod entries in redo log files are part of committed transaction
The simiplest recovery algorithm example shows how can the DB recover the data. Of course, this may not be the algorithm for DB to recover, as the performance issues. They will use other algorithms but the idea is same to the simplest one.
Nick
SCJP 1.2, OCP 9i DBA, SCWCD 1.3, SCJP 1.4 (SAI), SCJD 1.4, SCWCD 1.4 (Beta), ICED (IBM 287, IBM 484, IBM 486), SCMAD 1.0 (Beta), SCBCD 1.3, ICSD (IBM 288), ICDBA (IBM 700, IBM 701), SCDJWS, ICSD (IBM 348), OCP 10g DBA (Beta), SCJP 5.0 (Beta), SCJA 1.0 (Beta), MCP(70-270), SCBCD 5.0 (Beta), SCJP 6.0, SCEA for JEE5 (in progress)