Database Management Systems Essentials - Database Recovery Techniques

Featured Image Caption: Database Recovery

Jump to read...

As with any other computer networks and information technology systems, databases to are subjected to different types of failures. However, even if a database fails, the data stored in it should be up and available when required. When a database fails, it must also possess the facilities to recover the last saved version’s data. A database also must have atomicity as to whether a transaction is completed successfully or committed. All these transactions must not affect the database as a whole.

There are automated and non-automated ways to make data recovery during failure situations. Various techniques are used to recover any data loss due to transaction errors, system crashes, virus attacks, catastrophes in command execution, and so on. To prevent data loss, recovery techniques based on the immediate update and backing up data can be used effectively.

When it comes to data recovery, the techniques used are largely dependent on a unique file known as a system log. This log contains all information about the beginning and end of every database transaction and any updates that had occurred during the transactions. This lock keeps track of all the transaction operations, which affect the values of the database items. This information is necessary for data recovery during a transaction failure.

System log

The system log is kept on the disc start_transaction (T). This log records all given transactions as T starts execution when the transaction reaches a commit point when all its operations that access the database are executed successfully. Any transaction which has reached the commit point and aborts without completing it is recorded in the log. Once committed, the transaction is recorded permanently in the database. Commit entry is written on to the database log and also the log to the disk. If a system crash occurs, the item is further searched back in the given log for transaction T, which returns the log entry but does not return the commit entry. These transactions need to be further rolled back and affect the given database during the recovery process. For remote database administration and backup support, you can consult RemoteDBA expert.

If a transaction fails or crashes, the recovery manager may further do the transactions in reverse of the operations as executed in the transaction. This process involves scrutinizing the log entries and reversing this item’s value back to the old value.

There are two techniques for recovery from non-catastrophic transaction failures as Deferred Updates and Immediate Updates.

1. Deferred update –

This updating technique will not update the database physically on the desk until a transaction attains the commit point. Before reaching the commit, all the transaction updates are recorded in the local transaction workspace. If a transaction fails before attaining the commit point, it will not impact the database, so there is no need to undo it. However, it may also be needed to read the effect of the operations recorded in the local transaction, as these may remain in the database.

2. Immediate update –

In case of an immediate update, the database may be updated with some operational transactions before the given transaction reaches a midpoint. However, these operations are further recorded in a disk before they are applied to the actual database. This makes recovery possible at any given point. If the transaction fails to reach the given commit point, the operation’s effect must be undone, and the transaction must be rolled back. So, we need to do both undo and redo. So, the immediate update technique is also known as undo/redo algorithm.

Caching or buffering

In this approach, one or more of the disc pages, which consist of data items to be updated, are kept cached into the main memory buffers and are further updated in the memory before returning to the disk. The collection of the in-memory buffers is called DBMS cache. This is kept under the control of the DBMS to hold these buffers. Further, a directory is used to keep track of the database items in the buffer. There will be a larger set of data associated with each buffer, which is kept 0 if the buffer is not modified or 1 if the buffer is modified.

Shadow paging

This approach offers both durability and atomicity. There may be a directory with ‘N’ entries. When the transaction begins to execute, the current directory will be copied into the shadow directory. When the page is getting modified, then a shadow page is allocated where the changes are made. When it is becoming durable, all the pages which refer to the originals are further updated to refer to the new replacement page.

Backup techniques

Some of the common backup techniques are as below:

Full database backup – The full DB backup covers data and metadata information for restoring the whole database. Catalogs are backed up in the predefined time series.
Differential backup – This stores only data changes that have occurred since the last complete database backup. When the same set of data change many times since the last full backup, the differential backup stores the most recent version of this change data. To do this, we need first to restore a full database backup.
Transactional log backup – In this backup mode, all the events that have occurred in the database as a record of all the single statements executed are backed up. It is the backup of all transaction log entries, and this backup also contains transactions that had been executed on the database. With this, the database can be recovered to a specific point in time as we set. It is also possible to perform a backup from the transactional log if data files are destroyed, and there is no single committed transaction.

You need to evaluate your critical data sets’ nature and ensure that appropriate data backup and recovery practices are there in place. This is not enough, but you have to test your backup processes and recovery to ensure that it is always up in action.