1 / 13

Data and Database Administration

Data and Database Administration. Chapter 12 Modern Database Management. Outline. Data Recovery and Backup. Basic Recovery Facilities. A DBMS should provide four basic backup and recovery facilities Backup facilities Journalizing facilities Checkpoint facilities Recovery Manager.

cvandemark
Download Presentation

Data and Database Administration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data and Database Administration Chapter 12 Modern Database Management

  2. Outline • Data Recovery and Backup

  3. Basic Recovery Facilities • A DBMS should provide four basic backup and recovery facilities • Backup facilities • Journalizing facilities • Checkpoint facilities • Recovery Manager

  4. Backup Facilities • A DBMS should produce a backup copy of entire DB, plus control files and journals. • The copy should be stored in a secure place and used to restore the DB in the event of failures/loss/damage. • For backups, DBAs can use • Backup utilities available in (some) DBMS • Operating system commands • Export commands • SELECT … INTO SQL command • Other utilities

  5. Backup Facilities (Contd.) • Database Backup Strategies • For large DBs, regular backups may be impractical (time consuming). • Two ways of taking backup • Hot backup – when DB is not shutdown from use • Cold backup - when DB is shutdown from use • A database may be a critical system that should always be available, so a cold backup is not practical. • Regular backups may be taken of dynamic data (a so-called hot backup where only selected portion of DB is shutdown from use)

  6. Backup Facilities (Contd.) • Backups of static/less frequently changing data may be taken less often. • Incremental backups which record changes made since last full backup, may be taken on interim basis. Incremental backups take lesser time. • Database backup strategy depends on the demands being placed on the database system.

  7. Journalizing Facilities • A DBMS must provide journalizing facilities to keep an audit trail of transactions and database changes. • Transaction: a discrete unit of work that must be completely processed or not processed at all within a computer system. e.g., entering a customer record • In the event of failure, a consistent database state can be re-established by using the information in the journals together with the most recent complete backup. • There are two types of journals or logs: • Transaction log • Database change log

  8. Journalizing Facilities (Contd.) • Transaction log • It contains a record of the essential data for each transaction that is processed against the database • Data typically recorded for a transaction include • transaction code or ID • Action or type of transaction (e.g. insert) • Time of transaction • Terminal number or user ID • Input data values • Tables and records accessed • Records modified • And possibly the old and new field values

  9. Journalizing Facilities (Contd.) • Database Change log: • It contains the before-images and after-images of records that have been modified by transactions. • Before-image • A copy of a record before it has been modified • After-image • A copy of the same record after it has been modified • Some systems also keep a security log which can alert the DBA to any security violations.

  10. Database (current) Transaction Log Database Change log Database (backup) Figure - Database audit trail Transaction DBMS Recovery action Copy of database affected by transaction Effect of transaction or recovery action Copy of transaction

  11. Checkpoint Facilities • A facility by which DBMS periodically refuses to accept any new transactions. • All transactions in progress are completed and the journal files are updates. • At this point, system is in a quiet state, and the database & transaction logs are synchronized. • The DBMS writes a special record (checkpoint record) to log file which is like a snapshot of the state of database. • The checkpoint record contains info necessary to restart the system.

  12. Checkpoint Facilities (Contd.) • Any dirty blocks (memory pages containing changes not yet written to the disks) are written from memory to the disk storage, ensuring that all changes made prior to taking checkpoint have been written to long-term storage. • DBMS may perform checkpoints automatically or in response to user application programs. • Checkpoints should be taken frequently (several times an hour). • In case of failures, it is often possible to resume processing from the most recent checkpoint. • Thus only few minutes processing work must be repeated, compared with several hours for a complete restart of the day’s processing.

  13. Recovery Manager • It 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 type of restart used depends on the nature of the failure. • Recovery manager uses the logs as well as the backup copy to restore the database

More Related