RECOVERY SYSTEM

1
RECOVERY SYSTEM

• PRESENTED BY
• SQL

2
Database Recovery
Process of restoring the Database to a correct
state In the event of a failure
Why need recovery?
To ensure the database is reliable in a
consistent state in the presence of failures
3
Transaction failures
System failures

• Logical Error
• - e.g. Bad input, overflow
• System Error
• - e.g. Deadlock

• Hardware malfunction
• Transaction halt
• Bug in database software or in operating system

Types of failures
Disk failures

• Disk blocks loses its contents
• Failure during data transfer
• Head crash

4
Recovery Techniques

• Log-based recovery
• i. Deferred database modification
• ii.Immediate database modification
• CHECKPOINTS
• Concurrent Transaction
• Buffer Management
• - Database Buffering

5
LOG-BASED RECOVERY

• Uses a log file which contains a sequence of
  several types of records
• Some log-record types
• - update-log-record single Database write.
• - Contains(T-id, data item, old value, new
  value).
• - start-of-transaction (T-id, start).
• - commit (T-id, commit).
• - abort (T-id, abort).
• -checkpoint

6
Deffered-database modification

• ensures transaction atomicity by recording all
  modifications to the log then to the Database
  later.
• Only new values in the log.
• Redo the committed transactions.
• Delete the uncommitted transactions from the log.

7
Immediate database modification

• modify the database before the transaction commit
  (uncommitted modification).
• both new and old values are in the log.
• undo and redo lists are created, after failure,
  by the recovery system.

8
CHECKPOINTS

• Mainly for minimizing time consumed in processing
  the log file after crash has happened.

9
Advanced Recovery Techniques

• Logical undo logging
• Checkpoints
• Fuzzy checkpointing
• ARIES (Algorithm for Recovery and Isolation
  Exploiting Semantics)

10
Logical Undo Logging

• Undone by executing a deletion operation known as
  logical undo.
• Its in contrast with Physical Undo Logging.

11
Checkpointing

• Done as follows
• 1. Output all log records in memory to stable
  storage.
• 2. Output to disk all modified buffer blocks.
• 3. Output to log on stable storage.

12
Fuzzy Checkpointing

• Done as follows
• 1. Temporarily stop all updates by transaction.
• 2. Write a checkpointing log record and force
  log to stable storage.
• 3. Note list of modified buffer blocks.
• 4. Permit transactions to proceed with their
  actions.
• 5. Output to disk all modified buffer blocks in
  listing (Step 3).
• 6. Store a pointer to the checkpoint record in a
  fixed position last_checkpoint on disk.

13
ARIES

• The Aries recovery algorithm

• WAL (Write Ahead Logging)
• Repeating history during redo ARIES will
  retrace all actions of the database system prior
  to the crash to reconstruct the database state
  when the crash occurred.
• Logging changes during undo It will prevent
  ARIES from repeating the completed undo
  operations if a failure occurs during recovery,
  which causes a restart of the recovery process.
• ARIES uses fuzzy checkpointing

14

• The Aries recovery algorithm consist of 3 steps

• Analysis identifies the dirty (updated) pages in
  the buffer and the set of transactions active at
  the time of crash. The appropriate point in the
  log where redo is to start is also determined.
• Redo necessary redo operations are applied.
• Undo log is scanned backwards and the operations
  of transactions active at the time of crash are
  undone in reverse order.

15

• A log record stores
• Previous Logged Sequence Number (LSN) of that
  transaction It links the log record of each
  transaction. It is like a back pointer points to
  the previous record of the same transaction.
• Transaction ID
• Type of log record.

The Transaction table and the Dirty Page table
For efficient recovery following tables are also
stored in the log during checkpointing
Transaction table Contains an entry for each
active transaction, with information such as
transaction ID, transaction status and the LSN of
the most recent log record for the
transaction. Dirty Page table Contains an
entry for each dirty page in the buffer, which
includes the page ID and the LSN corresponding to
the earliest update to that page.
16
The ARIES Recovery Algorithm (a simple example)
The following steps are performed for recovery

• Analysis phase Start at the begin_checkpoint
  record and proceed to the end_checkpoint record.
  Access transaction table and dirty page table are
  appended to the end of the log. Note that during
  this phase some other log records may be written
  to the log and transaction table may be modified.
  The analysis phase compiles the set of redo and
  undo to be performed and ends.
• Redo phase Starts from the point in the log up
  to where all dirty pages have been flushed, and
  move forward to the end of the log. Any change
  that appears in the dirty page table is redone.
• Undo phase Starts from the end of the log and
  proceeds backward while performing appropriate
  undo. For each undo it writes a compensating
  record in the log.
• The recovery completes at the end of undo phase.

17
An example of the working of ARIES scheme
18
RECOVERY SYSTEM IMPLEMENTATION

• Operating System Microsoft Windows XP Pro
• Database Oracle 9i (Stand Alone)
• Objective To provide an example of how recovery
  system is being done in Oracle9i. This recovery
  process is done to recover the cold back-up
  meaning that the backup is stored in a hard disk,
  media or tape library.

19

• PRELIMINARIES

Add item below to the init.ora file and save.
The init.ora file should be located in your
oracle home folder in folder pfile.
Add this command in the init.ora file and save
the file
20
To confirm that the database is able to archive,
issue command sqlgt show parameter archive
The destination of the archive log file
The system should show that log_archive_start
TRUE
21
Create a user and grant dba to the user A, then
create a Table TRY.
22
BACKUP

• 3 type of files to backup
• Control Files
• Data Files
• Log Files

To check where are this files located, connect as
sysdba write the command below sqlgt select
file_name from dba_data_files // for data
files sqlgt select name from vcontrol file //
for control files sqlgt select member from v
logfile // for log files Before the backup
process being made, the sysdba should run this
command to trigger the logfile to be created in
the archive log folder. sqlgt alter system switch
logfile // to create log file
23
Make a copy of the original file and named as
backup.
The original folder that keeps the data files,
control file log files.
24
Connect as the user A and insert into table TRY
a row of data. Then connect as sysdba, shutdown
the database.
25
Then, delete the users01.dbf file from the
original data file C\oracle\oradata\jasrul\users
01.dbf which contains the table created by the
user A
26
Then, startup database. When the database is
being start, it will display an error message
stating that some file is missing.
27
To start the recovery process, copy the file
user01.dbf from the backup folder
C\ORACLE\BACKUP\JASRUL \USERS01.dbf to the
original folder C\ORACLE\ORADATA\JASRUL\USERS01.
dbf
Copy this file to the original folder.
28
After copying, issue the command sqlgt alter
database openThe system will display that the
database need to be recover.
29
To recover, issue the command sqlgt recover
database until cancel This command will extract
the logfile (created before the backup
processbeing done) that the user would need to
select for recovery process. After selecting,
type cancel and then, issue command Sqlgt recover
database to complete the recovery process.
30
Connect as sysdba and startup the database. Then,
connect as user A. And check the table try that
being created by user A.
END OF IMPLEMENTATION
31
THE END

• THANK YOU