Shulou(Shulou.com)06/01 Report--

Online backup and restore notes of DB2 database

Online incremental backup of database

As the general customer's production system has a large amount of data, frequent full backup of the database or tablespace is not feasible in spatial storage. DB2 supports two kinds of incremental backup, one is incremental backup (comulative backup), the other is differential backup (deltabackup). In general, incremental backups (including incremental and differential backups) contain in their backup image files only pages that have been updated since the last backup, in addition to the updated data and index pages, each incremental backup image also contains the initial database metadata (for example, database configuration, tablespace definition, database history, etc.) that is typically stored in the full backup image. The difference between an incremental backup and a differential backup is that it doesn't matter that the type of last backup is different. For example, an incremental backup only backs up pages that have changed since the last full backup. Differential backups backup changes since the last backup (which may be full backup, incremental backup, differential backup).

To make an incremental backup of the database, you need to turn on the TRACKMOD configuration parameter of the database, that is, change its value to on. The default is off. When this parameter value is set to on, the database manager tracks database revisions so that the backup utility can detect that those subsets of database pages must be checked by an incremental backup and may be included in the backup image.

Create the database:

Db2 create db mydb using codeset GB2312 territory CN pagesize 8192

Create a database table:

Db2 "create table T1 (id integer,name varchar (10))"

Insert content:

Db2 "insert into db2inst1.t1 values"

Turn on archive mode:

Db2 update database configuration for mydb using logarchmeth2 DISK:/home/db2inst1/logs/

Turn on incremental backup mode:

Db2 update database configuration for mydb using trackmod on / / enable incremental backup

Db2 get db cfg for mydb | grep-I trackmod / / check whether incremental mode is enabled

# db2stop force

# db2start

/ / restart the database before it takes effect after modifying the configuration parameters.

# db2 backup db mydb to / home/db2inst1/backup / / after starting the above parameters, the database is in backup pending status, and offline backup of the database is required, otherwise an SQL1116N error will be prompted.

Online full backup database:

$mkdir online

$cd online/

$db2 backup db mydb online include logs

Db2 backup db mydb online to / home/db2inst1/backup/online/ include logs

Insert the test table:

Perform an incremental backup:

Commands for db2 backup db mydb online incremental include logs / / incremental backup

Db2 backup db mydb online incremental to / home/db2inst1/online/ include logs / / specifies the directory where the backup files are stored.

Db2 backup db mydb online incremental delta include logs / / differential backup

Insert the contents of the test table again:

Db2 "insert into db2inst1.t2 values"

[db2inst1@zxt-02 online] $db2 backup db mydb online incremental to / home/db2inst1/online/ include logs

Perform an incremental backup again:

Note: (it can also be the same for differential backup)

If you want to view the historical backup and restore of the database, you can use the db2 list history backup all command. For backup image information, type N stands for online full backup, O for online incremental backup, E for online differential backup, type F for offline full backup, I for offline incremental backup, and D for speculative differential backup.

Online incremental restore:

If there are N backup images, it is common to perform 1 recovery to complete the entire recovery operation. During a manual restore, the user must manually issue a restore command for each image involved in the restore in the following order: last, first, second, third, and so on. and includes the last image (that is, perform the restore of the last one again).

First perform a restore on the last backup image, specifying the target image by using the taken at timestamp option of the restore database command. First go to / home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/, which is the active log directory of the sample database mydb. In this directory, delete all log files. Note that the production system cannot delete the activity log, otherwise the database will go down. This example is to demonstrate how to restore the database, delete the log and then perform the restore, in order to see more clearly how to use the log in the backup file to restore, so delete the previous activity log. Restore through the restore database command, as follows.

The first step of manual incremental restore:

Db2 restore db mydb incremental taken at 20151023140237 logtarget / home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/

Step 2 of manual incremental restore:

Db2 restore db mydb incremental taken at 20151023134213 logtarget / home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/

Step 3 of manual incremental restore:

Db2 restore db mydb incremental taken at 20151023135526 logtarget / home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/

Step 4 of manual incremental restore:

Db2 restore db mydb incremental taken at 20151023140237 logtarget / home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/

Restore completed, roll back:

Db2 rollforward db mydb to end of logs and stop

Db2 "rollforward db mydb to end of logs and stop overflow log path (/ home/db2inst1/log)" / / specify the log release directory

Note: if there is still a log in the database activity log directory during the rollback, the database restore program will scroll to the latest log date.

Db2 restore db mydb logs incremental taken at 20151023134213 logtarget / home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/ extract database log files from backup files.

The restore is complete at this point.

Online automatic incremental restore

If you specify the AUTOMATIC option at restore time, the database will be automatically restored. All you need to do is to perform a restore operation, and it is actually the database manager that automatically completes operations like restoring the database manually. For automatic incremental restore, issue the RESTORE command with the AUTOMATIC option only once when you specify the target image to use (the last image). DB2 then uses the database history to identify the remaining required backup images and restore them.

First go to the active log directory of the sample database. In this directory, delete all log files for the pair. Note that the production system cannot delete the activity log, otherwise the database will go down. This example is to demonstrate how to restore the database, delete the log and then perform the restore, in order to see more clearly how to use the log in the backup file to restore, so delete the previous activity log.

Restore the last incremental backup image (timestamp) first, specify the target image by using the TAKEN AT TIMESTAMP option of the RESETORE DATABASE command, and use the INCREMENTAL AUTOMATIC option to perform an automatic incremental restore.

Method 1:

Db2 restore db mydb incremental automatic taken at 20151023140237 / / automatically restore incremental backup files

Now that the restore is complete, you need to roll forward the sample database. In order to roll forward recovery, you need to restore the log files separately from each backup image. You can use the restore command to choose to restore only the log files saved in the backup image. To do this, you can use the LOGTARGET option of the RESTORE DATABASE command to specify the LOGS option. If the restore operation encounters any problems when restoring log files in this manner, the restore operation fails with an error.

Db2 restore db mydb logs incremental taken at 20151023134213 logtarget / home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/ extract database log files from backup files.

Roll forward log:

Db2 rollforward db mydb to end of logs and stop

Method 2:

Db2 restore db mydb incremental automatic from / home/db2inst1/backup/online/ taken at 20151030133636 logtarget / home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/

/ / use this command to automatically restore incremental backups to a specified point in time. There is no need to extract the database logs from each backup image separately for rollback, but directly rollback.

Roll forward log:

Db2 rollforward db mydb to end of logs and stop

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.