Data and Database Administration

1. Data and Database Administration Chapter 12 (Contd.)

2. Recovery and restart procedures

3. Transaction Integrity • Many DB transactions result in changes to one or more DB records.If an error occurs during a transaction processing, then some form of DB recovery will be required. • To understand DB recovery, we must understand the concept of transaction integrity. • A business transaction represents a business activity which has a sequence of steps. • When processing transactions, DBMS must ensure the four basic properties called ACID: • Atomic • meaning that the transaction can not be subdivided, it may be processed in entirety or not at all.

4. Transaction Integrity (Contd.) • Consistent • meaning that DB constraints that must be true before the transactions must also be true after the transaction. • Isolated • meaning changes to the DB are not revealed to the users until the transaction is committed. • Durable • meaning that changes are permanent, once a transaction is completed, no subsequent failures can revert back the effect of transaction.

5. Recovery and Restart Procedures • Type of recovery procedure depends on • The nature of failure • The sophistication of DBMS recovery facilities • Operational policies and procedures • Techniques frequently used are: • Switch • Restore/run • Backward recovery • Forward recovery

6. Recovery and Restart Procedures: Switch • To switch to an existing copy of the database, the database must be mirrored, i.e. two copies of the database must be kept and updated simultaneously. • On failure, processing is switched to the duplicate copy of the database. • This technique allows fastest recovery. • Level-1 RAID systems implement mirroring. • Instead of storing the DB on a large drive, it is it is distributed across several smaller, less expensive disks and mirrored to a second set of such disks. • On disk failure, system switches to the mirrored disks.

7. Recovery and Restart Procedures: Restore/run • This technique involves reprocessing the day’s transactions (upto point of failure) against the backup copy of the database/portion of DB. • The database is shut down • then most recent copy of DB to be recovered is mounted, • and all transactions that have occurred since that copy are rerun. • Advantage • simplicity (no need to create DB change journal, no restart procedures) • Disadvantages • reprocessing overhead (depending on frequency of making backup copies, several hours may be required) • Processing of new transactions will have to be deferred until recovery is completed. • The sequence in which transactions are rerun, may lead to quite different results. • Hence, restore/rerun is used only as a last resort in DB processing.

8. Backward Recovery (rollback) • Through this technique, the DBMS back outs or undoes the unwanted changes to the database. • Before-images of the records are applied to the database. Thus, the DB returns to its earlier state. • Backward recovery is used to reverse changes due to aborted or abnormally terminated transactions.

9. Forward Recovery (rollforward) • A technique that starts with an earlier copy of the database. • After-images (results of good transactions) are applied to the database, and DB is quickly moved forward to a later state. • It is much faster & accurate as compared to restore/rerun because: • It does not involve the time consuming reprocessing of transactions • Only the most recent after images need to applied. A DB-record may have a series of after-images but the most recent, good after-image is used in rollforward.

10. Types of DB failure • Four of the most common types of failures/problems are: • Aborted transactions • Incorrect (but valid) data • System failure • Database loss or destruction