370 likes | 713 Views
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
E N D
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
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
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