Split mirror and db2relocatedb – powerful db2 recovery options

by on April 12, 2013

All of us are familiar with backup/restore commands  and this is the most widely used method for db2 backup and recovery. But there are other ways you can use to backup and recover your data.

1)Advanced Copy Services(ACS)

2)Split mirror

3)db2relocatedb

Let me explain my experience with these utilities. We had a 4TB database and when we tried a redirected restore to move the database to new SAN it took us around 11 days to recover the database . But when we tried the same operation with db2relocatedb it only took us 1 day to complete the operation. So you can imagine the power of these utilities

Advanced Copy Services(ACS)

Advanced copy services enable you to use flash copying technology to backup/restore the data

To perform snapshot backup and restore operations, you need a DB2 ACS API driver for your storage device. Integrated into IBM® Data Server is a DB2 ACS API driver for the following storage hardware:

You can read more about this here:

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0052870.html

Split Mirror

Please read the below resources to get an overview:

http://www.ibm.com/developerworks/data/library/techarticle/dm-0508quazi/

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/t0006422.html

I am going to illustrate this with real time example. In this example we are going to backup from system A (source) and restore onto system B (target)

Basic flow:

1)      db2 set write suspend for database

2)      Split the mirror

3)      db2 set write resume for database

4)      Catalog the database on the target server

5)      Db2inidb to initiate the target

The db2inidb command initializes the split mirror so that it can be used:

  • As a clone database
  • As a standby database
  • As a backup image

Regardless of the variations on the split mirror process, all of the following must be split at the same time:

  • The entire contents of the database directory
  • All the tablespace containers
  • The local database directory
  • The active log directory, if it does not reside on the database directory

1)      db2 set write suspend for database. Issue this on the source system

db2 set write suspend for database

DB20000I  The SET WRITE command completed successfully.

2)Split the mirror. This method differs from vendor to vendor . Here I am using a simple tar copy method.

Tar USAGE:

To Tar:
tar –cvzf tarfilename foldername

Untar:
tar –xvzf tarfilename

Go to the source path and issue a tar. You have to refer to sysibmadm.dbpaths to obtain all the database paths that needs to be copied to the target system

/sourcepath/db2inst1 – Is the path where all the db files exist. In this example there is only one path and all the database files exist there.

tar -cvzf samparch.tar.gz /sourcepath/db2inst1

3)Set write resume. Once the copy is done you can issue write resume on the source system

db2 set write resume for database

DB20000I  The SET WRITE command completed successfully.

4)Move the tar file to the target directory on the target system and untar it in the desired path. Also catalog the database

Source path : /sourcepath/db2inst1

Destination path : /targetpath/test

—untar—-

cd to /targetpath/test

tar –xvzf samparch.tar.gz

–catalog–

db2 catalog db SAMPLE as SAMPLE on /targetpath/test

DB20000I  The CATALOG DATABASE command completed successfully.

DB21056W  Directory changes may not be effective until the directory cache is

refreshed.

5)Define the relocate config file(below is the config file for our example)

DB_NAME=SAMPLE

DB_PATH=/sourcepath/db2inst1/,/targetpath/test

LOG_DIR=/sourcepath/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/,/targetpath/test/db2inst1/NODE0000/SQL00001/SQLOGDIR/

INSTANCE=db2inst1,db2inst1

STORAGE_PATH=/sourcepath/db2inst1/,/targetpath/test/

6) Issue a initialization command

db2inidb SAMPLE as snapshot relocate using relocate.cfg

Relocating database…

Database relocation was successful.

DBT1000I  The tool completed successfully.

Please validate the new database and do a basic health check

Db2relocatedb

Db2relocatedb is a powerful tool that can be used to rename database , relocate tablespace or even relocate complete database

Please go through the information in the link below to begin with:

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0004500.html

1)Analyze your database paths

db2 -x “select type,path from sysibmadm.dbpaths”

Type can be :

Table 2. TYPE column values
Type value Description
TBSP_DEVICE Raw device for a database managed space (DMS) table space.
TBSP_CONTAINER File container for a DMS table space.
TBSP_DIRECTORY Directory for a system managed space (SMS) table space.
LOGPATH Primary log path.
LOGPATH_DEVICE Raw device for primary log path.
MIRRORLOGPATH Database configuration mirror log path.
DB_STORAGE_PATH Automatic storage path.
DBPATH Database directory path.
LOCAL_DB_DIRECTORY Path to the local database directory.

If you have automatic storage and want to know the paths used by your database

db2 “select * from sysibmadm.snapstorage_paths”

2)Preapre the cfg file for db2relocatedb

Various options ::

DB_NAME

Specifies the name of the database being relocated. If the database name is being changed, both the old name and the new name must be specified. This is a required field.

DB_PATH

Specifies the original path of the database being relocated. If the database path is changing, both the old path and new path must be specified. This is a required field.

INSTANCE

Specifies the instance where the database exists. If the database is being moved to a new instance, both the old instance and new instance must be specified. This is a required field.

NODENUM

Specifies the node number for the database node being changed. The default is 0.

LOG_DIR

Specifies a change in the location of the log path. If the log path is being changed, both the old path and new path must be specified. This specification is optional if the log path resides under the database path, in which case the path is updated automatically.

CONT_PATH

Specifies a change in the location of table space containers. Both the old and new container path must be specified. Multiple CONT_PATH lines can be provided if there are multiple container path changes to be made. This specification is optional if the container paths reside under the database path, in which case the paths are updated automatically. If you are changing more than one container where the same old path is being replaced by a common new path, a single CONT_PATH entry can be used. In such a case, an asterisk (*) can be used both in the old and new paths as a wildcard.

STORAGE_PATH

This is only applicable to databases with automatic storage enabled. It specifies a change in the location of one of the storage paths for the database. Both the old storage path and the new storage path must be specified. Multiple STORAGE_PATH lines can be given if there are several storage path changes to be made.

*** CFG contents *** (Config file used in our example)

DB_NAME=SAMPLE

DB_PATH=/sourcepath/db2inst7/db2inst7/NODE0000/SQL00001/

INSTANCE=db2inst7

STORAGE_PATH=/sourcepath/db2inst7,/targetpath/db2inst71

3) Stop the database and make sure nothing is running on the database

4) Copy the files recursively from source storage path to destination storage path. You can also use tar command as stated above in split mirror section

cp-rf present/directory /desire/directory

cp -rp /san01/db2inst7/ /san01/db2inst71/ (Do not ignore the backspace at the end)

Validate that all files are copied successfully.. You can compare the sizes and subfolders.

Do a du –sh * and compare the sizes

5) Execute db2relocatedb

db2relocatedb -f db2relocatedb_new.cfg

Files and control structures were changed successfully.

DBT1000I  The tool completed successfully.

6)Validate the database functionality

db2 “select * from sysibmadm.snapstorage_paths” – Validate if new paths appear

Validate all the tablespace container paths

Validate diaglog for errors

7) Remove/backup the old files after thorough testing

Leave a Comment

Previous post:

Next post: