• Table of contents
• Database Mirroring (DB2) on remote site
  • Introduction
  • Full Database Backup + Transaction Logs
  • Test Tools

Database Mirroring (DB2) on remote site¶

Introduction¶

To mirror production database(s) to remote site, there are a number of approaches:

1. Use (full) database backup
2. Use (full + cumulative) database backup
3. Use (full + delta) database backup
4. Use (full + cumulative+ delta) database backup - not recommended because it is too complex
5. Use (full database backup + transaction logs)

the approach 1-4 are described in Using DB2 incremental backup

We will only discuss about the last approach, full + transaction logs in the coming sections. Before that, let's do a quick compare for these approaches:

	full	full + cumulative	full + delta	full + cumulative + delta	full + transaction logs
Backup Image Size	Biggest	Small	Smallest	In between Small and Smallest	Smaller
Backup Time	Longest	Depends on how the data is stored across tablespaces. If all data is stored in one tablespace then it won't make any difference	Depends on how the data is stored across tablespaces. If all data is stored in one tablespace then it won't make any difference	Depends on how the data is stored across tablespaces. If all data is stored in one tablespace then it won't make any difference	Fast
Easy To Recover	Easiest	Easy	Difficult	Very Difficult	Easy
Need To Revise Backup Strategies	No	Yes	Yes	Yes	No
Resources Utilization	High	High to Medium	High to Medium	High to Medium	Low
DB is usable after first restored	Yes	No	No	No	No
Functions Used	backup + restore + rollforward	backup + restore + rollforward	backup + restore + rollforward	backup + restore + rollforward	backup + restore + rollforward + rsync

Full Database Backup + Transaction Logs¶

Below diagram describes how this approach works:

Sample steps:

1. Like any other approaches, you must perform a full database backup (either offline or online)
   • e.g.:
     

     $ db2 backup db prod_db online

2. Transfer the full backup image to the standby site and perform a database restoration:
   • e.g.:
     

     $ db2 restore db prod_db replace history file

3. Use the rsync command to do an one way incremental sync from remote (production) site to standby site.
   • e.g.:
     

     $ rsync -var --delete db2inst1@db-prod:/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/ /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR

4. Execute the rollfoward on the standby database: (do not include the complete option)
   • e.g.:
     

     $ db2 rollforward db prod_db to end of logs

5. Repeat the Step 3 & 4 to keep the standby database sync with the production database
6. If want to bring up the standby database, just execute this command (change the database alias):
   

   $ db2 rollforward db prod_db complete

The biggest advantage of this approach is there's little or no need to revise current backup strategies, however, the mirror database cannot be used until the rollforward process is completed (also happens on the other db incremental backup approaches).

Test Tools¶

Install the following VirtualBox appliances to try out the backup approaches mentioned above:

• db-prod - Acted as production database server
• db-prod-mirror - Acted as standby database server

Appliance Details:

• OS - CentOS 5.7 32-bit
• DB2 version - IBM DB2 Express-C for Linux 32-bit
• root user - root/abcdef
• DB2 user - db2inst1/abcdef
• DB2 instance - db2inst1
• IP Address:
  • vb-db-prod - 192.168.56.120
  • vb-db-prod-mirror - 192.168.56.121

Before continue, ensure that your computer have:

• A decent CPU processor, say, 2GHz Dual Core processor
• 20 GB of free disk space
• Those appliances will use up 1GB of RAM so your PC must have at least 3GB RAM installed.

How To Install:

1. Install the VirtualBox
2. Download and un-compress the following appliances:
   • vb-db-prod.zip
   • vb-db-prod-mirror.zip
3. Launch the VirtualBox and then go to File - Preferences - Network
4. Press the Add host-only network button to add VirtualBox Host-Only Ethernet Adapter
   1. Select the newly created adapter and press the Edit host-only network button, and then:
   2. Adjust the settings:
      • 
   • If encountered any error, refer to this article to remove unused network filters.
5. Navigate to the folder where the appliances being extracted, .../vb-db-prod, .../vb-db-prod-mirror, and then double-click the xxxxx.vbox, e.g vb-db-prod.vbox to boot up the appliances.
6. SSH to the appliance via this IP:
   • vb-db-prod - 192.168.56.120
   • vb-db-prod-mirror - 192.168.56.121
7. Done.

Do make use of the VirtualBox snapshots feature to revert the appliance state, testing, and etc...