Project

General

Profile

Actions

Database Mirroring (DB2) on remote site » History » Revision 10

« Previous | Revision 10/23 (diff) | Next »
chin-yeh, 03/22/2012 09:59 AM


Database Mirroring 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 restored Yes No No No No
Functions Used backup + restore 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. 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
  3. Execute the rollfoward on the standby database: (do not include the complete option)
    • e.g.:
      $ db2 rollforward db prod_db to end of logs
  4. 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

Updated by chin-yeh almost 13 years ago · 10 revisions