The First part of question:
As i know RedoLog in the Oracle DB is used to protect the DB from data loses. As i understand one example of RedoLog duty is recover data after transaction was interrupted. For example if electricity was unexpectedly turned off. But i can not understand is DB appliy changes in redo Log straight after DB next time wakes up? Is DB commit changes that are in redoLog straight to DataFiles. Or it is just starts the transaction and changes blocks in buffer and marks them as dirty? But who then commits this dirty blocks to DB files? How i can continue transaction that was interrupted?
Second part of the question:
If i have this DML in transacion:
Lets assume that a = 1
for id = 1
UPDATE test_table
SET a = (SELECT a FROM test_table WHERE id = 1) + 1
WHERE id = 1;
I just incremented A in TEST_TABLE. So, if the transaction fails, and my DML statement is written to the RedoLog, it will be restored next time DB will be turned on. But what if the value of A
was change with another DB instance in data file. What will do the redo change vector in the RedoLog (Can actually it happen, may be in ORACLE RCA?) Will it increment the old value or the new one?
I apologise about my bad English. Will appreciate any answers.
When you execute the commit
statement, all the cached data from SGA (commited and uncommited) goes to the online redo log files of your database. This work is done by LGWR (Log writer process). So all the data (even undo data) is protected by the online redo log files. The DBWR (DataBase WRiter) process uses lazy algorithm to store your data to the data files. It writes data into the data files every 3 sec (by default, you can customize this value) or if your buffer is filled up to 80% or if a checkpoint occurs.
So if you loose electricity, next time during the database starting your data from online redo will be roll-forwarded from the online redo log files and then roll-backed all the uncommitted transactions (your undo data is also protected by online redo log files).
About your example, if you don't commit your transaction, you will not find the updated data after the database recovering. If your A
value has been changed by a different DML, this data also will be stored in online redo log files, so at first your update will be roll-forwarded then the DML that changed the A
value will be roll-forwarded.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With