Actions
Database Mirroring (DB2) on remote site » History » Revision 16
« Previous |
Revision 16/23
(diff)
| Next »
chin-yeh, 03/22/2012 11:41 AM
- Table of contents
- Database Mirroring (DB2) on remote site
Database Mirroring (DB2) on remote site¶
Introduction¶
To mirror production database(s) to remote site, there are a number of approaches:- Use
(full)
database backup - Use
(full + cumulative)
database backup - Use
(full + delta)
database backup - Use
(full + cumulative+ delta)
database backup - not recommended because it is too complex - Use
(full database backup + transaction logs)
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:the approach 1-4 are described in Using DB2 incremental backup
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:- Like any other approaches, you must perform a full database backup (either offline or online)
- e.g.:
$ db2 backup db prod_db online
- e.g.:
- Transfer the full backup image to the standby site and perform a database restoration:
- e.g.:
$ db2 restore db prod_db replace history file
- e.g.:
- 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
- e.g.:
- Execute the
rollfoward
on the standby database: (do not include the complete option)- e.g.:
$ db2 rollforward db prod_db to end of logs
- e.g.:
- Repeat the
Step 3 & 4
to keep the standby database sync with the production database - 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 terminated (also happens on the other db incremental backup approaches).
Test Tools¶
Install the followingVirtualBox
appliances to try out the backup approaches mentioned above:
- db-prod - Acted as production database server
- db-prod-mirror - Acted as standby database server
- 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
- 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.
- Install the VirtualBox
- Download and un-compress the following appliances:
- Launch the
VirtualBox
and then go to File - Preferences - Network - Press the Add host-only network button to add
VirtualBox Host-Only Ethernet Adapter
- Select the newly created adapter and press the Edit host-only network button, and then:
- Adjust the settings:
- If encountered any error, refer to this article to remove unused network filters.
.../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.SSH
to the appliance via this IP:
- vb-db-prod -
192.168.56.120
- vb-db-prod-mirror -
192.168.56.121
Updated by chin-yeh almost 13 years ago · 16 revisions