Database recovery

1. Database recovery

2. Database Recovery • Mechanism for restoring a database quickly and accurately after loss or damage • RESPONSIBILITY OF ????? • Recovery facilities: • Backup Facilities • Journalizing Facilities • Checkpoint Facility • Recovery Manager

3. Back-up Facilities • A DBMS COPY utility that produces a backup copy (save) of the entire database or a subset of the database • Backup: not only the database files but also related database objects (repository, database indexes, source libraries, etc) • Periodic backup (e.g. nightly, weekly) • Backups stored in secure, off-site location • Backup copy-used to restore the database • Cold backup–database is shut down during backup • Hot backup–selectedportion is shut down and backed up at a given time • Incremental backups: record changes made since the last backup • Differential backups: record changes made since the last full/normal backup • the differences since the last full backup.

4. Back-up Facilities • Database downtime can be very expensive • The lost revenue needs to be balanced against the cost of additional technology, primarily disk storage, to achieve a desired level of availability • To achieve: some DBMS automatically make backup copies in real time. • Stored in on separate disk drives

5. Back-up Facilities Source: http://www.qadit.com/blog/?p=283

6. Source: http://kb.acronis.com/content/1536

7. Source: http://kb.acronis.com/content/1536

8. Source: http://kb.acronis.com/content/1536

9. Journalizing Facilities • Audit trail of transactions and database changes • In the event of failure: consistent database state can be reestablished using the information in the journals together with the most recent complete backup • Two basic journals or logs: • Transaction log–record of essential data for each transaction processed against the database • Transaction code, action, time, terminal no/user ID, input data values , tables/records accessed & modified and the old & new field values. • Database change log–images of updated data • Before-image–copy of a record before modification • After-image–copy of a record after modification Produces an audit trail

10. Database audit trail From the backup and logs, databases can be restored in case of damage or loss

11. Checkpoint Facilities • A facility by which the DBMS periodically refuses to accept new transactions. The system is in a quiet state and the database and transaction logs are synchronized • All transactions in progress are completed and journal files are brought up-to-date • DBMS writes a special record (checkpoint record) to the log file: snapshot of the state of the database • Checkpoint record contains information necessary to restart the system • Any dirty data blocks (pages of memory that contain changes that have not yet been written out to disk) are written from memory to disk storage • Automatically or response to commands in user application programs This allows recovery manager to resume processing from short period, instead of repeating entire day

12. Recovery Manager • A module of the DBMS that restores the database to a correct condition when a failure occurs and then resumes processing user requests. • Type of restart used depends on the nature of failure.

13. Recovery and Restart Procedures • Disk Mirroring–switch between identical copies of databases • Restore/Rerun–reprocess transactions against the backup • Transaction Integrity–commit or abort all transaction changes • Backward Recovery (Rollback)–apply before images • Forward Recovery (Roll Forward)–apply after images (preferable to restore/rerun)

14. Disk Mirroring • Database must be mirrored  switch to an existing copy of the database • 2 copies of the database must be kept & updated simultaneously • Media failure occurs: processing switch to the duplicate copy • Allows fastest recovery Recovery and Restart Procedures

15. Restore/Rerun • Involves reprocessing the day’s transactions (up to the point of failure) against the backup copy of the database • Database is shut down • The most recent copy of the database /file to be recovered is mounted • All transactions that have occurred since that copy (stored on the transaction log) are rerun Recovery and Restart Procedures

16. Restore/Rerun • Advantage: • Simplicity • DBMS does not need to create a database change journal & no special restart procedures required • Disadvantages: • Time to reprocess transactions may be prohibitive • Processing of new transactions delayed until recovery completed • Sequencing of transactions will often be different from when they were originally processed: may lead to different results. • Original Run: customer deposit may be posted before withdrawal • Rerun: Withdrawal transaction may be attempted first. • Last resort in database processing Recovery and Restart Procedures

17. Backward Recovery (Rollback) • DBMS backs out of or undo unwanted changes to the DB – before images captured • Reverse the changes made by transactions that have aborted or terminated abnormally • Example: transfer 100 from account for cust A to cust B • Program reads the record for customer A and subtracts 100 from the acc balance • Program reads the record for customer B and adds 100 to the acc balance. • Program writes the updated record for A to the dbase. • In attempting to write the record for B, program encounters an error condition and cannot write the record. • An UNDO command – recovery manager to apply the before image for record A to restore acc balance to its original value. Recovery and Restart Procedures

18. Basic recovery techniques Rollback Recovery and Restart Procedures

19. Forward Recovery (Roll Forward) • A technique that starts with an earlier copy of the database. After images are applied to the database and the database is quickly moved forward to a later state. • Much faster than Restore/Rerun: • The time consuming logic of reprocessing each transaction does not have to be repeated • Only the most recent after-images need to be applied. DB record may have series of after image – most recent (good) after image is required for rollback Recovery and Restart Procedures

20. Basic recovery techniques Rollforward Recovery and Restart Procedures

21. Transaction Integrity • Integrity of transactions: DB is updated by processing transactions that results in changes to one or more DB records • When processing transactions, DBMS must ensure that the transactions follow four well-accepted properties – ACID • Atomic • Consistent • Isolated • Durable Recovery and Restart Procedures

22. Transaction Integrity • To maintain transaction integrity – DBMS must provide facilities for the user or application program to define transaction boundaries – logical beginning and end of transaction. BEGIN TRANSACTION . . UPDATE INSERT . . COMMIT Recovery and Restart Procedures

23. Database Failure Responses • Aborted transactions • Preferred recovery: rollback • Alternative: Rollforward to state just prior to abort • Incorrect data • Preferred recovery: rollback • Alternative 1: rerun transactions not including inaccurate data updates • Alternative 2: compensating transactions • System failure (database intact) • Preferred recovery: switch to duplicate database • Alternative 1: rollback • Alternative 2: restart from checkpoint • Database destruction • Preferred recovery: switch to duplicate database • Alternative 1: rollforward • Alternative 2: reprocess transactions

24. Disaster Recovery (New) • Contingency plans to cater for disasters – destroy/damage data center • Natural disasters • Planning for DR • Develop a detailed DR plan • Schedule regular test of plan • Choose multi-disciplinary team to carry out plan • Fast backup data center – off site location • Send back up copies to backup data center

25. Contingency Plan • Contingency plan is established to deal with unusual events that are not part of the normal daily routine • Contingency plans detail the response necessary to deal with the types of event that may occur • A contingency plan should include : • who the key personnel are and how they can be contacted • if the key personnel are unavailable, a list of alternative personnel and how they can be contacted • who decides that a contingency exists and how that is decided • the technical requirements of transferring operations elsewhere • the operational requirements of transferring operations elsewhere • any outside contacts who may help • whether any insurance exists to cover the situation

26. THE END