Perfect exercises for online backup and recovery of IBM DB2 UDB!

The following articles mainly describe the actual online backup and recovery operations of IBM DB2 UDB. If you encounter online backup and recovery of IBM DB2 UDB in actual operations, however, you do not know how to perform the operation correctly, so the following articles must be good teachers and friends for you.

IBM, DB2IBM, practice, UDB, online

Why is Online Incremental Backup required?

Online means that other connections are allowed during backup, instead of stopping the database.

Incremental means that you do not need to back up a large database each time.

It also means that you can restore the database to the State before the crash, instead of the State during the last backup, minimizing data loss.

Set the database to support Online Incremental Backup

The Online Incremental backup of db2 requires the archive record type instead of the cyclic record type. The database created by db2 adopts the cyclic record type by default. There are two methods to set the archive record type:

Change logretain to recovery or on,

Change userexit to on

In this way, db2 logs will not be used cyclically, but will continue to increase, so that Incremental Backup can be performed and the database will be restored to the crash time point.

For incremental db2 backup, you need to set tracemod to on, so that the database will record the changed part pages on the physical page and Mark dirty.

The following is a script to set the testdb database to support online backup:

 
 

  
  
connect to testdb;  
  
  
update db cfg using logretain on;  
  
  
update db cfg using trackmod on;  
  
  
db2stop force;  
  
  
db2start; 
 
 

Generally, you should set mongologpath to back up the db2 log image:

Update db cfg using mirrologpath which you think is safe

How to perform online backup?

Backup db testdb online to backup path (full backup)

Backup db testdb online incremental to backup path (incremental backup)

Backup db testdb online incremental delta to backup path (delta backup)

Restore

Assume that we want to perform this task: Restore the online backup of the lw database to the f disk of the target machine and rename it loanmgr.

1. obtain detailed information about the backup file and prepare the file.

Create the "F: \ db2 backup data" folder to directly copy the directory structure and files automatically generated during online backup:

F: \ db2 backup data \ LW.0 \ DB2 \ NODE0000 \ CATN0000 \ 20070308 \ 085446.001

Command Line execution

Db2ckbkp-h F: \ db2 backup data \ LW.0 \ DB2 \ NODE0000 \ CATN0000 \ 20070308 \ 085446.001

Pay attention to the red part in the result. If you only have one file, create a directory structure and rename the file according to the red icon.

 
 

  
  
The proper image path would be:  
  
  
LW.0\DB2\NODE0000\CATN0000\20070308\085446.001 
 
 

2. Prepare to restore the lw database to the new database loanmgr (do not create loanmgr in advance)

Restore db lw from F: \ db2 backup data taken at 20070308085446 to f into loanmgr redirect

Result

SQL1277N recovery has detected that one or more tablespace containers are inaccessible, or they have been set to "mandatory"

Memory must be defined ".

The DB20000I restore database command is successfully completed.

 
 

  
  
set tablespace containers for 0 using (path 'e:\db2\ts0');  
  
  
set tablespace containers for 1 using (path 'e:\db2\ts1'); 
 
 

3. Create a tablespace Copy command and change the red number until an error such as "invalid tablespace ID" is executed. The tablespace is ready.

Use list tablespaces to check the current table space.

Start to recover. This process actually begins to write data to the tablespace:

 
 

  
  
restore db lw continue 
 
 

Note that the name of the source database is written here, instead of the name of the target database. Result:

The DB20000I restore database command is successfully completed.

4. Roll Forward

Run

Get db cfg for loanmgr

Note in the result:

Log File Path = F: \ DB2 \ NODE0000 \ SQL00001 \ SQLOGDIR \

Overflow Log Path (OVERFLOWLOGPATH) =

Mirror logpath = e: \ db2mongologpath \ NODE0000 \

First activity log file = S0001216.LOG

Copy the log file group to the red path. These logs should contain all the files after S0001216.LOG.

Rollforward db testdb to end of logs and complete

All completed ..