Database Recovery Using Redirected Incremental Restore

Roman Melnyk (mailto:roman_b_melnyk@hotmail.com), Senior Member of the DB2 Information Development team, IBM Toronto Lab

Roman B. Melnyk , Ph.D., is a senior member of the DB2 Information Development team, specializing in database administration and DB2 utilities. During more than eight years at IBM, Roman has written numerous DB2 books and other related materials. Roman recently coauthored DB2: The Complete Reference (Osborne/McGraw-Hill, 2001), DB2 Fundamentals Certification for Dummies (Hungry Minds, 2001), and DB2 for Dummies (IDG Books, 2000). You can reach him at roman_b_melnyk@hotmail.com .

Summary: How do you recover your database to a new server when no full backup image representing the current state of the database exists? This article gives you a strategy for recovery from disaster.

Tag this!

Date: 05 Dec 2002
Level: Introductory
Activity: 405 views

This article is written for IBM® DB2® Universal Database^TM for Linux, UNIX®, and Windows®

In a previous article (DB2 Basics: Cloning DB2 Databases Using Redirected Restore), I outlined the precise steps that you need to take to restore a DB2 database on a different machine, using a redirected restore operation. A redirected restore operation consists of a two-step database restore process with an intervening table space container definition step. This is a great way to clone a database using a full database backup image. But what about the case in which a database must be restored on a different server, but no full backup image representing the current state of the database exists?

Suppose your database server is destroyed by fire. Luckily, you have a good backup strategy in place. This strategy includes remote storage of backup images and recovery logs. The set of backup images that you have at your disposal includes full database backup, online incremental backup, and online incremental delta backup images. Your database is enabled for rollforward recovery (the logretain database configuration parameter is set to ON) and incremental backup (the trackmod database configuration parameter is set to ON), and you have the logs that contain any transactions that occurred during and after the last backup operation.

To recover this database on another server, you will have to carry out a redirected incremental database restore operation, followed by a database rollforward operation. A redirected incremental restore operation is a redirected restore operation that is applied to a set of incremental backup images. An incremental backup image contains all changes made since the last full backup operation. An incremental delta backup image contains all changes made since the last backup operation, be it full or incremental. For more information about incremental backup operations, see the article Using DB2 Incremental Backup. During a database rollforward operation, transactions that are not part of any database backup image (but that are recorded in the recovery logs) are reapplied against the database following the successful completion of required database restore operations.

The following (Windows) example shows you exactly how to successfully perform a redirected incremental restore and rollforward operation. Instead of restoring backup images from another server, I will show you how to recover a database from a different instance. The principle is the same, and the details are identical.

The source instance in this example is called PROD. The target instance is called MYINST. We are going to use the SAMPLE database that ships with DB2. The SAMPLE database in this case will be created on drive D.

Create the SAMPLE database on PROD, enable it for rollforward recovery and incremental backup, and then create a full database backup image:
set DB2INSTANCE=PROD db2start db2sampl db2 update db cfg for sample using logretain on db2 update db cfg for sample using trackmod on db2 backup db sample
The timestamp for this image is 20021022205203, and the image is located in D:\SAMPLE.0\PROD\NODE0000\CATN0000\20021022. DB2 creates this subdirectory tree off of the directory from which the BACKUP DATABASE command is issued. The subdirectory names represent the following:
<database_alias.backup_type>\<instance_name>\<node_number>\<catalog_node_number>\<date>
Inside this subdirectory tree, there will be a file (205203.0) representing the actual backup image. The file name represents the time at which the backup was taken.

Note: If you were relocating the backup image to another machine, create the above path on the target machine (substituting the appropriate target instance name for PROD) and copy the backup image to that directory.

Connect to the SAMPLE database and apply some transactions against one of its tables (SALES, for example), then invoke an online incremental backup operation. (An online backup operation permits concurrent database access by other applications.)

 
db2 connect to sample 
db2 insert into sales values ('10/21/2002','GOUNOT','Ontario-South',3) 
db2 insert into sales values ('10/23/2002','GOUNOT','Ontario-North',7) 
db2 insert into sales values ('10/24/2002','GOUNOT','Quebec',4) 
db2 insert into sales values ('10/25/2002','GOUNOT','Manitoba',2) 
db2 backup db sample online incremental

The timestamp for this image is 20021022205237. There are now two backup images inside the subdirectory tree.

Connect to the SAMPLE database and apply additional transactions against the SALES table. Invoke an online incremental delta backup operation:

 
db2 connect to sample 
db2 insert into sales values ('10/28/2002','LEE','Ontario-South',5) 
db2 insert into sales values ('10/28/2002','LEE','Ontario-North',1) 
db2 backup db sample online incremental delta

The timestamp for this image is 20021022205302. There are now three backup images inside the subdirectory tree (full, incremental, and incremental delta).

Connect to the SAMPLE database and apply some more transactions against the SALES table:

 
db2 connect to sample 
db2 insert into sales values ('10/31/2002','LUCCHESSI','Ontario-South',9) 
db2 insert into sales values ('10/31/2002','LUCCHESSI','Quebec',2) 
db2 insert into sales values ('10/31/2002','LUCCHESSI','Manitoba',7) 
db2 connect reset

We will need the transaction logs, as well as the backup images, to recover the SAMPLE database on MYINST. If necessary, verify the location of these logs:

 
db2 get db cfg for sample

Output from this command includes the path to the log files.

Start MYINST and then invoke the first step of a redirected restore operation.
set DB2INSTANCE=MYINST db2start db2 restore db sample incremental automatic from d taken at 20021022205302 to d redirect
The FROM parameter must be specified if the RESTORE DATABASE command is invoked from a directory other than the one that contains the backup image. The TO parameter specifies the target database directory (only the drive letter is specified on Windows operating systems). If you specify AUTOMATIC, you do not have to issue a separate RESTORE DATABASE command for every backup image in the set of images (full and incremental) required to restore the database.
Define new table space containers for the three default table spaces that are associated with the restored database (SYSCATSPACE, TEMPSPACE1, and USERSPACE1):
db2 set tablespace containers for 0 using (path 'd:\ts0con1') db2 set tablespace containers for 1 using (path 'd:\ts1con1') db2 set tablespace containers for 2 using (path 'd:\ts2con1')
You can use the LIST TABLESPACES SHOW DETAIL command to get information about all of the table spaces in the source database; this will help you ensure that containers have been set for all of the table spaces in the target database.
Complete the redirected restore operation:
db2 restore db sample continue
The database is in rollforward pending state, and must be rolled forward before it can be accessed.
Determine the log path for the SAMPLE database on MYINST, and copy the logs from the SAMPLE database on PROD to this location:
db2 get db cfg for sample copy PROD\NODE0000\SQL00001\SQLOGDIR\*.* D:\MYINST\NODE0000\SQL00002\SQLOGDIR
Output from the GET DATABASE CONFIGURATION command includes the path to the log files.
Complete the database recovery operation by rolling the transaction logs forward to capture all changes made to the database after the last backup operation:
db2 rollforward db sample to end of logs and stop

Verify that the restored database has been cataloged properly, that the table space containers that you have defined for it are in fact associated with the restored database, and that all of the transactions against the SAMPLE database on PROD are reflected in the recovered database on MYINST:

 
db2 list db directory 
db2 connect to sample 
db2 list tablespace containers for 0 
db2 list tablespace containers for 1 
db2 list tablespace containers for 2 
db2 select * from sales 
db2 connect reset 
db2 terminate 
db2stop 
set DB2INSTANCE=PROD 
db2stop

Resources

Incremental backup and restore, see the article Using DB2 Incremental Backup by Miro Flasza, Dale McInnis, and Blair Adamache.
DB2 backup and restore operations, see David Kline's article Production to Development: Moving Databases.
Redirected restore operations, see DB2 Basics: Cloning DB2 Databases Using Redirected Restore by Roman B. Melnyk.
Redirected restore scripts in an AIX® environment, see the article DB2 Redirected Restore Scripts by Mark Mulligan.

About the author

Comments (Undergoing maintenance)

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/

SITE_ID=1

Zone=Information Management

ArticleID=14254

ArticleTitle=Database Recovery Using Redirected Incremental Restore

publish-date=12052002

author1-email=mailto:roman_b_melnyk@hotmail.com

author1-email-cc=