Section 18.3. Recovering Your Database


18.3. Recovering Your Database

As mentioned previously, a DB2 database is recovered in two primary steps: a version recovery, in which the database is restored from backup, and a rollforward recovery, in which the transaction log replays transactions that have occurred since the last backup. This section first covers the two different scenarios in which you would perform a version recovery and then explains how to do a rollforward recovery against a restored database.

18.3.1. Performing an In-Place Version Recovery

If you are restoring a database in the location where it was originally backed up, we'll call it an in-place restore, to differentiate it from a redirected restore, in which you recover to another location. (Redirected restores are covered in the next section.)

18.3.1.1. Step 1: Gather your database backups

In order to restore a database or one of its tablespaces, we need to have access to our backups. This may sound obvious, but it is not so trivial when dealing with incremental and delta backups (see the section "Backup levels" earlier in this chapter). In short, we need to have all of the database backups necessary to restore our database/tablespace(s). If the files were moved to a different machine or network, gather the backup files in preparation for the restore operation. If you can't find all your backup files, you might want to consider digging into the recovery history file of your database (see the section "Discovering the history of your backup operations" earlier in this chapter). The recovery history file can tell you where the database backups were created and the names of their files if they were stored on disk. If possible, put the backup files needed for your restore into a single directory. For our example, we'll use the directory C:\backups.

18.3.1.2. Step 2: Make sure the containers that existed during your backup are still around

If you do not intend to perform a redirected restore of your database and want the database backup to be restored into the database it came from, it is important that the containers that existed at the time when the backup image was made are still around. This is commonly not the case if you have to restore your database to a new machine after the original machine failed. If the tablespace containers that existed when your database backup was performed don't exist in the location you are trying to restore, you get an error during the recovery operation. To avoid such a problem, you can use a redirected restore (as described later in this chapter).

18.3.1.3. Step 3: Issue the restore or recover database command

We are finally ready to issue the restore database command. If you're running a version prior to 8.2, use the following syntax to restore a database:

C:> restore DB database_name_or_alias from backup_location taken at yyyymmddhhmmss

For example, the following command specifies that we want to restore our database named sample from a backup identified by the timestamp 20060227145655:

C:> db2 restore db sample from c:\backups taken at 20060227145655 replace existing

As mentioned earlier, the timestamp of a backup is part of its filename. The replace existing clause states that existing data (if any) is deleted and replaced by the content of our backup.

You can also add the without rolling forward clause at the end of the restore. Using this clause causes you to lose any transactions that occurred after the backup, but it makes your database usable immediately after the restore command executes. Therefore, while this clause brings the database online quicker, it will most likely result in a loss of data.


You can also restore just a tablespace. Tablespaces can be restored online or offline (except for a tablespace containing the system catalog tables, which must be restored offline). Tablespaces can be restored from full-fledged database backups or from backups that were performed at the tablespace granularity. You can use the following syntax to restore tablespace(s) of a database:

restore database database_name_or_alias tablespace  (tablespace_name, other_table_space_name,...) online from backup_location taken at  yyyymmddhhmmss replace existing

For example, the following command performs an online restore of the tablespace named userspace1 of the sample database from a backup identified by the timestamp 20060227145655 and located in C:\backups.

C:> db2 restore db sample tablespace(userspace1) online from  c:\backups taken at 20060227145655 replace existing

If you're running 8.2 or later, and you want to recover the entire database, you have the option of using the recover command instead:

C:> db2 recover db sample to isotime

This command automatically selects a backup that is prior to isotime, restores it, then rolls forward to the most recent transaction that it can find prior to isotime.

18.3.1.4. Step 4: Perform rollforward recovery

If you use the recover command in 8.2, you can skip this step.


If you did not include the without rolling forward clause when issuing your restore command, the database is left in a rollforward pending state after the restore command completes. Tablespace restoration always places the restored tablespace(s) in the rollforward pending state. The database cannot be used until it is brought out of this state with the rollforward command. See the section "Performing a Rollforward Recovery" later in this chapter.

18.3.1.5. Step 5: Reorganize the data and collect statistics

This step is covered in detail in the section "Reorganizing Data and Collecting Statistics" later in this chapter.

18.3.2. Performing a Redirected Version Recovery

The filesystem paths used by the database are stored in the backup image. If you attempt to restore a database backup to a system that does not have the filesystems and physical devices that the database backup is expecting, you will receive an error during the database restoration operation. To avoid this, you can use the redirected restore operation. It consists of three steps.

18.3.2.1. Step 1: Restore the database backup and specify the redirect option

Assume that you have a valid backup that you want to recover. Let's use our earlier example from the restore command procedure, but this time perform a redirected restore:

C:> db2 restore db sample from c:\backups taken at 20060227145655 redirect

By specifying the redirect option, DB2 actually pauses so that you can define the appropriate tablespace containers for your target database.

The recover command does not yet support redirected restores, so you need to use the restore and rollforward commands.


18.3.2.2. Step 2: Define appropriate tablespace containers for the target database

At this point, we need to define the appropriate tablespace containers for our target database. The question is, how do we know what tablespace containers we need? This is where we can use the list tablespaces show detail command or the list tablespace containers for tablespace_num command, where tablespace_num is an integer representing one of the entries returned from issuing the list tablespaces command.

Of course, you cannot learn about tablespace container information from the source database if the source database has failed. Accordingly, you should get information about the tablespaces in the source database being backed up and put it away for safekeeping when needed for a redirected restore operation. You can use the output of the db2look command to provide this information (see the section "Using db2look" earlier in this chapter).

Next, define tablespace containers for the tablespaces that are associated with the database backup being restored. For example, the following commands create new tablespace containers for the syscatspace, tempspace1, and userspace1 tablespaces. In this case, we use directory names relative to the database directory, not absolute pathnames.

C:> db2 "set tablespace containers for 0 using (path 'tbsp0cont1')"  C:> db2 "set tablespace containers for 1 using (path 'tbsp1cont1')"  C:> db2 "set tablespace containers for 2 using (path 'tbsp2cont1')"

18.3.2.3. Step 3: Continue the redirected restore operation

In Step 1, we were able to pause the restore db operation by specifying the redirect option. Now that we have defined the needed containers in Step 2, we can proceed with restoring the database:

C:> db2 restore db sample continue 

18.3.2.4. Step 4: Perform rollforward recovery

As was the case with our in-place restore, if you are interested in accounting for transactions that occurred after our backup and after archive logging was enabled, you need to perform a rollforward recovery by issuing the rollforward command described in the following section "Performing a Rollforward Recovery."

18.3.2.5. Step 5: Reorganize the data and collect statistics

This step is covered in detail in the section "Reorganizing Data and Collecting Statistics" later in this chapter.

18.3.3. Performing a Rollforward Recovery

If you used the recover command, you can skip this section, because the rollforward has already happened.


As mentioned earlier, a database restore command takes the database only to the state it was in when the backup command was performed on it. It is very likely that changes to the database occurred after your last available backup, and you would like those changes reapplied to the database. As mentioned earlier, if archive logging was enabled for a database, the database/tablespace(s) you restored are left in a rollforward pending state after a restore. A database or tablespace(s) in the rollforward pending state cannot be used until it is brought out of this state by applying the rollforward command to the database/tablespace(s).

A rollforward recovery allows DB2 to use its transaction logs to restore a database or a tablespace to the state it was in after the backup. This point in time can be either all the way to the end of the archive logs or some time before the end of the logs. However, the stipulation is that the point in time (PIT) must be at least the minimum point in time allowed for the tablespace(s). This minimum PIT ensures that the tablespace and logs are consistent with the system catalogs. To illustrate the concept of a minimum PIT, suppose you have a tablespace named userspace1 that you backed up at time T0. At time T1, you decide to create a new table in tablespace userspace1. By doing so, you effectively set the minimum PIT to T1 because if after T1 you tried rolling forward to some point between T0 and T1, the system catalogs would clash with the rollforward recovery. The rollforward recovery would not account for the new table. Accordingly, in order to avoid such synchronization issues with the system catalogs, DB2 forces a rollforward operation to at least the minimum PIT.

In this example, the minimum PIT was updated via a table creation. Other factors can affect the minimum PIT. Whenever DDL statements are run against the tablespace or against tables constituting the tablespace, the minimum PIT is affected. Since you cannot perform a rollforward operation on a tablespace using a value prior to the minimum PIT, you might be asking yourself how to determine the minimum PIT for a tablespace. The minimum PIT can be obtained using these commands:

C:> db2 connect to sample C:> db2 create table test like staff in userspace1 C:> db2 list tablespaces show detail

The userspace1 information now looks like this:

Tablespace ID                        = 2  Name                                 = USERSPACE1  Type                                 = System managed space  Contents                             = Any data  State                                = 0x0000    Detailed explanation:      Normal  Total pages                          = 408  Useable pages                        = 408  Used pages                           = 408  Free pages                           = Not applicable  High water mark (pages)              = Not applicable  Page size (bytes)                    = 4096  Extent size (pages)                  = 32  Prefetch size (pages)                = 32  Number of containers                 = 1  Minimum recovery time                = 2006-03-01-13.23.19.232911

After performing a rollforward operation, committed transactions in the log are restored into the database; incomplete (that is, uncommitted) transactions in the log are rolled back. At the end of our rollforward recovery, we have a database in a transaction-consistent state, ready for use by applications.

As you can see, for a rollforward recovery to work, it is paramount that the logs of a database remain pristine and intact. As such, you should consider using RAID arrays and other precautionary hardware to ensure the safety of the DB2 logs.

To perform the rollforward command, you must have the sysadm, sysctrl, or sysmaint database authority.


As with the DB2 restore command, the rollforward command can be applied in offline mode only if you are restoring at the database level. If you are restoring at the tablespace level, you can rollforward online. As with the DB2 restore command, an online rollforward operation cannot be performed on the system catalog tablespace (syscatspace). The syscatspace must be treated with uniqueness because this tablespace must be rolled forward all the way to the end of the logs.

After a tablespace PIT rollforward operation is completed, DB2 puts the tablespace in the backup pending state. At this point you must perform a backup of the database before you use it.


The following sections describe the procedure to roll forward a database/tablespace(s).

18.3.3.1. Step 1: Gather your logfiles

In order for rollforward recovery to work, you need to have all your logfiles. The rollforward command assumes that the logfiles are in the log directory specified in the logpath configuration parameter; alternatively, you can specify the rollforward command with an overflow log path log directory. The overflow log path directory might be where a user exit saves archived logs or where the logarchmeth1 parameter, available in 8.2 and later, copies them. If you are recuperating from a disk failure, move the archived logs to the overflow log path directory so the rollforward command can see the archived logs.

18.3.3.2. Step 2: Determine the minimum PIT

If you plan to roll forward tablespaces to a PIT prior to the end of your logs, this PIT must be equal to or greater than the minimum PIT for your tablespaces. As mentioned earlier, you can examine the minimum PIT with this command:

C:> db2 list tablespaces show detail

18.3.3.3. Step 3: Issue the rollforward command

Let's cover a few examples of the rollforward command to exemplify how you might use it.

C:> db2 rollforward db sample user db2admin using password  to 2006-03-01-13.23.19.232911 and stop

This command rolls the sample database forward. In this example, the rollforward operation is carried out under the username db2admin with a password of password. The timestamp, 2006-03-01-13.23.19.232911, specifies the point that the rollforward should stop. As you can imagine, this flexibility of PIT recovery can help if a database is corrupted by some rogue or even trusted application. Simply recover your database and then rollforward to before things went bad. The and stop clause in the example command tells DB2 to take the database out of the rollforward pending state so you can start using it.

If you want to roll forward all the way to the end of our logs, use this command:

C:> db2 rollforward db sample user db2admin using password to end of logs and stop

You may have noticed that we did not try to perform the rollforward operations using the online keyword. This is because you cannot perform rollforward operations online at the database level. You can do this at the tablespace level, though, as shown in the following example:

C:> db2 rollforward db sample user db2admin using password  to end of logs and stop tablespace(userspace1) online

This command rolls forward the sample database's tablespace named userspace1. The command is issued following the restore command for the userspace1 tablespace. The rollforward operation is carried out under the username db2admin with a password of password. An online rollforward operation cannot be performed on the system catalog tablespace (syscatspace). As done earlier, the and stop clause of our example command tells DB2 to take our database out of the rollforward pending state so that we can start using the database.

18.3.3.4. Step 4: Set constraints (if necessary)

Special attention also needs to be paid when rolling forward tablespaces with tables that have referential integrity relationships with tables housed in other tablespaces. When you roll forward such a tablespace, DB2 leaves the table in the check pending state. This state prevents the table from being used until you check its constraints. You can restore a table from the check pending state to a normal state by executing a set constraints statement.

When issuing the set constraints command, it is a good idea to specify an exception table into which rows violating the defined constraints are placed. The following command removes the table test from the check pending state. But before doing so, the contents of the table are checked against defined constraints for the table. Violators of the constraints are placed in the badtest table. To finish things up, the table is returned to the normal state. Future updates to the table are performed only if the updates do not violate the constraints of the table.

C:> db2 set constraints for test immediate checked for exception in test use badtest

18.3.3.5. Step 5: Perform a database backup (if necessary)

If you performed a rollforward operation at the tablespace level in Step 3, your tablespaces are in a backup pending state. You must perform a database backup before using the tablespaces.

18.3.3.6. Step 6: Reorganize data and collect statistics

This step is covered in detail in the next section.

18.3.4. Reorganizing Data and Collecting Statistics

The following commands optimize access to your data after a recovery. This example allows applications to access the data while the maintenance is performed.

C:> db2 connect to sample  C:> db2 reorgchk update statistics on table all > reorgchk.txt

The reorgchk command calls the runstats command and gathers a new set of statistics for the database. It returns a lot of data, so we redirect the output to a file for further evaluation. The last field of the reorgchk command's output identifies which tables need to be reorganized. Those tables are identified with an asterisk in one of several columns in the field. Assume the following output was returned from the reorgchk command. The department table would not need to be reorganized. It would be a good idea, however, to reorganize the data in the employee table.

SCHEMA    NAME         CARD    OV    NP    FP ACTBLK    TSIZE  F1  F2  F3 REORG ------------------------------------------------------------------------------- Table: ADMINISTRATOR.DEPARTMENT ADMINIST> DEPARTMENT      9     0     1     1      -      549   0   - 100 ---  Table: ADMINISTRATOR.EMPLOYEE ADMINIST> EMPLOYEE       32     0     2     2      -     2784   0  69 100 -*-

You reorganize the employee table with the following commands, allowing access to the data during the reorganization. Note that you need to fully qualify the table with the schema name in the runstats command.

C:> db2 reorg table employee allow read access C:> db2 runstats on table administrator.employee allow write access

Hopefully, this chapter about how to back up and recover your DB2 database has been helpful. You should also make sure you are familiar with the DB2 manuals on the subject, especially the Data Recovery and High Availability Guide and Reference for your particular version.

BackupCentral.com has a wiki page for every chapter in this book. Read or contribute updated information about this chapter at http://www.backupcentral.com.





Backup & Recovery
Backup & Recovery: Inexpensive Backup Solutions for Open Systems
ISBN: 0596102461
EAN: 2147483647
Year: 2006
Pages: 237

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net