Data Recovery

1
Data Recovery Cold Backup Presented by Karen
2
Data Recovery

• Todays companies must backup their data or risk
  loosing it.
• Threats to a companies data come in many forms,
  mainly
• Human Error
• Software Corruption
• Computer Viruses
• AND
• Natural Disasters
• Hardware Malfunction

3
Data Recovery

• Backup and recovery is one of the most important
  aspects of a DBAs job. If you lose your company's
  data, you could very well lose your job. Hardware
  and software can always be replaced, but your
  data may be irreplaceable!

4
Data Recovery

• Typically, a business has an annual contract with
  a company that offers hot and cold site services
  with a monthly service charge.
• If an enterprise must use a hot or cold site,
  there are usually daily fees and other incidental
  fees in addition to the basic service charge.

5
Cold Backup

• A cold backup is when the database is not
  running.
• It is also known as an offline backup.
• The database needs to be shut down whilst
  performing this backup to ensure consistency.
• Advantages
• The benefit of taking a cold backup is that it is
  typically easier to administer the backup and
  recovery process.
• There is also a slight performance gain when
  doing a cold backup as the database does not have
  to save any files and archive them to disk.

6
Cold Backup

• A cold site is less expensive, but it takes
  longer to get an enterprise in full operation
  after the disaster.
• Most backups are done whilst the computer is
  still running, therefore hot backups are most
  commonly used.

7
Hot Back Up Presented By Norman
8
Hot Backup

• What is Hot Backup?
• Hot Backup is a term used to describe the backup
  of a database application whilst the application
  is active.
• Hot Backup is also known as online Backup

9
Advantages Disadvantages

• The obvious advantage of HOT BACKUP is that the
  system is not SHUTDOWN during operations and is
  therefore accessible.
• HOT BACKUP also allows for the database to be
  recovered to a specific moment in time
• It is sensible not to perform the HOT BACKUP at
  the busiest time of the day
• Such an obvious conclusion is nevertheless a
  restriction
• Limitations  The database must be operating in
  ARCHIVELOG mode for hot backups to work.

10
How does Hot Backup work?

• A hot Backup is a complex operation, because each
  table space involves a complete backup operation.
  It therefore makes sense to break the hot backup
  into its component parts.
• Place the tablespace in backup mode
• Copy all the database files associated with the
  tablespace
• Take the tablespace out of backup mode
• Repeat steps 1 through 3 until all the
  tablespaces have been backed up
• Copy the control file
• Copy the online redo logs.

11
Terminology

• Archivelogs Archived log files are redo logs
  that have been filled with redo, made inactive
  and copied or archived to a backup location.
• Tablespace Tablespace is a logical group of data
  files in a database. A tablespace plays a role
  similar to that of a folder on the hard drive of
  a computer.
• Datafiles Every Oracle database has one or more
  physical datafiles. A database's datafiles
  contain all the database data. The data of
  logical database structures such as tables and
  indexes is physically stored in the datafiles
  allocated for a database.

12
Terminology

• Redo log files. Redo log files for a database is
  collectively known as the databases redo log.
  Oracle uses the redo log to record all changes
  made to data.
• Control Files Control file is used to identify
  the datafiles and redo log files that must be
  opened for database operation to proceed.

13
Back Up Frequency Presented By Kevin
14
Back-up frequency

• A back up strategy is important within a company
  and should be tailored according to the company
  needs. For example, if a company can afford to
  loose data from a certain point within the
  organisation then backups will not need to be
  performed.
• The disadvantages of infrequent back-ups are that
  the company may lose data, and if that data was
  important then they will waste valuable time
  trying to recover it.
• There are many different types of back up
  frequency, I will now go on to look at some of
  these concepts in greater detail.

15
The two basic Backup and recovery processes

• Physical backups Copy a byte for byte image of
  all the database disk storage to a backup device.
• Logical Backups Copy all the logical entities in
  the database to a backup device.
• Physical backups are usually much faster
  than logical backups, this is so because the
  source can be read sequentially meaning the data
  can be retrieved at full speed.
• Logical backups inspect the last
  modified date of each file and decide whether or
  not the file has been updated since the most
  recent backup.

16

• Online backups Online backups are a popular
  solution for databases that must be available 24
  hours per day. Online backups are facilitated
  with other various software which can provide a
  consistent snapshot of all the database table
  spaces to backup utilities.
• Offline Backups If a large database needs to be
  taken out of use for a short period of time then
  offline backups are often the choice. Offline
  backups usually out perform online backups
  because of the lack of contention for system
  resources, and the fact that they have no impact
  on the transaction rates once the database is
  back in use again.

17
Mirror Databases Presented By Mark Fryer
18
Mirror Databases.
Mirror Databases are basically copies or clones
of a database that can be used for backup
operations. It offers speed and reliability to
database backup operations.
DATABASE SCENARIO
Mirroring is a very important backup tool. If you
picture a database and there is a power surge and
errors occur. The recovery log is damaged and can
not be fully recovered. Without mirroring the
recovery process when the server restarts the
database will not happen. If the recovery log and
database are mirrored, a mirror volume or drive
can replace the missing data.
19
An example for this presentation will be Spilt
Mirror Backups, a technique used to implement a
high degree of database availability.
Split Mirror Technique

• The mirror backup is extremely fast
• This allows the user to backup database
  information more frequently
• Restoring the database from a split mirror is
  also very quick
• This reduces the time that a database is not
  available during a restore activity

20
Mirror Database Requirements

• Split Mirror backup requires a number of unique
  software and hardware options for it to work.
• The database must be stored on a disk sub system
• A Example being RAID 10 on a SAN ( Specialised
  software and disk system)
• It also requires a volume management software
  package to communicate with the SQL server
• It is a question of weighing the price of this
  extra requirements against the benefits of this
  technique

21
How Split Mirror Backup Works
If we consider three separate disk drives or
volume stacks, then state that the original
database was created on two of the stacks to
minimise the occurrence of errors. We now add the
third drive to the other two mirrored databases.
All three drives or volumes are in the same disk
subsystem so the SQL server writes to them all at
the same time. When a Split Mirror backup is made
the specialised software will separate or split
the third database from the first two mirrored
drives.
22
How Split Mirror Backup Works
This split has now made a separate backup of the
database. This separate drive is known as a
BUSINESS CONTINUANCE VOLUME (BCV) or as a CLONE.
The two remaining mirrored sets of volumes are
left to continue and will remain running
concurrently to provide fault tolerance. To
gain maximum benefit from this technique it is
appropriate to store the BCV or clone on a
separate database server or place it on a tape
system.
23
Split Mirror Restore Operation

• To restore from a split mirror backup operation
  the following technique is followed.
• Re-activate the BCV or clone as the third mirror
  drive for the database.
• The disk subsystem will run the three databases
  simultaneously and the restored data will be
  matched throughout the three mirrors.
• The SQL server can read the third set
  immediately, so the database becomes available
  instantly.

24
Recovery Logs Presented By Jenny
25
Recovery Logs

• The log includes a program that will restore a
  database from a backup copy
• The log is then used to re-run any commands
  entered from the time that the database was last
  backed up
• It is beneficial to back up frequently
• If the log becomes full, any further commands
  will not be able to be recorded and re-run,
  resulting in data loss

26
Log Performance

• The size of the buffer pool of a recovery log can
  affect its performance
• Buffers store data while it is being manipulated
• A larger buffer pool reduces the number of writes
  to the recovery log
• If data is constantly being transferred then it
  will slow down the process and reduce performance
  

27
Criteria for Assessing Buffer Pool Size

• The amount of log pool pages that there are in
  the buffer pool
• The percentage of pages that are used for writing
  changes to the recovery log after a transaction
  has been completed
• The number of requests for a page while non are
  available, due to them all waiting to write to
  the recovery log

28
Pinned Recovery Logs

• If it appears to be full, the log may have become
  pinned by one or more operations on the server
• Space cannot be made available for current
  transactions
• Action may need to be taken to terminate the
  operation that is causing the problem

29
Recovery Log Protection

• Mirroring the recovery log can protect the
  information stored in it
• A recovery log or whole database can be lost as a
  result of a hardware failure on a single drive
• The considerable loss of data caused by the
  failure can be avoided by mirroring the drives

30
Roll Forward Presented By Rizna
31
Roll-Forward
Backup and recovery in general refers to the
various strategies and operations involved in
protecting your database against data loss and
reconstructing the data should that loss
occur. There are two types of automatic
recovery strategies employed by the operating
system after an instance failure. Crash
recovery Instance recovery
32
Both involves two distinct phases Rolling
forward the backup to a more current time by
applying online redo records Rolling Back all
changes made in uncommitted transactions to
their original state.
33
Criteria for Rollforward Recovery
Backup of the database must have been taken Logs
should have been archived (by enabling either
the logretain or the userexit database
configuration parameters, or both) The database,
restore and roll forward operations must always
be performed offline. During an online back up
operation , roll forward recovery ensures that
all table changes are captured and reapplied if
that backup is restored
34

• Types of roll forward recovery
• Database roll forward recovery Transactions
  recorded in database logs
• are applied following the database restore
  operation.
• Figure 4. Database Rollforward Recovery.
• There can be more than one active log in the case
  of a long-running transaction.

35
The database logs record all changes made to the
database. This method completes the recovery of
the database to its state at a particular point
in time, or to its state immediately before
the failure (i.e. to the end of the active
logs) In a partitioned database environment if
you are performing point-in-time roll forward
recovery all database partitions must be
rolled forward to ensure that all partitions are
at the same level.
36

• Types of roll forward recovery
• Table space rollforward recovery
• If the database is enabled for forward
• recovery, it is also possible to back up,
• restore and roll table spaces forward.
• Figure 5. Table Space Rollforward Recovery.
• There can be more than one active
• log in the case of a long-running transaction.

37
To perform a table space restore and roll forward
operation, you need a back up either the entire
database, or one or more individual table
spaces. Log records that affect the table spaces
that are to be recovered is also required You
can roll-forward through the logs to one of two
points The end of the logs or A particular point
in time
38
Roll Back Presented By Alex
39
Rolling Back a Transaction

• Transaction A sequence of information change
• Commit The final step when it has been
  determined that a transaction was successful
• Rollback The undoing of a partly completed
  database changes, when a transaction has been
  determined as failed.

40
Example of Rolling Back a Transaction

• BEGIN TRANSACTION transaction_name
• SQL Statement(s)
• ROLLBACK TRANSACTION

41
The ACID Model

• Atomicity - All or Nothing Rule
• Consistency - No transactions that violate
  consistency
• Isolation - Multiple Transactions occurring at
  the same time do not impact each others
  execution
• Durability Any transaction committed to the
  database will not be lost

42
Rolling Back a Transaction

• 1.BEGIN TRANSACTION
• 2.Book Flight 1
• 3.Book Flight 2
• 4.Book Flight 3
• 5.Book Flight 4
• 6.COMMIT TRANSACTION
• 7.ON ERROR ROLLBACK TRANSACTION

• 1.BEGIN TRANSACTION transaction_name
• 2.SQL Statement(s)
• 3.ROLLBACK TRANSACTION

43
Rolling Back Part of a Transaction

• 1.BEGIN TRANSACTION transaction_name
• 2.SQL Statement(s)
• 3.SAVE TRANSACTION savepoint_name
• 4.SQL Statement(s)
• 5.ROLLBACK TRANSACTIONsavepoint_name

• 1.BEGIN TRANSACTION
• 2.Book Flight 1
• 3.Book Flight 2
• 4.Establish Savepoint
• 5.Book Flight 3
• 6.Book Flight 4
• 7.On Error ROLLBACK to SAVEPOINT
• 8.COMMIT TRANSACTION

44
Reversibility

• A System is reversible in the sense that we
  can always back-up to the state at any earlier
  time, by simply going back to the initial state
  and proceeding forwards from there. (Michael
  Frank, 1996)

45
Recovery Models

• Simple Full/Differential Backups
• Bulk All operations are logged except few
• Full More log space than simple/bulk
• Full Recovery will log all operations
• Transaction log space can be saved using simple
  recovery

46
Transaction Logs Presented By John
47
Transaction Logs
The transaction log is a serial record of all
modifications that have occurred in the database
as well as the transaction that performed each
modification. The transaction log records the
start of each transaction and records the changes
to the data and enough information to undo the
modifications (if necessary later) made during
each transaction.
48
Transaction Log Content

• Information recorded on the transaction log
  includes
• the beginning time of each transaction
• the actual changes made to the data and enough
  information to undo the modifications made during
  each transaction (accomplished using before and
  after images of the data)
• the allocation and de-allocation of database
  pages
• the actual commit or rollback of each transaction
  

49
Transaction Logs
For some large operations, such as CREATE INDEX,
the transaction log instead records the fact that
the operation took place. The log grows
continuously as logged operations occur in the
database. The transaction log records the
allocation and de-allocation of pages and the
commit or rollback of each transaction.
50
Transaction Log and Roll Forward
This allows SQL Server either to apply (roll
forward) or back out (roll back) each
transaction in the following ways A
transaction is rolled forward when you apply a
transaction log. SQL Server copies the after
image of every modification to the database or
reruns statements such as CREATE INDEX. These
actions are applied in the same sequence in which
they originally occurred. At the end of this
process, the database is in the same state it was
in at the time the transaction log was backed up.

51
Transaction Log and Roll Back
A transaction is rolled back when you back out an
incomplete transaction. SQL Server copies the
before images of all modifications to the
database since the BEGIN TRANSACTION. If it
encounters transaction log records indicating
that a CREATE INDEX was performed, it performs
operations that reverse the statement logically.
These before images and CREATE INDEX reversals
are applied in the reverse of their original
sequence.