Raghu On TechPro tips on managing data at scale
How To Configure DB2 To Use NetBackup ?
Configure Db2 LUW database and transactions log backups to use Veritas NetBackup Enterprise backup solution.
The task procedure in the next section makes following assumptions. As a result please read below bullet points carefully.
- You are using RedHat Enterprise Linux family related Operating System.
- You already installed NetBackup client on the Db2 server that you are planning to configure NetBackup.
- Installed NetBackup at “/usr/openv/netbackup“.
- Database name is assumed to be SAMPLE.
- Your NetBackup Administrator created a NetBackup policies of type Db2 and of names DB2_<APPLICATIONAME>_BACKUPS, DB2_<APPLICATIONNAME>_LOGS
- Where <APPLICATIONNAME> is the name of your application and an assumption is made that MAXIMA is my application name. So my policy names would become DB2_MAXIMA_BACKUPS and DB2_MAXIMA_LOGS.
- You already negotiated retention policies for backup and log policies with your NetBackup Administrator.
- You already requested your NetBackup Admin to add the Db2 server as the client for the NetBackup policies (database and logs).
Before you can backup your database and transaction logs using Veritas NetBackup solution, you need to perform some configuration with respect to NetBackup and DB2. Therefore, we will see the configuration in the initial part of this procedure.
Update NetBackup Configuration File:
Configure /usr/openv/netbackup/bp.conf, below is how my /usr/openv/netbackup/bp.conf file looks. If its not obvious, I modified the hostnames for my netbackup media and master servers. I also modified my Db2 server name.
SERVER = nbmaster SERVER = nbmaster.example.com SERVER = nbmedia1 SERVER = nbmedia1.example.com SERVER = nbmedia2 SERVER = nbmedia2.example.com SERVER = nbmedia3 SERVER = nbmedia3.example.com SERVER = nbmedia4 SERVER = nbmedia4.example.com SERVER = nbmedia5 SERVER = nbmedia5.example.com SERVER = nbmedia6 SERVER = nbmedia6.example.com SERVER = nbmedia7 SERVER = nbmedia7.example.com SERVER = nbmedia8 SERVER = nbmedia8.example.com SERVER = nbmedia9 SERVER = nbmedia9.example.com CLIENT_NAME = yourdb2server.example.com CONNECT_OPTIONS = localhost 1 0 2 EMMSERVER = nbmaster.example.com
I actually got the above information from my NetBackup administrator. In the above output, the first “SERVER” entry is the NetBackup master server name (both short and long hostnames). Rest of the 9 “SERVER” entries belong to media servers. NetBackup master server is the one that owns the scheduler, have job information, client information, policy information and access to backend database(s) to update any of the job/policy related information.
Where as NetBackup media server(s) are optional that means if your master server have tape drives assigned to it, it can handle everything but that solution is not very scalable. As a result most enterprises create multiple media servers with different types of storage attached to them. Media servers handling active backups usually have high performing storage, corporations might choose to keep last 7 days worth of backups on the high performing storage/disk and then archive them over to a cheaper storage such as tape devices etc. for the rest of their retention policy period. You may find more information about differences between NetBackup master and media servers at “Veritas Open Exchange (VOX)“, for e.g. below thread has some discussion going on
“CLIENT_NAME” parameter in “/usr/openv/netbackup/bp.conf” identifies the server that is being backup. This parameter name might cause some confusion to you but look it at from NetBackup server point of view. For NetBackup server our Db2 server is a client, since our client is using NetBackup server services for backup and restores.
“EMMSERVER” parameter identifies the emergency media server, this parameter kicks in when rest of all the media servers go unavailable. In our case its the master server. There could be other parameters that your NetBackup Admin might request you to add to “/usr/openv/netbackup/bp.conf” to tune performance or change other default behaviors of NetBackup clients interaction with the Server.
Update Db2 Configuration Files:
There are a few configuration files that you need to update on the Db2 end, for Db2 to be able to talk to NetBackup and vice versa. Even though file db2.conf is not strictly part of the Db2 software, it usually resides in the Db2 instance home directory.
Here is how my db2.conf looks. I will explain each of these entries below.
# $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. DATABASE SAMPLE OBJECTTYPE DATABASE BKUP_IMAGE_PERM ANY POLICY DB2_MAXIMA_BACKUPS SCHEDULE Default-Application-Backup ENDOPER # The following settings are used by NetBackup to backup/restore DB2 log files. # # Reminders: # 1) DB2 transcation logs can be backed up/restored using either # (a) DB2 User Exit Program # The specified policy must have a type of "Standard" (for UNIX DB2 # host) or "MS-Windows" (for Windows DB2 host). # # (b) LOGARCHMETH1 can be configured to use NetBackup. # (When LOGARCHMETH1 = "VENDOR:NetBackup for DB2 agent") # This option is possible only for versions 8.2 and above. # 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. DATABASE SAMPLE OBJECTTYPE ARCHIVE BKUP_IMAGE_PERM ANY POLICY DB2_MAXIMA_LOGS SCHEDULE Default-Application-Backup ENDOPER
Important: db2.conf file can act really goofy. I mean you can not leave any uncommented non-blank lines. It does not even like lines with spaces, tabs or any other white space characters. If you leave any uncommented non-blank lines your backups may not succeed.
Getting back to db2.conf file entries, there are only two blocks of entries that you should pay attention to. One for Db2 database backups and another one for Db2 transaction log backups. Consider the following block which specifies the policy and schedule for the database backups.
- Here DATABASE stands for name of the database you are trying to backup.
- OBJECTTYPE can be either DATABASE for database backup entry or ARCHIVE for transaction log backup entry as you can see in the second entry for Transaction logs.
- POLICY here stands for name of the Db2 NetBackup policy that is being used by database backups. Remember this policy needs to be created as Db2 type from NetBackup end or your backups will not succeed.
- SCHEDULE parameter here represents any backup schedule you might have provided to your NetBackup administrator. In our case we did not want NetBackup to trigger our backup jobs, so we left it as default (Default-Application-Backup) i.e. we will run the backup jobs from the Db2 server rather than from NetBackup Master server.
Update LOGARCHMETH1 To Use NetBackup Db2 Library:
Updating LOGARCHMETH1 to vendor library lets Db2 backup the archive log files to media server provided an ARCHIVE entry for the database exists in the db2.conf file. Your Db2 library for NetBackup should reside at “/usr/openv/netbackup/bin/nbdb2.so64” by default.
$ ll /usr/openv/netbackup/bin/nbdb2.so64 -rwxr-xr-x 1 root root 92448 Jan 11 2016 /usr/openv/netbackup/bin/nbdb2.so64 $ $ db2 -v "UPDATE DB CFG FOR SAMPLE USING LOGARCHMETH1 VENDOR:/usr/openv/netbackup/bin/nbdb2.so64" UPDATE DB CFG FOR SAMPLE USING LOGARCHMETH1 VENDOR:/usr/openv/netbackup/bin/nbdb2.so64 DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
Update LOGARCHOPT1 To Allow For Alternate/Cross Node Restores:
You should not need to do this technically, since you already updated db2.conf. However I encountered situations where value in the db2.conf was not picked up for some reason. Updating LOGARCHOPT1 to BKUP_IMAGE_PERM=ANY, lets you perform restore of Log backups taken on one Db2 instance/server to another Db2 instance/server.
$ db2 "update db cfg for sample using LOGARCHOPT1 BKUP_IMAGE_PERM=ANY" DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. SQL1363W One or more of the parameters submitted for immediate modification were not changed dynamically. For these configuration parameters, the database must be shutdown and reactivated before the configuration parameter changes $ db2 "get db cfg for sample"|grep -i logarchopt1 Options for logarchmeth1 (LOGARCHOPT1) = BKUP_IMAGE_PERM=ANY
Test Database Backup Using NetBackup For Db2 Library:
Your database backup command will slightly change to load NetBackup library for Db2. Lets have a go at it and see what happens.
$ db2 -v "backup db SAMPLE ONLINE LOAD /usr/openv/netbackup/bin/nbdb2.so64 OPTIONS BKUP_IMAGE_PERM=ANY compress include logs without prompting" backup db SAMPLE ONLINE load /usr/openv/netbackup/bin/nbdb2.so64 OPTIONS BKUP_IMAGE_PERM=ANY compress include logs without prompting Backup successful. The timestamp for this backup image is : 20180316113855
After viewing the backup command above, you are probably wondering what are “LOAD” and “OPTIONS” parameter. First of all let me explain what LOAD option tells Db2. “LOAD /usr/openv/netbackup/bin/nbdb2.so64” tell Db2 where to look for NetBackup library to communicate with the NetBackup server to send its database and log backups to.
Secondly, “OPTIONS BKUP_IMAGE_PERM=ANY” is very important if you intend to restore the backups taken on one machine/instance onto another machine/instance. By default NetBackup assigns read/write permissions to the group and owner of the user running the backup. If a different user or a user not belonging to the group that owns backup file tries to restore the backup you will encounter an error from NetBackup API. Please remember that the error message you might get is not very helpful.
Test Transaction Log Backup Using NetBackup For Db2 Library:
You can force an active log to get archived using archive log command as show below and see the log archival status in db2diag.log as shown below.
$ db2 "archive log for database sample" DB20000I The ARCHIVE LOG command completed successfully. $ db2 "get db cfg for sample"|grep -i first First active log file = S0000009.LOG First log archive method (LOGARCHMETH1) = VENDOR:/usr/openv/netbackup/bin/nbdb2.so64 2018-03-16-18.104.22.1685747-240 E34468993E436 LEVEL: Info PID : 8073 TID : 46912954689280 PROC : db2sysc 0 INSTANCE: traveler NODE : 000 DB : SAMPLE HOSTNAME: yourdb2server.example.com EDUID : 354 EDUNAME: db2logmgr (SAMPLE) 0 FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3108 DATA #1 : <preformatted> Started archive for log file S0000008.LOG. 2018-03-16-22.214.171.1241010-240 I34469430E527 LEVEL: Info PID : 8073 TID : 46912954689280 PROC : db2sysc 0 INSTANCE: traveler NODE : 000 DB : SAMPLE HOSTNAME: yourdb2server.example.com EDUID : 354 EDUNAME: db2logmgr (SAMPLE) 0 FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3180 DATA #1 : <preformatted> Completed archive for log file S0000008.LOG to VENDOR chain 0 from /home/db2iadm1/traveler/traveler/NODE0000/SQL00001/LOGSTREAM0000/.
Verify Your Backups:
You should be able to verify that the backup has been completed successfully from both Db2 and NetBackup end. On Db2 end you can use “list history” command. Where as on the NetBackup side you can view the log file under “/usr/openv/netbackup/logs/user_ops/dbext/logs“
$ db2 list history backup since 20180316 for db sample Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ -------------- B D 20180316113855002 N O S0000001.LOG S0000001.LOG ---------------------------------------------------------------------------- Contains 7 tablespace(s): 00001 SYSCATSPACE 00002 USERSPACE1 00003 IBMDB2SAMPLEREL 00004 IBMDB2SAMPLEXML 00005 SYSTOOLSPACE 00006 INDEX_TS 00007 TS_16K ---------------------------------------------------------------------------- Comment: DB2 BACKUP SAMPLE ONLINE Start Time: 20180316113855 End Time: 20180316113910 Status: A ---------------------------------------------------------------------------- EID: 16 Location: /usr/openv/netbackup/bin/nbdb2.so64
Below are the contents of log file on the NetBackup side. It appears that NetBackup creates a log file for each backup operation i.e. one log file for database backup and a log file every time a transaction log gets archived. You can identify the log file of interest with the backup timestamp. As you can see it is obvious that our Db2 database backup completed successfully.
# pwd /usr/openv/netbackup/logs/user_ops/dbext/logs # ls -altr total 60 drwxrwxrwx 3 traveler db2iadm1 4096 Mar 16 11:38 .. -rw-r--r-- 1 traveler db2iadm1 1411 Mar 16 11:38 12226.0.1521214718 -rw-r--r-- 1 traveler db2iadm1 1411 Mar 16 11:39 21235.0.1521214735 -rw-r--r-- 1 traveler db2iadm1 1411 Mar 16 11:39 21199.0.1521214742 -rw-r--r-- 1 traveler db2iadm1 1411 Mar 16 11:39 21235.0.1521214744 -rw-r--r-- 1 traveler db2iadm1 1411 Mar 16 11:39 21199.0.1521214750 -rw-r--r-- 1 traveler db2iadm1 1411 Mar 16 13:46 30501.0.1521222359 -rw-r--r-- 1 traveler db2iadm1 1411 Mar 16 13:46 30501.0.1521222374 -rw-r--r-- 1 traveler db2iadm1 1411 Mar 16 13:46 30501.0.1521222379 -rw-r--r-- 1 traveler db2iadm1 1411 Mar 16 13:46 30501.0.1521222385 -rw-r--r-- 1 traveler db2iadm1 1411 Mar 16 13:46 30501.0.1521222391 -rw-r--r-- 1 traveler db2iadm1 1411 Mar 16 13:46 30501.0.1521222396 -rw-r--r-- 1 traveler db2iadm1 2196 Mar 16 16:48 30501.0.1521230388 drwxrwxrwx 2 traveler db2iadm1 4096 Mar 16 17:34 . -rw-r--r-- 1 traveler db2iadm1 1411 Mar 16 17:34 8727.0.1521236053 # cat 12226.0.1521214718 Backup started Fri Mar 16 11:38:38 2018 11:38:49 Initiating backup 11:38:49 INF - Starting bpbrm 11:38:50 INF - Data socket = nbmedia5.IPC:/usr/openv/var/tmp/vnet-21187521214730458685000000124-AxH7GM;3d4edbd3f293443f807fd16b2d66b6fe;11;900 11:38:50 INF - Name socket = nbmedia5.IPC:/usr/openv/var/tmp/vnet-21188521214730541690000000124-M1H1TM;da6b08d0ff599cdf19c00ba8000fc67c;11;900 11:38:50 INF - Job id = 11572629 11:38:50 INF - Backup id = yourdb2server.example.com_1521214729 11:38:50 INF - Backup time = 1521214729 11:38:50 INF - Policy name = DB2_MAXIMA_BACKUPS 11:38:50 INF - Snapshot = 0 11:38:50 INF - Frozen image = 0 11:38:50 INF - Backup copy = 0 11:38:50 INF - Master server = nbmaster 11:38:50 INF - Media server = nbmedia5 11:38:50 INF - Multiplexing = 0 11:38:50 INF - New data socket = nbmedia5.IPC:/usr/openv/var/tmp/vnet-21186521214730372664000000124-4SpKtM;ce61048e8788ec55628ce7c182a4c155;11;900 11:38:50 INF - Use shared memory = 0 11:38:50 INF - Compression = 0 11:38:50 INF - Encrypt = 0 11:38:50 INF - Keep logs = 7 11:38:50 INF - Client read timeout = 7200 11:38:50 INF - Media mount timeout = 0 11:38:53 INF - Beginning backup on server nbmedia5 of client yourdb2server.example.com 11:38:54 INF - Server status = 0 11:38:54 INF - Backup by traveler on client yourdb2server.example.com using policy DB2_MAXIMA_BACKUPS, sched Default-Application-Backup:the requested operation was successfully completed
In conclusion, this blog post covers some basic steps you need to perform to configure your Db2 databases on Linux to use Veritas NetBackup for your Db2 backup and restores.
However, there is chance that you may run into issues while configuring NetBackup for Db2. If that is the case you may want to check out below blog post on troubleshooting NetBackup issues with Db2.