Transcript Recovery

IT420: Database Management and
Organization
Crash Recovery
12 April 2006
Adina Crăiniceanu
www.cs.usna.edu/~adina
1
Goals
 Finish database security
 Database Recovery
 Problems
 Solutions
 DBA Tasks
Kroenke, Database Processing
2
DBA Tasks







Managing database structure
Controlling concurrent processing
Managing processing rights and responsibilities
Developing database security
Providing for database recovery
Managing the DBMS
Maintaining the data repository
Kroenke, Database Processing
3
Database Security
 Database security - only authorized users can
perform authorized activities
 Developing database security
 Determine users’ rights and responsibilities
 Enforce security requirements using security features
from both DBMS and application programs
Kroenke, Database Processing
4
Application Security
 If DBMS security features are inadequate,
additional security code could be written in
application program
 Example In Project 2?
Kroenke, Database Processing
5
SQL Injection Attack
 SQL injection attack occurs when data
from the user is used to modify a SQL
statement
 Example:
 User input: 71111 OR TRUE
SELECT * FROM EMPLOYEE
WHERE EMPLOYEE.ID = 71111 OR TRUE;
 Result: every row of the EMPLOYEE table will
be returned
 Check user input !
Kroenke, Database Processing
6
Database Security Review
 Purpose: only authorized users can perform
authorized activities
 Methods:
 Run DBMS behind a firewall
 Apply the latest OS and DBMS service packs and
fixes
 Use the least functionality possible
 Protect the computer that runs the DBMS
 Manage accounts and passwords
 Write additional security code in application programs
Kroenke, Database Processing
7
DBA Tasks







Managing database structure
Controlling concurrent processing
Managing processing rights and responsibilities
Developing database security
Facilitate database recovery
Managing the DBMS
Maintaining the data repository
Kroenke, Database Processing
8
Review: ACID Properties
 Atomicity: All actions happen, or none
happen
 Consistency
 Isolation
 Durability: If a transaction commits, its
effects persist
 The Recovery Manager guarantees
Atomicity & Durability.
Kroenke, Database Processing
9
Motivation
 Atomicity:
 Transactions may abort (“Rollback”) – need restore
previous state
 Durability:
 DBMS might stop running – need preserve state
Desired behavior after system
restarts:
T1, T2 & T3 should be durable.
T4 & T5 should be aborted
(effects not seen).
Kroenke, Database Processing
10
Basic Idea: Logging
 Periodically save the database to disk
 Keep a transaction log - small
 Saved on another disk
 Contains records of the data changes in
chronological order, for all transactions
Kroenke, Database Processing
11
Transaction Log
Kroenke, Database Processing
12
Write-Ahead Logging (WAL)
 Must write to log before writing to the
database  atomicity
 Must write all log records for a transaction
before commit  durability
Kroenke, Database Processing
13
Class Exercise
 Write transaction log entries for the
following schedule:




T1: R(Ch)
R(Sa), W(Sa),
Ch = 100
Sa=1000, Sa=500
T2:
R(Ch)
Ch = 100
Abort
Kroenke, Database Processing
R(Sa), W(Sa), Commit
Sa=1000, Sa=1100
14
Rollback/Rollforward
 When there is a failure:
 Analyze: figure out which transactions
commited, which failed
 REDO (Rollforward): restore database using
saved data and re-execute all changes since
the last save
 UNDO (Rollback): undo the changes made
by failed transaction to the database
Kroenke, Database Processing
15
Rollforward
After-image: a copy of every database record (or
page) after it was changed
Kroenke, Database Processing
16
Rollback
Before-image: a copy of every database record
(or page) before it was changed.
Also used for
simple transaction
abort
Kroenke, Database Processing
17
Checkpoint
 A checkpoint is a point of synchronization between the
database and the transaction log
 DBMS refuses new requests, finishes processing outstanding
requests, and writes its buffers to disk
 The DBMS waits until the writing is successfully completed 
the log and the database are synchronized
 Checkpoints speed up database recovery process
 Database can be recovered using after-images since the last
checkpoint
 Checkpoint can be done several times per hour
 Most DBMS products automatically checkpoint
themselves
Kroenke, Database Processing
18
Database Recovery:
A Processing Problem Occurs
Kroenke, Database Processing
19
Database Recovery:
Recovery Processing
Kroenke, Database Processing
20
Class Exercise




T1: R(Ch)
R(Sa), W(Sa),
Ch = 100
Sa=1000, Sa=500
T2:
R(Ch)
Ch = 100
Abort
R(Sa), W(Sa), Commit
Sa=1000, Sa=1100
 Process crash
 Analysis
 REDO
 UNDO
Kroenke, Database Processing
21
DBA Tasks for Recovery
 Ensure database saves and logs are
generated
 Periodically backup database
 Periodically backup log
 On disk
 On tapes
Kroenke, Database Processing
22
DBA Tasks







Managing database structure
Controlling concurrent processing
Managing processing rights and responsibilities
Developing database security
Providing for database recovery
Managing the DBMS
Maintaining the data repository
Kroenke, Database Processing
23
Managing the DBMS
 Generate database application performance
reports
 Investigate user performance complaints
 Assess need for changes in database structure
or application design
 Modify database structure
 Evaluate and implement new DBMS features
 Tune the DBMS
Kroenke, Database Processing
24
Maintaining the Data Repository
 DBA is responsible for maintaining the data repository
 Data repositories are collections of metadata about
users, databases, and its applications
 The repository may be
 Virtual as it is composed of metadata from many different
sources: DBMS, code libraries, Web page generation and editing
tools, etc.
 An integrated product
 The best repositories are active and they are part of the
system development process
Kroenke, Database Processing
25
DBA Tasks - Conclusions







Managing database structure
Controlling concurrent processing
Managing processing rights and responsibilities
Developing database security
Providing for database recovery
Managing the DBMS
Maintaining the data repository
Kroenke, Database Processing
26