Project

General

Profile

Database Mirroring (DB2) on remote site » History » Version 14

chin-yeh, 03/22/2012 11:26 AM

1 1 chin-yeh
{{toc}}
2
3 12 chin-yeh
h1. Database Mirroring (DB2) on remote site
4 2 chin-yeh
5
h2. Introduction
6
7 3 chin-yeh
To mirror production database(s) to remote site, there are a number of approaches:
8
# Use <code>(full)</code> database backup
9 10 chin-yeh
# Use <code>(full + cumulative)</code> database backup
10 3 chin-yeh
# Use <code>(full + delta)</code> database backup
11 10 chin-yeh
# Use <code>(full + cumulative+ delta)</code> database backup - _not recommended because it is too complex_
12 3 chin-yeh
# Use <code>(full database backup + transaction logs)</code>
13
14 1 chin-yeh
> the approach 1-4 are described in "Using DB2 incremental backup":http://www.ibm.com/developerworks/data/library/techarticle/dm-0910db2incrementalbackup/
15
16 10 chin-yeh
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:
17
||_.full|_.full + cumulative|_.full + delta|_.full + cumulative + delta|_.full + transaction logs|
18
|*Backup Image Size*|Biggest|Small|Smallest|In between Small and Smallest|Smaller|
19
|*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|
20
|*Easy To Recover*|Easiest|Easy|Difficult|Very Difficult|Easy|
21
|*Need To Revise Backup Strategies*|No|Yes|Yes|Yes|No|
22
|*Resources Utilization*|High|High to Medium|High to Medium|High to Medium|Low|
23 11 chin-yeh
|*DB is usable after first restored*|Yes|No|No|No|No|
24
|*Functions Used*|backup + restore + rollforward|backup + restore + rollforward|backup + restore + rollforward|backup + restore + rollforward|backup + restore + rollforward + rsync|
25 3 chin-yeh
26 4 chin-yeh
27
h2. Full Database Backup + Transaction Logs
28 5 chin-yeh
29
Below diagram describes how this approach works:
30
31
!full_transaction_logs_approach.png!
32 6 chin-yeh
33
_Sample steps:_
34
# Like any other approaches, you must perform a full database backup _(either offline or online)_
35 1 chin-yeh
** e.g.:
36
<pre>$ db2 backup db prod_db online</pre>
37 11 chin-yeh
# Transfer the full backup image to the standby site and perform a database restoration:
38
** e.g.:
39
<pre>$ db2 restore db prod_db replace history file</pre>
40 6 chin-yeh
# Use the <code>rsync</code> command to do an one way incremental sync from remote (production) site to standby site.
41
** e.g.:
42
<pre>$ rsync -var --delete db2inst1@db-prod:/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/ /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR</pre>
43
# Execute the <code>rollfoward</code> on the standby database: (*do not* include the *complete* option)
44 1 chin-yeh
** e.g.:
45 6 chin-yeh
<pre>$ db2 rollforward db prod_db to end of logs</pre>
46 13 chin-yeh
# Repeat the <code>Step 3 & 4</code> to keep the standby database sync with the production database
47 9 chin-yeh
# If want to bring up the standby database, just execute this command (_change the database alias_):
48 6 chin-yeh
<pre>$ db2 rollforward db prod_db complete</pre>
49 13 chin-yeh
** _Once completed the rollforward, _
50 6 chin-yeh
51 10 chin-yeh
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 <code>rollforward</code> process is terminated (_also happens on the other db incremental backup approaches_).
52 6 chin-yeh
53 12 chin-yeh
h2. Test Tools
54 1 chin-yeh
55 13 chin-yeh
Install the following <code>VirtualBox</code> appliances to try out the backup approaches mentioned above:
56
* "db-prod":http://192.168.2.13/download/vb-db-prod.zip - Acted as production database server
57
* "db-prod-mirror":http://192.168.2.13/download/vb-db-prod-mirror.zip - Acted as standby database server
58
59
Appliance Details:
60
* *OS* - CentOS 5.7 32-bit
61
* *DB2 version* - IBM DB2 Express-C for Linux 32-bit
62
* *root user* - root/abcdef
63
* *DB2 user* - db2inst1/abcdef
64
* *DB2 instance* - db2inst1
65
66
Before continue, ensure that your computer have:
67
* A decent CPU processor, say, 2GHz Dual Core processor
68
* 20 GB of free disk space
69
* Those appliances will use up 1GB of RAM so your PC must have at least 3GB RAM installed.
70
71
_How To Install:_
72
# Install the "VirtualBox":http://192.168.2.13/download/VirtualBox-4.1.10-76836-Win.exe
73
# Download and un-compress the following appliances:
74
** "vb-db-prod.zip":http://192.168.2.13/download/vb-db-prod.zip
75
** "vb-db-prod-mirror.zip":http://192.168.2.13/download/vb-db-prod-mirror.zip
76
# Launch the <code>VirtualBox</code> and then go to _File - Preferences - Network_
77
# Press the _Add host-only network_ button to add <code>VirtualBox Host-Only Ethernet Adapter</code>
78 14 chin-yeh
## Select the newly created adapter and press the _Edit host-only network_ button, and then:
79
## Adjust the settings:
80
*** !Host-only_Network_Details.png!
81
*** !Host-only_Network_Details2.png!
82
** _If encountered any error, refer to "this article":http://answers.microsoft.com/en-us/windows/forum/windows_7-performance/how-can-i-find-out-how-many-network-filter-drivers/78a969d0-1e37-402a-b94d-877334cc07a7?tab=MoreHelp&auth=1 to remove unused network filters_.
83
# Navigate to the folder where the appliances being extracted, <code>.../vb-db-prod</code>, <code>.../vb-db-prod-mirror</code>, and then double-click the <code>xxxxx.vbox</code>, e.g _vb-db-prod.vbox_ to boot up the appliances.
84
# Done.