Database recovery is how DB2 makes your database consistent in the event of a failure. A database is "consistent" when all committed transactions have been applied to the database and any uncommitted transactions that might have been performed have been rolled back.
To minimize the loss of your data, you need to have a recovery strategy, ensure that it works, and constantly practice it. The following are some recovery scenarios you should consider.
System outage : A power failure, hardware failure, or software failure can cause your database to be in an inconsistent state.
Transaction failure : Users may inadvertently corrupt your database by modifying it with incorrect data or delete useful data.
Media failure : If your disk drive becomes unusable, you may lose all or part of your data.
Disaster : The facility where your system is located may be damaged by fire, flooding, or other catastrophe.
To plan your recovery strategy, ask yourself:
Can the data be loaded from another source?
How much data can we afford to lose?
How much time can we spend recovering the database?
What storage resources are available for storing backups and log files?
A unit of work (UOW), also known as a transaction, consists of one or more SQL statements that end with a
COMMIT or
ROLLBACK statement. All of the statements inside this UOW are treated as a unit, which ensures data consistency. A typical example to explain this concept is that of a customer trying to transfer $100 from his savings account to his checking account. The UOW in this case would include all three of the following:
DELETE 100 dollars from SAVINGS account
INSERT 100 dollars to CHECKING account
COMMIT
If these statements are not treated as a unit and a hardware failure occurs after the
DELETE and before the
INSERT , then this person loses $100! Since the statements are treated as a unit, this will never happen because DB2 knows that the unit did not complete as a
COMMIT was not issued. When the system is restarted after the failure, DB2 will
ROLLBACK the statements, meaning it will bring the database back to the state prior to beginning of the transaction.
NOTE
An analogy for understanding the
COMMIT statement is to compare it to the
Save button in word processing software. When you click this button, you expect your text document to be saved. Changes made after you save the document are lost if your server crashes, but what was saved will remain on disk. Similarly, when you issue a
COMMIT statement, changes made to the database are saved. If your server crashes, anything that was committed can be recovered, and anything that was not will be lost.
There are three types of recovery in DB2:
Crash recovery
Version recovery
Roll forward recovery
Each of these types of recovery is discussed in detail in the next sections.
Crash recovery protects a database from being left in an inconsistent state following an abnormal termination. An example of an abnormal termination is a power failure. Using the banking example, if a power failure occurs prior to the
COMMIT statement, the next time DB2 is restarted and the database accessed, DB2 will
ROLLBACK the
INSERT statement, followed by the
DELETE statement. Note that statements are rolled back in reverse order, not in the order they were originally executed. This ensures that the data is consistent, and that the person still has the $100 in his savings account.
By default, DB2 automatically initiates crash recovery when a database is accessed for the first time following an abnormal termination. You can disable the automatic crash recovery by setting the database configuration parameter AUTOSTART to OFF. If you do that, you will need to perform crash recovery manually using the
RESTART DATABASE command. If you do not restart the database manually in the event of a system crash, you will receive the following error when you try to connect to the database:
[View full width]SQL1015N The database must be restarted because the previous session did not conclude
normally.
Version recovery allows for the restoration of a previous version of a database using a backup image created with the
BACKUP command. The restored database will be in exactly the same state it was in when the
BACKUP command was executed. If further activity was performed against the database after this backup was taken, those updates are lost. For example, assume you back up a database and then create two tables,
table1 and
table2 . If you restore the database using the backup image, your restored database will not contain the two new tables.
Roll forward recovery extends version recovery by using full database backups in conjunction with log files. A backup must be restored first as a baseline, and then logs are applied on top of this backup image. Therefore, changes you made
after you backed up the database can be applied to the restored database. Using the previous example, with roll forward recovery you have three choices to restore your database:
You can restore the database using only the backup image. This is identical to version recovery. In this case, the restored database will not contain
table1 and
table2 .
You can restore the database using the backup image, and then roll forward the logs to the point when
table1 was created. In this case, the restored database will contain
table1 but not
table2.
You can restore the database using the backup image, and then roll forward the logs all the way to the end of the logs. In this case, the restored database will contain both
table1 and
table2.
By default, crash recovery and version recovery are enabled. You will learn how to enable roll forward recovery in Section 13.2.4, Logging Methods.