Transcript Database Recovery

 Mechanism for restoring a database quickly
and accurately after loss or damage
 RESPONSIBILITY OF ?????
 Recovery facilities:
•
•
•
•
Backup Facilities
Journalizing Facilities
Checkpoint Facility
Recovery Manager









A DBMS COPY utility that produces a backup copy (save) of
the entire database or a subset of the database
Backup: not only the database files but also related database
objects (repository, database indexes, source libraries, etc)
Periodic backup (e.g. nightly, weekly)
Backups stored in secure, off-site location
Backup copy-used to restore the database
Cold backup–database is shut down during backup
Hot backup–selected portion 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 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
Source: http://www.qadit.com/blog/?p=283
Source: http://kb.acronis.com/content/1536
Source: http://kb.acronis.com/content/1536
Source: http://kb.acronis.com/content/1536



Audit trail of transactions and database changes
In the event of failure: consistent database state
can be reestablished using the information in
the journals together with the most recent
complete backup
Two basic journals or logs:

Transaction log–record of essential data for each
transaction processed against the database
 Transaction code, action, time, terminal no/user ID,
input data values , tables/records accessed & modified
and the old & new field values.

Database change log–images of updated data
 Before-image–copy of a record before modification
 After-image–copy of a record after modification






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


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.





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)




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
Recovery and Restart Procedures

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

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



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
Example: transfer 100 from account for cust A to cust B





Program reads the record for customer A and subtracts 100 from
the acc balance
Program reads the record for customer B and adds 100 to the acc
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.
Recovery and Restart Procedures
Recovery and Restart Procedures
18




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
Recovery and Restart Procedures
20


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 wellaccepted properties – ACID
Atomic
 Consistent
 Isolated
 Durable

Recovery and Restart Procedures

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

Aborted transactions



Incorrect data




Preferred recovery: rollback
Alternative 1: rerun transactions not including inaccurate
data updates
Alternative 2: compensating transactions
System failure (database intact)




Preferred recovery: rollback
Alternative: Rollforward to state just prior to abort
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








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 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
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