Raghu On Tech

Pro tips on managing data at scale

How To Perform Db2 Alternate Restore Using NetBackup ?

by | Mar 20, 2018 | 5 comments

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.

5 Comments

  1. alex

    Hello Raghu

    While performing restore, when we are running the command :
    db2 set tablespace containers for 0 using “(path path>)”

    we are getting error :
    a connection to database cannot be made because a previous restore is incmplete or still in progress.

    We’ve checked but there is no restore process running.

    In db2diag.log I could find :
    no match for a database image file was found based on the source database alias and timestamp

    Can you help here ?

    Reply
    • Raghu

      Alex,

      Usually this means a previous backup has been incomplete or failed.
      Also if you are running a redirected restore make sure you run “restore command”, “set tablespace containers commands” and “restore continue” command from the same command line window or you will see these issues.

      If you are running on DB2 above V9.1, you should be able to use “generate script” option of the redirected restore command. You can simply edit the generated script per your target environment and run it against the target environment.

      To over come this issue, easy way is to simply drop the database and redo your redirect restore procedure.

      Thanks!
      Raghu.

      Reply
    • Jimesh Makwana

      Hi alex,

      Try to use below command

      db2 set tablespace containers for 0 using automatic storage

      Jimesh Makwana

      Reply
  2. alex

    Hello Raghu

    While performing restore, we are getting error :
    “A connection to database cannot be made because a previous restore is incomplete or still in progress.”

    I’ve checked and there is no process running. in db2diag.log, I can see error:
    “No match for a database image file was found based on the source database alias database-alias and timestamp timestamp provided

    Can you help here?

    Reply
  3. Jayden

    Hi Raghu,

    From this statement, “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.”

    In the db2.conf, i saw the 4 entries. However, all 4 CLIENT_NAME are mysource.example.com. Is that the case or should it be 2 for mysource.example.com and 2 for mytarget.example.com?

    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

    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

    Reply

Submit a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Shares
Share This

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close