Quick recovery procedure for IBM DB2 Databases

The following article describes how to use tablespace backup to quickly restore an IBM DB2 database, the following is an introduction to the actual steps for restoring the IBM DB2 database. We hope to help you in this regard.

IBM DB2 database

In DB2 V9, an important new feature is provided, that is, using the backup of the DB2 tablespace to quickly restore the database, and you can even choose to recover some important data based on the importance of the data, to achieve quick recovery. This article introduces this important technical feature of DB2 V9 in detail based on examples. It is helpful for you to plan system backup/recovery policies.

Rebuild)

When our DB2 database suffers database corruption due to some serious errors (such as storage corruption), we usually need to fix the related errors, use the Restore command to Restore the database (DB2 also supports high system availability through multi-machine fault tolerance mechanisms such as HADR. This article only damages the single-host database, database recovery is required ).

The general practice is to restore the whole database through the full backup of the previous database, and then roll the database through the log, so that the database can be restored to a time close to the disaster point. However, when the data volume of our database is large, full backup and full recovery of the database will be very time-consuming.

In DB2 V9, an important new feature is provided, that is, the use of DB2 tablespace backup to quickly restore the IBM DB2 database, and even the ability to choose to recover a part of important data based on the importance of data, to achieve quick recovery. This article introduces this important technical feature of DB2 V9 in detail based on examples. It is helpful for you to plan system backup/recovery policies.

Scenario 1: Use tablespace backup to reconstruct the entire DB2 database

During database reconstruction, DB2 V9 now supports the reconstruction of the entire database through table space-level backup, without the need for full backup of the entire database. This capability of DB2 makes it possible for us to quickly back up and restore important data in the core system. Let's take a look at the following example:

Suppose we have a database named TEST, which uses archive logs. One day, the system suddenly loses power, causing damage to the disk stored in the database. At this time, the database will be unavailable. As a DBA, We need to quickly restore the database. Assume that the database has the following table spaces:

SYSCATSPACE (system tablespace)

USERSPACE1 (user data table space 1)

USERSPACE2 (user data table space 2)

USERSPACE3 (user data table space 3)

The data you can use to recover an IBM DB2 database includes:

All database log files are not damaged because the logs are stored on another disk (and in many cases, we also mirror the logs because they are too important.

You do not have full database backup, but you have the following table space backup:

TEST.3.DB2. node).catn316.20060515135047.001-SYSCATSPACE and USERSPACE 1 tablespace are backed up at, 2006051513504;

TEST.3.DB2. node).catn).20060516135136.001-backup of tablespaces in USERSPACE 2 and USERSPACE 3 at, 2006051613513;

TEST.3.DB2. node).catn).20060517135208.001-USERSPACE 3 tablespace backup at 2006051713520.

For the traditional DB2 recovery policies of Restore and Rollforward, we need a full backup image of a database to Restore the database, and then use logs to perform database Rollforward operations, however, unfortunately, in this example, we do not have full database backup, but only do tablespace backup at different times.

Wrong database recovery method

If we try to use tablespace backup directly to restore the entire database, we will get the following error message:

Listing 1: error message about using tablespace backup to restore the entire database

 
 

  
  
db2 restore db test taken at 20060517135208  
  
  
SQL2560N The target database is not identical to the source database  
  
  
for a restore from a table space level backup.  
 
 

The preceding commands support the restoration of the database backed up by the complete database, but do not support tablespace-level database restoration.

Restore a database using tablespace backup

In DB2 V9, a new feature is provided, that is, the entire IBM DB2 database is quickly rebuilt through tablespace backup and logs, this function is implemented by adding the REBUILD option to the restore database command.

The following steps help us restore the TEST database using tablespace backup using the REBUILD option:

Step 1: Use tablespace backup to execute the restore database command with the REBUILD option to RESTORE the DATABASE.

Listing 2: restoring the TEST database using tablespace backup using the REBUILD Option

 
 

  
  
db2 restore db test rebuild with all tablespaces in database taken at 20060517135208 
 
 

In this step, we select a backup from several existing tablespace backup images for database recovery. Generally, we will select the tablespace Image recently backed up. This backup Image is called the Target Image ), it contains the latest tablespace backup, database configuration parameters, log sequences, and other important information required for restoring the TEST database. In fact, this "Target Image" can be any type of backup (full backup, tablespace backup, Incremental backup, online or offline backup ). In this example, the most recent backup image is TEST.3.DB2. node).catn).20060517135208.001. Therefore, we chose it as the "Target Image" for database recovery ".

After the preceding RESTORE command is executed, the structure of the TEST database will be rebuilt and restored. We can obtain the parameters of the IBM DB2 database, their backup history, and other information. If we issue the list history command (FOR example, list history all for test), we will get the following output FOR reference in listing 3 ).

Listing 3: Using list history to query database backup HISTORY

 
 

  
  
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID  
  
  
R D 20060519121107001 F 20060517135208  
  
  
Contains 1 tablespace(s):  
  
  
00001 USERSPACE3   
  
  
Comment: RESTORE TEST WITH RF   
  
  
Start Time: 20060519121107   
  
  
End Time: 20060519121108   
  
  
Status: A   
  
  
EID: 7 Location:   
  
  
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID  
  
  
R P 20060519121108001 F 20060515135047   
  
  
Contains 2 tablespace(s):  
  
  
00001 USERSPACE1   
  
  
00002 SYSCATSPACE  
  
  
Comment: RESTORE TEST WITH RF   
  
  
Start Time: 20060519121108   
  
  
End Time: 20060519121113   
  
  
Status: A  
  
  
EID: 8 Location:  
  
  
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID  
  
  
R P 20060519121113001 F 20060516135136  
  
  
Contains 1 tablespace(s):  
  
  
00001 USERSPACE2  
  
  
Comment: RESTORE TEST WITH RF  
  
  
Start Time: 20060519121113  
  
  
End Time: 20060519121114  
  
  
Status: A  
  
  
EID: 9 Location:  
  
  
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID  
  
  
R D 20060519121107 R S0000001.LOG S0000003.LOG 20060518135208  
  
  
Contains 4 tablespace(s):  
  
  
00001 USERSPACE3  
  
  
00002 USERSPACE2  
  
  
00003 USERSPACE1  
  
  
00004 SYSCATSPACE  
  
  
Comment: REBUILD TEST WITH RF  
  
  
Start Time: 20060519121107  
  
  
End Time: 20060519121115  
  
  
Status: A  
  
  
EID: 10 Location:  
 
 

As shown above, the list history command generates four output entries (EID 7-EID 10), which are related to the restoration of our database. The first entry, EID 7, contains the backup image made at. In this backup image, we only back up USERSPACE3. However, to review the commands we issued during database recovery, refer to listing 4.

Listing 4: Use the all tablespaces parameter to restore the database

 
 

  
  
db2 restore db test rebuild with all tablespaces in database taken at 20060517135208 
 
 

We use the all tablespaces parameter to restore all tablespaces. Therefore, DB2 uses the other backup images seen in list history to restore other TABLESPACES in the database. (Note that TEST.3.DB2 is used. node).catn).20060516135136.001 EID = 9 when the backup image is restored. Although the image includes USERSPACE2 and USERSPACE3 backups, DB2 only restored USERSPACE2 because USERSPACE3 has passed the updated backup image TEST.3.DB2. node).catn).20060517135208.001 is restored ).

After the preceding restoration, the tablespace is in the ROLL-FORWARD state. By running the list history command, we can see that all tablespaces are marked with rf, indicating that these tablespaces are in the ROLL-FORWARD state. In addition, all backup images must be stored in the backup path indicated by history file to ensure smooth recovery. Otherwise, DB2 will provide an error message indicating that the backup image cannot be found.

Step 2: Roll Forward the IBM DB2 database test using the rollforward database Command and the to end of logs option TO restore it TO the latest Point in Time ).

Listing 5: Roll the database to the latest synchronization time point

 
 

  
  
db2 rollforward db test to end of logs 
 
 

When all tablespaces are restored, they will be in the rollforward pending state. We need to use the database log and the rollforward command to roll forward the database and set the database to Normal) status.

To successfully complete the roll-forward operation, the database logs between the first time of the backup image and the last time must exist, it is used to roll the tablespace that is restored by backing up images at different time points to the same time point. In this example, logs from 20060515135047 to 20060517135208 must exist before the tablespace can be synchronized to the same time point. If we still want to roll back the database, we still need the log files from.

In this example, we assume that all these log files can be found in the directory specified by the LOGPATH database configuration parameters. If they are moved, you also need to specify the new location of these LOG files using the overflow log path option in the ROLLFORWARD command.

Step 3: run the rollforward database command to end the DATABASE rollback status.

Listing 6: ending the IBM DB2 database rollback command

 
 

  
  
db2 rollforward db test stop 
 
 

After the command is executed, the TEST database returns to the NORMAL state, so that you can use it normally.