Like any other random computer system, databases, too, are subjected to failures. In case of any failure, it is crucial not to lose the data stored in it, and also, in case of a data failure, it should be immediately recovered to the normal state. A good database needs to have atomicity, which means either transaction is completed and committed, or the transaction does not affect the database.
There are automatic and manual ways for backing up your data and recovering the same in case of any failure. One of the simplest and most popular ways to do this is to use MySQL GUI. The techniques for recovering the lost data in case of a system crash or transaction error due to virus attacks or catastrophic failures are known as database recovery techniques. To prevent any data loss, we can use recovery techniques as a deferred update, immediate update, backing up, etc.
The recovery techniques we use are dependent heavily on a particular file, which is called a system log. It contains info about the start and end of each transaction and any interim update in the transaction. This log will keep track of the operations and also affect the database item values. All this info is needed in case of a failure to recover the data.
Recovery log
This database log for recovery is ideal for keeping on the disk; note the following logs:
- start_transaction(T): This is a log used to entry records with which transaction T initiates the execution.
- write_item(T, X, old_value, new_value): it is a log entry, which will enter the records for which transaction T will change item X’s database value from the old value to new value.
- read_item(T, X): A long to enter the records in which transaction T will read the value of X’s database item.
- Commit (T): A log entry to record transaction T when it completes all database accesses successfully. The effects of the same can be committed to / recorded permanently on the database.
- Abort (T): Will record when transaction T gets aborted.
Checkpoint is a unique mechanism where all the previous logs will be removed from the system and gest stored permanently into storage disk. The checkpoint declares the unique point before the DBMS was in the consistent state, and all the transactions were done.
As the transaction T may reach the commit point when all operations which access the database may be successfully executed, the transaction is said to reach the point at which it may not abort. Once the transaction is committed, then it will be recorded permanently into the database.
Commitment involves writing commit entries to the log and then writing this to the disk. In case of a system the crash occurs, this item will search back in logs for getting transactions T written as start_transaction (T) into the log, but not written a commit (T) yet. These transactions will be rolled back to reverse the effect on the database during the recovery process. For any confusion you have in database administration and recovery, you can get RemoteDBA.com experts‘ assistance.
Undoing – If any transaction tends to crash, then the recovery manager will undo all the transactions as reversing all transaction operations. This includes checking a transaction for the log entry for write_item (T, x, old_value, new_value) and then setting up the item x value in the database into the old-value. There are two primary techniques used to recover the same from the transaction failures as immediate and deferred updates.
Deferred updates – In this technique, you cannot physically update the database on the disk until the transaction reaches the commit point. Before reaching the commit, all the transaction updates get recorded in a local workspace. If the transaction tends to fail before reaching the commit point, it will not change the database, so you need not have to UNDO it. It may also be necessary to REDO the same effect of the operations, which are recorded in the local transaction workspace as their impact may not have been there at the database. Owing to this, the deferred update is also called as a no-undo or no-redo algorithm.
Immediate update – On immediate update, the database may get updated by the transaction operations before any transaction meets the commit point. All the operations will be recorded in a log before getting applied to the database. If any transaction may fail to reach the commit point, then the operation’s effect may be undone. That means the transaction should be rolled back for which we require bot redo and undo. So, this technique is also called a undo/redo algorithm.
Buffering or Caching – In this, there are one or more disk pages that include the data items to be updated and cached to the primary memory buffers, which then get updated in the memory before being written to the disk. Such a collection of buffers in memory is called DBMS cache, which is ideally kept under DBMS’s control, which holds these buffers.
Shadow paging – This is an approach to provide durability and atomicity. There will be a directory constructed with n entries, in which a particular entry points to a specific page of the database. When the given transaction starts to execute, then the directory may get copies to a shadow directory. While the given needs to be modified, a shadow page will be allocated in which all the changes are recorded. Once it becomes durable, all pages that refer to the original page get updated to refer to the replacement page.
Some of the most popular backup techniques are
Full database backup – the complete database, including meta-information, gets backed up to restore it, including the full-text catalogs.
Differential backup – Storing only the data changes which occurred since the last backup. When the same data needs to be changed many times since previous database backup, differential backup may store only the most recent data version.
Transaction log backup – For this backup model, all the events occurred in a database like each statement’s record is backed up. It is also possible to perform a backup from the transaction log if data files get destroyed.
Knowing these essentials will help you to enable your database backup and recovery in a foolproof way.
You come across a little out of context-could you add more examples?