Cross node DB2 Database recovery from TSM

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.

tsm cross node recovery

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..https://db2talk.wordpress.com/wp-includes/js/tinymce/plugins/wordpress/img/trans.gif

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

ServerName TSM_PROD_DB2
COMMMethod TCPip
TCPPort 1500
TCPServeraddress tsm02prod.example.com
PasswordAccess generate
NodeName prodclstr_db2

$ more /opt/tivoli/tsm/client/api/bin64/dsm.opt

ServerName TSM_PROD_DB2

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
ServerName TSM_DR_DB2

COMMMethod TCPip
TCPPort 1500
TCPServeraddress tsmdr.example.com
PasswordAccess generate
NodeName drclstr_db2

ServerName TSM_PROD_DB2
COMMMethod TCPip
TCPPort 1500
TCPServeraddress tsm02prod.example.com
PasswordAccess generate
NodeName prodclstr_db2

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:

ServerName      TSM_DR_DB2

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.

ServerName TSM_PROD_DB2

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

ServerName tsm_prod_db2

db2inst1@drlhost01:/opt/tivoli/tsm/client/api/bin64> more dsm.opt.dr

ServerName TSM_DR_DB2

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
export DSMI_DIR=/opt/tivoli/tsm/client/api/bin64
export DSMI_CONFIG=/opt/tivoli/tsm/client/api/bin64/dsm.opt.prod
export DSMI_LOG=$HOME/sqllib/db2dump

When I want DR server to point to DR TSM, userprofile file would look like shown below:

db2inst1@drhost01:~/sqllib> more userprofile
export DSMI_DIR=/opt/tivoli/tsm/client/api/bin64
export DSMI_CONFIG=/opt/tivoli/tsm/client/api/bin64/dsm.opt.dr
export DSMI_LOG=$HOME/sqllib/db2dump

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
DSMI_DIR=/opt/tivoli/tsm/client/api/bin64
DSMI_LOG=/db2home/db2inst1/sqllib/db2dump
DSMI_CONFIG=/opt/tivoli/tsm/client/api/bin64/dsm.opt.prod

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.

Pavan Kristipati.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s