How does one perform the backup and recovery operations on the DB2 partitioned database?

Today, we will mainly describe the correct steps for backing up and recovering DB2 partitioned databases, we use examples to illustrate how to correctly operate a DB2 partitioned database that has enabled the rollback and recovery mode, and use commands such as BACKUP, RESTORE, and ROLLFORWARD to back up data.

Restore and roll-forward operations with the OVERFLOW option. For the partitioned database of DB2, some directly issued DB2 commands only apply to the current partition, BACKUP and recovery of BACKUP/RESTORE) belong to this type of commands. However, the data in the same database is distributed across multiple partitions.

Therefore, the backup and recovery operations must take into account the integrity and synchronization of data on each partition. Therefore, you can add the db2_all command before the DB2 command, so that DB2 commands can be executed on each partition of the database, without repeatedly issuing the same command for each partition.

In addition, the rollback and data recovery modes of databases are enabled in DB2, which uses the archive log method instead of the default loop LOG method. In this way, you can use the RESTORE command to RESTORE the backup of the database or tablespace, and then use the ROLLFORWARD command to roll back the transactions in the archive log, restore the transactions committed after the database backup time point to maximize the protection of database data.

Unlike the backup and recovery commands, the rollback command can only be run on the catalog partitions of the partitioned database to implement the rollback operation for each partition of the database.

The following describes how to back up, restore, and perform rollback in a partitioned database SAMPLE with the rollback and recovery mode enabled. Assume that the database is created on a server and has four hard disk data recovery partitions. The Cataloguing partition is partition 0.

I. Backup operations on the partitioned database:

As described above, the backup operation only applies to the current partition of the partitioned database, so you need to use the "db2_all" command to back up all partitions. Here, the online backup mode is used), that is:

 
 

  
  
db2_all "db2 backup db sample online" 
 
 

However, the preceding commands are used in serial mode for backup of each partition. To improve the performance of backup operations, there is also a method that enables Parallel intervals. That is to say, add the following option before the DB2 command of the preceding command to achieve parallel command:

<+ Partition Number <: indicates that subsequent commands act on the partition.

<-Partition Number <: indicates that subsequent commands act on partitions other than this partition.

| <-Partition Number <or <-Partition Number;: indicates that subsequent commands are used to recover data from other partitions except the partition, and are performed in parallel.

However, backup and recovery operations require exclusive partitioning. Therefore, before performing parallel backup operations on other partitions, you must complete backup operations on the partition. The method is as follows:

Db2_all "<+ 0 <db2 backup db sample online" -- backup of cataloguing partitions

Db2_all "| <-0 <db2 backup db sample online" -- Parallel backup of hard disk data recovery in other partitions

Or db2_all "<-0 <; db2 backup db sample online"

Note: After the backup of the partitioned database is complete, a backup image file is generated for each partition.

2. Restore A partitioned database:

Since backup and recovery operations only apply to the current database partition, they can also be divided into serial and parallel methods. To introduce the use of the OVERFLOW option in the previous Rollback Recovery, we will restore the SAMPLE database to a new database named SAMPNEW in the same instance. The command should be:

· Interval-based serial mode:

 
 

  
  
db2_all "db2 restore db sample into sampnew without prompting" 
 
 

Note: As it is a partitioned database, when the first partition is restored and the second partition is restored, the system displays the SQL2529W warning, indicating that the database already exists, whether to overwrite the data recovery prompt. In some platforms, such as Linux, this prompt will cause the partition database recovery operation to fail, for example:

 
 

  
  
$ db2_all "db2 restore db sample into sapmnew"  
  
  
DB20000I The RESTORE DATABASE command completed successfully.  
  
  
rhas3: db2 restore db sample into sapmnew completed ok  
  
  
SQL2529W Warning! Restoring to an existing database that is different from the backup image database,   
  
  
and the alias name "SAPMNEW" of the existing database do es not match the alias name "SAMPLE"   
  
  
of the backup image, and the database name "SAPMNEW" of the existing database does not match the database name "SAMPLE"   
  
  
of the backup image. The target database will be overwritten by the backup version.   
  
  
The Roll-forward recovery logs associated with the target database will be deleted.  
  
  
Do you want to continue ? (y/n) SQL2001N The utility was interrupted. The output data may be incomplete.  
  
  
rhas3: db2 restore db sample into sapmnew completed rc=4 
 
 

Therefore, we recommend that you add the "without prompting" option to the above hard disk data recovery command and Hunan SEO to avoid this prompt.

· Interval-based parallel mode

 
 

  
  
Db2_all "<+ 0 <db2 restore db sample into sampnew"
  
  
Db2_all "| <-0 <db2 restore db sample into sampnew without prompting"
  
  
Or db2_all "<-0 <; db2 restore db sample into sampnew without prompting"
 
 

Because the catalog partition is the first partition to be restored, you do not need to add the "without prompting" option.

Because the backup image file we use is generated through online backup, after the restoration operation is complete, the database will be in the roll-up and hold state, and archive logs must be rolled back through the roll-down operation, to cancel the roll-out pending state, so that the database is available.

Iii. rollback operations on the partitioned database:

Because the Log Path of the new database SAMPNEW does not contain the archive log file under the Log Path of the source SAMPLE database, before the rollback operation, you need to copy the archive log file of the SAMPLE to a specific path, and then use the OVERFLOW option to specify the path when issuing the rollback command, to replace the data recovery Log Path of SAMPNEW to provide the archive log files to be used for rollback.

To find the path for storing the archive logs of the source SAMPLE Database, run the following command:

 
 

  
  
db2_all "db2 get db cfg for sample" | grep "Path"  
  
  
Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/  
  
  
Path to log files = /home/db2inst1/db2inst1/NODE0001/SQL00001/SQLOGDIR/  
  
  
Path to log files = /home/db2inst1/db2inst1/NODE0002/SQL00001/SQLOGDIR/  
  
  
Path to log files = /home/db2inst1/db2inst1/NODE0003/SQL00001/SQLOGDIR/  
 
 

Assume that the archive log files of each partition are copied to/sampnew, because the rollback command can only be executed on the directory partition, therefore, the command to roll forward to the end of the daily hard disk data recovery log file in the cataloguing partition and end the rollback status should be written as follows:

 
 

  
  
db2 "rollforward db sampnew to end of logs and complete overflow log path  
  
  
/sampnew/NODE0000/SQL00001/SQLOGDIR,  
  
  
/sampnew/NODE0001/SQL00001/SQLOGDIR on dbpartitionnum 1,  
  
  
/sampnew/NODE0002/SQL00001/SQLOGDIR on dbpartitionnum 2,  
  
  
/sampnew/NODE0003/SQL00001/SQLOGDIR on dbpartitionnum 3,  
  
  
)" 
 
 

Note: For partition 0, the "on dbpartitionnum 0" clause cannot be used in the OVERFLOW option; otherwise, the following error occurs:

 
 

  
  
SQL0104N An unexpected token "on" was found following "<identifier>". Expected tokens may include: ")". SQLSTATE=42601 
 
 

The command syntax is incorrect.

So far, we have completed the DB2 partition database SAMPLE that has enabled the hard drive data recovery Rollback Recovery mode, using the BACKUP command for online BACKUP, use the RESTORE and ROLLFORWARD commands to RESTORE to a new database SAMPNEW. If you want to know