Task Description:

You need to restore a Db2 database backup and archival logs backed up on source Db2 instance/server on to a different target Db2 instance/server, how would you go about it ?

Task Assumptions:

• Host name of the source Db2 server is mysource.example.com. Source Instance name is srcinst1.
• Host name of the target Db2 server is mytarget.example.com. Target Instance name is trginst1.
• Name of the NetBackup Db2 policy is DB2_MAXIMA_BACKUPS

Task Prerequisites:

• Acquire sudo access or make arrangements with some one in your organization to update “/usr/openv/netbackup/bp.conf“.
• Ensure that the database backup on source database is taken with option BKUP_IMAGE_PERM=ANY. If not your target server/instance will not be able to read the database backup image.
• Ensure that LOGARCHOPT1 parameter on source database was set to BKUP_IMAGE_PERM=ANY. If not your target server/instance will not be able to read transaction log files for rollforward recovery.
• Confirm that “/usr/openv/netbackup/db/altnames/No.Restrictions” exist on the NetBackup master server.

Task Procedure:

Source side:

You can see what backup files are available for you to restore using the bplist command on the source. If you run the below command please note that it will use the CLIENT_NAME from /usr/openv/netbackup/bp.conf and not the one from db2.conf under instance home directory.

/usr/openv/netbackup/bin/bplist -l -t 18 -k DB2_MAXIMO_BACKUPS -R /
 -l specifies that a search is case insensitive
 -t specifies the type of policy, 18 is DB2 policy
 -k specifies the policy name
 -R recursively lists all the backed up files upto n levels, where n is 999 by default.

Above command will produce the output similar to below output

-rw-rw-r-- srcinst1 srcinstg 50364416 May 17 09:03 /DB2/SAMPLE/node0000/20160517120259/SAMPLE.0.srcinst1.node0000.0.20160517120259.1

Target side:

Only changes you need to make on the target is to db2.conf under the target instance home directory. Following are the changes that needs to be made.

• Add an entry stating that the restore we are about to perform is alternate restore.
• There should be a block entry for source database, target database, source log files and target log files. Here is where things get tricky, you should absolutely add two entries one for source and another one for target even if the database names are same and entries look identical.
• Modify the CLIENT_NAME in side db2.conf to point to the source database server. DO NOT modify the CLIENT_NAME property in /usr/openv/netbackup/bp.conf or other Db2 instances using the bp.conf will get impacted during this restore process.

Below is how db2.conf should look like after the edits.

$ cat db2.conf

# $Header: db2.conf,v 1.6 2009/08/26 15:58:31 $
#
# ***************************************************************************
# * $Copyright: Copyright 2013 Symantec Corporation, All Rights Reserved $ *
# ***************************************************************************
#
# The following settings are used by NetBackup to backup/restore a DB2 database.
#
# Reminders:
# 1) The specified policy must have a type of "DB2".
# 2) The specified schedule must be defined for this policy.
# 3) The DB2 database host must be entered as a client for this policy.



OBJECTTYPE ALTERNATE
SRCINST srcinst1
SRCALIAS SAMPLE
DESTINST trginst1
DESTALIAS SAMPLE
ENDOPER



DATABASE SAMPLE
OBJECTTYPE DATABASE
POLICY DB2_MAXIMA_BACKUPS
SCHEDULE Default-Application-Backup
CLIENT_NAME mysource.example.com
ENDOPER



DATABASE SAMPLE
OBJECTTYPE DATABASE
POLICY DB2_MAXIMA_BACKUPS
SCHEDULE Default-Application-Backup
CLIENT_NAME mysource.example.com
ENDOPER




# --------------------------------------------------------------
# LOGARCHMETH1 = "VENDOR.." parameter for versions 8.2 and above
# Policy is of type DB2.
# --------------------------------------------------------------
#
# Comment the above DB2 User Exit section when using LOGARCHMETH1 = "VENDOR.."
#


DATABASE SAMPLE
OBJECTTYPE ARCHIVE
POLICY DB2_MAXIMA_BACKUPS
SCHEDULE Default-Application-Backup
CLIENT_NAME mysource.example.com
ENDOPER


DATABASE SAMPLE
OBJECTTYPE ARCHIVE
POLICY DB2_MAXIMA_BACKUPS
SCHEDULE Default-Application-Backup
CLIENT_NAME mysource.example.com
ENDOPER

Restoring The Database:

If the tablespace container definitions are same between both source and target database, you can perform a restore operation normally. Otherwise you need to perform redirected restore operation as shown below.

When you do not specify a timestamp during a restore operation then the latest backup image on the media storage will be used.

db2 -v "restore db SAMPLE load /usr/openv/netbackup/bin/nbdb2.so64 redirect generate script sample_redirect_restore.sql"

DB20000I  The RESTORE DATABASE command completed successfully.

Now edit the file generated by the generate script option of redirected restore operation to update your tablespace containers paths or  Storage group paths or Automatic storage paths or Database name or NEWLOGPATH etc.

$ db2 -tvf sample_redirect_restore.sql

UPDATE COMMAND OPTIONS USING S ON Z ON SAMPLE_NODE0000.out V ON
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.


SET CLIENT ATTACH_DBPARTITIONNUM  0
DB20000I  The SET CLIENT command completed successfully.


SET CLIENT CONNECT_DBPARTITIONNUM 0
DB20000I  The SET CLIENT command completed successfully.


RESTORE DATABASE SAMPLE LOAD '/usr/openv/netbackup/bin/nbdb2.so64' OPEN 1 SESSIONS TAKEN AT 20160518020143 ON '/db2data/trginst1' DBPATH ON '/db2data/trginst1' INTO SAMPLE REDIRECT
SQL1277W  A redirected restore operation is being performed.  Table space configuration can now be viewed and table spaces that do not use automatic storage can have their containers reconfigured.
DB20000I  The RESTORE DATABASE command completed successfully.


RESTORE DATABASE SAMPLE CONTINUE
DB20000I  The RESTORE DATABASE command completed successfully.

Now perform the rollforward to end of the log files.

$ db2 "rollforward db SAMPLE to end of logs and stop"

                                 Rollforward Status

 Input database alias                   = SAMPLE
 Number of nodes have returned status   = 1
 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0003890.LOG - S0003898.LOG
 Last committed transaction             = 2016-05-18-06.05.02.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.

To conclude, when you are using NetBackup as your storage solution for backups and you need to perform a restore of database from one server to another server, you need to use Alternate restore even though the name of the database is same and the tablespace container paths do not change. To sum it up, the NetBackup term Alternate restore is not equivalent to redirected restore on the Db2 end.

Please leave a comment if you have any questions related to this topic.