888-685-3101, ext. 2

Rolling DB2 tablespace location change using HADR

by | May 16, 2013 | Blog, Databases | 0 comments

One of our Virtual-DBA clients has a small DB2 environment with version 9.7 on Linux and HADR connecting a primary and standby system.  When the application team created the database, it was created using Automatic Storage on the /home filesystem.  This will always happen when the default setting of DFTDBPATH in the DBM configuration file is set to the instance owner’s home unless the CREATE DATABASE command specifies a different location.
So, they had all tablespaces in the database residing in /home and it was quickly growing.   Here are the details of the project.
The Mission:
Move the database and all tablespace containers from /home to a new /db2data tablespace.   Do this while the application is online and the database is available.
The Plan:
Use HADR and Redirected Restores to move the database while the application is online and have no data loss.  First restore a copy of the Primary onto the Standby using a redirected restore to put the tablespace containers on the new filesystem.  Then sync up with HADR and Takeover HADR on the Standby to swap roles.  After the Primary has changed roles to Standby, stop HADR there.  Take a new online backup from the new primary and restore it on the new Standby using another redirected restore to the new filesystem.  Then start HADR again and let them sync.  Once they are in PEER state you can takeover again on the orginal Primary.  At this point both servers will have the database on the new filesystem.
Execution:

Server1 is the primary and Server2 is the standby.

1.  Online backup of the database on Server1.  This is a small database and it takes 2 minutes to backup.

  •  =>db2 backup db ITIMDB online compress

2.  Copy the backup image from Server 1 to Server 2

3.  From Server2,  generate a redirected restore script using the newly copied backup.

  • =>db2 restore db ITIMDB from /db2backups redirect generate script restore.ddl

4.  Edit the script to restore the db using the ON and DBPATH ON paramaters set to the new filesystem

5.  Stop HADR on the standby (Server2)

  • =>db2 deactivate db ITIMDB
  • =>db2 stop hadr on db ITIMDB

6.  Drop the database on the standby (Server2)

  • =>db2 drop db ITIMDB

7.  Restore the database by running the redirect restore script (restore.ddl) on Server2

  • =>db2 -tvf restore.ddl

8.  Once the restore finishes, edit the HADR settings on Server2

  • =>db2 update db cfg for ITIMDB using HADR_LOCAL_HOST SERVER2 HADR_REMOTE_HOST SERVER1

9.  Start HADR on the standby (Server2)

  • =>db2 start hadr on db ITIMDB as standby

10.  Once they are in PEER state, Takeover the database on Server2 so the same steps can take place on Server1.  Run this from Server2

  • =>db2 takeover HADR on db ITIMDB

11.  Now take an online backup of the database on Server2

  • =>db2 backup db ITIMDB online compress

12.  Copy the new backup from Server2 to Server1

13.  From Server1, generate a redirected restore script using the fresh backup.

  • =>db2 restore db ITIMDB redirect generate script restore.ddl

14.  Edit the script to restore the db using the ON and DBPATH ON paramaters set to the new filesystem.

15.  Stop HADR on the standby (Server1)

  • =>db2 deactivate db ITIMDB
  • =>db2 stop hadr on db ITIMDB

16.  Drop the database on the standby (Server1)

  • =>db2 drop db ITIMDB

17.  Restore the database by running the redirect restore script (restore.ddl)

  • =>db2 -tvf restore.ddl

18.  Once the restore finishes, edit the HADR settings

  • =>db2 update db cfg for ITIMDB using HADR_LOCAL_HOST server1 HADR_REMOTE_HOST server2

19.  Start HADR on the standby (Server1)

  • =>db2 start hadr on db ITIMDB as standby

20.  Once they are in PEER state, Takeover the database on Server1.

  • =>db2 takeover HADR on db ITIMDB
Now both Server1 and Server2 have their tablespace containers on the /db2data filesystem.  This was done online with no data loss!  Gotta love HADR.
We also had Alternate Client Reroute set on both the Primary and Standby so that applications would quickly know to connect the primary database server when we swapped roles with the TAKEOVER HADR command.
Keep in mind that if the application uses connection pooling, the app should be restarted after setting the Automatic Client Reroute settings.

Submit a Comment

Your email address will not be published. Required fields are marked *

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

Share This