Thanks for the question, Thippeswamy.
Asked: September 05, 2017 - 12:39 pm UTC
Last updated: September 07, 2017 - 1:07 am UTC
Version: 11.2.0.4.0
Viewed 1000+ times
You Asked
My Question:
1. When there is check point, data is written on Disk even if the transaction is not committed yet, when roll back issued, how does it happen..? I Mean end up with physical delete insert and update or logical operation like if
• Insert --- Decommission the data blocks from table space.
• Update -- Replace with data in the in undo table space.
• Delete -- Replace with data in the in undo table space.
2. When dirty buffer block reaches upper limit, CKPT process asks DBWn (Db writer) to write transactions to disk(committed or uncommitted), does mean it is check point..? Control file updated gets updated with newer log file and data files..?
and Connor said...
Even when we have the concept of "rollback", ultimately everything in the database is just "moving forward with changes"
So lets say you do:
delete from EMP;
=> wipe rows from block, change the SCN on the block from (say) 100 to 101
and then you do a rollback.
Conceptually that can be thought of as the same as two "normal" transactions:
1) wipe rows from block, change the SCN on the block from 100 to 101
2) insert 14 rows into the block, change the SCN on the block from 101 to 100
This is why a checkpoint doesn't really need to 'care' about committed versus uncommitted, we are really just bringing the datafiles up to date as of a point in time. You can almost think of datafiles as just as "performance enhancement" because theoretically every state of the database (ie, what the data is like now, or was like at 9am yesterday) could be generated from scratch by applying every change from the redo logs (which includes the changes that are done as part of a rollback operation). But of course, that would be very slow. Datafiles just give us a helping hand.
Is this answer out of date? If it is, please let us know via a Comment