IBM Books

Administration Guide


Overview of Recovery

You need to know the strategies available to you to help when there are problems with the database. Typically you will deal with media and storage problems, power interruptions, and application failures. You need to know that you can back up your database, or individual table spaces, and then rebuild them should they be damaged or corrupted in some way. The rebuilding of the database is called recovery. Crash recovery automatically attempts to recover the database after a failure. There are two ways to recover a damaged database: version recovery and roll-forward recovery.

Non-recoverable databases have both the logretain and userexit database configuration parameters turned "off". This means that the only logs that are kept are those required for crash recovery. These logs are known as active logs, and they contain current transaction data. Version recovery using offline backups is the primary means of recovery for problems with a non-recoverable database. (An offline backup means that no other application can use the database when the backup operation is in progress.) When you restore the database, you can only restore it offline, and it is restored to the same state it was in when you took the backup image.

Recoverable databases have either the logretain or userexit (or both) database configuration parameters turned "on". Active logs are still available for crash recovery, but you also have the archived logs, which contain committed transaction data. When you restore the database, you can only restore it offline, and it is restored to the same state it was in when you took the backup image. However, with forward recovery, you can then roll the database forward (that is, past the time of the backup image) by using the active and archived logs to either a specific point in time, or to the end of the active logs.

Unlike non-recoverable databases, you can perform the backup operation for a recoverable database either offline or online (online meaning that other applications can connect to the database during the backup operation). The database restore and roll forward operations must always be performed offline. Although during an online backup operation, changes may also be occurring on the tables, the roll-forward recovery method ensures that all table changes are captured and reapplied if that backup is restored.

If you have a recoverable database, you can also back up, restore, and roll forward individual table spaces in it. When you back up a table space online, it is still available for use, and changes made to its tables during the backup are recorded in the logs. When you perform an online restore or roll forward on a table space, the table space itself is not available for use until the operation completes, but users are not prevented from accessing tables in other table spaces.

Crash recovery protects a database from being left in an inconsistent, or unusable, state. Transactions, or units of work, against the database can be interrupted unexpectedly. For example, should a failure (power interruption, application failure) occur before all of the changes that are part of the unit of work are completed and committed, the database is left in an inconsistent and unusable state.

Figure 23. Rolling Back Units of Work


SQLD0RLB


The database then needs to be moved to a consistent and usable state. This is done by rolling back incomplete transactions and completing committed transactions that were still in memory when the crash occurred.

You can do this by entering a RESTART DATABASE command. If you want this done in every case of a failure, then you should consider the use of the automatic restart enable (autorestart) configuration parameter. The default for this configuration parameter is that the RESTART DATABASE routine will be started every time it is needed. When (autorestart) is enabled, the next connect request to the database after the failure causes RESTART DATABASE to be executed.

Crash recovery moves the database to a consistent and usable state. If, however, crash recovery occurs for a database that is enabled for forward recovery (that is, the logretain or userexit configuration parameter is "on"), and an error occurs during crash recovery that is attributable to an individual table space, that table space is taken offline, and cannot be accessed until it is repaired. Crash recovery continues. See "Rolling Forward Changes in a Table Space" for more information. At the completion of crash recovery, the other table spaces in the database are still usable and connections to the database can be established. (There are exceptions involving the table spaces that have the temporary tables or the system catalog tables. These will be discussed under roll-forward recovery.)

Following crash recovery, you may need to take additional action. You may need to work with the table spaces taken offline as mentioned above. You may need to conduct a version recovery or a roll-forward recovery, depending on the error.

There are two recovery methods supported by DB2:

  1. Version recovery allows for the restoration of a previous version or image of the database that was made using the BACKUP command.

    Figure 24. Restoring a Database


    SQLD0RST


    A database restore will rebuild the entire database using a backup of the database made at some point earlier. A backup of the database allows you to restore a database to a state identical to the time when the backup was made. Every unit of work from the time of the backup to the time of the failure is lost. (To re-create these units of work requires the roll-forward recovery method, which is discussed later.)

    Using the version recovery method, you must schedule and perform a full backup of the database on a regular basis.

    In a partitioned database environment, the database is located across many database partition servers (or nodes). You must restore all database partitions, and the backups that you use for the RESTORE must all have been taken at the same time. (Each database partition is backed up and restored separately.) A backup of each database partition taken at the same time is known as a version backup.

  2. To use the roll-forward recovery method, you must have taken a backup of the database as well as archived the logs (by enabling either the logretain or userexit database configuration parameters, or both. For information on the decisions that you must make regarding the logging procedure that you use, see "Database Logs".) If you restore the database and specify WITHOUT ROLLING FORWARD, it is the same as the version recovery method. The database is restored to a state identical to the time when the offline backup was made. If you restore the database and do not specify WITHOUT ROLLING FORWARD for the restore operation, the database will be in the roll-forward pending state at the end of the restore. This allows roll-forward recovery to take place.

    The two types of roll-forward recovery to consider are:

    1. Database roll-forward recovery follows the restore of the database with the application of database logs. 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 just before the failure (that is, to the end of the active logs.)

      Figure 25. Database Roll-Forward Recovery


      SQLD0RLF


    2. When the database is enabled for forward recovery, it is also possible to back up and restore table spaces. To perform table space restore, you need a backup image of either the entire database (that is, all of the table spaces) or of one or more individual table spaces. This method restores the selected table spaces to a state identical to the time the backup was taken.

      Notes:

      1. Those table spaces not selected at the time of the BACKUP will not be in the same state as those that were restored.

      2. Using the roll-forward recovery method with table spaces, you must identify "key" table spaces in the database to be recovered as well as schedule and perform a backup of the database or the "key" table spaces on a regular basis.

    In a partitioned database system, the database is located across many database partitions. In this 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. If you need to restore a single database partition, you can perform roll-forward recovery to the end of the logs to bring it up to the same level as the other database partitions in the database.

Figure 26. Restoring One or More Table Spaces


SQLD0TSR


Table space roll-forward recovery is required in the following two situations:

  1. If one or more table spaces are in a roll-forward pending state because of crash recovery, first correct the problem with the table space. Then you can use the ROLLFORWARD command to apply the logs against the table spaces to either a point in time, or to the end of the logs.
    Note:If the table space in error contains the system catalog tables, you will not be able to start the database. You must restore the SYSCATSPACE table space, then perform roll-forward recovery on it to the end of the logs.

    Figure 27. Table Space Roll-Forward Recovery


    SQLD0RFT


  2. After a table space restore, the table space is always in the roll-forward pending state, and it must be rolled forward. Again, use the ROLLFORWARD command to apply the logs against the table spaces to either a point in time, or to the end of the logs.

In a partitioned database system, if you are rolling forward a table space to a point in time, you do not have to supply the list of nodes (database partitions) on which the table space resides. The database manager submits the rollforward request to all database partitions.

In a partitioned database system, if you are rolling forward a table space to the end of the logs, you have to supply the list of nodes if you do not want to roll the table space forward on all database partitions. If you want to roll forward all table spaces on all database partitions that are in the roll-forward pending state to the end of the logs, you do not have to supply the list of nodes. By default, the ROLLFORWARD request is sent to all database partitions.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]