In this blog post, I am going to share how to get ready for what I call a cross node database recovery from TSM. Cross node database recovery from TSM involves executing a series of steps to be able to access from (say) DR database server the database backups and logs that are on TSM before recovery operation can begin. These backups and logs actually would have originated from a different (say Production) database server.
As DBAs we are sometimes asked to execute a test DR exercise. Assume that your data warehouse or non-critical OLTP database does not have a hot DR solution. That means standing up database on DR servers actually could involve executing restore and rollforward operations. If the database backup destination is local disk, then you are looking to either FTP’ing the backup (works fine if it is a small backup) or mounting the file system (that has backup) as onto the DR server. However, if the db backup storage is an external entity like IBM’s TSM (Tivoli Storage Manager), then you have to follow a specific set of steps so that the DR server could actually identify database backups and logs. As discussed above, these backups originally originated from Production database (and hence in theory a different database — remote to the DR server) server.
This blog outlines these series of steps / commands that need to happen / run before you actually can execute restore and rollforward operations but here is a quick background of how DB2 actually identifies which TSM server to backup to..
On database server, there are two files dsm.sys and dsm.opt that have TSM server configuration in them. The location of these files (in most cases) is either “/opt/tivoli/tsm/client/api/bin64/” or “/usr/tivoli/tsm/client/api/bin64/”
Sample files on PROD could be:
$ more /opt/tivoli/tsm/client/api/bin64/dsm.sys
$ more /opt/tivoli/tsm/client/api/bin64/dsm.opt
On the DR server (say drhost01), you could (needs root authority) simply add one more stanza (about Prod TSM Info) in dsm.sys file. You do not have to delete/ modify existing stanza if any.
Example is shown below:
db2inst1@drhost01:/opt/tivoli/tsm/client/api/bin64> more dsm.sys
The stanza that gets picked up from dsm.sys is actually determined by contents in dsm.opt file. For example if dsm.opt file has:
in it, then the DR TSM would be the active one and hence no Production backups would be listed when you issue ‘db2adutl query’ command.
Modifying dsm.opt file to “Production TSM” servername (shown below) is what needs to be done to be able to list production backups and logs using ‘db2adutl query’ command. You might only want to have one line in dsm.opt file to reflect the stanza that needs to be picked up from dsm.sys file.
However, what I do is to actually maintain 2 files named dsm.opt.prod and dsm.opt.dr. I have my UNIX Admin (requires root) list TSM Prod server name in dsm.sys.prod and TSM DR server name listed in dsm.sys.dr.
Here is an example:
db2inst1@drhost01:/opt/tivoli/tsm/client/api/bin64> more dsm.opt.prod
db2inst1@drlhost01:/opt/tivoli/tsm/client/api/bin64> more dsm.opt.dr
The advantage with this approach is that I do not have to have “root” authority every time I want to switch from PROD to DR and back.
But how does actually DB2 know which file to pick up? This is where userprofile located at $HOME/sqllib ($HOME of instance owner) plays an important role.
When I want DR server to point to PROD TSM, userprofile file would look like shown below:
db2inst1@drhost01:~/sqllib> more userprofile
When I want DR server to point to DR TSM, userprofile file would look like shown below:
db2inst1@drhost01:~/sqllib> more userprofile
All that needs to be done after making change (requires instance owner authority) as mentioned above is to execute .profile which in turn should execute db2profile which in turn executes userprofile. Easy way to reflect changes would be to logout and log back in as instance owner.
How to check if you are pointing to TSM Prod or TSM DR server?
If you’re pointing to PROD TSM, “env” (environment) variable DSMI_CONFIG would indicate that.
db2inst1@drldwadm01:~> env | grep -i dsmi
You could then list production backups and logs using ‘db2adutl’ command. Sample is shown below:
db2inst1@drhost01:~> db2adutl query full database proddb
Query for database PRODDB
Retrieving FULL DATABASE BACKUP information.
1 Time: 20140317145106 Oldest log: S0000429.LOG DB Partition Number: 0 Sessions: 2
2 Time: 20140317145106 Oldest log: S0000188.LOG DB Partition Number: 1 Sessions: 2
3 Time: 20140317145106 Oldest log: S0000187.LOG DB Partition Number: 2 Sessions: 2
4 Time: 20140317145106 Oldest log: S0000188.LOG DB Partition Number: 3 Sessions: 2
5 Time: 20140317145106 Oldest log: S0000188.LOG DB Partition Number: 4 Sessions: 2
6 Time: 20140317145106 Oldest log: S0000158.LOG DB Partition Number: 5 Sessions: 2
7 Time: 20140317145106 Oldest log: S0000159.LOG DB Partition Number: 6 Sessions: 2
8 Time: 20140317145106 Oldest log: S0000158.LOG DB Partition Number: 7 Sessions: 2
9 Time: 20140317145106 Oldest log: S0000158.LOG DB Partition Number: 8 Sessions: 2
10 Time: 20140317145106 Oldest log: S0000158.LOG DB Partition Number: 9 Sessions: 2
11 Time: 20140317145106 Oldest log: S0000158.LOG DB Partition Number: 10 Sessions: 2
12 Time: 20140317145106 Oldest log: S0000159.LOG DB Partition Number: 11 Sessions: 2
13 Time: 20140317145106 Oldest log: S0000160.LOG DB Partition Number: 12 Sessions: 2
Once you are able to list the database backups and logs using ‘db2adutl’ command, it is evident that DR server is able to talk to PROD TSM server. Next steps would be to perform restore and rollforward. Few scenarios have been covered in a series of blog posts here, here and here. These detail tablespace recovery but could be good reads to grasp concept of restore and rollforward in a DPF environment.
Thanks for reading. Questions / comments are welcome.