Use tablespace backup to quickly restore the IBM DB2 database

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.

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 a 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 takenat 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 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 database parameters, 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 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: commands for ending database rollback

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.

Scenario 2: Using tablespace backup to quickly reconstruct a database and restore important data
Through the above example, we know that DB2 V9 now supports the reconstruction and restoration of the entire database through the backup of different tablespaces at different times, this powerful new feature will undoubtedly inspire us to consider backup/recovery policies.

Next we will further discuss this feature. Let's go back to the previous example. We assume that the TEST database is an important database that records online transactions. The table spaces USERSPACE1 and USERSPACE2 store all the important data required by the online exchange, while the USERSPACE3 tablespace stores some historical data, which is mainly used for Historical queries. The data volume is large, and its importance is relatively low.

Restore the data of tablespace USERSPACE1 and USERSPACE2

Once our database system fails, if we need to restore all three tablespaces, the recovery speed will be slow due to the large amount of USERSPACE3 data. What we hope is that we can first quickly restore the data in the tablespace USERSPACE1 and USERSPACE2, and quickly restore online transactions. Then, at the right time, we can restore the historical data in the tablespace USERSPACE3. Next, let's take a look at how to implement our ideas.

Step 1: Issue the restore database command and the REBUILD option to tell DB2 to RESTORE the tablespace USERSPACE1 and USERSPACE2 first.

Listing 7: Restore tablespace USERSPACE1 and USERSPACE2 method 1

Db2 restore db test rebuild with tablespace (SYSCATSPACE, USERSPACE1, USERSPACE2)

Takenat 20060516135136

Although we want to restore only the tablespace USERSPACE1 and USERSPACE2, the system tablespace of SYSCATSPACE is required; otherwise, DB2 cannot work normally. The "Target Image" TEST.3.DB2. node).catn).20060516135136.001 specified in the preceding command contains the backup of the tablespace USERSPACE2 and USERSPACE3. This is the most recent backup image that includes the USERSPACE2 and USERSPACE3 tablespace backups. It should be noted that although 20060517135208 is a recent backup image, it does not include USERSPACE1, USERSPACE2, or SYSCATSPACE tablespace backup, so we will not use it for restoration.

The following command can achieve the same effect.

Listing 8: Restore tablespace USERSPACE1 and USERSPACE2 method 2

Step 2: Execute the rollforward database Command and the to end of logs option TO roll the database to the latest synchronization time point.

Listing 9: Roll back a database

Db2 rollforward db test to end of logs

Step 3: Issue the rollforward database command and STOP option to STOP the rollback.

Listing 10: Stopping database rollback

Db2 rollforward db test stop

Of course, you can also roll the database to a certain time point instead of the end of the log, but this time point must be a time point after the time point of your backup image. After the preceding command is executed, the database can be used normally, and USERSPACE1 and USERSPACE2 can also be accessed normally. USERSPACE3 is still in the restore pending state. You can recover it later.

Restore table space USERSPACE3 data

Step 1: Execute the restore database command to RESTORE the data of the tablespace USERSPACE3.

Listing 11: Restoring tablespace USERSPACE3

Db2 restore db test tablespace (USERSPACE3) taken at 20060517135208

Second, execute the rollforward database and to end of logs options TO roll forward the USERSPACE3 tablespace.

List 12: Roll Forward tablespace USERSPACE3

Db2 rollforward db test to end of logs tablespace (USERSPACE3)

Step 3: Issue the rollforward database Command and the STOP option to end the rollback of USERSPACE3.

Listing 13: ending the rollback of table space USERSPACE3

Db2 rollforward db test stop

Now, all four tablespaces of the TEST database can be used normally.

Through the two examples above, we learned how to reconstruct and restore the entire database through partial or full tablespace backup, or perform step-by-step reconstruction and restoration of the Database Based on the importance and priority of the data.

This feature effectively reduces the data backup time. We can select tablespaces of different importance for backup at different frequencies, rather than the entire database. This feature can also shorten the recovery time of the system. We can restore the database step by step based on the importance of data, without the need to restore the entire database at a time. Finally, it is worth noting that in order to use this good feature, database logs and table space backup in the SYSCATSPACE system are still crucial.

1. Steps for restoring a database from a DB2 backup
2. Oracle Database File recovery and backup ideas
3. General steps for MySQL backup and data recovery