Mechanism for restoring a database Mechanism for restoring a database quickly and accurately after loss or quickly and accurately after loss or damagedamage

RESPONSIBILITY OF ?????RESPONSIBILITY OF ????? Recovery facilities:Recovery facilities:

• Backup FacilitiesBackup Facilities• Journalizing FacilitiesJournalizing Facilities• Checkpoint FacilityCheckpoint Facility• Recovery ManagerRecovery Manager

A DBMS COPY utility that produces a A DBMS COPY utility that produces a backup copy (save) of the entire database backup copy (save) of the entire database or a subset of the databaseor a subset of the database

Periodic backup (e.g. nightly, weekly)Periodic backup (e.g. nightly, weekly) Backups stored in secure, off-site locationBackups stored in secure, off-site location Backup copy-used to restore the Backup copy-used to restore the

databasedatabase Cold backupCold backup–database is shut down –database is shut down

during backupduring backup Hot backupHot backup–selected–selected portion is shut portion is shut

down and backed up at a given timedown and backed up at a given time Incremental backups: Incremental backups: record changes record changes

made since the last full backupmade since the last full backup

Audit trail of transactions and database Audit trail of transactions and database updates/changesupdates/changes

In the event of failure: consistent database In the event of failure: consistent database state can be reestablished using the state can be reestablished using the information in the journals together with the information in the journals together with the most recent complete backupmost recent complete backup

Two basic journals or logs:Two basic journals or logs: Transaction log–record of essential data for each Transaction log–record of essential data for each

transaction processed against the databasetransaction processed against the database Transaction code, action, time, terminal no/user ID, Transaction code, action, time, terminal no/user ID,

input data values , tables/records accessed & input data values , tables/records accessed & modified and the old & new field values.modified and the old & new field values.

Database change log–images of updated dataDatabase change log–images of updated data Before-image–copy of a record before modificationBefore-image–copy of a record before modification After-image–copy of a record after modificationAfter-image–copy of a record after modification

A facility by which the DBMS periodically refuses to A facility by which the DBMS periodically refuses to accept new transactions. The system is in a accept new transactions. The system is in a quietquiet state and the database and transaction logs are state and the database and transaction logs are synchronizedsynchronized

All transactions in progress are completed and All transactions in progress are completed and journal files are brought up-to-datejournal files are brought up-to-date

DBMS writes a special record (checkpoint record) to DBMS writes a special record (checkpoint record) to the log file: snapshot of the state of the databasethe log file: snapshot of the state of the database

Checkpoint record contains information necessary Checkpoint record contains information necessary to restart the systemto restart the system

Any dirty data blocks (pages of memory that Any dirty data blocks (pages of memory that contain changes that have not yet been written out contain changes that have not yet been written out to disk) are written from memory to disk storageto disk) are written from memory to disk storage

Automatically or response to commands in user Automatically or response to commands in user application programsapplication programs

A module of the DBMS that restores the A module of the DBMS that restores the database to a correct condition when a database to a correct condition when a failure occurs and then resumes failure occurs and then resumes processing user requests.processing user requests.

Type of restart used depends on the Type of restart used depends on the nature of failure.nature of failure.

Disk Mirroring–switch between Disk Mirroring–switch between identical copies of databasesidentical copies of databases

Restore/Rerun–reprocess transactions Restore/Rerun–reprocess transactions against the backupagainst the backup

Transaction Integrity–commit or abort Transaction Integrity–commit or abort all transaction changesall transaction changes

Backward Recovery (Rollback)–apply Backward Recovery (Rollback)–apply before imagesbefore images

Forward Recovery (Roll Forward)–apply Forward Recovery (Roll Forward)–apply after images (preferable to after images (preferable to restore/rerun)restore/rerun)

Database must be mirrored Database must be mirrored switch to switch to an existing copy of the databasean existing copy of the database

2 copies of the database must be kept & 2 copies of the database must be kept & updated simultaneouslyupdated simultaneously

Media failure occurs: processing switch to Media failure occurs: processing switch to the duplicate copythe duplicate copy

Allows fastest recoveryAllows fastest recovery

Recovery and Restart Procedures

Involves reprocessing the day’s Involves reprocessing the day’s transactions (up to the point of failure) transactions (up to the point of failure) against the backup copy of the databaseagainst the backup copy of the database Database is shut downDatabase is shut down The most recent copy of the database /file to The most recent copy of the database /file to

be recovered is mountedbe recovered is mounted All transactions that have occurred since that All transactions that have occurred since that

copy (stored on the transaction log) are reruncopy (stored on the transaction log) are rerun

Recovery and Restart Procedures

Advantage:Advantage: SimplicitySimplicity

DBMS does not need to create a database change DBMS does not need to create a database change journal & no special restart procedures requiredjournal & no special restart procedures required

Disadvantages:Disadvantages: Time to reprocess transactions may be prohibitiveTime to reprocess transactions may be prohibitive

Processing of new transactions delayed until recovery Processing of new transactions delayed until recovery completedcompleted

Sequencing of transactions will often be different Sequencing of transactions will often be different from when they were originally processed: may from when they were originally processed: may lead to different results.lead to different results. Original Run: customer deposit may be posted before Original Run: customer deposit may be posted before

withdrawalwithdrawal Rerun: Withdrawal transaction may be attempted first.Rerun: Withdrawal transaction may be attempted first.

Last resort in database processingLast resort in database processingRecovery and Restart Procedures

DBMS backs out of or undo unwanted changes to the DBMS backs out of or undo unwanted changes to the DB – before images capturedDB – before images captured

Reverse the changes made by transactions that have Reverse the changes made by transactions that have aborted or terminated abnormallyaborted or terminated abnormally

Example: transfer 100 from account for cust A to cust Example: transfer 100 from account for cust A to cust BB Program reads the record for customer A and subtracts 100 Program reads the record for customer A and subtracts 100

from the acc balancefrom the acc balance Program reads the record for customer B and adds 100 to Program reads the record for customer B and adds 100 to

the acc balance.the acc balance. Program writes the updated record for A to the dbase. Program writes the updated record for A to the dbase. In attempting to write the record for B, program encounters In attempting to write the record for B, program encounters

an error condition and cannot write the record.an error condition and cannot write the record. An UNDO command – recovery manager to apply the before An UNDO command – recovery manager to apply the before

image for record A to restore acc balance to its original image for record A to restore acc balance to its original value.value.

Recovery and Restart Procedures

14Recovery and Restart Procedures

A technique that starts with an earlier A technique that starts with an earlier copy of the database. After images are copy of the database. After images are applied to the database and the database applied to the database and the database is quickly moved forward to a later state.is quickly moved forward to a later state.

Much faster than Restore/Rerun:Much faster than Restore/Rerun: The time consuming logic of reprocessing The time consuming logic of reprocessing

each transaction does not have to be each transaction does not have to be repeatedrepeated

Only the most recent after-images need to Only the most recent after-images need to be applied. DB record may have series of be applied. DB record may have series of after image – most recent (good) after after image – most recent (good) after image is required for rollbackimage is required for rollback

Recovery and Restart Procedures

16Recovery and Restart Procedures

Integrity of transactions: DB is updated Integrity of transactions: DB is updated by processing transactions that results in by processing transactions that results in changes to one or more DB recordschanges to one or more DB records

When processing transactions, DBMS When processing transactions, DBMS must ensure that the transactions follow must ensure that the transactions follow four well-accepted properties – four well-accepted properties – ACIDACID AtomicAtomic ConsistentConsistent IsolatedIsolated DurableDurable

Recovery and Restart Procedures

To maintain transaction integrity – DBMS To maintain transaction integrity – DBMS must provide facilities for the user or must provide facilities for the user or application program to define transaction application program to define transaction boundaries – logical beginning and end of boundaries – logical beginning and end of transaction.transaction.

BEGIN TRANSACTION..

UPDATEINSERT

.

.COMMIT

Recovery and Restart Procedures

Aborted transactionsAborted transactions Preferred recovery: rollbackPreferred recovery: rollback Alternative: Rollforward to state just prior to abortAlternative: Rollforward to state just prior to abort

Incorrect dataIncorrect data Preferred recovery: rollbackPreferred recovery: rollback Alternative 1: rerun transactions not including Alternative 1: rerun transactions not including

inaccurate data updatesinaccurate data updates Alternative 2: compensating transactionsAlternative 2: compensating transactions

System failure (database intact)System failure (database intact) Preferred recovery: switch to duplicate databasePreferred recovery: switch to duplicate database Alternative 1: rollbackAlternative 1: rollback Alternative 2: restart from checkpointAlternative 2: restart from checkpoint

Database destructionDatabase destruction Preferred recovery: switch to duplicate databasePreferred recovery: switch to duplicate database Alternative 1: rollforwardAlternative 1: rollforward Alternative 2: reprocess transactionsAlternative 2: reprocess transactions

Contingency plans to cater for disasters – Contingency plans to cater for disasters – destroy/damage data centerdestroy/damage data center

Natural disastersNatural disasters Planning for DRPlanning for DR Develop a detailed DR planDevelop a detailed DR plan Schedule regular test of planSchedule regular test of plan Choose multi-disciplinary team to carry out Choose multi-disciplinary team to carry out

planplan Fast backup data center – off site locationFast backup data center – off site location Send back up copies to backup data centerSend back up copies to backup data center

Contingency plan is established to deal with Contingency plan is established to deal with unusual events that are not part of the normal unusual events that are not part of the normal daily routinedaily routine

Contingency plans detail the response necessary Contingency plans detail the response necessary to deal with the types of event that may occurto deal with the types of event that may occur

A contingency plan should include :A contingency plan should include : who the key personnel are and how they can be contactedwho the key personnel are and how they can be contacted if the key personnel are unavailable, a list of alternative if the key personnel are unavailable, a list of alternative

personnel and how they can be contactedpersonnel and how they can be contacted who decides that a contingency exists and how that is who decides that a contingency exists and how that is

decideddecided the technical requirements of transferring operations the technical requirements of transferring operations

elsewhereelsewhere the operational requirements of transferring operations the operational requirements of transferring operations

elsewhere elsewhere any outside contacts who may helpany outside contacts who may help whether any insurance exists to cover the situation whether any insurance exists to cover the situation