Database BACKUP & recovery
Outline • Facilities for backup and recovery • Recovery and Restart procedures • Types of database failure • Disaster Recovery
Why Backup Data? • Backing up data is vital for businesses • Lost information can cause major crisis or lead to business failure • Common Problems • System outage / not available • Power and hardware failure • Transaction failure • Users may accidentally corrupt the database (incorrect or invalid data) • Media Failure • Disk drive becomes unusable (computer virus, damaged) • Disaster • Database facility damaged by fire, flood or other disaster
Database Recovery • Mechanism for restoring a database quickly and accurately after loss or damage • RESPONSIBILITY OF DBA • DBMS should provide FOUR basic facilities for backup & recovery of a database: • Backup Facilities – periodic backup • Journalizing Facilities – audit trail of transactions • Checkpoint Facility – suspends and synchronizes • Recovery Manager – restore and restart processing
Back-up Facilities • A DBMS provides COPY utility that produces a backup copy (save) of the entire database or a subset of the database • Backup: user data, catalogs and all control files (buffer pool files, table space file, database configuration file) • Periodic backup (e.g. nightly, weekly) • Backups stored in secure, off-site location • Backup copy-used to restore the database
Back-up Facilities • Cold backup / offline backup (DB2)–database is shut down during backup • Hot backup / online backup (DB2) – 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.
Database backup syntax (DB2) BACKUP DATABASE <dbname> [ONLINE] [TO <dest_path>] • Offline backup example backup database sampleto C:\backups • Online backup example backup database sampleonline to C:\backups
Database backups – File naming convention
Table space backup • Enable user to backup a subset of database • Multiple table spaces can be specified • Database must be using archival logging • Table space backup can run in both online and offline backup • Table space can be restored from either database backup or table space backup • Use keyword TABLESPACE to specify table spaces
Database Backup • In DB2, backup can now be automatically done and/or compressed • To configure automatic backup: • Graphical user interface tool • Command line interface • Stored procedure
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
Journalizing Facilities • Keep track of changes made to database objects and their data using audit trail / log • In the event of failure: consistent database state can be reestablished using the information in the journals together with the most recent complete backup • Can be stored in files or raw devices • Logging is always ON for regular tables in DB2 • It is possible to mark some tables or columns as NOT LOGGED • It is possible to declare and use USER temporary tables which may not be logged
Database logging
Types of logs • Types of logs – based on log file allocation: • Primary logs are PRE-ALLOCATED • Secondary logs are ALLOCATED as needed (costly) • For day to day operations, ensure that you stay within your primary log allocation • Types of logs – based on information stored in logs: • Active logs: • Information has not been externalized (Not on the table space disk) • Archive logs • All information externalized
Types of logging • Circular logging • For non-production systems • Logs that become archived, can be overwritten • What if information externalized to the table space was wrong? (Human error). No logs to redo things! • Archival logging • For Production systems • No logs are deleted. • Some are stored online (with active logs), others offline in an external media
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
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.
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)
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 • Hot-swappable: damaged disk can be rebuilt from mirrored disk with no disruption in service to user Recovery and Restart Procedures
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
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
DB2 Restore Utility • Restore utility is the complement of backup utility • Restores database or table space from a previously taken backup • TAKEN AT – specify the time stamp of the database backup image. Backup image timestamp is displayed after successful completion of a backup • Without prompting – overrides any warnings
Example DB2 Restore SAMPLE.0.DB2INST.NODE0000.CATN0000.20110718131210.001 RESTORE DATABASE sample FROM <db_path> TAKEN AT 20110718131210
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
Transaction Integrity • Atomic • Transaction cannot be subdivided • Once transaction is processed – changes are committed • Transaction fails - aborted • Consistent • Constraints that are true before the transaction must be true after • Isolated • Changes to DB not revealed to users until transaction is committed • Durable • Changes are permanent – once committed no failure can reverse the effect of the transaction
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
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 Recovery and Restart Procedures
Backward Recovery (Rollback) • Example: transfer 100 from CUSOMER A account to CUSTOMER B account • Program reads the record for customer A and subtracts 100 from the account balance • Program reads the record for customer B and adds 100 to the account 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.
Basic recovery techniques Rollback Recovery and Restart Procedures
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
Basic recovery techniques Rollforward Recovery and Restart Procedures
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 – human intervention
Database Failure Responses • 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
Disaster Recovery • 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
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
Contingency Plan • 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
THE END
