Lecture 6 on Database Recovery

1. Lecture 6 on Database Recovery This lecture covers the backup recovery of a relational database in case of system failure such that the original relational database can be reconstructed from a copy of a saved relational database beforehand. Similarly, it can also be saved from an incomplete transaction etc.

2. Types of database system failures • Action failure • Transaction failure • System failure • Media failure

3. Database Recovery Techniques • Dumps/Checkpoints • Duplication • Generation method

4. Dumps/checkpointing Recovery procedure used will be in a form of dumping plus logging for most on-line database systems. This technique entails making safe copies of the database or part of it, at intervals termed checkpoints; and accumulating a separate log-file of copies of transactions affecting the database. During recovery from a system failure, the database is restored by reapplying transactions followed the checkpoint to the safe copy of the database.

5. Elements in dumping/ Checkpointing • Elements Database dump Before/After images Transaction log Success unit Checkpoint Write-ahead log • Recovery Procedures Reprocessing Roll-forward Roll-back Roll-forward with roll-back Delayed updating

6. Success unit It is a unit of processing, and during the execution of this the data it uses must not be updateable by any other process. For example, Undo(Undo(Undo…(x)))=Undo(x) for all x The result of nested undo(x)s is the same as an undo(x). That is, an undo may be interrupted due to system failure, but will be restarted and continue with another nested undo until it completes the undo, during which all of its data it uses must not be updateable by any other process.

7. Duplication Duplication is applied where immediate recovery from a hardware fault is required. The method is to keep two identical copies of the database, and apply all updates to both simultaneously. If one of the copy is damaged, the recovery procedure is to set a few status switches to the other one and operate on it. Then the damaged copy will be recovered and brought forward to current status.

9. Generation method This is to redo all transactions to the database from previous generations. For each generation, the database is dumped onto archive storage and all the transactions are logged. The dump should be taken when the database is static, that is, when no transactions are currently active, and all updates have been forced out to secondary storage. This is to prevent the dump from containing uncommitted changes that will have to be undone if the dump is used in the recovery process.

10. Generation method recovery procedure • Load the database on the new device from the most recent archive dump. • Use the transaction log to redo all transactions that completed since that dump was taken.

12. Before/After images They are recovery logs used to maintain recovery data. Before-image is a copy of the old version of any physical block of the database which is to be modified. After-image is a copy of the new version of any physical block of the database which has just been updated.

13. Logs of before/after image • Transaction sequence number. • Source terminal identity. • Date and time of change. • Transaction type. • Data set. • Records being modified.

14. Transaction Log • Transaction sequence number. • Source terminal identity. • Transaction type. • Date and time of transaction initiation. • Input message.

16. Checkpoint It is defined at which the database is known to be consistent. The database dump is made at the checkpoint. Recovery can only be applied to those transactions after the checkpoint. The checkpoint interval is chosen by considering the cost of taking checkpoints against the cost of restart.

18. Write-Ahead log It is a method used in keeping log record before a change is made to the database. If a failure occurs, a change may be recorded in the log and not in the database. On restart, the undo/redo entry points of the update, delete and insert components must be prepared for a request to undo/redo a change that was never done.

21. Recovery Procedures • Forward recovery – used where physical damage has occurred to the storage media. • Backward recovery – used where the storage media is not damaged, but the transaction(s) are incomplete.

22. Forward recovery • Image copies are made. • Recovery log records all changes to the current files. • If there is a media failure, the database is restored from the latest image copy. • The recovery log is used to redo all the changes since the last image copy.

25. Reprocessing • Restore the database or a particular area of a database from a dump copy. • Align the log, which only contains transactions records, to a point corresponding to the restored state of the database. • Reprocess all transactions until the end of the log file. • Restart processing of terminal input.

26. Roll-forward • Restore the database or a particular area of the database from a dump copy. • Align the log file containing after-images to a system recovery point (checkpoint) corresponding to the restored state of the database. • Apply after-image until a nominated system recovery point is reached. This is the last checkpoint before failure. • Restart processing of transactions from the nominated recovery point by receiving terminal inputs.

27. Redo transaction procedures for roll-forward

28. Suppress duplicate output messages A search is made of the log file between the last checkpoint and the point of failure and only those transactions on the log file which do not have a corresponding end of transaction indicator transmit output messages. The transactions which did complete successfully are re-run, but message output is suppressed.

29. Roll-back This is a method of backward-recovery. It is accomplished by means of removal of debris by the overwriting a recently modified units of the database with copies taken prior to modification (i.e. before images).

30. Roll-back requirements • Accurate knowledge of the current state of the database and run unit(s). • An accurate before-image journal. • Identifiable run unit recovery points.

31. Undo transaction procedures for roll-back

32. Database backward recovery Procedure

33. Roll-forward with roll-back • Restore the database or a particular area of the database from the dump copy. • Align the after-image to the checkpoint corresponding to the restored state of the database. • Apply after-images until the end of the log. • Apply before-images back to the last system recovery point in order to achieve a consistent state of the database.

34. Delayed updating This is an alternative strategy to roll-back for phase recovery. Like roll-back, it presupposes that database records are locked against concurrent access until phase-end. Updates performed during a phase are saved-up, and only applied upon successful phase end. Phase-failures require only discarding of the delayed updates.

35. Checkpoint Recovery • If a transaction fails, its uncommitted changes are undone by traversing the log backwards. • After a crash, the system uses the shadow versions that were recorded at the last checkpoint. Any transactions that committed after the checkpoint are redone.

36. Sample transaction system failures with checkpoints

37. Examples of transaction recovery System initial states • A system failure has occurred at time tf. • Latest checkpoint was taken at time tc prior to time tf. • Transaction T1 were completed before tc. • Transaction T2 started prior to tc and completed after tc and before tf. • Transaction T3 started prior tc but did not complete by tf. • Transaction T4 started after tc and completed before tf. • Transaction T5 started after tc but did not complete by time tf. System recovery states At restart, transactions T3 and T5 must be undone. Transactions T2 and T4 must be redone.

38. Database recovery system • Perform maintenance operations so as to leave the database in a correct state in the event of a failure. • Maintain recovery data that can be used after a failure.

39. Lecture 6 Summary The lecture describe forward, backward and their combination method of database recovery. With backup recovery, a database system can be reliably run and uptime can be achieved at all time. The technique is to save a replicate copy of the production database beforehand.

40. Review Question What are the differences in database recovery operations among Roll Forward, Roll Back and Roll Forward with Roll Back? Under what situations do you perform Roll Forward, Roll Back and Roll Forward with Roll Back?

41. Tutorial Question What are the impact factors of checkpoint interval duration with respect to database recovery? What are the causes and solutions for temporary damages to a database? What are the causes and solutions for permanent damages to a database?

42. Reading Assignment Chapter 19 Database Recovery Techniques of “Fundamentals of Database Systems” fifth edition by Elmasri & Navathe, Pearson, 2007.