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. |