Transcript Recovery & Backup - Al Akhawayn University

Unfortunately
Nothing works perfectly all the time.
 What will happen if:

Your hard-drive crashes.
 Your last transaction made a failure.
 Power shutdowns while you’re saving
changes on your database.

Wiliwili…..
1
Recovery
By:
Sebbane Mehdi
Supervised by:Dr. H.Haddouti
Monday, April 14th 2003
2
Agenda
Intro…
 Transactions Failures…
 System Failures…
 Media Failures…
 Two-Phase Commit…
 ARIES Recovery Algorithm…
 Conclusion…
 References…

3
Intro…


Data warehouse are mission-critical.
Downtime can lead to IMPORTANT loss in




Revenue
Productivity
Profitability &
Customers.
4
Intro…


(more)
The most frequent causes for data warehouse
downtime are storage related:

Component failure.

Lengthy load times.

Lengthy backups.
Others:

User Errors.

System failure.
5
Failures classification


Synchronous:

Trappable by the operating system.

No loss of data of any kind.

Possible causes: Program/logic errors: e.g. division by zero
Asynchronous:


System crash:
•
Assume loss of all data on volatile storage.
•
Possible causes: power failure, OS error.
Media crash:
•
Loss of data on online and volatile storage.
•
Possible causes: damage of storage media: human errors.
6
What is Recovery???
Recovery = Redundancy….
 Simple example:

Periodically, copy or dump the database to an
archive storage.
 For every change, a log entry is made.
 If failure:

 A)
Database damaged…
 B) Content unreliable…
7
Why not Duplexing??

Have two identical databases.
Applying changes simultaneously.

However,



Twice as much storage.
The 2 copies should be independent. To reduce the
chance that a single failure affects both copies.
Very hard to achieve.
8
Transaction Failures…
Intro…
 Transactions Failures…
 System Failures…
 Media Failures…
 Two-Phase Commit…
 ARIES Recovery Algorithm…
 Conclusion…
 References…

9
Transaction Failures…


Intro…
Transactions Failures…










Transactions.
Message handling.
Transaction structure.
Transaction failures.
System Failures…
Media Failures…
Two-Phase Commit…
ARIES Recovery Algorithm…
Conclusion…
References…
10
Transactions…


The fundamental purpose of dbase system is to
carry out transactions.
Transaction is:
the smallest unit of work…
 Atomic…
BEGIN TRANSACTION

recoverable operations
recoverable operations
…
…
COMMIT or ROLLBACK
11
Transactions…

A recoverable operations are:



All database updates (for which an entry has
been loged)
message I/O.
Example:
TRANSFERT $1000 3452332 TO 9087665
(more)
TRANSFERT: PROC;
GET (FROM, TO, AMOUNT);
FIND UNIQUE (ACCOUNT WHERE ACCOUNT = FROM);
ASSIGN (BALANCE – AMOUNT) TO BALANCE;
IF BALANCE < 0
THEN
DO;
ROLLBACK;
PUT (‘INSUFFICIENT FUNDS’);
ELSE
DO;
FIND UNIQUE (ACCOUNT WHERE ACCOUNT = TO);
ASSIGN (BALANCE + AMOUNT) TO BALANCE;
COMMIT;
PUT (‘TRANSFER COMPLETE’);
END;
END /* TRANFERT */;
12
Transactions…
(more)
To the user: “transfer x dollars from account A to account B”
is a single operation.
 Either succeed of fail.

Succeed: well & good
 Fail: nothing should have changed in the
database.

What about messages??????
13
Message handling

TRANSFERT: PROC;
GET (FROM, TO, AMOUNT);
FIND UNIQUE (ACCOUNT WHERE ACCOUNT = FROM);
ASSIGN (BALANCE – AMOUNT) TO BALANCE;
IF BALANCE < 0
THEN
DO;
ROLLBACK;
PUT (‘INSUFFICIENT FUNDS’);
ELSE
DO;
FIND UNIQUE (ACCOUNT WHERE ACCOUNT = TO);
ASSIGN (BALANCE + AMOUNT) TO BALANCE;
PUT (‘TRANSFER COMPLETE’);
In the TRANSFER example:


the transaction not only updates the database,
it also sends messages to the end users:


INSUFFICIENT FUNDS.
TRANSFER COMPLETE.
Handling messages is done by the Data
Communication COMMIT;
Manager.
END;
 Note: output messages
should not be transmitted
END /* TRANFERT */;
until the planned end-of-transaction…
WHY?????

FAILURE
14
DC Manager




DC Manager that receives the original input
message: (giving FROM, TO and AMOUNT)
a.
writes a log record, and
b.
places the message on the input queue
GET: retrieve a message from the input queue.
PUT: put a message in the output queue.
COMMIT & ROLLBACK affects also
messages.
15
COMMIT & ROLLBACK

They cause the DC Manager to:
 Write
a log entry for the messages on the
output queue.
 Arrange for the actual transmission of those
messages.
 Remove messages from the input queue.
Note: A transaction failure such as overflow causes the
DC Manager to cancel the output messages
16
Transaction structure

As we can see from the TRANSFER examlpe:
 Accept input message;
 Perform database processing;
 Send output message(s);


Too simple.
What about complex structures with multiple
communications???
17
Complex structures

Two ways to deal with multiple
communications:
• Subdivide them into a sequence of simple
transactions.
• the database may be changed in the interval
between two “conversations”
• treated as one big transaction.
• at any time the end-user must be prepared to get a
message like “ignore all previous messages, a
failure occurred”.
18
Transaction failures.




Transaction local failures that are detected by
the application code itself (INSUFFISANT
FUNDS).
Transaction local failures that are not explicitly
handled by the application code (arithmetic
overflow).
System-wide failures (CPU failure) that affect all
transactions currently in progress but do not
damage the database.
Media failures (disk head failure) that damage
the database, or some portion of it, and affect all
transactions currently using that portion.
19
Why???

Conditions that may cause such
terminations include:
 arithmetic
operation overflow,
 division by zero, and
 storage protection violation.

Transaction failure means that the
program did not reach its planned
termination.  ROLLBACK
20
How???

undo all changes the transaction made on
the database and cancel all output
messages.

three basic types of changes:




updating an existing record,
deleting an existing record, or
inserting a new record.
For more convenience, the log file should be
kept on a direct access device…..
However….
21
What about if…

A failuer happens during rolling back…

UNDO must be idempotent :
UNDO(UNDO(UNDO(. . . (x))))= UNDO(x) for all x.

As a transaction is a unit of work… It is also a
unit of receovery.
22
System Failures…
Intro…
 Transactions Failures…
 System Failures…
 Media Failures…
 Two-Phase Commit…
 ARIES Recovery Algorithm…
 Conclusion…
 References…

23
System Failures…



Intro…
Transactions Failures…
System Failures…










Checkpoints.
Types of transactions.
REDO.
Write-Ahead-Log.
System startup.
Media Failures…
Two-Phase Commit…
ARIES Recovery Algorithm…
Conclusion…
References…
24
System Failures…
we mean by this any event that cause the
system to stop and thus require a system
restart.
 How the recovery manager knows at
restart which transactions to rollback??

 checkpoints
25
Checkpoints


1.
2.
3.
4.
reduce the search time drastically.
very straightforward: Periodically, the system
“takes a checkpoint”:
Force-writing any log records that are still in main
storage out to the actual log;
Forcing a “checkpoint record” out to the log data set;
Force-writing any updates that are still in main storage
out to the actual database;
Writing the address of the checkpoint record within the
log data set into a “restart file”.
26
Checkpoints

Each checkpoint record contains:
 A list
of all transaction active at the time of the
checkpoint; together with
 The address within the log of each such
transaction’s most recent log record.
At restart time, the manager needs then to check
which transactions need to be undone, and
which should be redone.
27
Types of transactions

Consider the
following:


A system failure
has occurred at
time t2.
The most recent
checkpoint prior
to time t2 was
taken at time t1.
T
1
T
2
T
3
T4
T
5
t
Checkpoint
1
t
System
crash
2
28
Types…



Transactions of type
T1 were complete
before time t1.
Transactions of type
T2 started after time
t1 and completed
before time t2.
Transactions of type
T3 started prior to
time t1 and
completed after t1
and before time t2.
T
1
T
2
T
3
T4
T
5
t
Checkpoint
1
t
System
crash
2
29
Types…


Transactions of type
T4 started prior to
time t1 but did not
complete by time t2.
Finally, transactions
of type T5 started
after time t1 but did
not complete by time
t2.
T
1
T
2
T
3
T4
T
5
t
Checkpoint
1
t
System
crash
2
30
Types….
What will
happen at
restart????
 Undo T4 & T5

T
1
T
2
T
3
T4
T
5
t

But also redo
T2 & T3
Checkpoint
1
t
System
crash
2
31
REDO

Recovery manager able to track the log
and invoke REDO for appropriate
transactions.
 Idempotent.

handling messages:

reschedule transactions of type T2 and T3.

force-write input messages log records.
32
Write-Ahead Log Protocol

Up to now,
Changing the database.
 Writing the log record.

Two separate operations….
 What will happen after a failure occurring
in the interval between the two.


Write-Ahead-Log…
33
Write-Ahead Log Protocol

for safety, the log record should always be
written first.

A transaction is not allowed to write a record to
the physical database until at least the undo
portion of the corresponding log record has been
written to the physical log.
A transaction is not allowed to complete
COMMIT processing until both the redo and the
undo portions of all log records for the
transaction have been written to the physical log.

34
System startup
How does a
system react to
failures…
 3 types:


Emergency restart.

the process that is
invoked after a
system failure has
occurred. It involves
the recovery
procedures (UNDO or
REDO).
35
System startup
How does a
system react to
failures…
 3 types:

Emergency restart.
 warm start.


the process of starting
up the system after a
controlled system
shutdown.

On a receipt of a
SHUTDOWN
command…
36
System startup
How does a
system react to
failures…
 3 types:

Emergency restart.
 warm start.
 Cold start.




Starting the system
from scratch
the process of starting
the system after some
disastrous failure that
makes warm start
impossible
involves starting
again from some
archive version of the
database.
37
Media Failures…
Intro…
 Transactions Failures…
 System Failures…
 Media Failures…
 Two-Phase Commit…
 ARIES Recovery Algorithm…
 Conclusion…
 References…

38
Media failures…
A media failure is a failure in which a
portion of the secondary storage medium
is damaged.
 The recovery process consists of:

restoring the database from an archive dump,
then
 use the log to redo transactions run since that
dump was taken.

39
Media failures…

A Media failure occurred….



All current transactions will be abnormally
terminated.
New device should be allocated to replace
the one that failed.
A utility program is then run which:
a.
b.
load the database on to the new device from the
most recent archive dump, and
use the log to redo all the transactions that
completed since the dump was taken.
40
Two-Phase Commit…
Intro…
 Transactions Failures…
 System Failures…
 Media Failure…
 Two-Phase Commit…
 ARIES Recovery Algorithm…
 Conclusion…
 References…

41
Two-Phase commit
Required whenever a transaction is able to
invoke multiple independent resource
managers.
 No separate COMMITs.
 Transaction issues a single “global”
COMMIT to the coordinator.


Coordinator goes in the two phases:
42
Two-Phase commit

Phase I:
request all resource managers to get them selves
into a valid state (commit or rollback)
 If the resource manager succeeds reaching this
state, it replies “OK” to the coordinator.


Phase II:
If all replies are “OK”, broadcasts the command
“COMMIT” to all.
 Otherwise, broadcasts the command “ROLLBACK”

43
ARIES Recovery Algorithm…
Intro…
 Transactions Failures…
 System Failures…
 Media Failure…
 Two-Phase Commit…
 ARIES Recovery Algorithm…
 Conclusion…
 References…

44
reminder

Up to now,
Transaction failures.
 System failures.
 Media failures.
 WAL,2PC, and checkpoint


All this is good, but how to combine it to
have a better recovery……
45
ARIES Recovery Algorithm…






Intro…
Transactions Failures…
System Failures…
Media Failure…
Two-Phase Commit…
ARIES Recovery Algorithm…








The concept.
Data structure used in ARIES.
ARIES in details.
ARIES features.
Why ARIES.
ARIES optimizations.
Conclusion…
References…
46
ARIES Recovery Algorithm


The dominant crash recovery algorithm in
commercial DBMSs.
Based on three concepts:


Write-Ahead logging;
Repeating history during REDO.


Retrace all actions of the DBMS prior the crash to reconstruct
the database state when crash occurred.
Logging changes during UNDO.

Prevents ARIES from repeating complete undo operations
when a failure occurred during recovery.
47
ARIES Recovery Algorithm


Step 1: analysis

Identify updated pages in the buffer.

Identify active transactions when the crash occurred.

Identify the point in the log where redo should start.
Step 2: REDO

Redo operations are applied until he end of the log.

Include writes from uncommitted transactions.

Only necessary redo operations are applied.
48
ARIES Recovery Algorithm

Step 3:UNDO
Log is scanned backward.
 Updates from active transactions are undone.

49
Data Structures Used in ARIES
50
ARIES Data Structures

Log sequence number (LSN) identifies each
log record
Must be sequentially increasing
 Typically an offset from beginning of log file to
allow fast access

 Easily
extended to handle multiple log files
51
ARIES Data Structures

Each page contains a PageLSN which is
the LSN of the last log record whose
effects are reflected on the page

To update a page:
 Lock
the page, and write the log record
 Update the page
 Record the LSN of the log record in PageLSN
 Unlock page

PageLSN is used during recovery to prevent
repeated redo.
 Thus
ensuring idempotence
52
ARIES Data Structures

Each log record contains LSN of previous
log record of the same transaction
LSN TransId PrevLSN

RedoInfo
UndoInfo
Special redo-only log record called
compensation log record (CLR) used to
log actions taken during recovery that
never need to be undone

Have a field UndoNextLSN to note next
(earlier) record to be undone
 Required
to avoid repeated undo of already
undone actions
LSN TransID UndoNextLSN RedoInfo
53
ARIES Data Structures

DirtyPageTable
List of pages in the buffer that have been
updated
 Contains, for each such page

 PageLSN
of the page
 RecLSN is an LSN such that log records before this
LSN have already been applied to the page version
on disk


Set to current end of log when a page is inserted into dirty
page table (just before being updated)
Recorded in checkpoints, helps to minimize redo work
54
ARIES Data Structures

Checkpoint log record

Contains:
 DirtyPageTable
and list of active transactions
 For each active transaction, LastLSN, the LSN of
the last log record written by the transaction

Fixed position on disk notes LSN of last
completed checkpoint log record
55
ARIES In Details…
56
ARIES Recovery: Analysis
Starts from last complete checkpoint log
record
Reads in DirtyPageTable from log record
 Sets RedoLSN = min of RecLSNs of all pages in
DirtyPageTable

 In
case no pages are dirty, RedoLSN = checkpoint
record’s LSN
Sets undo-list = list of transactions in checkpoint
log record
 Reads LSN of last log record for each
transaction in undo-list from checkpoint log
record

57
ARIES Recovery: Analysis
Scans forward from checkpoint
If any log record found for transaction not in
undo-list, adds transaction to undo-list
 Whenever an update log record is found

 If
page is not in DirtyPageTable, it is added with
RecLSN set to LSN of the update log record
If transaction end log record found, delete
transaction from undo-list
 Keeps track of last log record for each
transaction in undo-list

 May
be needed for later undo
58
ARIES Recovery: Analysis

At end of analysis pass:
RedoLSN determines where to start redo
pass
 RecLSN for each page in DirtyPageTable
used to minimize redo work
 All transactions in undo-list need to be rolled
back

59
Our log
60
Our tables
CRASH
61
After the crash
Transaction table
TransID LastLSN Status
T1
3
commit
T2
2
in progress
After analysis
Transaction table
TransID LastLSN Status
T1
3
commit
T2
8
commit
T3
6
in progress
dirty page table
PageID LSN
C
1
B
2
dirty page table
PageID LSN
C
1
B
2
A
6
62
ARIES Redo Pass
Redo Pass: Repeats history by replaying every
action not already reflected in the page on disk, as
follows:

Scans forward from RedoLSN. Whenever an update
log record is found:
1.
2.
If the page is not in DirtyPageTable or the LSN of the
log record is less than the RecLSN of the page in
DirtyPageTable, then skip the log record
Otherwise fetch the page from disk. If the PageLSN of
the page fetched from disk is less than the LSN of the
log record, redo the log record
NOTE: if either test is negative the effects of the log
record have already appeared on the page. First test
avoids even fetching the page from disk!
63
ARIES: Undo Pass
Performs backward scan on log undoing all
transaction in undo-list
 Backward scan optimized by skipping unneeded
log records as follows:


Next LSN to be undone for each transaction set to
LSN of last log record for transaction found by
analysis pass.
At each step pick largest of these LSNs to undo,
skip back to it and undo it
64
ARIES: Undo Pass
 After
undoing a log record
 For
ordinary log records, set next LSN to
be undone for transaction to PrevLSN
noted in the log record
 For compensation log records (CLRs) set
next LSN to be undo to UndoNextLSN
noted in the log record
 All intervening records are skipped since
they would have been undo already
65
ARIES Features

Recovery Independence

Pages can be recovered independently of others
 E.g.
if some disk pages fail they can be recovered from a
backup while other pages are being used

Savepoints:

Transactions can record savepoints and roll back to
a savepoint
 Used
to rollback just enough to release locks on deadlock
66
ARIES Features
Recovery optimizations: For example:
Dirty page table can be used to prefetch pages
during redo
 Out of order redo is possible:

redo can be postponed on a page being fetched
from disk, and performed when page is fetched.
 Meanwhile other log records can continue to be
processed

67
MORE for ARIES



Simple.
Incorporates numerous optimizations to reduce
overheads during normal processing and to speed up
recovery.
ARIES has
1.
log sequence number (LSN) to identify log records

2.
3.
4.
Stores LSNs in pages to identify what updates have already been
applied to a database page.
Physiological redo
Dirty page table to avoid unnecessary redos during recovery
Fuzzy checkpointing that only records information about dirty
pages, and does not require dirty pages to be written out at
checkpoint time
68
ARIES Optimizations

Physiological redo


Affected page is physically identified, action
within page can be logical.
Used to reduce logging overheads

e.g. when a record is deleted and all other records have to be
moved to fill hole
 Physiological redo can log just the record deletion

Why caring about the process of filling the newly
created hole.
69
ARIES Optimizations

Fuzzy checkpointing is done as follows:
1. Temporarily
stop all updates by transactions
2. Write a <checkpoint L> log record and force
log to stable storage
3. Note list M of modified pages
4. Now permit transactions to proceed with their
actions
5. Save on disk all modified pages in list M
6. Store a pointer to the checkpoint record in a
fixed position last_checkpoint on disk
70
ARIES Optimizations

When recovering using a fuzzy checkpoint,
start scan from the checkpoint record
pointed to by last_checkpoint
Log records before last_checkpoint have
their updates reflected in database on disk,
and need not be redone.
 Incomplete checkpoints, where system had
crashed while performing
checkpoint, are handled safely

71
ARIES family
Aries for shared disk
 Aries for semi-structured data.
 ARIES/CSA.
 ARIES/RRH.
 ARIES/NT.
 ARIES/KVL.
 ARIES/IM.
 ARIES/LHS.

72
Conclusion
Implementing the right recovery procedure
is as important as benchmarking your
warehouse product performance.
 Big giants SQL, DB2 … have their own
recovery technique specific to them.
 They have their own recovery products.


Just for DB2
73
Figure 1
74
Conclusion

If you want account for
catastrophic failures:



Backup entire
database.
Backup system log
more frequently.
That may be costly…..
75
Conclusion

Recovery procedure should be relative to
the:
Amount of data to protect.
 The importance of this data.

76
Thank you
Q&A
77
References…

Date Book: Chapter 1 recovery…
Database Management Systems

http://discovery.csc.ncsu.edu/~pning/Courses/csc742-Spring-02/T15_Recovery_6.pdf


ARIES Recovery Algorithm:
Recovery with aries…
http://www-2.cs.cmu.edu/afs/cs/academic/class/15721-f01/www/lectures/recovery_with_aries.pdf
 Figure 1
http://www.bmc.com/products/database/resourcecenter/db2backup_recovery.pdf

Figure 2
http://www2.cs.cmu.edu/afs/cs/academic/class/15721f01/www/lectures/rec
overy_with_aries.pdf
78