Lesson 7: Database Recovery

7 Database Recovery Recovery brings the database from the temporary inconsistent state to a consistent state. Database recovery can also be defined as mechanisms for restoring a database quickly and accurately after loss or damage. Databases are damaged due to human error, hardware failure, incorrect or invalid data, program errors, computer viruses, or natural catastrophes. Since the organization depends on its database, the database management system must provide mechanisms for restoring a database quickly and accurately after loss or damage. 7.1 Types of Database Failures A wide variety of failures can occur in processing a database, ranging from the input of an incorrect data value or complete loss or destruction of the database. Some of the types of failures are listed below:  System crashes, resulting in loss of main memory  Media failures, resulting in loss of parts of secondary storage  Application software errors  Natural physical disasters  Carelessness or unintentional destruction of data or facilities  Sabotage 7.2 Recovery Facilities DBMS should provide following facilities to assist with recovery. i. Backup mechanism, which makes periodic backup copies of database ii. Logging facilities, which keep track of current state of transactions and database changes iii. Checkpoint facility, which enables updates to database in progress to be made permanent iv. Recovery manager, which allows DBMS to restore the database to a consistent state following a failure 7.2.1 Backup Mechanism The DBMS should provide backup facilities that produce a backup copy of the entire database. Typically, a backup copy is produced at least once per day. The copy should be stored in a secured location where it is protected from loss or damage. The backup copy is used to restore the database in the event of hardware failure, catastrophic loss, or damage. With large databases, regular backups may be impractical, as the time required to perform the backup may exceed that available. As a result, backups may be taken of dynamic data regularly but backups of static data, which do not change frequently, may be taken less often. 1 7.2.2 Logging Facilities The two types of logs are “transaction log” and “database change log.” A transaction log is a record of the essential data for each transaction that is processed against the database. In database change log, there are before and after images of records that have been modified. Transaction log. Transaction log contains a record of the essential data for each transaction that is processed against the database. Data that are typically recorded for each transaction include the transaction code or identification, action or type of transaction, time of the transaction, terminal number or user ID, input data values, table and records accessed, records modified, and possibly the old and new field values. Database change log. The database change log contains before and after images of records that have been modified by transactions. A before-image is a copy of a record before it has been modified, and an after-image is a copy of the same record after it has been modified. 7.2.3 Checkpoint Facility A checkpoint facility in a DBMS periodically refuses to accept any new transactions. All transactions in progress are completed, and the journal files are brought up to date. At this point, the system is in a quiet state, and the database and transaction logs are synchronized. The DBMS writes a special record (called a checkpoint record) to the log file, which is like a snapshot of the state of the database. The checkpoint record contains information necessary to restart the system. Any dirty data blocks are written from memory to disk storage, thus ensuring that all changes are committed prior to taking the snapshot. 7.2.4 Recovery Manager The recovery manager is a module of the DBMS which restores the database to a correct condition when a failure occurs and which resumes processing user requests. The recovery manager uses the logs to restore the database. Three main recovery techniques that are commonly used by the recovery manager are: i. ii. iii. Deferred update Immediate update Shadow paging Deferred update. Deferred updates are not written to the database until after a transaction has reached its commit point. If transaction fails before commit, it will not have modified database and so no undoing of changes are required. Deferred update may be necessary to redo updates of committed transactions as their effect may not have reached database. Immediate update. In the case of immediate update, updates are applied to database as they occur. There is a need to redo updates of committed transactions following a failure. Also there may be need to undo effects of transactions that had not committed at time of failure. It is essential that log records are written before write to database. If no “transaction commit” record 2 in log, then that transaction was active at failure and must be undone. Undo operations are performed in reverse order in which they were written to log. Shadow paging. Shadow paging maintains two-page tables during life of a transaction, current page and shadow page table. When transaction starts, two pages are the same. Shadow page table is never changed thereafter and is used to restore database in the event of failure. During transaction, current page table records all updates to database. Crash Recovery Crash recovery is the process of protecting the database from catastrophic system failures and media failures. Recovery manager of a DBMS is responsible for ensuring transaction atomicity and durability. Atomicity is attained by undoing the actions of transactions that do not commit. Durability is attained by making sure that all actions of committed transactions survive system crashes. 3